Postgres Logical Replication for Zero-Downtime Major Upgrades
KU
Kiril Urbonas
last week • 7 min read•
pg_upgrade is fast but takes downtime; logical replication lets you cut over while the old DB still serves traffic. The runbook, the gotchas, and the post-cutover checklist.
pg_upgrade is the official answer for major version upgrades. It works, it's fast, and it takes downtime — your DB is unavailable from "stop the old server" to "start the new one." For a single instance that's minutes. For a busy production DB with replicas and connection pools to drain, it's an hour-plus maintenance window.
Logical replication offers a different path: stand up the new version alongside the old, replicate writes in real time, cut over with seconds of downtime. The mechanism is good enough that we've used it for our last three major upgrades. This is the runbook and what we learned.
Logical replication uses Postgres's WAL but decodes it into row-level changes (INSERT/UPDATE/DELETE) rather than physical block changes. Critically, the decoded changes can be applied to a different major version. The new DB doesn't need to be a binary clone of the old.
The flow:
Total downtime in our last upgrade: 4 seconds (the time to swap a DNS record and restart connection pools).
The first surprise: not everything is in the replication stream. You have to handle these manually:
Sequences. Sequence values (the nextval state for SERIAL columns) are not replicated. If you cut over and the new DB's sequence is at 1 while the old was at 1,000,000, the next INSERT collides. Solution: bump sequences manually after sync, before cutover.
Schema changes. DDL (CREATE TABLE, ALTER TABLE, etc.) is not replicated. If you ALTER TABLE on the source during replication, you have to apply the same change on the target manually. We freeze schema changes during the cutover window.
Large objects (lo_* API). Not replicated. If you use them (rare), use file storage instead.
Tables without a primary key. Replication can't UPDATE/DELETE rows without a unique identifier. Either add a PK before starting (preferred) or set REPLICA IDENTITY to FULL (replicates entire row in the WAL — heavy).
For most modern Postgres schemas, only sequences are an issue.
On the old DB:
-- Enable logical replication
ALTER SYSTEM SET wal_level = logical;
-- Restart required for wal_level
-- Create a publication for all tables
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;
You can publish specific tables if you're doing partial migration; for a full upgrade, FOR ALL TABLES is cleanest.
Confirm the publication:
SELECT * FROM pg_publication;
Install Postgres 17 (or your target version) on a new host. Create the database with the same name, owner, and encoding as the source.
Dump the schema (no data):
pg_dump -h source-db --schema-only --no-owner --no-privileges mydb > schema.sql
Apply to target:
psql -h target-db mydb < schema.sql
Apply roles/permissions separately if needed.
On the target:
CREATE SUBSCRIPTION upgrade_sub
CONNECTION 'host=source-db user=replicator dbname=mydb password=...'
PUBLICATION upgrade_pub;
This starts the initial sync immediately. For a 500GB DB, initial sync took ~6 hours in our case. During this time the source is under additional load (WAL retention + read load from the sync workers). Plan for it.
Monitor sync progress:
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_subscription_rel WHERE srsubstate != 'r';
srsubstate = 'r' means "ready" (caught up). All tables at r = initial sync complete.
After initial sync, replication continues. Check lag:
SELECT
application_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS sent_lag,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag
FROM pg_stat_replication;
replay_lag should be near zero in steady state. If it's growing, the target can't keep up — usually because the target hardware is undersized or because a slow query is blocking the apply worker. Investigate.
Before cutting over, verify:
SELECT count(*) FROM big_table on both. Don't do this on every table or you'll be there forever; pick the 10 tables you care most about.-- On source:
SELECT last_value FROM my_sequence;
-- On target:
SELECT setval('my_sequence', <last_value + safety_margin>);
The downtime window. Roughly 5-30 seconds depending on your fleet.
ALTER SUBSCRIPTION upgrade_sub DISABLE; This prevents further replication.If the app uses PgBouncer, swap PgBouncer's databases config to point at the target and RELOAD CONFIG. App connections through PgBouncer don't need to restart; pooler handles the swap.
The old DB is now stale; the new DB is authoritative.
pg_stat_statements if you use it (you start a fresh baseline).If something goes wrong in the first hour, you can swap back to the old DB — but any writes that happened on the new DB will be lost. The window for safe rollback is tight; verify aggressively in the first 30 minutes.
Underestimating initial sync time. First time we did this, the sync took 14 hours and we'd planned for 4. Sync is bounded by network + target disk write throughput; benchmark on a non-prod copy first.
Forgetting sequences. First cutover: app immediately started failing on INSERT because the sequence was at 1. We rolled back, set the sequences, tried again. Now it's step 5b in our runbook.
Replicating into a DB with existing data. Don't. Target should be empty. If for some reason it's not, the subscription will replicate INSERTs that collide and fail.
Big tables without WHERE clauses. Logical replication does the initial sync as essentially a SELECT * from each table. A 500GB table is a long-running query. Consider replicating in batches by partition if your tables are partitioned.
Logical replication isn't free — it adds operational complexity for the duration of the migration. But for a busy production DB where a 90-minute maintenance window is unacceptable, it's the path. The runbook above is what we've crystallized after three upgrades; expect to refine it for your shape.
Get the latest tutorials, guides, and insights on AI, DevOps, Cloud, and Infrastructure delivered directly to your inbox.
Horizontal and vertical autoscalers solve different problems and break in different ways. The thresholds, cooldowns, and conflicts we learned the hard way.
Token caching, model routing, prompt compression, and the boring discipline of measuring. The levers that cut our LLM bill 60% without touching feature scope.
Explore more articles in this category
The "three pillars" framing misses the point — what matters is correlating across them. The patterns that earn their place and the tooling decisions that pay back.
Sharding isn't just "split the table" — the shard key choice cascades through queries, joins, rebalancing, and operations. The decisions that pay off and the ones we redid.
The single most useful Postgres extension you might not be using. The queries it surfaces, the indexes it implies, and the operational discipline of reading it weekly.
