A practical walkthrough for upgrading your Postgres major version without downtime using logical replication. We cover the often-overlooked steps—sequence syncing, replica identity, DDL freeze, and rollback planning—that quickstart guides miss.
TL;DR — You can go from Postgres 14 to 16 without your users noticing, but only if you respect the parts quickstarts skip. The pattern: (1) stand up a new v16 cluster with schema only, (2) set up logical replication from old→new, (3) monitor lag and validate everything, (4) freeze DDL, sync sequences, and cut over Right. Let’s break down each step in detail, focusing on the traps that catch teams who follow only the official docs.
1. Stand up the new v16 cluster — schema only
Spin up a fresh Postgres 16 instance. Do not use pg_dump with --data-only on the new side; you want only the schema. Pull the DDL with:
pg_dump -h old-host -U app_user -d mydb --schema-only --no-owner --no-privileges -f schema.sql
psql -h new-host -U app_user -d mydb -f schema.sql
Remove any statements that create replication slots or subscriptions — they’ll clash later. After the restore, verify that every extension (like pg_stat_statements, postgis) is at a version compatible with Postgres 16. That part is often missed; test it now, not at 3 a.m.
2. Logical replication — and the replica identity surprise
On the old cluster, create a publication for all tables you want to replicate:
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;
On the new cluster, create a subscription that points back:
CREATE SUBSCRIPTION upgrade_sub
CONNECTION 'host=old-host dbname=mydb user=repl_user password=***'
PUBLICATION upgrade_pub;
Here’s the snag most guides skip. Logical replication needs to uniquely identify rows. If a table lacks a primary key or a suitable unique index, the publication will skip UPDATE and DELETE operations silently — not obvious until you compare counts. For each table, set a replica identity explicitly. For tables with a primary key, it’s already USING INDEX. For tables without one, you need either a unique not-null index or to use the entire row as identifier:
ALTER TABLE my_table REPLICA IDENTITY FULL;
FULL works but is slower; prefer creating a dedicated unique index if possible. Run this check across all tables before you start replicating. MyDBA’s health-check dashboard (https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgres-zero-downtime-major-upgrade-logical-replication) includes a preflight check for replica identity gaps, so you don’t have to write your own catalog queries.
3. Monitor lag and validate everything
Don’t trust pg_stat_replication alone. Track replication lag at the subscription level:
SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS bytes_behind
FROM pg_replication_slots WHERE slot_type = 'logical';
And on the subscriber, use the view from Postgres 16:
SELECT subscription_name, status, last_msg_send_time, last_msg_receipt_time
FROM pg_stat_subscription;
Compare row counts between old and new for critical tables. Do it repeatedly while the app is writing; small discrepancies often signal missing replica identity or a table that wasn’t added to the publication. It’s tedious when you have hundreds of tables; a tool that surfaces drift automatically saves hours. (That’s exactly why we built MyDBA’s logical replication observability suite.)
4. Freeze DDL, sync sequences, and the final sync window
Once replication lag is near zero, declare a DDL freeze on the old cluster. Any schema change that triggers an ALTER TABLE … ADD COLUMN with a default can cause a full table rewrite, which stalls replication.
Now sync sequences. Logical replication doesn’t replicate sequence state — your new cluster still has sequences at 1. You must snapshot them after all writes are on the old side. A reliable approach:
-- on old cluster
COPY (SELECT 'SELECT setval(' || quote_literal(sequence_name) || ', ' || last_value || ');'
FROM information_schema.sequences) TO '/tmp/seq_sync.sql';
Then apply that script on the new cluster. Do it during the cutover window while your application is pointing to the old cluster but not accepting writes, or you’ll risk primary key collisions. If you can’t stop writes, you’ll need to set sequences to a safe value above the current maximum IDs, then let the app catch up.
5. Cut over — with a safety net
The cutover itself is a routing change: point your application connection string to the new v16 cluster. But before you flip DNS or config, prepare a rollback plan. If you reverse the replication direction (new→old) too late, old data might re-enter the new cluster. A safer pattern is to stop all writes on the old side, wait for replication to drain, then drop the subscription and repoint the app. If something goes wrong, you can reconnect to the old cluster without trying to unwind replicas.
To make rollback painless, keep the old cluster running but in read-only mode for a few days. If you set default_transaction_read_only = on globally, no accidental writes happen. At worst, you change the connection string back.
pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgres-zero-downtime-major-upgrade-logical-replication
After the cutover: what you’ll wish you’d automated
The steps above are mechanical, but what often burns teams are the silent failures: a table that never replicated because of a missing replica identity, a sequence that sat at zero for three days, a replication slot that fell behind and bloated the old primary’s disk. You can catch these with careful scripting, but the reality is that a dedicated Postgres observability tool that understands logical replication semantics saves a weekend of panicked recovery. MyDBA watches replica identity, lag, sequence drift, and disk usage from replication slots — all in one place. If you’re planning a major upgrade without downtime, give it a try at https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgres-zero-downtime-major-upgrade-logical-replication so your next cutover is boring instead of legendary.
Top comments (0)