Near-zero-downtime major version upgrade with logical replication

Command

# 0) On the old version wal_level = logical (restart); every table needs a PRIMARY KEY or REPLICA IDENTITY
# 1) Move roles and schema to the new version (a 'role postgres already exists' error is expected):
pg_dumpall -h <old_host> --globals-only | psql -h <new_host> -X -d postgres
pg_dump -h <old_host> --schema-only --create -d <db> | psql -h <new_host> -X -d postgres
# 2) Publication on the old version, subscription on the new version (initial copy + continuous stream):
psql -h <old_host> -d <db> -X -c "CREATE PUBLICATION upg_pub FOR ALL TABLES;"
psql -h <new_host> -d <db> -X -c "CREATE SUBSCRIPTION upg_sub CONNECTION 'host=<old_host> dbname=<db> user=<repl_user>' PUBLICATION upg_pub;"
# 3) Wait until all tables are 'r' (ready) and lag is ~0:
psql -h <new_host> -d <db> -X -c "SELECT srsubstate, count(*) FROM pg_subscription_rel GROUP BY 1;"
psql -h <old_host> -d <db> -X -c "SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag FROM pg_replication_slots WHERE slot_name = 'upg_sub';"
# 4) Cutover: stop application writes, confirm lag = 0, synchronize sequences (logical-sync-sequences)
# 5) Drop the subscription (the remote slot is dropped too), point the application to the new server:
psql -h <new_host> -d <db> -X -c "DROP SUBSCRIPTION upg_sub;"

What it does

Builds an empty cluster on the new version, moves the schema and continuously streams data from the old version with logical replication; only the final cutover step (stopping writes, syncing sequences, redirecting) causes a short outage. A second replication in the reverse direction can keep a rollback path open.

When to use it

When the outage required by pg_upgrade is unacceptable, when moving to new hardware/OS at the same time, or when skipping several major versions.

Caution

DDL, sequences, large objects and materialized view contents are not replicated; freeze the schema during the migration. On tables without a primary key/replica identity, UPDATE/DELETE fail on the publisher. On large databases the initial copy takes long and holds WAL and catalog_xmin on the old server. Each database needs its own publication/subscription. Compare row counts/checksums before cutover.