I'm trying to upgrade all postgres servers at my work place. I've began
with oldest versions moving them to newer ones, basically from 8.4/9.2
to 9.6. When all are in 9.6, I'll go with v10 and after that... we'll see.
I would go to 11.6 straight away (11.6 will be available later this week).
By making intermediate stops you'll make total downtime bigger.
We are going to free some space in main_db moving old data to another
server (~90GB) and changing the app that uses it but this db grows quite
fast and we'll be probably in the same size in a few months.
I know that using --link would make pg_upgrade much faster but by now
we're not using it because we want to have the old version available
just in case newer one gives us any problem.
Before upgrading master DB, we always make sure that we have physical replica, that is up to date.
Then we're free to use pg_upgrade in link mode. In any case, having replica around for the major maintenance activities
is a good thing to do in any case.
For the upgrade, typical procedure is:
- create a new cluster on the upgrade-to version (11)
- open configuration files of new and old cluster side by side and transfer settings.
Do not overwrite new configuration file, as it typically contains quite some new options.
By overwriting the config, you will not be able to “see” them.
- transfer pg_hba (and other) settings
- transfer any custom extensions/FTS dictionaries/etc.
- make sure pg_wal points to the right location, if you're using symlinks
- do a schema-only dump of the old cluster and try to load it into the new cluster.
If fails, correct errors, re-initdb new cluster and try again, till schema loads fine.
- run pg_upgrade in the `--check` mode to make sure no surprises will pop up during the upgrade
(typical thing to remember — tablescpaces locations)
- perform the upgrade
We've been using this procedure without issues for years.