Обсуждение: Strategy for upgrade highly used server
Hi all, 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. One of the servers to be upgraded has special needs and I'd like your advice about how to upgrade it with the least downtime possible (and less chance to make a mistake I would add). This server has problems with free disk space (don't know details but it seems quite difficult to add more disks). And the database needs to be up almost 24x7. We can stop it if we really need it, of course, but if the stop is long then we'll have undesirable side effects. The server has v9.2 installed: postgres=# select version(); version --------------------------------------------------------------------------------------------------------------- PostgreSQL 9.2.18 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit The cluster has these databases: postgres=# select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) as size, pg_database_size(pg_database.datname) FROM pg_database order by 3 desc; datname | size | pg_database_size ------------------------------+---------+------------------ main_db | 332 GB | 356418016376 db1 | 8078 MB | 8470254712 db2 | 3279 MB | 3438187640 db3 | 2658 MB | 2786694264 db4 (maybe this can be deleted) | 321 MB | 336548984 db5 | 175 MB | 183596152 db6 | 10 MB | 10974328 db7 | 6493 kB | 6648952 postgres | 6493 kB | 6648952 template1 | 6493 kB | 6648952 template0 | 6377 kB | 6529540 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. I've read about replicas to make upgrade with almost no downtime, but they look like a bit complex to get them running (and I'm not sure if we have a server to be used as slave). I'm familiar with them in Sql Server and Oracle, but I've been working with Postgres for a few months only and I'm still "learning". Two options I've mentioned are the only options that we have to pg_upgrade fast or is there any other option? Regards, Ekaterina
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.
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.
Then we're free to use pg_upgrade in link mode. In any case, having replica around for the major maintenance activities
- 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.
- 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
Hi Monica,
Please, remember to answer to the list or your answer will be only available to me.
You say ...1 - we want to have the old version available just in case newer one gives us any problem.And also2 - This server has problems with free disk spaceAnd3 - And the database needs to be up almost 24x7 (please describe ALMOST in hours/min/sec andindicate if it applies to all dbs)1 and 2 are not compatible for your task. If you can't solve 2 you've only have pg_upgrade, so, youwill lose old version.
Using pg_upgrade it's possible to have old and new postgres versions running. You only need minor reconfiguration. Of course, this is only possible if the --link option is not used, which is the option that needs less disk space.
I you can solve 2 as you need old version you must have double the space of PG_DATA.You have to decide this before building a plan.Talking about 3, all dba's here this, negotiate.Your timeline depends on main_db (332 GB). You have to test pg_upgrade time for this databasein a similar server, and also test pd_dump/pg_restore.
Testing before any change in production environment is a must, always.
IF databases are not related ... and only in this case you have another option that is install new versionand migrate or upgrade bid database first and then add databases db1 to db7 (pg_dump/pg_restore)one by one to new version (with this you minimize the amount of disk space you need for pg_upgradeand downtime).
I've used this strategy before, when upgrading other servers with databases without the almost-no-downtime needs.
Wrong: this is only true if --link option is used. All the servers I've upgraded by now have both versions available, though the old one is down most of the time.Choices.A) all with pg_upgrade- less space needed- NO old version
B) pg_dump/pg_restore- you need double disk space- you will have to versions- more downtime needed (you need to test it no know how much)
C) A combination of both - ONLY for desperates[ dump databases db1 to db7, delete databases db1 to db7 from old version, pg_upgrade main_db, pg_restore db1 to db 7 ]- less space needed- NO old version- less downtime for main_dbYou have to TEST all plans before.Good luck!
Thank you for the schematic summary of this options (I' already knew). You've commented anything about replication option though.
--
пн, 11 нояб. 2019 г. в 11:13, Ekaterina Amez <ekaterina.amez@zunibal.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.
I intended to have all database servers at the same version level, that's why I have chosen 9.6 (when I began with this task we had v7.4 in one of the servers, and now lower version in our database servers is 9.2).
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 activitiesis 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 upgradeWe've been using this procedure without issues for years.
When you describe the upgrade procedure, do you mean upgrading the master or the slave? Do you promote server as master while upgrading original master? Let people access only to the replica in read-only mode while upgrading the master?
--Victor Yegorov