PostgreSQL major upgrades with pg_upgrade

Major version changes of PostgreSQL carry a bad reputation they partly deserve: they are the one event of the year that does not resolve with a simple package update. The fear is understandable, but the procedure is well within craft territory when treated as a runbook instead of an adventure. Since PostgreSQL 16 shipped in September 2023, a fresh major version stands ready again, and the path there follows the same pattern as with its predecessors.

Why major versions are special cases

Minor version changes, say 16.1 to 16.2, are unproblematic: the on-disk format stays the same, and the change is a short restart. Major versions change the internal format of the system catalogs and partly of the data files, so there is no path that only swaps a new binary in. Three routes exist: the dump route, exporting data logically and importing it into the new version; pg_upgrade, which converts the files directly into the new format; and replication-based procedures that build the new version alongside the old one. For most server landscapes, pg_upgrade offers the best ratio of runtime to effort.

The runbook shape

The real trick behind a calm upgrade is preparation, not the upgrade itself. Our pattern in steps:

• Preparation: a fresh backup taken and its restore tested, pg_upgrade with the --check option run against copies of the data, reporting every blocker without changing anything.
• Scheduling: the downtime covers the stop, the upgrade itself, and the start plus follow-up; it gets measured realistically on the copy system instead of guessed.
• The procedure: stop the service, run pg_upgrade with the correct paths to the old and the new version, wait, activate the new configuration, start the service.
• Follow-up: let the analysis runs complete so the query planner works with current statistics again, and let the applications run before the maintenance window ends.

The --check phase is the part that turns anxiety into planning. It reports the obstacles in advance: outdated data types, missing libraries, configuration options that no longer exist. Every one of those resolved before the window is one that does not consume time during it.

The rehearsal on the copy

What separates an upgrade procedure from a hope is the rehearsal on a copy. A restore of the current state into a test system, then the complete upgrade, then the applications running against the copy while we watch the behavior. That step costs hours and earns them back: it measures the real runtime, finds the queries that behave differently under the new planner, and strips the remaining schedule of surprises. The rehearsal belongs in the calendar, not in theory.

The rollback net underneath

An upgrade that fails midway is the exception state for which we want a plan that works without creativity. Two nets, sensibly combined: a snapshot of the data volume before the stop, created with the LVM snapshot mechanics described in an earlier post, returns the system to its pre-procedure state within minutes as long as the snapshot was sized cleanly. The fresh backup before the stop covers the case where even that does not hold. Together they cost little preparation and turn the exception state into a planned recovery.

Honesty belongs in the expectations too: the snapshot does not replace the database's consistency checks, and the restore from backup is the slower of the two paths. The order stands: the snapshot for the quick return, the backup for the worst case, the rehearsal as the step that makes both rarer.

After the change

The hours after the start deserve the same attention as the procedure: statistics complete their runs, query plans should stay under observation, and the typical suspects are queries that choose a different plan on the new version. An observation window of a few days belongs to the procedure; the version counts as adopted when the reports stay quiet.

What we additionally check before every upgrade

Alongside the runbook, a few checks have proven worth a short walk before every major change. First the extensions: databases with external extensions have the property that the new major version expects the library in a different version. Those dependencies get listed in advance and checked against the package sources of the new version, because they are the most common cause of surprises in the middle of the procedure.

Second, the configuration difference: the configuration file of the running version gets laid against the sample file of the new version, and every line carrying a dropped or reinterpreted keyword gets moved beforehand. That is fine-grained craft work, but it is exactly what prevents a start that fails on an unknown option.

Third, the application view: the queries that carry the critical paths in daily operation run against the test copy with realistic data volumes. A planner change that stays invisible on ten lines shows itself very clearly on a million, and that is exactly why the test runs at the real size instead of a convenient small copy.

One sentence about timing belongs to the planning: the major change happens when the application is prepared for it, not when the support end of the old version nears. The difference sounds theoretical and shows in practice through a simple observation: whoever makes the change under the pressure of a security deadline runs the procedure under appointment stress, and exactly that stress produces the errors the runbook was built against. A calendar that plans the change six months before support end costs nothing and provides the room in which the rehearsal and the exercises find their place.

If you are planning the major upgrade for your databases and want a runbook instead of a hope, the DevOps and backup service page describes the matching service, and the contact page is the fastest way to reach us.

Sources

• PostgreSQL documentation, Release Notes 16 (Release date: 2023-09-14), https://www.postgresql.org/docs/16/release-16.html (accessed 2026-11-03 via the archived copy https://web.archive.org/web/20231004225012/https://www.postgresql.org/docs/16/release-16.html, because the direct request from the work environment returns an empty body)
• PostgreSQL documentation, pg_upgrade, https://www.postgresql.org/docs/16/pgupgrade.html (accessed 2026-11-03)
• PostgreSQL versioning policy, https://www.postgresql.org/support/versioning/ (accessed 2026-11-03)