Upgrade PostgreSQL 9.X to 12.X in Windows

Question

Upgrade PostgreSQL 9.X to 12.X in Windows

Jignesh Raiyani

SSC Veteran

Points: 262
More actions
February 9, 2021 at 12:00 am

#3841281

Comments posted to this topic are about the item Upgrade PostgreSQL 9.X to 12.X in Windows

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

not wishing to minimize the article itself which I consider well written I wonder why would posting this on a SQL Server forum be a good idea.

james.of.rivendell SSC-Addicted Points: 425 More actions · Answer 2

I am not too sure about changing ports. Imagine a large scale company that relies on Postgres and changing ports in a myriad of applications is not something that gets done in a snap of a finger. You might even need to configure the firewall to allow the new port. All of these tasks might involve different teams. Personally I would install the newer version on a separate host, migrate the databases, point whatever DNS aliases to the new host. That way it won't ever involve the applications team (other than post-migration connection testing). You also get to keep the old host alive for a grace period before shutting it down.

This reply was modified 3 years, 10 months ago by james.of.rivendell.

Jignesh Raiyani SSC Veteran Points: 262 More actions · Answer 3

Absolutely right; if port switching is not an easy step, you can run new Postgres on the same port and stop the older Postgres service. If you configure new Postgres on another server, it will be a tech-refresh kind of activity that requires a new H/W and N/W setup.

This approach will be effectively helpful when users want to use Postgres's new features with the same setup. i.e., CDC and many more.

vbgamer45 Newbie Points: 1 More actions · Answer 4

Jignesh you have the best guide so far for a windows based upgrade.

I am going from 9.5 to 9.6. Trying to upgrade my jira/bitbucket system.

Getting stuck on the upgrade process. " password authentication failed for user" Have the same password for Postgres for both systems. Changed the port number for 9.6 to 5433. Tried editing both pg_hba.conf and giving full permissions

host all all 127.0.0.1/32 trust

host all all ::1/128 trust

C:\temp>SET PGPASSWORD = passwordhere

C:\temp>"C:\Program Files\PostgreSQL\9.6\bin\pg_upgrade" -d "C:\Program Files\Po

stgreSQL\9.5\data" -D "C:\Program Files\PostgreSQL\9.6\data" -b "C:\Program File

s\PostgreSQL\9.5\bin" -B "C:\Program Files\PostgreSQL\9.6\bin" -U Postgres

Performing Consistency Checks

-----------------------------

Checking cluster versions ok

connection to database failed: FATAL: password authentication failed for user "

Postgres"

Jignesh Raiyani SSC Veteran Points: 262 More actions · Answer 5

can you please check the Authentication with a lower case of the user name? and if possible then share the log.

thebaals92@gmail.com Newbie Points: 2 More actions · Answer 6

I m getting following error while upgrading PG 11.2 to PG 14.

Performing Consistency Checks

-----------------------------

Checking cluster versions ok

The source cluster was not shut down cleanly.

Failure, exiting

I used pg_ctl to start and stop both PG11.2 and PG14 those are using 5432 and 5433 ports respectively. My machine is Windows Server 2019 Base.

thebaals92@gmail.com Newbie Points: 2 More actions · Answer 7

Target PG 14 Data Directory size(258 MB) is less than PG 11.2(1.1 GB) data directory size after the upgrade.

I am trying to update PG 11.2 to PG 14.0. My PG 11 Data directory size is 1.1 GB and my PG 14 data directory is 41 MB before the upgrade.

I am now running my below upgrade command:

"C:\Program Files\PostgreSQL\14.0\bin\pg_upgrade.exe" -U postgres --old-bindir "C:\Program Files\PostgreSQL\11.2\bin" --new-bindir "C:\Program Files\PostgreSQL\14.0\bin" --old-datadir "C:\pgData112" --new-datadir "C:\pgData140" -p 5432 -P 5433 --retain

All the steps are stated as OK and the Upgrade Completed message is displayed.

Now, why the orginal PG 11.2 data size does not matched with PG 14 data size? is there any issue happened while upgrade? I dont see any error message also.

Kindly help me on this.

Jignesh Raiyani SSC Veteran Points: 262 More actions · Answer 8

Big size difference, Please verify the folder directory again. and if it simillar again then get the Database\table wise size, if total is around your 41 MB then ok to upgrade else test the same on another machine with copying the database.

dtack Grasshopper Points: 11 More actions · Answer 9

I am running into issues during this process at this point below. I am upgrading from 12 to 16. Inside the command prompt, I set the password as instructed. then I run the following "pg_upgrde" command. I get this error "connection to server at "localhost" (::1), port 50432 failed: fe_sendauth: no password supplied"

Screenshot 2023-10-31 at 8.51.59 AM

Screenshot 2023-10-31 at 10.05.18 AM

This reply was modified 1 year, 1 month ago by dtack.
This reply was modified 1 year, 1 month ago by dtack.

shaba Mr or Mrs. 500 Points: 529 More actions · Answer 10

I have followed the instructions and set the postgres folder with full control for the Users group under Security but get the following error when upgrading from 15 to 16..

Performing Consistency Checks

-----------------------------

Checking cluster versions ok

connection to server at "localhost" (::1), port 50432 failed: FATAL: password authentication failed for user "Postgres"

could not connect to source postmaster started with the command:

"c:/Program Files/PostgreSQL/15/bin/pg_ctl" -w -l "c:/Program Files/PostgreSQL/16/data/pg_upgrade_output.d/20240927T122045.691/log/pg_upgrade_server.log" -D "c:/Program Files/PostgreSQL/15/data" -o "-p 50432 -b " start

Failure, exiting

This reply was modified 2 months, 3 weeks ago by shaba.
This reply was modified 2 months, 3 weeks ago by shaba.

shaba Mr or Mrs. 500 Points: 529 More actions · Answer 11

Ahhh, this was resolved by changing the username to lowercase - thanks