Avatar of marrowyung
marrowyung
 asked on

Upgrade PostgreSQL 9.3.x to 11 in other box

hi,


we have PostgreSQL 9.3.x by an application and we'd like to upgrade the PostgreSQL to 11, I am not sure if these link are correct about the upgrade: 


  PostgreSQL 9.3.x DIRECT upgrade to PostgreSQL 11 is possible ?

1) https://www.hutsky.cz/blog/2019/02/upgrade-postgresql-from-9-3-to-11/

2) https://www.percona.com/blog/2019/04/12/fast-upgrade-of-legacy-postgresql-with-minimum-downtime-using-pg_upgrade/


  inplace upgrade on the SAME box is ok ?


  How about PostgreSQL 9.3.x DIRECT upgrade to PostgreSQL 13/14?


  As according to the URLs above, it seems we just install new postgreSQL 11/13/14 on the New box, then run pg_ugprade from new instance and connect to old instance, pg_upgrade will do everything for us ,e.g. copy data, event it is 406GB to the new instance with all login , role and password?


PostgreSQLInstallation

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
David Favor

Many times this will work. Other times this will fail. Same with MariaDB + MySQL.

1) Always make a backup.

2) Try your upgrade + if it works, you're done.

Be sure to study your PostgreSQL /var/log files, as some errors show up there, then escalate later.

3) If any errors occur, my approach is to... delete all SQL related packages + destroy all raw data files, then install a fresh version of SQL packages + do a restore of my database backups.

4) Since you have considerable data... and a long upgrade path 9 -> 14, if possible, upgrade only 1x version at a time.

Might be PostgreSQL already does this for you.

I only work with MariaDB these days + I know for some version jumps, MariaDB + MySQL both might do many version upgrades, as some version jumps require this.

And sometimes, even large version jumps only require upgrading tables once.

Tip: After you've finished this process, likely best to go through this upgrade process each time a major upgrade releases, as this will make it more likely each upgrade will succeed.
SOLUTION
lcohan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
marrowyung

ASKER
lcohan,

pg_upgrade does the work for sure just make sure you run the pg_upgrade from the version you will upgrade to I.E. in your case from version 11.  
sorry we just have some updated information that our platform can use PostgreSQL 13.1 and 13.3, any good thing on 13.3 than 11 ?

direct upgrade from 9.3.x to 13 also possible ?

we run pg_upgrade LOCALLY on top of the 9.3.x PostgreSQL box and the process will take care everything?

"You need to test ALL apps/systems/services running against Postgres just so you don't face any errors like the one I encountered and that was caused by Postgres own metadata changes:"

for sure we will.

we just know that our application support postgreSQL 13.1/13.3, I am not sure anything like what you experience exists in our case.
you can re-run this step 5 until you get the message that postgres upgrade is all clean

Step 5:

/usr/pgsql-11/bin/pg_upgrade --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-11/bin/ --old-datadir=/var/lib/pgsql/9.3/data/ --new-datadir=/var/lib/pgsql/11/data/ --check

Open in new window


This assumes that both PostgreSQL install on the same box ?
so we run pg_upgrade from the newer version and it needs to check bindir files and new and old data directory?

it seems the check is AFTER postgreSQL 11/13.3 new installation installed?

how about on windows? the same way ?


Also in order to have a solid rollback please make sure to take a snapshot storage of the volume where postgres data is PRIOR to running pg_upgrade.

we will do a full backup first.

Alternatively if you can afford the downtime the other option to upgrade should be to use pg_dump all on your current (unsupported) 9.3 and restore into the desired upgrade version 11.

The pg_dump will gives down time ?

This should be logical backup and should only slow DB down?

What is other option for no down time ?

what is unsupported means for 9.3 ?




ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
tks.

will try!

by this, it seems postgresql doing good on upgrading and migration!
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
hi,

one thing, people may ask why upgrade postgresqL, do you have any URL shows the change/enhancement from PostgreSQL 9.3.6 to 13.3 ?

and I found this https://www.postgresql.org/about/featurematrix/
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

ASKER
hi,

tks.

I think this is a good resource and we found the same thing ! PostgreSQL do a good job to compare it

https://www.postgresql.org/about/featurematrix/


marrowyung

ASKER
tks all and I might come back on this when I am doing the upgrade (might be before christmas)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.