Zero-Downtime PostgreSQL Upgrades & Replication

This walks through moving a running production PostgreSQL database to a new major version without stopping it, using logical replication to run old and new side by side until you're ready to cut over, instead of the downtime pg_upgrade in place normally requires.

Why pg_upgrade in place forces downtime

pg_upgrade, even in its fast --link mode, locks the entire cluster for the duration of the catalog conversion: down from the moment you start until it finishes verifying and restarting. That duration scales with catalog complexity, extensions, number of relations, not data size, which is why "20 minutes" estimates are unreliable. Worse: if the upgrade fails partway through, your rollback plan is restoring from backup, at whatever hour it fails.

Logical replication sidesteps this: you build a new-version replica from scratch, replicate into it while the old primary keeps serving traffic, and redirect the application once it's proven caught up. The old primary gets retired afterward, on your schedule.

Standing up the new-version replica

Provision the new major-version instance (say, PostgreSQL 17, upgrading from 14) as a separate cluster, not a pg_upgrade'd copy of the old one. Load it via pg_dump/pg_restore (or a base snapshot), then wire up logical replication from the old primary:

-- On the OLD primary (PostgreSQL 14)
CREATE PUBLICATION app_upgrade_pub FOR ALL TABLES;
 
-- Confirm wal_level supports logical replication
SHOW wal_level; -- must be 'logical', not 'replica'

-- On the NEW replica (PostgreSQL 17), after schema + initial data load
CREATE SUBSCRIPTION app_upgrade_sub
  CONNECTION 'host=old-primary.internal dbname=app user=replicator password=...'
  PUBLICATION app_upgrade_pub
  WITH (copy_data = false, create_slot = true, slot_name = 'app_upgrade_slot');

Setting copy_data = false is deliberate: you've already loaded the data via pg_dump/pg_restore at a known LSN, so the subscription should only stream changes forward, not re-copy everything. (Skip the manual load and copy_data = true handles the initial sync itself: simpler, but slower for large databases and less control over the snapshot.)

Also watch for DDL: logical replication doesn't replicate schema changes. Ship a migration while the subscription runs and apply it manually to both sides, keeping the publisher's schema a superset of (or identical to) the subscriber's. Add a nullable column to the subscriber first; a dropped column needs the opposite order.

Monitoring replication lag until it's caught up

Don't trust a subscription that says active, trust the lag number. Run this on the old primary to see how far behind the new replica is, in time and bytes:

SELECT
  slot_name,
  active,
  pg_current_wal_lsn() AS publisher_lsn,
  confirmed_flush_lsn AS subscriber_confirmed_lsn,
  pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes,
  now() - pg_stat_activity.query_start AS wal_sender_age
FROM pg_replication_slots
LEFT JOIN pg_stat_activity ON pg_stat_activity.pid = pg_replication_slots.active_pid
WHERE slot_name = 'app_upgrade_slot';

On the subscriber side, pg_stat_subscription gives the corresponding view: latest_end_lsn and latest_end_time show its last confirmed flush back to the publisher. Watch lag_bytes trend to zero (or a small, stable number) under normal write load. If it oscillates and never converges, the new replica's hardware likely can't keep up with the write rate.

That's why I run this query on a loop for at least 24-48 hours, spanning a full peak-traffic cycle: a subscription that's caught up on a quiet Tuesday afternoon tells you nothing about whether it holds up under your actual load peak.

The cutover sequence

Once lag has been consistently at or near zero across a full traffic cycle, the cutover itself should take seconds, not minutes:

#!/usr/bin/env bash
set -euo pipefail
 
OLD_PRIMARY="old-primary.internal"
NEW_PRIMARY="new-primary.internal"
APP_CONN_STRING_SECRET="projects/prod/secrets/app-db-conn"
 
echo "[1/6] Pausing application writes (readiness probe returns 503)..."
kubectl -n app scale deployment app-writer --replicas=0
 
echo "[2/6] Waiting for in-flight transactions to drain..."
psql -h "$OLD_PRIMARY" -Atc \
  "SELECT count(*) FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid();"
sleep 5
 
echo "[3/6] Verifying replication lag is zero..."
LAG=$(psql -h "$OLD_PRIMARY" -Atc \
  "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) FROM pg_replication_slots WHERE slot_name = 'app_upgrade_slot';")
if [ "$LAG" -ne 0 ]; then
  echo "ABORT: lag is $LAG bytes, not zero. Not safe to cut over." >&2
  exit 1
fi
 
echo "[4/6] Reconciling sequences on new primary..."
psql -h "$NEW_PRIMARY" -f ./sync-sequences.sql
 
echo "[5/6] Repointing application connection string to new primary..."
gcloud secrets versions add "$APP_CONN_STRING_SECRET" --data-file=./new-primary-conn.txt
 
echo "[6/6] Resuming application writes against new primary..."
kubectl -n app scale deployment app-writer --replicas=3

Step 3 is the actual safety gate: fail it and you abort, leaving the old primary serving traffic, untouched.

The DNS/connection-string repoint in step 5 is where teams lose time if they haven't rehearsed it: a TTL that's too long, a connection pooler (PgBouncer, RDS Proxy) caching the old host, or an app that doesn't reconnect cleanly on a config change. Rehearse the repoint against a throwaway target beforehand, separate from testing the migration.

Rollback plan

The advantage of this approach over in-place pg_upgrade is that the old primary still exists, untouched, immediately after cutover. Keep it running rather than decommissioning it the same day. Keep the replication slot around too, and hold it for at least one full business cycle, long enough to catch peak-load or weekly-batch-job problems.

If the new version misbehaves post-cutover (a query planner regression, an extension incompatibility, a client library that doesn't like a wire-protocol change), the rollback is the same cutover script run in reverse; repoint back to the old primary and resume writes: a real rollback, not a restore-from-backup-and-hope exercise.

Closing

A 30-second cutover and a four-hour outage can be the exact same migration, to the same PostgreSQL version. What separates them is whether the old and new databases ran side by side long enough to prove the new one works before you committed to it. pg_upgrade in place skips that step and bills you for it later, as downtime, at a time you don't choose.