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 ?
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?
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.
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
/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
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 ?
by this, it seems postgresql doing good on upgrading and migration!