Major Version Migrations

A major version changes on-disk formats, defaults and sometimes query planning. There are two viable approaches, and they differ mainly in how much downtime you accept and how easily you can roll back.

In-place upgrade

The engine converts the existing data directory.

# PostgreSQL: --link uses hard links instead of copying, which is much faster.
pg_upgrade \
  --old-datadir=/srv/pgdata/16/main \
  --new-datadir=/srv/pgdata/17/main \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/17/bin \
  --check          # run this first; it validates without changing anything

Downtime is the conversion time — minutes for most databases with --link, longer without.

After the upgrade, statistics are not carried over. Run ANALYZE before letting production traffic in, or the first queries run with no statistics and choose poor plans.

Logical replication

Build a new cluster on the target version and replicate into it. This is the near-zero-downtime path, and it keeps the old cluster fully intact.

1. Create the new cluster and the schema.
2. Set up publication and subscription; wait for the initial copy.
3. Wait for lag to reach zero, and verify row counts and checksums.
4. Stop application writes briefly.
5. Confirm lag is zero, advance all sequences, switch the application over.
6. Keep the old cluster running until the new one has proven itself.

See PostgreSQL Logical Replication for the mechanics and the things logical replication does not carry — DDL, sequence values, large objects.

Choosing

For a small database with an acceptable maintenance window, in-place is simpler. For anything where minutes of downtime matter, or where the ability to roll back after cutover is required, logical replication is worth the second cluster.

Before either

• Read the release notes end to end, particularly removed features and changed defaults.
• Test on a restored production backup, not on a synthetic dataset.
• Compare query plans for your slowest queries on both versions. Planner changes cut both ways.
• Verify extensions, client drivers and tooling support the target version.
• Take and verify a backup immediately before the upgrade window.

After

Run ANALYZE, watch query latency closely for a full business cycle, and keep the old cluster restorable until the monthly and quarterly workloads have run successfully on the new one.