pg_upgrade issue from postgres 9.3 to 9.6

Question

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 refused

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 ?

Answer 1

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:

   ---

   

   ---

   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:

   ---

   

   ---

   -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:

   ---

   

   ---

   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:

---

---

Hope this helps.