Postgres Major Version Upgrades: pg_upgrade, Logical Replication, and Minimal Downtime

PostgreSQL ships a new major version every year and supports each for five years. Minor upgrades (17.4 → 17.6) are binary-compatible: install, restart. Major upgrades (16 → 17, 17 → 18) change the internal data format, so the data must be converted or copied. The three approaches trade simplicity against downtime.

Option 1: dump and restore

pg_dump -Fd -j 8 -d app -f /backup/app.dump          # from old
pg_restore -j 8 -d app /backup/app.dump               # into new (after creating roles)
pg_dumpall --globals-only > globals.sql               # roles and tablespaces

• Pros: simplest, most robust; also defragments tables and rebuilds indexes; works across architectures and major version gaps.
• Cons: downtime (writes must stop) proportional to data size — rebuilding every index is the slow part. Hours for hundreds of gigabytes.

Fine for small databases and many managed-service "upgrade" buttons use a variant. (Postgres Backup and Restore.)

Option 2: pg_upgrade

pg_upgrade converts the system catalogs and reuses the data files, which are compatible across majors.

# Both versions' binaries installed; new cluster initialised with matching settings
pg_upgrade \
  -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
  -d /var/lib/postgresql/17/main -D /var/lib/postgresql/18/main \
  --link --check          # dry run first; then without --check

Transfer modes:

Mode	Speed	Old cluster after upgrade
copy (default)	Slow (copies all data)	Intact — easy rollback
--link	Seconds to minutes (hard links)	Unusable once the new cluster starts
--clone	Fast (reflinks on supporting filesystems, e.g. XFS/Btrfs)	Intact
--swap (PostgreSQL 18)	Fast (moves directories)	Unusable

With --link, rollback means restoring from backup or failing over to a replica kept on the old version — so take a snapshot first.

After pg_upgrade:

• Statistics. Historically, planner statistics weren't carried over, and the database performed poorly until ANALYZE finished; PostgreSQL 18's pg_upgrade preserves most optimizer statistics. Either way, run vacuumdb --all --analyze-in-stages (or the post-upgrade analyse the tool suggests) promptly.
• Replicas. Physical standbys must be rebuilt (or upgraded with the documented rsync procedure) — they can't stream across major versions.
• Extensions. Every extension must exist in the new version's build; then ALTER EXTENSION … UPDATE. --check catches missing ones.

Downtime: minutes, plus analyse time. The right default for self-managed databases where a short maintenance window is acceptable.

Option 3: logical replication cutover

For near-zero downtime, run the new version alongside the old one and replicate changes, then switch.

1. Provision the new cluster; create roles; load the schema only (pg_dump --schema-only).
2. Publish on the old primary:

   CREATE PUBLICATION upgrade_pub FOR ALL TABLES;
   

3. Subscribe on the new cluster — this copies existing data, then streams changes:

   CREATE SUBSCRIPTION upgrade_sub
     CONNECTION 'host=old-db dbname=app user=replicator'
     PUBLICATION upgrade_pub;
   

4. Wait for catch-up; compare row counts and checksums on key tables.
5. Cut over: stop writes (or put the app in read-only mode briefly), wait for lag to reach zero, sync sequences, repoint the app, and drop the subscription.

Downtime is the cutover window — seconds to a minute.

What logical replication doesn't carry

• DDL. Schema changes aren't replicated. Freeze migrations during the upgrade window, or apply them to both sides. (Postgres Migrations on Large Tables.)
• Sequences. Values aren't replicated; before cutover, set each sequence on the new cluster above the old one's value (setval), with headroom.
• Large objects (pg_largeobject) aren't replicated.
• Tables without a primary key (or replica identity) can't replicate UPDATE/DELETE. Add keys, or set REPLICA IDENTITY FULL (slow for large tables).
• Materialized views must be refreshed on the new side.

And the initial copy of large tables takes time and WAL retention on the source: watch the replication slot so it doesn't fill the old primary's disk. Drop slots you're no longer using.

Pitfalls regardless of method

• Collations. If the new server's OS has a different glibc or ICU version, text sort order can change, silently corrupting B-tree indexes on text columns. Compare collation versions; reindex affected indexes, or use the builtin/ICU providers with care. This bites when upgrading PostgreSQL and the OS together.
• Removed or changed features. Read the release notes' "Migration" section for each major version you're crossing — changed defaults, removed configuration parameters, changed function behaviour.
• Extension compatibility. PostGIS, TimescaleDB, pgvector, and others have their own version matrices.
• Connection strings and poolers pointing at the old host. (Postgres Connection Pooling.)
• Monitoring and backups reconfigured for the new cluster — including WAL archiving for point-in-time recovery. (Postgres Point-in-Time Recovery.)
• Performance regressions from plan changes. Capture the top queries from pg_stat_statements before, and compare plans after.

Runbook skeleton

1. Read release notes for every major crossed; inventory extensions and versions.
2. Restore a recent backup to a scratch environment; rehearse the full procedure and time it.
3. Run the app's test suite and a load test against the upgraded copy. (Load Testing Your App.)
4. Take a fresh backup/snapshot; confirm restore works.
5. Execute (maintenance window or logical cutover).
6. Analyse; verify extensions, collations, sequences, replicas, backups, monitoring.
7. Keep the old cluster (or snapshot) until confidence is high; then decommission.

Choosing

Constraint	Method
Small DB, downtime acceptable	Dump/restore
Self-managed, minutes of downtime acceptable	pg_upgrade --link/--clone after a snapshot
Near-zero downtime required	Logical replication cutover
Managed service	The provider's in-place upgrade or blue/green feature — still rehearse on a copy

---

EasySpawn manages each project's Postgres with daily and on-demand backups, so an upgrade can be rehearsed on a restored copy and rolled back to a known-good backup — or to a snapshot of the whole server, available as an add-on. See pricing or join the waitlist.

Related: Postgres Point-in-Time Recovery · Postgres Backup and Restore · Dev, Staging, and Production Explained