Each PostgreSQL major version is supported for five years. After that it receives no fixes, including security fixes. An upgrade is therefore a scheduled piece of work, and the question is how much downtime and how much risk the chosen method carries.

Three ways to upgrade

  • pg_upgrade. Converts the existing data directory to the new version in place. With link mode the downtime is usually minutes, whatever the database size. It needs both versions installed on the host. It is the default choice for most self-managed systems.
  • Logical replication. A new cluster on the new version receives changes from the old one, and the application is switched over when it has caught up. Downtime is seconds to minutes. It asks more of the schema: tables need a primary key or replica identity, and sequences, schema changes and large objects are not replicated and need their own handling.
  • Dump and restore. pg_dump from the old version, restore into the new. Simple and clean, with downtime that grows with database size. Reasonable for small databases.

On managed services the mechanism is the provider's, usually in-place or a blue/green switch. The checks below still apply, because extensions, statistics and application behavior are yours.

What to check before choosing a date

-- What is installed, and at which version?
SELECT version();
SELECT extname, extversion FROM pg_extension ORDER BY extname;

-- Tables that logical replication cannot follow without a key.
SELECT c.relname
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = 'r'
AND    n.nspname NOT IN ('pg_catalog', 'information_schema')
AND    NOT EXISTS (SELECT 1 FROM pg_constraint k
                   WHERE k.conrelid = c.oid AND k.contype = 'p');
  • Extensions. Every extension must exist for the target version before the upgrade starts. One missing extension stops the whole plan.
  • Release notes. Read the incompatibilities section for every major version being crossed, not only the last one.
  • Standbys. After pg_upgrade, standbys do not upgrade themselves. They are rebuilt or resynchronized. Until that is done, the system has no failover target.
  • Operating system changes. If the upgrade also moves to a new OS release, text sort order can change with the system library, and indexes on text columns may need a rebuild.
  • Drivers and tools. Application drivers, connection poolers, backup tools and monitoring agents each need to support the new version.
  • Configuration. Settings are renamed or removed between versions. Start the new version with the intended configuration in a rehearsal, not for the first time in the window.

Rehearse on production-like data

  • Run pg_upgrade --check against a copy. It reports blockers without changing anything.
  • Perform the full upgrade on a restored copy of production and record the wall-clock time of every step.
  • Run the application's test suite and the slowest reports against the upgraded copy.
  • Compare query plans for the queries that matter. A new planner version can change them.

What to do straight after

  • Statistics. Before PostgreSQL 18, pg_upgrade does not carry optimizer statistics across. Until ANALYZE has run, the planner is guessing and queries can be slow. Run it in stages so the most important statistics arrive first.
  • Extensions. Run ALTER EXTENSION ... UPDATE where the new version ships a newer extension release.
  • Standbys and backups. Rebuild standbys and take a fresh base backup. Backups from the old version cannot restore into the new one.
  • Watch. Query latency, replication, autovacuum and error logs through at least one full business cycle.

Keeping a way back

With pg_upgrade in link mode, the old cluster cannot be safely started once the new one has been started. The way back is then a standby that was kept on the old version, or a restore from backup. With copy mode the old cluster stays intact, at the cost of time and disk space. With logical replication the old cluster keeps running until the switch, which is the main reason to choose it.

Decide the method for returning before the window, and decide the point after which the team no longer returns. Once the application has written to the new version, going back means losing or replaying those writes.

Upgrade checklist

  • Target version chosen, with its end-of-support date recorded.
  • Method chosen, with expected downtime agreed by the business.
  • Every extension confirmed available on the target.
  • Release notes read for each version crossed.
  • Full rehearsal done on production-like data, with timings.
  • Application and reports tested against the rehearsal copy.
  • Way back defined, with a named owner and a point of no return.
  • Standby rebuild and fresh base backup planned for the same window.
  • ANALYZE and extension updates scripted.
  • Monitoring thresholds reviewed for the days after.

Where this leads

Datrick plans and carries out this work under a written scope, with a named engineer accountable for the rehearsal, the window and the days after. The service page describes how an engagement runs: PostgreSQL consulting services. Where the need is ongoing, see PostgreSQL DBA services.

Related: PostgreSQL high availability · backup verification and restore drill · disaster recovery and high availability · emergency DBA support