Zero-Downtime Database Migrations: Lessons from Production

1. The Multi-Phase Zero-Downtime Migration Architecture

Migrating production transactional databases between AWS accounts, VPCs, or cloud providers under active customer load carries high risk. Naive approaches requiring extended maintenance windows cause severe revenue loss and SLA breaches.

Our production migration blueprint relies on a 5-stage protocol: Schema Baseline Initialization, Continuous Logical Replication (CDC), Real-time Consistency Verification, Microsecond Traffic Drain via PgBouncer, and Reverse Replication Failback.

2. Setting Up PostgreSQL Logical Replication (pgoutput)

PostgreSQL logical replication uses a publish-and-subscribe model based on WAL decoding. Unlike physical streaming replication, logical replication allows data transfer across different PostgreSQL major versions and cloud providers.

Ensure wal_level = logical and configure appropriate max_replication_slots on the source instance before creating the publication.

-- On Source Database (Publisher)
ALTER SYSTEM SET wal_level = 'logical';

CREATE PUBLICATION prod_migration_pub 
FOR ALL TABLES;

-- On Destination Database (Subscriber)
CREATE SUBSCRIPTION prod_migration_sub
CONNECTION 'host=source-db.technofreaks.internal port=5432 dbname=production user=replicator password=REDACTED sslmode=require'
PUBLICATION prod_migration_pub
WITH (copy_data = true, create_slot = true);

3. Expand and Contract Schema Migrations

Never execute breaking schema modifications (renaming columns, changing data types, dropping constraints) in a single step. Follow the Expand and Contract pattern:

1. Expand: Add the new column or table as nullable. Deploy application code that writes to both old and new columns.

2. Backfill: Migrate historical records in batches to populate the new column.

3. Contract: Deploy application code that reads only from the new column, then safely drop the legacy column in a subsequent release.

4. PgBouncer Traffic Pausing & Microsecond Cutover

During the final cutover, rather than restarting application servers or waiting for DNS to propagate, PgBouncer is used to pause incoming client connections.

Issuing PAUSE <database> holds incoming TCP connections in a pending queue while allowing in-flight queries to finish. The replication lag is verified to be 0 bytes, connection strings are updated to point to the new destination, and RESUME <database> is executed.

#!/usr/bin/env bash
set -euo pipefail

echo "==> Step 1: Pausing incoming database writes on PgBouncer..."
psql -h 127.0.0.1 -p 6432 -U admin pgbouncer -c "PAUSE production_db;"

echo "==> Step 2: Verifying logical replication lag is exactly 0 bytes..."
LAG=$(psql -h source-db -U admin -t -c "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) FROM pg_replication_slots WHERE slot_name='prod_migration_sub';")
echo "Replication Lag: ${LAG} bytes"

echo "==> Step 3: Updating PgBouncer target database host to Destination..."
sed -i 's/source-db.technofreaks.internal/dest-db.technofreaks.internal/g' /etc/pgbouncer/pgbouncer.ini
psql -h 127.0.0.1 -p 6432 -U admin pgbouncer -c "RELOAD;"

echo "==> Step 4: Resuming traffic to new primary database..."
psql -h 127.0.0.1 -p 6432 -U admin pgbouncer -c "RESUME production_db;"
echo "Zero-downtime cutover completed successfully in < 3 seconds!"

5. Sequence Synchronization & Reverse Replication

Logical replication replicates table row data but does NOT automatically increment PostgreSQL sequence objects (e.g. bigserial / GENERATED ALWAYS AS IDENTITY).

Immediately upon promoting the destination database, run an automated script to advance all sequences to MAX(id) + 1000 to prevent duplicate key collisions on subsequent writes.