pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Strategy for upgrade highly used server 4+ messages / 2 participants [nested] [flat]
* Strategy for upgrade highly used server @ 2019-11-11 09:12 Ekaterina Amez <ekaterina.amez@zunibal.com> 0 siblings, 2 replies; 4+ messages in thread From: Ekaterina Amez @ 2019-11-11 09:12 UTC (permalink / raw) To: pgsql-admin 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strategy for upgrade highly used server @ 2019-11-11 14:24 Victor Yegorov <vyegorov@gmail.com> parent: Ekaterina Amez <ekaterina.amez@zunibal.com> 1 sibling, 1 reply; 4+ messages in thread From: Victor Yegorov @ 2019-11-11 14:24 UTC (permalink / raw) To: Ekaterina Amez <ekaterina.amez@zunibal.com>; +Cc: pgsql-admin пн, 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. 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strategy for upgrade highly used server @ 2019-11-15 09:13 Ekaterina Amez <ekaterina.amez@zunibal.com> parent: Ekaterina Amez <ekaterina.amez@zunibal.com> 1 sibling, 0 replies; 4+ messages in thread From: Ekaterina Amez @ 2019-11-15 09:13 UTC (permalink / raw) To: Mónica Gamarra <monica.gamarra@gmail.com>; pgsql-admin@lists.postgresql.org Hi Monica, Please, remember to answer to the list or your answer will be only available to me. El 11/11/19 a las 14:37, Mónica Gamarra escribió: > You say ... > 1 - we want to have the old version available just in case newer one > gives us any problem. > And also > 2 - This server has problems with free disk space > And > 3 - And the database needs to be up almost 24x7 (please describe > ALMOST in hours/min/sec and > indicate 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, you > will 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 database > in 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 version > and 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_upgrade > and downtime). I've used this strategy before, when upgrading other servers with databases without the almost-no-downtime needs. > > Choices. > A) all with pg_upgrade > - less space needed > - NO old version 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. > > 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_db > > You 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. > > -- > /............................................ > Mónica Gamarra Barrios > monica.gamarra@gmail.com <mailto:monica.gamarra@gmail.com>/ ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strategy for upgrade highly used server @ 2019-11-15 09:25 Ekaterina Amez <ekaterina.amez@zunibal.com> parent: Victor Yegorov <vyegorov@gmail.com> 0 siblings, 0 replies; 4+ messages in thread From: Ekaterina Amez @ 2019-11-15 09:25 UTC (permalink / raw) To: Victor Yegorov <vyegorov@gmail.com>; +Cc: pgsql-admin El 11/11/19 a las 15:24, Victor Yegorov escribió: > пн, 11 нояб. 2019 г. в 11:13, Ekaterina Amez > <ekaterina.amez@zunibal.com <mailto: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 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. 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-11-15 09:25 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-11-11 09:12 Strategy for upgrade highly used server Ekaterina Amez <ekaterina.amez@zunibal.com> 2019-11-11 14:24 ` Victor Yegorov <vyegorov@gmail.com> 2019-11-15 09:25 ` Ekaterina Amez <ekaterina.amez@zunibal.com> 2019-11-15 09:13 ` Ekaterina Amez <ekaterina.amez@zunibal.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox