0

I am trying to use pg_upgrade to upgrade postgres server from 9.3 to 9.6. I have both binaries on the windows server R8.

Here are the 2 pg_hba.conf :

> 9.3 :
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
#local    all             all             127.0.0.1/32            TRUST
host    all             all             127.0.0.1/32            TRUST
host    all             all             10.0.0.0/8              md5

9.6 :
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
#local    all             all            127.0.0.1/32           TRUST
host    all             all             127.0.0.1/32            TRUST
host    all             all             10.0.0.0/8              md5
  • I have all permissions on the directories.
  • From 9.6 bin I execute : pg_upgrade.exe path_of_oldbin path_of_newbin path_of_olddata path_of_newdata

All I got is :

connection to database failed : could not connect to server : connection refusedpg_upgrade fails

I tried unsuccessfully some different config (local) as you see in pg_hba.conf, but can't find the reason why it can't connect. Any idea ?

  • This error comes from before pg_hba.conf is even reached. Are you sure the DB accepting connections on that port? – dezso Apr 18 '18 at 14:18
  • How can I test that? – Leehan Apr 18 '18 at 14:39
  • Try to start it up using the command shown in the error and then try to connect. What is listen_addresses in postgresql.conf? – dezso Apr 18 '18 at 14:46
  • 1
    "TRUE" is not a legal authentication method, so the server would not even start up given those pg_hba.conf files. If you followed the hints and looked at the last few lines of the server startup log file, it probably would have indicated this. Are you looking for "trust" rather than "TRUE"? – jjanes Apr 18 '18 at 19:07
  • @deszo In postgres.conf : listen_adresses = '*', port = 5432 ; You are right jjanes, I don't know why I used TRUE rather than TRUST. Anw I changed in first post. But the error message is still the same. And in log : it says that TRUST is not a valid method. (I forget : both servers are off) – Leehan Apr 23 '18 at 7:04
0

I just went through this same issue myself, trying to upgrade from 9.4 to 9.6.

I am running PostgreSQL in Windows 10.

I received the same error message.

I was confused about running cmd.exe under user: postgres. My system doesn't have an account with that name.

I did two things.

  1. pg_hba.conf file were modified for both versions to add the "method" "trust". I did not add extra lines as you might have found in other references. However, comparing my entries with the original poster, I had the default localhost entries as per what they added. I would comment md5 lines temporarily:


    pg_hba.conf


    I duplicated and commented out the lines you see with : md5 under IPv4 and IPv6

  2. Running the pg_upgrade command might be affected if you run cmd.exe as admin or as a regular user. Extra consideration may be needed to ensure write privileges on affected folders (check folder security if you get the same error) when running pg_upgrade command.

    In my case, my setup has the PostgreSQL application files stored in C:\program files directory and data is stored in C:\APPS\PostgreSQL\version\data

    Referencing my setup, I ran the following command:


    pg_upgrade command


    -c option is to check clusters - I consider it a test. You would run without this option once your ready to migrate.

    -U postgres - this was added after I updated the pg_hba.conf file correctly and not seeing the same error but instead a new one:


    results from pg_upgrade command


    The -U option allows you to specify a different postgreSQL user account for the command to use. This is where I specified the user to be postgres. Maybe a correction is required with PostgreSQL documentation to perform RUNAS /USER:postgres CMD.EXE and instead mention to use -U option

This is the result I received after running modified command:


successfully running pg_upgrade using -U option


Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.