PostgreSQL Upgrades are hard! | ads' corner

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.
Lætitia 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 Lætitia, Nikolay, Bruce and Jimmy for helping with this blog post, and organizing and participating in the panel discussion.