Postgres 12 to 16 with no downtime

PLACEHOLDER POST — seed data. Replace with your real writeup.

Postgres 12 was out of support. The in-place pg_upgrade path meant roughly 40 minutes of downtime on a database that takes orders around the clock, so that was off the table.

The approach

1. Stand up a Postgres 16 cluster from the schema only
2. Create a publication on 12 and a subscription on 16, then let the initial copy run (about 19 hours)
3. Keep them in sync, and run read-only shadow traffic against 16 to compare results
4. Cut over behind PgBouncer

The cutover, in order

-- on 12: stop writes at the pooler, then confirm the replica has caught up
SELECT pg_current_wal_lsn();
-- on 16: wait until the subscription's received LSN matches
SELECT received_lsn, latest_end_lsn FROM pg_stat_subscription;
-- sequences are NOT replicated — copy them explicitly
SELECT setval('orders_id_seq', :last_value + 1000);

After that, PgBouncer’s config pointed at the new host and we ran RELOAD, then RESUME.

Things that would have bitten us

• Sequences don’t replicate. Rehearsal #3 hit duplicate key errors.
• Tables without primary keys can’t replicate updates. We found four.
• Large objects aren’t supported by logical replication. We had one, and it was unused.

Eleven rehearsals sounds like a lot. The real cutover was the most boring ten minutes of the quarter.