Rolling Back a Blue-Green Database Cutover

The cut-over to the new PostgreSQL 17 cluster went perfectly: an eight-second write pause, green healthy, dashboards clean. Forty minutes later the p99 latency of the search endpoint tripled — a query plan changed under the new version’s planner — and the incident commander asked the obvious question: can we go back to blue? Blue was still running. But it had stopped receiving changes at the moment of cut-over, and forty minutes of orders, payments and sign-ups existed only on green. Switching back would lose them. A blue-green database cut-over only has a rollback path if green’s writes flow back to blue from the first second after the switch, and if the rollback itself has been rehearsed. This guide sets up that reverse path, defines when rollback is still possible, and walks through executing it. It belongs to Blue-Green Deployments for Databases.

Rollback Window With and Without Reverse Replication Timeline after a cut-over. Without reverse replication, blue stops receiving writes at cut-over, so rolling back after 40 minutes would lose 40 minutes of writes. With reverse replication, green's writes stream back to blue continuously, so rollback at 40 minutes loses nothing and takes a brief pause. Rollback Window With and Without Reverse Replication cut-over rollback decision Green writes all writes land on green Blue, no reverse frozen at cut-over — rollback loses writes Blue, reverse sync receives green's writes — rollback safe primary stale in sync
Reverse replication turns rollback from a data-loss decision into another seconds-long pause.

Symptom / Error Signatures

The moment rollback is needed is usually a production symptom on green: a latency regression from plan changes, an extension or collation behaving differently on a new version, a configuration problem, or errors in a code path that was not exercised in rehearsal. The rollback itself fails in predictable ways:

  • Blue is stale because reverse replication was never set up — rolling back means losing every write since cut-over.
  • Reverse replication was set up with copy_data = true, so it tried to copy all of green into blue and conflicted with existing rows.
  • Blue’s sequences are behind, so after switching back, inserts fail with duplicate keys.
  • Schema changes were applied to green after cut-over, so blue cannot accept the replicated rows (missing replicated column).

Root Cause Analysis

After cut-over, green is the authority; blue is only useful as a rollback target if it keeps up with green. The mechanism is the forward setup in reverse: a publication on green, a subscription on blue created before cut-over with copy_data = false (blue already has all the data up to the cut-over point) and left disabled, then enabled at the cut-over, after writes move to green. From then on, every change on green reaches blue.

Rollback remains safe only while three conditions hold, and each defines part of the point of no return:

Condition Breaks when Consequence
reverse replication current the reverse subscription fails or is dropped blue misses writes
schemas compatible DDL applied to green only reverse replication stops
blue’s features sufficient green-only features used (new SQL functions, types) rows cannot be applied on blue

Until one of those is broken, rollback is another controlled pause: stop writes at the pooler, wait until blue has replayed all of green’s changes, sync sequences on blue, point connections back to blue, resume.

Can We Still Roll Back? Decision tree after a cut-over. If reverse replication is not running and current, rollback would lose writes: fix forward. If it is current, check whether green has schema or features blue lacks; if yes, fix forward; if no, roll back with a pause, sequence sync and retarget. Can We Still Roll Back? Reverse replication running and current? yes no Green uses schema or features blue lacks? yes no Fix forward Roll back: pause, sync, retarget Fix forward (rollback loses writes)
The point of no return is crossed by breaking reverse replication, not by elapsed time.

Immediate Mitigation

1. Check reverse replication before deciding.

-- PostgreSQL · on green · the reverse subscriber (blue) should be streaming with small lag
SELECT application_name, state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag
FROM pg_stat_replication WHERE application_name = 'reverse_sub';

2. If it is current, execute the rollback runbook — the mirror image of the cut-over.

# Shell · operator host · rollback from green to blue
# WARNING: only valid while reverse replication is current and schemas match.
set -euo pipefail
psql "$PGB_ADMIN" -c "PAUSE app;"
GREEN_LSN=$(psql -At "$GREEN_URL" -c "SELECT pg_current_wal_lsn()")
until [ "$(psql -At "$GREEN_URL" -c "SELECT bool_and(replay_lsn >= '$GREEN_LSN') FROM pg_stat_replication WHERE application_name = 'reverse_sub'")" = "t" ]; do sleep 0.2; done
psql -At "$GREEN_URL" -c "SELECT format('SELECT setval(%L, %s, true);', schemaname||'.'||sequencename, coalesce(last_value, 1)) FROM pg_sequences" \
  | psql "$BLUE_URL"
ln -sfn /etc/pgbouncer/pgbouncer-blue.ini /etc/pgbouncer/pgbouncer.ini    # [databases] back to blue
psql "$PGB_ADMIN" -c "RELOAD;"
psql "$PGB_ADMIN" -c "RESUME app;"

3. If it is not current, do not switch back. Fix forward on green — for plan regressions, restore statistics with ANALYZE, add or adjust indexes, or pin the plan-affecting setting — and schedule a proper rollback rehearsal before the next attempt.

Permanent Fix / Long-Term Pattern

Make reverse replication a mandatory part of every database cut-over plan, prepared in advance and enabled inside the cut-over script, as in using logical replication for blue-green database cutover:

-- PostgreSQL · before cut-over · on green: what to send back
CREATE PUBLICATION reverse_pub FOR ALL TABLES;
-- on blue: subscribe without copying (blue already has the data), start disabled
CREATE SUBSCRIPTION reverse_sub
  CONNECTION 'host=green dbname=app user=replicator password=***'
  PUBLICATION reverse_pub WITH (copy_data = false, enabled = false);
-- ROLLBACK PATH: DROP SUBSCRIPTION reverse_sub (blue); DROP PUBLICATION reverse_pub (green).

Keep a schema freeze in force for the soak period, since DDL on green breaks the reverse path, and avoid using green-only features (new built-in functions, types, syntax) in application code until blue is retired. Rehearse the rollback on staging with the same script as the cut-over. Define the soak period in advance — typically one to seven days, covering a weekly traffic cycle — and when it ends, record the decision to retire blue: disable and drop the reverse subscription, archive blue if required, and decommission it. The same “rollback path first” thinking applies to application releases in Rollback Automation.

Soak Period Gates Gates during the soak period after cut-over. Reverse replication lag must stay under a few seconds; schema freeze must hold; after the soak duration, a retirement decision is recorded; then the reverse subscription is dropped and blue decommissioned. Soak Period Gates reverse lag < 5 s? freeze no DDL? Soak period 1–7 days Retire decision recorded Drop reverse sub decommission blue fix before soak ends sync blue schema fail
Blue is retired by a recorded decision at the end of the soak, not by drift.

Verification Checklist

Frequently Asked Questions

Why create the reverse subscription with copy_data = false? Because blue already contains all data up to the cut-over. Copying would duplicate rows and fail on unique constraints; the subscription only needs changes made on green after the switch.

When does rollback stop being possible? When reverse replication stops being current, when green’s schema diverges from blue’s, or when the application starts relying on features blue lacks. Until then, rollback is another short pause.

Is rolling back a major version upgrade really safe? It is safe for data if reverse replication carried every write. Behaviour may still differ — the reason for rolling back — so test the application against blue after rollback, as you did before cut-over.

What if the cut-over used a managed blue/green feature? Check whether your provider supports reverse replication or a switchback after cut-over. If not, rollback may require the same manual reverse-replication setup, or it may not be possible without data loss — decide before cut-over, not during the incident.

How long should the soak period be? Long enough to see the traffic patterns that could expose a regression — usually at least a full day, often a week to cover weekly jobs. The cost is keeping blue running and the schema frozen.

Related Articles