https://andreas.scherbaum.la/blog/archives/1116-PostgreSQL-Upgrades-are-hard!.html#_content Skip to content ads' corner a place to store my stuff Quicksearch [ ] [Go!] Navigation PostgreSQL Upgrades are hard! Posted by Andreas 'ads' Scherbaum on Wednesday, June 22. 2022 Together with Laetitia Avrot and Nikolay Samokhvalov I was invited to participate in a Community Panel (YouTube video) about PostgreSQL Upgradability at Postgres Vision 2022. The panel was moderated by Bruce Momjian and initiated and organized by Jimmy Angelakos. Bruce did talk with each of us before, which helped a lot to guide the discussion in the right direction. The recording of the panel discussion is available on the Postgres Vision website. During this panel each of us provided examples for how easy or complicated PostgreSQL upgrades still are. Minor version upgrades One result of our discussion is that minor upgrades (as example v14.0 to v14.1) are relatively easy to do, but might hold some surprises for anyone who does not pay attention to the details (e.g., the release notes). A good example is the recent release of PostgreSQL version 14.4. It is a minor upgrade, so no new features. But it requires extensive work on B-tree indexes. For the DBA it is not easy to figure out how much work this is, or how long the maintenance window needs to be. Laetitia points out that people like the PostgreSQL approach of not adding new features in minor upgrades. This makes it easy to install the new version without re-testing applicatios. Major version upgrades Major upgrades (as example v13 to v14) are a whole different category. Everyone agrees that those upgrades are complicated and need a lot of preparation and testing, both on the database side as well as on the application side. PostgreSQL has the pg_upgrade tool to run a major version upgrade on the same server, but even this tool is lacking features which are crucial for today's business world. As example, the --check option does a preliminary check of the database, but this does not cover all the details, doesn't give a time estimate, and does not catch all errors. During our upgrade tests at Adjust we discovered that some of our extensions we are using can't be migrated automatically, but this was not checked by pg_upgrade. For our databases in the 20-30 TB range, or with tens of thousands of tables (and therefore a very large catalog), it would be nice to at least get an estimate for the time required to run pg_upgrade. Support policy Currently the PostgreSQL Project supports 5 major versions. Compared to commercial products, that is a large number of supported versions. This becomes a problem for some users: by the time the product runs out of support, the amount of changes is so big that a major upgrade is no longer feasible, or easy to do. One discussed approach are LTS releases, and not supporting every major release for 5 years, but this will not make the actual problem go away. Cluster awareness In today's business world, many PostgreSQL installations run in a replicated fashion. Either by using physical or logical replication. However PostgreSQL by itself is not aware of the cluster, and does not support this out of the box. The primary database can show the replication status to the next level replicas, but there is no way to gather all the information about each system in a more complicated cluster setup. Failing over to a replica is a manual step, or handled by tools like Patroni. Making PostgreSQL look like one system to the application, while still providing HA, requires additional tools like PgBouncer or Pgpool-II in front of the cluster. Managing these tools is extra work, and workarounds are necessary to find out which database is currently the leading primary. Downtime required for an upgrade Nikolay pointed out that zero-downtime upgrades are not possible. The time required for the upgrade can be minimized, but it can't be zero. One of the main reasons is that the server must be restarted for the upgrade, which interrupts ongoing connections. A restart requires a checkpoint, which in turn writes out all dirty buffers to disk. Prepping the database with advanced checkpoints is possible, but write-heavy databases will still need some time to write out the remaining changes, and then restart the server. During that time no new connections are accepted, and applications trying to connect will receive an error. Even a connection pooler can only hold incoming connections, but it's not possible to keep connections and queries running while the server is upgraded. Upgrade strategies Over time, the PostgreSQL world developed several different upgrade strategies, just to name a few: * Running pg_upgrade in -link mode: this is relatively fast (minutes, usually), but once the new version is started a rollback is not possible (short of using additional tools like filesystem snapshots). * Running pg_upgrade in copy mode (the default): This requires twice the disk space, because the upgrade process creates a copy of each file. It is also slow, and the runtime depends on the size of the database and the performance of the hardware. Can be hours. * Logical replication: This approach creates replication slots on the primary, a new replica with the new version is set up using a pg_dump backup, and then the logical changes stream into the replica until it catches up with the primary. Then the application is switched to the new replica, running the new version. This approach might overload the primary, because it needs to store WAL for the time from when the backup starts to the time the replica caught up with all the changes. Write-heavy databases can create several TB or WAL during that time. * Slony-I: Slony uses triggers to gather changes in tables, and send them to the replicas. This is a resource consuming approach, and can't deal with DDL changes. Development of Slony-I stopped a while ago. * pg_dump and pg_restore: Use the pg_dump tool of the new version to take a snapshot of the database, and install it in the new version. This approach works across multiple servers, but requires twice the disk space, and a lot of time. During the restore every index needs to be rebuilt, and afterwards the statistics need to be updated. Data changes are not possible during the upgrade, however the applications can access the old database in read-only mode. Possible improvements During the panel discussion we discussed a number of possible changes and improvements: * Use pg_upgrade for all upgrades, and have an option which shows necessary changes and steps between upgrades, also between minor upgrades * Better verification of the upgrade process, catch more possible problems in advance * Calculate time and space estimates for the upgrade * Make PostgreSQL cluster-aware: show information about each node in all nodes * Make it possible to upgrade the entire cluster at once Acknowledgements Thanks to Laetitia, Nikolay, Bruce and Jimmy for helping with this blog post, and organizing and participating in the panel discussion. Categories: PostgreSQL News | 0 Comments Random Entry: Das Navi beim Abbiegen < Previous posting Defined tags for this entry: Backup, Conference, PostgreSQL, Replication, Update, Upgrade Related entries by tags: * PostgreSQL Project @ GSoC 2022 * My PostgreSQL database is empty! * Google Sheets: Format cell background based on a value in a cell in another sheet * It's beer o'clock for PostgreSQL * Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way Trackbacks Trackback specific URI for this entry No Trackbacks Comments Display comments as Linear | Threaded No comments Add Comment Name* [ ] Email* [ ] Homepage [ ] [ ] [ ] [ ] [ ] [ ] [ ] [ ] [ ] [ ] Comment* [ ] In reply to [[ Top level ]] Enclosing asterisks marks text as bold (*word*), underscore are made via _word_. E-Mail addresses will not be displayed and will only be used for E-Mail notifications. To leave a comment you must approve it via e-mail, which will be sent to your address after submission. Form options [ ]Remember Information? [Preview] [Submit Comment] Sidebar Blog Administration Open login screen Calendar Mon Tue Wed Thu Fri Sat Sun - Back June '22 Forward - 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Ad [INS::INS] Bookmarks * Bookmarks + Impressum + My company + ea + Pascal + Writings & Talks Upcoming Events Swiss PGDay 2022) Rapperswil, Switzerland 01.07.2022, 09:00 Uhr PGConf.Brasil 2022 Sao Jose dos Campos, Brazil 26.08.2022, 09:00 Uhr PGDay Israel 2022 (virtual) Israel / online 20.10.2022, 09:00 Uhr pgconf.eu 2022 Berlin, Germany 25.10.2022, 09:00 Uhr hCalendar Categories * [ ]XML / * [ ]XML Databases * [ ]XML Greenplum * [ ]XML InfluxDB * [ ]XML other DBs * [ ]XML PostgreSQL * [ ]XML Code Snippets * [ ]XML PostgreSQL News * [ ]XML PWN * [ ]XML EMC * [ ]XML Events * [ ]XML Externes * [ ]XML Geocaching * [ ]XML gequalte Sprache * [ ]XML Hardware * [ ]XML Raspberry Pi * [ ]XML Kino * [ ]XML Languages * [ ]XML C * [ ]XML make * [ ]XML Perl * [ ]XML PHP * [ ]XML PL/pgSQL * [ ]XML Python * [ ]XML Shell * [ ]XML SQL * [ ]XML TeX * [ ]XML Links * [ ]XML myself * [ ]XML Ausgehen * [ ]XML Cycling * [ ]XML Familie * [ ]XML Fotografie * [ ]XML Hochzeit * [ ]XML Running * [ ]XML Scuba Diving * [ ]XML Unterwegs * [ ]XML AIDA * [ ]XML Die Bahn * [ ]XML Online * [ ]XML Pivotal * [ ]XML Politisches * [ ]XML Steuern * [ ]XML Regionales * [ ]XML Berlin * [ ]XML Glienicke/Nordbahn * [ ]XML Hamburg * [ ]XML Magdeburg * [ ]XML Sachsen-Anhalt * [ ]XML Schonebeck/Elbe * [ ]XML Wien * [ ]XML Social networks * [ ]XML Software * [ ]XML Android * [ ]XML Ansible * [ ]XML Apple * [ ]XML iPod * [ ]XML iTunes * [ ]XML Django * [ ]XML git * [ ]XML Huginn * [ ]XML Hugo * [ ]XML KDE * [ ]XML Let's Encrypt * [ ]XML openHAB * [ ]XML OpenStreetMap * [ ]XML Operating Systems * [ ]XML Linux * [ ]XML restic * [ ]XML S9y * [ ]XML Typo3 * [ ]XML Stupid * [ ]XML Vereinsleben * [ ]XML Freifunk Oberhavel * [ ]XML PGUG Europe * [ ]XML PGUG Germany * [ ]XML Rundendreher e.V. * [ ]XML UniHelp [Go!] Statistics Last entry: 2022-06-22 14:38 1096 entries written 594 comments have been made 0 visitor(s) online Recent Entries PostgreSQL Upgrades are hard! Wednesday, June 22 2022 PostgreSQL Project @ GSoC 2022 Tuesday, June 21 2022 Ansible: Lookup group name by gid Sunday, June 12 2022 Archives * June 2022 * May 2022 * Recent... * Older... Syndicate This Blog * XML PostgreSQL Buch PostgreSQL fur Anwender, Administratoren und Entwickler [postgresql] Webseite zum Buch Powered by Serendipity PHP Weblog Powered by Serendipity & the 2k11 theme.