Upgrade PostgreSQL 9.X to 12.X in Windows
-
Comments posted to this topic are about the item Upgrade PostgreSQL 9.X to 12.X in Windows
-
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.
-
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.
-
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.
-
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"
-
can you please check the Authentication with a lower case of the user name? and if possible then share the log.
-
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.
-
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.
-
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.
-
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"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply