Zero-Downtime PostgreSQL Upgrades & Replication

This walks through moving a running production PostgreSQL database to a new major version while it keeps serving traffic. Logical replication runs old and new side by side until you're ready to cut over, which avoids the downtime an in-place pg_upgrade normally requires. The only interruptions are one scheduled restart of the old primary to enable logical decoding, and a write pause of a few seconds at cutover.

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.

Pre-flight on the old primary

Two things have to be true on the old primary (PostgreSQL 14 in this example) before anything else.

wal_level must be logical. On PostgreSQL 14 that change needs a restart, so schedule a short one in a quiet window, days before the migration:

-- On the OLD primary (PostgreSQL 14)
SHOW wal_level; -- must be 'logical', not 'replica'
 
ALTER SYSTEM SET wal_level = 'logical';
-- Cap the WAL an unconsumed replication slot can hold (PostgreSQL 13+),
-- so a stuck or forgotten slot can't fill the disk
ALTER SYSTEM SET max_slot_wal_keep_size = '100GB';
-- Then restart PostgreSQL (e.g. systemctl restart postgresql) and re-check SHOW wal_level.

Every published table needs a replica identity. With FOR ALL TABLES, a table that has no primary key and no REPLICA IDENTITY still replicates inserts, but UPDATE and DELETE on it start failing on the production primary the moment the publication exists. Find those tables first:

-- Tables whose UPDATE/DELETE would break once they're published
SELECT c.oid::regclass AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND (
    c.relreplident = 'n'
    OR (c.relreplident = 'd'
        AND NOT EXISTS (SELECT 1 FROM pg_index i
                        WHERE i.indrelid = c.oid AND i.indisprimary))
  );

Fix each one before creating the publication. Add a primary key where you can, or fall back to ALTER TABLE public.audit_log REPLICA IDENTITY FULL;. FULL logs the whole old row for every update and delete, and on large tables it can make the subscriber slow to apply them, so use it sparingly.

Standing up the new-version replica

Provision the new major-version instance (say, PostgreSQL 17) as a separate cluster, not a pg_upgrade'd copy of the old one. Roles are global objects that pg_dump doesn't include, so copy them first with pg_dumpall --globals-only from the old server.

The order of the next steps matters, because getting it wrong loses data without any error. The initial copy and the replication stream must start from exactly the same point. Create the replication slot first, have it export a snapshot, and dump from that snapshot. If you dump first and create the slot afterwards, any write committed between the two ends up in neither the dump nor the stream, and nothing will tell you.

-- 1. On the OLD primary: the publication must exist before the slot is created
CREATE PUBLICATION app_upgrade_pub FOR ALL TABLES;

# 2. Terminal 1: open a REPLICATION connection to the old primary and create the slot.
#    Leave this session open and idle until the dump finishes: the exported snapshot
#    only stays valid while this session is connected and runs no other command.
psql "host=old-primary.internal dbname=app user=replicator replication=database sslmode=verify-full"
 
app=> CREATE_REPLICATION_SLOT app_upgrade_slot LOGICAL pgoutput EXPORT_SNAPSHOT;
    slot_name     | consistent_point |    snapshot_name    | output_plugin
------------------+------------------+---------------------+---------------
 app_upgrade_slot | 5A/3C0012F8      | 00000003-000001B2-1 | pgoutput

# 3. Terminal 2, while terminal 1 stays open: dump exactly that snapshot.
#    Use the NEW version's pg_dump/pg_restore binaries.
pg_dump "host=old-primary.internal dbname=app user=migrator sslmode=verify-full" \
  --snapshot=00000003-000001B2-1 \
  --no-publications --no-subscriptions \
  -Fd -j 8 -f /backup/app.dump
 
# 4. Once pg_dump has finished, terminal 1 can be closed (the slot is permanent).
#    Restore into the new server:
pg_restore -d "host=new-primary.internal dbname=app user=migrator sslmode=verify-full" \
  -j 8 /backup/app.dump

