Blue-Green Deployments for Databases

Blue-green deployment is easy to explain for stateless services: run the new version (green) alongside the old (blue), switch traffic when green is healthy, switch back if it is not. Databases break the metaphor, because the data is not a build artefact that can be duplicated and discarded — it is the one thing that must stay continuous across the switch. There are two very different things teams mean by “blue-green” where databases are involved. The first is blue-green for the application with a shared database: two application versions run against one database, which is only safe if the schema supports both at once. The second is blue-green for the database itself: a second database (a new major version, a new instance class, a restructured schema) is kept in sync with the first by replication, and traffic is cut over to it. This part of CI/CD & Migration Automation covers both, with the emphasis where the risk is: keeping the schema compatible with two versions, keeping two databases in sync, and keeping a rollback path after the cut-over. It serves platform teams building deployment pipelines and DBAs planning upgrades.

Concept & Mechanism

Shared-database blue-green. When blue and green application versions run concurrently against the same database, every schema change applied for green must be tolerated by blue, and blue must not write anything green cannot read. That is precisely the expand-and-contract contract from Expand and Contract Methodology: additive changes ship before green, destructive changes wait until blue is gone for good. The difference from a rolling deploy is duration and reversibility — blue may stay warm for hours as the rollback target — so the compatibility window is longer and must be planned explicitly, as covered in running blue-green deploys with a shared database.

Database blue-green. A second database is created and kept continuously in sync with the first. Physical replication (streaming replicas) produces an identical copy and is how failover works, but it cannot change the major version or the physical layout. Logical replication — PostgreSQL publications and subscriptions, MySQL binlog replication, or CDC tools — replicates row changes rather than disk blocks, so the green database can run a newer major version, a different configuration, or even a different schema layout. The cut-over then consists of stopping writes to blue briefly, waiting until green has applied every change, fixing up anything logical replication does not carry (sequence values in PostgreSQL, for instance), and pointing connections at green. Managed platforms package the same idea — several cloud providers offer blue/green deployment features for their database services that automate the replication and switchover steps — but the underlying mechanics and constraints are the same.

Connection management is the part of a database cut-over that is easiest to underestimate. Every client that writes must stop writing to blue and start writing to green at the same moment, and in most organisations “every client” is a longer list than anyone expects: application pods, background workers, cron jobs, data pipelines, BI tools with write-back features, administrative scripts on laptops. The only reliable way to switch them all at once is to make them all connect through one control point — a pooler such as PgBouncer or ProxySQL, a DNS name with a short TTL, or a service-discovery entry — and to verify beforehand, from pg_stat_activity or the processlist, that no client connects to blue directly. Where a direct connection cannot be removed, revoke its write privileges on blue during the cut-over so it fails loudly instead of writing into the old database.

Performance deserves the same rehearsal as correctness. Green is a new server with a cold cache, possibly a new major version with a different planner, and freshly loaded tables whose statistics may be incomplete. Run ANALYZE on green before the cut-over, warm frequently used tables and indexes (for example with pg_prewarm on PostgreSQL), and compare query plans for the application’s most important queries between blue and green. A cut-over that is correct but twice as slow for the first hour is still an incident; most of that risk can be removed in advance with a replay of production read traffic against green.

Finally, database blue-green interacts with ordinary schema migrations. While replication runs, both sides must agree on the schema; while reverse replication runs after cut-over, they must still agree. Treat the whole window — from the start of the initial copy to the retirement of blue — as a schema freeze, and communicate it to every team that ships migrations, or coordinate each change as green-first additive DDL applied by the same team that runs the cut-over.

Prerequisites & Decision Criteria

Pick the approach from what is changing.

Before a database blue-green cut-over:

• Every replicated table has a primary key or a replica identity, so updates and deletes can be applied on green.
• A schema freeze covers the replication window, or DDL is applied to green before blue in a coordinated order.
• The cut-over runbook includes syncing sequences, checking replication lag to zero, and repointing every connection path.
• Reverse replication (green → blue) is prepared and tested, so rollback does not lose writes made on green.
• Application connection handling tolerates a brief pause and reconnect — pool timeouts exceed the planned write pause.

Step-by-Step Procedure

The procedure below upgrades a PostgreSQL database to a new major version with logical replication; upgrading Postgres major versions with minimal downtime expands it.

1. Build green with the schema only. Restore the schema on the new version; verify the application’s test suite passes against an empty green.

# Shell · operator host · schema only; green runs the new major version
pg_dump --schema-only --no-owner "$BLUE_URL" | psql -v ON_ERROR_STOP=1 "$GREEN_URL"

2. Publish on blue and subscribe on green. The subscription copies existing data, then streams changes.

-- PostgreSQL · on blue (source) · requires wal_level = logical
CREATE PUBLICATION bluegreen FOR ALL TABLES;
-- on green (target)
CREATE SUBSCRIPTION bluegreen_sub CONNECTION 'host=blue dbname=app user=replicator password=***'
  PUBLICATION bluegreen WITH (copy_data = true);
