Zero-Downtime PostgreSQL Major Version Upgrade with Logical Replication

Method              Downtime    Risk    Complexity
--------------------|-----------|--------|----------
pg_dumpall + restore  Hours      Low     Low
pg_upgrade (in-place) Minutes    Medium  Medium
Logical replication   Seconds    High    High
Blue-green deploy     Seconds    Medium  High

1. Provision new PG16 instance
2. pg_dump --schema-only from PG14, restore schema to PG16
3. Create replication user on PG14
4. Set wal_level = logical on PG14
5. Create publication on PG14: CREATE PUBLICATION upgrade_pub FOR ALL TABLES
6. Create subscription on PG16 (initial data copy + live replication)
7. Monitor lag until subscription catches up (lag = 0)
8. Stop writes to PG14 (maintenance mode)
9. Verify final row counts match
10. Update connection strings to PG16
11. Drop subscription and publication

# Step 2: Schema only
pg_dump -h pg14-host -U postgres --schema-only mydb | \
  psql -h pg16-host -U postgres mydb

-- Step 3: On PG14
CREATE USER logical_migrator WITH REPLICATION LOGIN PASSWORD 'mig_pass';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO logical_migrator;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO logical_migrator;

-- Step 5: On PG14
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

-- Step 6: On PG16
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=pg14-host dbname=mydb user=logical_migrator password=mig_pass'
  PUBLICATION upgrade_pub;

-- Step 7: Monitor until caught up
SELECT subname, received_lsn, latest_end_lsn,
       now() - latest_end_time AS lag
FROM pg_stat_subscription;

-- Step 9: Verify counts after stopping writes
SELECT count(*) FROM orders;  -- run on both and compare

-- Step 11: Cleanup on PG16
DROP SUBSCRIPTION upgrade_sub;
-- On PG14:
DROP PUBLICATION upgrade_pub;