Upgrading Postgres Major Versions with Minimal Downtime

The production database runs PostgreSQL 13, which reaches end of support in November 2025, and the application team wants features from 16 and 17. A dump and restore of the 2 TB database would take most of a day. PostgreSQL offers two faster paths. pg_upgrade rewrites the system catalogs for the new version and, with --link, reuses the existing data files through hard links — the data is not copied, but the database is down for the duration, which is minutes plus the time to rebuild planner statistics. Logical replication builds a new-version cluster alongside the old one and cuts over with a pause of seconds, at the cost of more setup and a few replication limitations. This guide helps you choose, and covers the steps that make either path safe: extension compatibility, statistics, application testing and rollback. It applies Blue-Green Deployments for Databases to the most common database-level change.

Symptom / Error Signatures

Upgrades that go badly usually fail in one of these ways:

• pg_upgrade stops at its pre-check with incompatibilities: Your installation contains extensions that should be updated, Checking for incompatible polymorphic functions ... fatal, reg* data types in user tables, or missing extension binaries for the new version.
• After an upgrade, queries are slow for hours because planner statistics were not carried over and ANALYZE has not finished (before PostgreSQL 18, pg_upgrade did not transfer optimizer statistics).
• Application errors appear from behaviour changes between versions — removed functions, changed defaults, stricter casting.
• With logical replication: tables without primary keys cannot replicate updates; sequences are behind after cut-over; extensions differ between clusters.

Root Cause Analysis

A major version changes the on-disk catalog format, so data files cannot simply be opened by the new server. pg_upgrade handles that by dumping the schema, creating it in a new cluster, and transferring the data files — copying, hard-linking (--link) or cloning them. With --link, the transfer is nearly instant regardless of size, but the old cluster must not be started once the new one has written to the shared files. Everything happens while both clusters are stopped, so downtime equals the upgrade time plus whatever warming the new cluster needs.

Logical replication never stops the old cluster. It creates the schema on a new-version cluster, streams data into it, and moves connections in a short pause, as detailed in using logical replication for blue-green database cutover. Its constraints are the usual logical-replication ones: DDL is not replicated, sequences need syncing, tables need a replica identity.

Immediate Mitigation

1. Run the compatibility checks first, whichever path you choose. pg_upgrade --check against a copy reports catalog incompatibilities without changing anything.

# Shell · staging host with both versions installed · both clusters stopped · read-only check
/usr/lib/postgresql/17/bin/pg_upgrade --check \
  --old-bindir=/usr/lib/postgresql/13/bin --new-bindir=/usr/lib/postgresql/17/bin \
  --old-datadir=/var/lib/postgresql/13/main --new-datadir=/var/lib/postgresql/17/main

2. Inventory extensions and confirm each is available for the new version, at a compatible release.

-- PostgreSQL · read-only · on the old cluster
SELECT extname, extversion FROM pg_extension ORDER BY 1;

3. Plan statistics. After pg_upgrade, run vacuumdb --all --analyze-in-stages immediately — it produces rough statistics fast and refines them — so the planner is not flying blind. For logical replication, ANALYZE green before cut-over.

Permanent Fix / Long-Term Pattern

Treat major upgrades as a routine, rehearsed operation rather than an event every five years. The rehearsal is the same for both paths: restore a recent production backup to staging, perform the upgrade exactly as planned, run ANALYZE, and run the application’s test suite and a replay of real query patterns against it — comparing plans for critical queries as in reproducing production query plans in staging. Time each step; the rehearsal’s timings are the maintenance window (or cut-over pause) you announce.

For pg_upgrade --link, take a fresh backup immediately before, keep the old binaries available, and remember the rollback limitation: once the new cluster has started, the linked data files belong to it, so rolling back means restoring from that backup. For logical replication, keep reverse replication running through a soak period, as in rolling back a blue-green database cutover. In both cases, freeze schema migrations for the upgrade window and update the version pinned in CI, linting configuration and scratch databases right after, so the next migration is tested against the version it will run on.

# Shell · production host · maintenance window · pg_upgrade with hard links
# WARNING: after starting the new cluster, the old cluster must not be started again; rollback = restore.
systemctl stop postgresql@13-main
/usr/lib/postgresql/17/bin/pg_upgrade --link \
  --old-bindir=/usr/lib/postgresql/13/bin --new-bindir=/usr/lib/postgresql/17/bin \
  --old-datadir=/var/lib/postgresql/13/main --new-datadir=/var/lib/postgresql/17/main
systemctl start postgresql@17-main
/usr/lib/postgresql/17/bin/vacuumdb --all --analyze-in-stages

Whichever path you choose, communicate the window in the same way as any other high-risk migration: announce it, name an owner and a rollback decision-maker, and keep the checklist and timings from the rehearsal open during the real run. Upgrades fail less often from technical surprises than from steps skipped under time pressure.

Verification Checklist

• pg_upgrade --check (or a logical-replication rehearsal) passed on a copy of production.
• Every extension is available and compatible on the new version.
• Statistics are rebuilt immediately after the upgrade (or before cut-over), and critical query plans were compared.
• A rollback path exists: a fresh backup for pg_upgrade, or reverse replication for logical replication.
• CI, linters and scratch databases were updated to the new major version after the upgrade.

Frequently Asked Questions

How long does pg_upgrade --link take? Mostly independent of data size: it is dominated by dumping and restoring the schema (proportional to the number of objects) and then by rebuilding statistics. Minutes is typical for databases with a moderate number of tables.

Can I skip major versions, for example 13 to 17? Yes. Both pg_upgrade and logical replication support upgrading across several major versions in one step. Read the release notes of every skipped version for behaviour changes.

Do replicas survive pg_upgrade? Physical replicas must be upgraded too — commonly by rebuilding them from the new primary, or with the documented rsync procedure for --link upgrades. Plan that time into the window.

Why are queries slow right after an upgrade? Planner statistics are not carried over by pg_upgrade in versions before PostgreSQL 18, so the planner works with defaults until ANALYZE completes. Run vacuumdb --analyze-in-stages immediately after starting the new cluster.

Which path should a team with a strict SLA choose? Logical replication, provided every table has a primary key and a schema freeze is possible. It keeps the write pause to seconds and preserves a rollback path.