-- ROLLBACK PATH: DROP SUBSCRIPTION bluegreen_sub (on green); DROP PUBLICATION bluegreen (on blue).

3. Verify convergence. Compare row counts and checksums for key tables, and watch replication lag reach near zero under normal load, as in using logical replication for blue-green database cutover.

4. Prepare reverse replication. Create a publication on green and a subscription on blue with copy_data = false, left disabled until the cut-over.

5. Cut over. Pause writes (PgBouncer PAUSE, or stop writers), wait for lag to reach zero, sync sequences, enable the reverse subscription, repoint connections to green, resume.

6. Soak, then retire blue. Keep blue in sync via reverse replication for an agreed period; decommission it only when rollback is no longer needed, per rolling back a blue-green database cutover.

Verification & Observability

The two numbers that matter are replication lag and data equivalence. Lag must be near zero before the cut-over; equivalence must be proven, not assumed.

-- PostgreSQL · on blue · replication position of the green subscriber
SELECT application_name, state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag_bytes
FROM pg_stat_replication;

For equivalence, compare row counts per table and checksums over key columns in ranges, as in reconciling divergence between dual-written tables. After the cut-over, watch error rates, latency, and the reverse replication lag from green to blue — the rollback path is only as good as that lag. Record the cut-over’s write-pause duration as a metric; it is the user-visible cost of the migration.

Rollback Path

For application blue-green on a shared database, rollback is switching traffic back to blue, which works as long as the schema still supports blue — the reason destructive changes wait. For database blue-green, rollback after cut-over means moving connections back to blue without losing writes made on green, which is only possible if green’s writes have been replicating back to blue since the cut-over. With reverse replication running, rollback is another brief pause, sequence sync and repoint. Without it, rollback means choosing between losing green’s writes and a manual reconciliation.

-- PostgreSQL · on blue · reverse subscription prepared before cut-over, enabled at cut-over
CREATE SUBSCRIPTION reverse_sub CONNECTION 'host=green dbname=app user=replicator password=***'
  PUBLICATION reverse_pub WITH (copy_data = false, enabled = false);
ALTER SUBSCRIPTION reverse_sub ENABLE;   -- run at cut-over, after writes move to green

Common Errors & Fixes

ERROR: cannot update table "x" because it does not have a replica identity and publishes updates. Root cause: a table without a primary key in a publication. Fix: add a primary key or set REPLICA IDENTITY FULL (slower) before replicating.

Duplicate key errors on green right after cut-over. Root cause: sequences were not synced; logical replication does not carry sequence values. Fix: set each sequence on green above the current maximum during the write pause.

Replication breaks during the window with logical replication target relation ... is missing replicated columns. Root cause: DDL applied to blue but not to green. Fix: enforce a schema freeze, or apply additive DDL to green first.

Cut-over takes minutes instead of seconds. Root cause: lag was not near zero when writes paused, or long transactions delayed the pause. Fix: gate on lag, clear long transactions, and use a pooler-level pause for a clean stop.

Child Page Index

Four guides cover the pieces in depth. Running blue-green deploys with a shared database handles the application-level pattern and its schema compatibility window. Using logical replication for blue-green database cutover builds and verifies the replication. Rolling back a blue-green database cutover covers reverse replication and the rollback runbook. And upgrading Postgres major versions with minimal downtime applies it all to the most common use case.

Moving individual tables between databases, rather than whole databases, is covered in dual-writing across two databases during a cutover.

Frequently Asked Questions

Can a database be blue-green deployed with zero write downtime? Almost. Logical replication keeps green current, but a consistent cut-over needs a brief pause in writes — typically seconds — while replication finishes and connections move. Reads can usually continue throughout.

Why not use physical replication and fail over? Physical replication produces an identical copy, including the major version and on-disk format, so it cannot perform an upgrade or restructuring. It is the right tool for failover and instance changes, not for version upgrades.

Is database blue-green worth it for a small database? Often not. If a dump and restore or pg_upgrade fits comfortably in an acceptable maintenance window, the extra machinery of replication, sequence syncing and reverse replication adds risk without much benefit. Blue-green pays off when downtime must be measured in seconds or the database is too large to copy within a window.

Do schema migrations have to stop during a database blue-green window? With PostgreSQL logical replication, DDL is not replicated, so schema changes must be applied to both sides in a compatible order or frozen for the window. Most teams freeze migrations for the duration.

Does MySQL support the same approach? Yes. A green MySQL server can replicate from blue with binlog replication (including across major versions in the supported direction), and the cut-over follows the same pattern: stop writes, wait until the replica has applied everything, repoint connections, and keep replication running back to blue for rollback if you set it up in advance.

How long should blue be kept after cut-over? Until you are confident rollback will not be needed — often a day to a week — with reverse replication keeping it current. After that, stop the reverse subscription and decommission blue.