Using Logical Replication for Blue-Green Database Cutover

The team needs to move the production PostgreSQL database to a new cluster — new hardware, a new major version, a different storage layout — and the business will accept a pause measured in seconds, not the hours a dump and restore would take. PostgreSQL’s built-in logical replication makes that possible: the new (“green”) database subscribes to the old (“blue”) one, copies the existing data, streams every subsequent change, and at a chosen moment the application’s connections move from blue to green after a brief write pause. The mechanism is simple; the details — tables without primary keys, sequences, DDL during the window, how exactly to pause writes and repoint connections — are where cut-overs go wrong. This guide works through them in order. It is the core procedure of Blue-Green Deployments for Databases.

Symptom / Error Signatures

The failures that derail logical-replication cut-overs are well known:

ERROR:  cannot update table "audit_events" because it does not have a replica identity and publishes updates
ERROR:  logical replication target relation "public.orders" is missing replicated column: "region"
ERROR:  duplicate key value violates unique constraint "orders_pkey"          -- on green, after cut-over
ERROR:  could not create replication slot "bluegreen_sub": ERROR: all replication slots are in use

Operationally: WAL piling up on blue because the subscription stalled (pg_replication_slots shows a growing retained size), a cut-over pause that stretches to minutes because lag was not near zero, and applications that keep connections to blue because they bypass the pooler.

Root Cause Analysis

Logical replication decodes blue’s WAL into row changes for the tables in a publication and applies them on green through a subscription. Its guarantees and gaps determine the procedure:

The cut-over is a consistency problem: at the moment connections move, green must contain every change committed on blue, and nothing may write to blue afterwards. The cleanest way to guarantee that is a pooler-level pause (PgBouncer PAUSE waits for in-flight transactions to finish and holds new ones), followed by confirming green has replayed blue’s final WAL position, then switching the pooler’s target.

Immediate Mitigation

If a cut-over attempt is going wrong:

1. Abort cleanly while blue is still authoritative. If writes are paused and something fails before the switch, resume the pooler against blue; nothing has been lost, and the subscription keeps green in sync for the next attempt.

# Shell · PgBouncer admin console · resume against blue if the cut-over is aborted
psql -h pgbouncer -p 6432 -U pgbouncer pgbouncer -c "RESUME app;"

2. Fix replica identity problems for tables without primary keys before retrying.

-- PostgreSQL · on blue · tables in the publication without a primary key
SELECT c.oid::regclass 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 NOT EXISTS (SELECT 1 FROM pg_constraint k WHERE k.conrelid = c.oid AND k.contype = 'p');
-- then, per table (slower updates, but correct):
ALTER TABLE audit_events REPLICA IDENTITY FULL;

3. If green shows duplicate-key errors after cut-over, sequences were not synced. Set them immediately (next step’s script) — the pause does not need to be repeated.

Permanent Fix / Long-Term Pattern

1. Prepare green and replication well before the cut-over. Restore the schema on green, create the publication and subscription, let the initial copy finish, and watch lag stabilise near zero under normal load. Freeze schema migrations for the window, or apply additive DDL to green before blue.

2. Verify equivalence. Compare row counts for every table and checksums for important tables in id ranges on both sides; investigate any difference.

3. Rehearse the cut-over on staging, timing each step.

4. Execute the cut-over as a script.

# Shell · operator host · PgBouncer admin + psql to blue and green
# WARNING: run only after lag is near zero; the PAUSE blocks application queries until RESUME.
set -euo pipefail
psql "$PGB_ADMIN" -c "PAUSE app;"
BLUE_LSN=$(psql -At "$BLUE_URL" -c "SELECT pg_current_wal_lsn()")
until [ "$(psql -At "$BLUE_URL" -c "SELECT bool_and(replay_lsn >= '$BLUE_LSN') FROM pg_stat_replication WHERE application_name = 'bluegreen_sub'")" = "t" ]; do sleep 0.2; done
psql -At "$BLUE_URL" -c "SELECT format('SELECT setval(%L, %s, true);', schemaname||'.'||sequencename, coalesce(last_value, 1)) FROM pg_sequences" \
  | psql "$GREEN_URL"
psql "$BLUE_URL" -c "ALTER SUBSCRIPTION reverse_sub ENABLE;"
ln -sfn /etc/pgbouncer/pgbouncer-green.ini /etc/pgbouncer/pgbouncer.ini   # [databases] now points at green
psql "$PGB_ADMIN" -c "RELOAD;"
psql "$PGB_ADMIN" -c "RESUME app;"
# ROLLBACK PATH: PAUSE, wait for green→blue lag 0, sync sequences to blue, point PgBouncer at blue, RESUME.

The exact mechanism for retargeting the pooler varies by deployment (a config reload, a DNS change, a service discovery update); what matters is that it happens while the pause is in effect. Some teams disable the forward subscription on green after the switch so nothing from blue can arrive late.

5. Soak with reverse replication running, then retire blue as described in rolling back a blue-green database cutover. Monitor slot lag throughout, per using change data capture instead of application dual-writes.

Verification Checklist

• Every published table has a primary key or an explicit replica identity.
• Row counts and checksums match between blue and green before the cut-over.
• A schema freeze (or green-first DDL ordering) covered the replication window.
• The cut-over script pauses at the pooler, waits for zero lag, syncs sequences, enables reverse replication, and retargets connections.
• Every application path connects through the pooler, so no client keeps writing to blue after the switch.

Frequently Asked Questions

Can green run a newer PostgreSQL major version than blue? Yes. Logical replication works between major versions (from PostgreSQL 10 onwards), which is why it is the standard approach for low-downtime major upgrades.

Why are sequences a problem? Logical replication copies row changes but not sequence state, so green’s sequences stay at their initial values. Without syncing them during the pause, the first inserts on green collide with replicated rows.

How long does the write pause last? Typically a few seconds in a rehearsed cut-over: draining in-flight transactions, waiting for final replay, syncing sequences, and retargeting connections. Long-running transactions at pause time are the usual reason it takes longer.

What if an application connects directly to blue, bypassing the pooler? It will keep writing to blue after the switch, and those writes will not reach green (unless reverse replication carries them, which then conflicts with green’s own writes). Inventory every connection path before the cut-over.

Do I need to stop DDL during the window? Yes, or coordinate it: logical replication does not replicate schema changes, and a column added on blue but not on green breaks the subscription.