-- 5. On the NEW primary (PostgreSQL 17), as a superuser: attach to the EXISTING slot.
--    The password lives in the passfile (owned by the postgres OS user, chmod 600), not in the catalog.
CREATE SUBSCRIPTION app_upgrade_sub
  CONNECTION 'host=old-primary.internal port=5432 dbname=app user=replicator sslmode=verify-full sslrootcert=/etc/postgresql/tls/ca.crt passfile=/var/lib/postgresql/.pgpass'
  PUBLICATION app_upgrade_pub
  WITH (copy_data = false, create_slot = false, slot_name = 'app_upgrade_slot');

copy_data = false is correct here only because the data came from the slot's own snapshot. The slot streams every change committed after that snapshot, so nothing is missed or applied twice. From the moment the slot exists, the old primary retains WAL for it, so don't let the dump/restore step drag, and keep an eye on disk space.

The simpler alternative is to restore only the schema (pg_dump --schema-only) and create the subscription with the defaults (copy_data = true, create_slot = true). PostgreSQL then creates the slot and does a consistent initial copy of each table itself. It's slower for large databases and gives you less control over the copy, but there's no snapshot to manage.

Also watch for DDL, because logical replication doesn't replicate schema changes. If you ship a migration while the subscription runs, apply it by hand on both sides and keep the subscriber's schema identical to, or a superset of, the publisher's. The subscriber must have every column the publisher sends. A new column therefore goes on the subscriber first, then the publisher, and a dropped column goes the other way round.

Monitoring replication lag until it's caught up

A subscription showing active tells you little, so look at the lag numbers. Run this on the old primary to see how far behind the new replica is, in bytes and in time:

SELECT
  s.slot_name,
  s.active,
  pg_current_wal_lsn() AS publisher_lsn,
  s.confirmed_flush_lsn AS subscriber_confirmed_lsn,
  pg_wal_lsn_diff(pg_current_wal_lsn(), s.confirmed_flush_lsn) AS lag_bytes,
  r.write_lag,
  r.flush_lag,
  r.replay_lag
FROM pg_replication_slots s
LEFT JOIN pg_stat_replication r ON r.pid = s.active_pid
WHERE s.slot_name = 'app_upgrade_slot';

lag_bytes is the WAL the subscriber hasn't confirmed yet. replay_lag is how long it recently took for a change to be applied on the subscriber (it reads NULL when there's nothing to replicate). On the subscriber, pg_stat_subscription gives the matching view, where received_lsn, latest_end_lsn and latest_end_time show what it has received and last confirmed back to the publisher. Under normal write load, lag_bytes should trend to zero or to a small, stable number. If it oscillates and never converges, the new replica's hardware probably 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 stayed at or near zero across a full traffic cycle, the cutover itself should take seconds. Two details matter here. The old primary is made read-only for the application role, so you don't have to trust that every writer stopped. And reverse replication (new to old) is in place before any write lands on the new primary, which is what makes the rollback work.

#!/usr/bin/env bash
# Run with an admin role (not the app role); passwords come from ~/.pgpass.
set -Eeuo pipefail
 
OLD="host=old-primary.internal dbname=app user=admin sslmode=verify-full"
NEW="host=new-primary.internal dbname=app user=admin sslmode=verify-full"
APP_ROLE="app_rw"
APP_CONN_STRING_SECRET="projects/prod/secrets/app-db-conn"
 
abort() {
  echo "ABORT: $1. Old primary back to read-write." >&2
  psql "$OLD" -c "ALTER ROLE $APP_ROLE RESET default_transaction_read_only;"
  kubectl -n app scale deployment app-writer --replicas=3
  exit 1
}
 
echo "[1/7] Pausing application writes..."
kubectl -n app scale deployment app-writer --replicas=0
 
echo "[2/7] Making the old primary read-only for the app and draining writers..."
psql "$OLD" -c "ALTER ROLE $APP_ROLE SET default_transaction_read_only = on;"
# Existing sessions keep their old setting, so disconnect them; they reconnect read-only
psql "$OLD" -Atc "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
                  WHERE usename = '$APP_ROLE' AND backend_type = 'client backend';" >/dev/null
