PostgreSQL Upgrade Strategies: In-Place vs pg_upgrade vs Migration

Why You Can’t Just Swap Binaries

Coming from Oracle, the instinct is to treat a version bump like a patch: apply, restart, done. PostgreSQL minor updates work exactly like that — same on-disk format, just replace the binaries and restart. But a major upgrade (say 15 → 17) changes the on-disk data format, so you need a real strategy. There are three, and the right one depends on two things: your database size and your downtime tolerance.

1. dump / restore — simplest, portable, slow

Take a logical dump from the old cluster, restore it into the new one. It’s the most portable path (it also crosses architectures) and the easiest to reason about — but it re-inserts every row and rebuilds every index, so it’s only practical for small databases.

pg_dumpall --globals-only -f globals.sql        # roles, tablespaces
pg_dump -Fc -f app.dump appdb                    # per database
# on the new major version:
psql -f globals.sql
pg_restore -d appdb -j 8 app.dump

2. pg_upgrade — the in-place workhorse

pg_upgrade migrates a cluster to a new major version in place. Its superpower is --link mode: instead of copying the data files, it hard-links them into the new cluster, so even a multi-terabyte upgrade finishes in minutes of downtime rather than hours.

pg_upgrade \
  --old-datadir /var/lib/pgsql/15/data \
  --new-datadir /var/lib/pgsql/17/data \
  --old-bindir  /usr/pgsql-15/bin \
  --new-bindir  /usr/pgsql-17/bin \
  --link        # hard-link data files: fast, minimal downtime

# ALWAYS run --check first (no changes made):
pg_upgrade ... --check

Two catches to plan for: with --link the old cluster is no longer safely usable once the new one starts (the files are shared), so rehearse on a clone first; and every extension in the old database must be installed on the new cluster before you run it.

3. Logical replication — near-zero downtime

For a system that can’t take a maintenance window, stand up the new major version as a logical replication subscriber of the old one, let it catch up while both run, then cut the application over in seconds. It’s the same technique used for migrations — and it works across major versions, which is exactly what an upgrade needs. Remember its rules: DDL isn’t replicated, and sequences aren’t advanced on the subscriber, so sync those at cutover.

The step everyone forgets: ANALYZE

None of these paths carry over the planner statistics. Start the upgraded database and it’s flying blind until you rebuild them — which shows up as mysteriously slow queries right after a “successful” upgrade. Run it immediately:

vacuumdb --all --analyze-in-stages   # fast rough stats first, then full

Which path?

• Small database → dump/restore. Simple and safe.
• Large database, short window is OK → pg_upgrade --link. Minutes of downtime.
• Large database, near-zero downtime required → logical replication.

Whichever you choose: rehearse it on a clone, confirm your extensions exist on the new version, check the release notes for removed features, and ANALYZE the moment it’s up.