Re: Strategy for upgrade highly used server

пн, 11 нояб. 2019 г. в 11:13, Ekaterina Amez <ekaterina(dot)amez(at)zunibal(dot)com>:

> 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.

--
Victor Yegorov