WRITERS=1
for _ in $(seq 1 30); do
  # A session holding a transaction id has written something that isn't finished
  WRITERS=$(psql "$OLD" -Atc "SELECT count(*) FROM pg_stat_activity
                               WHERE backend_type = 'client backend' AND backend_xid IS NOT NULL
                                 AND pid <> pg_backend_pid();")
  [ "$WRITERS" = "0" ] && break
  sleep 1
done
[ "$WRITERS" = "0" ] || abort "$WRITERS sessions still writing after 30s"
 
echo "[3/7] Waiting for replication lag to reach zero..."
LAG=1
for _ in $(seq 1 60); do
  LAG=$(psql "$OLD" -Atc "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
                          FROM pg_replication_slots WHERE slot_name = 'app_upgrade_slot';")
  [ "${LAG:-1}" = "0" ] && break
  sleep 1
done
[ "${LAG:-1}" = "0" ] || abort "lag is still ${LAG:-unknown} bytes"
 
echo "[4/7] Reconciling sequences on the new primary (+1000 headroom)..."
psql "$OLD" -Atc "SELECT format('SELECT setval(%L, %s);',
                    quote_ident(schemaname) || '.' || quote_ident(sequencename), last_value + 1000)
                  FROM pg_sequences WHERE last_value IS NOT NULL;" > ./sync-sequences.sql
psql "$NEW" -v ON_ERROR_STOP=1 -f ./sync-sequences.sql
 
echo "[5/7] Switching replication direction: new -> old, for rollback..."
# Dropping the subscription also drops app_upgrade_slot on the old primary
psql "$NEW" -v ON_ERROR_STOP=1 -c "DROP SUBSCRIPTION app_upgrade_sub;"
psql "$NEW" -v ON_ERROR_STOP=1 -c "CREATE PUBLICATION app_rollback_pub FOR ALL TABLES;"
psql "$OLD" -v ON_ERROR_STOP=1 -c "CREATE SUBSCRIPTION app_rollback_sub
  CONNECTION 'host=new-primary.internal port=5432 dbname=app user=replicator sslmode=verify-full sslrootcert=/etc/postgresql/tls/ca.crt passfile=/var/lib/postgresql/.pgpass'
  PUBLICATION app_rollback_pub
  WITH (copy_data = false, slot_name = 'app_rollback_slot');"
 
echo "[6/7] Repointing application connection string to new primary..."
gcloud secrets versions add "$APP_CONN_STRING_SECRET" --data-file=./new-primary-conn.txt
 
echo "[7/7] Resuming application writes against new primary..."
kubectl -n app scale deployment app-writer --replicas=3

Steps 2 and 3 are the safety gates. If either fails, the script aborts, puts the old primary back to read-write and resumes writers against it, and the new server is left untouched. copy_data = false in step 5 is safe because nothing has been written to the new primary yet, and the slot it creates starts from the current position.

Teams that haven't rehearsed the DNS/connection-string repoint in step 6 usually lose time there. The usual causes are 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, separately from testing the migration.

Rollback plan

The advantage over an in-place pg_upgrade is that the old primary still exists right after cutover, and thanks to app_rollback_sub it keeps receiving every write the new primary accepts. Don't decommission it the same day. Keep it running for at least one full business cycle, long enough to catch peak-load or weekly-batch-job problems.

If the new version misbehaves after cutover (a query planner regression, an extension incompatibility, a client library that doesn't like a wire-protocol change), the rollback is the same sequence with the roles swapped. Pause writers, make the app role read-only on the new primary, wait for app_rollback_slot lag on the new primary to reach zero, reconcile sequences from new to old, reset default_transaction_read_only for the app role on the old primary, repoint, and resume writes. No writes are lost and no backup is involved. Rehearse it once on staging so the rollback path is tested too.

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.