Transformation: PostgreSQL major version upgrade with logical replication and minutes of downtime

#When to use this instead of pg_upgrade

#1. Prepare the source

SHOW wal_level;                       -- must be logical (restart to change)
SHOW max_replication_slots; SHOW max_wal_senders;

Tables without a primary key need a replica identity, or updates/deletes on them fail to replicate:

ALTER TABLE audit_log REPLICA IDENTITY FULL;    -- works, but slow for big tables; add a PK if you can

Large objects (pg_largeobject) aren't replicated. Move them separately or migrate them to bytea first.

#2. Build the new cluster

• New servers (or the same host with a different port) with PostgreSQL 17 installed.
• Same extensions available; same locale/collation settings.
• Copy roles: pg_dumpall --globals-only from source, apply to target.
• Copy schema:

pg_dump -h old -U postgres -d app --schema-only | psql -h new -U postgres -d app

#3. Start replication

-- old (source)
CREATE PUBLICATION upg_pub FOR ALL TABLES;

-- new (target)
CREATE SUBSCRIPTION upg_sub
  CONNECTION 'host=old port=5432 dbname=app user=replicator password=...'
  PUBLICATION upg_pub WITH (copy_data = true, streaming = on);

Initial copy runs table by table. Watch progress:

SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel WHERE srsubstate <> 'r';   -- 'r' = ready

For very large databases, PostgreSQL 17's pg_createsubscriber can turn a physical standby into a logical subscriber, which avoids a slow initial copy. It only works when the source is already on 17 (or for future 17 → 18 upgrades), so it isn't an option for upgrading to 17.

#4. Test on the new version

While replication runs:

• Run read-only application tests and reports against the new cluster.
• Compare EXPLAIN ANALYZE for top queries (planner changes between majors).
• Check extension versions (ALTER EXTENSION ... UPDATE).

#5. Switchover

1. Block writes on old: put apps in maintenance, or ALTER ROLE app_user NOLOGIN; plus terminate sessions.
2. Wait for lag zero:

-- on old
SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS bytes_behind FROM pg_replication_slots;

3. Sync sequences (generate setval statements from pg_sequences on old, run on new).
4. DROP SUBSCRIPTION upg_sub; on new.
5. Point apps to new (DNS/pooler change), re-enable logins, ANALYZE (or vacuumdb --analyze-in-stages).

#6. Rollback option

Before letting apps write to the new cluster, set up reverse replication (publication on new, subscription on old with copy_data = false). If something goes wrong after switchover, you can switch back without losing the new writes. Remove it after the rollback window.

Caution

DDL during replication silently breaks it: a new column on the source that doesn't exist on the target stops the apply worker. Freeze schema changes until the switchover is done.

#Checklist

• wal_level=logical; PK/replica identity on all tables
• Large objects and sequences handled separately
• Roles and schema copied; extensions present
• Initial sync complete; tests run on new version
• Switchover rehearsed; reverse replication for rollback