Routing Reads During a Blue-Green Extension Cutover
How to move read traffic from the blue environment to the green one at the moment of a blue-green extension cutover — draining in-flight queries, switching the router atomically, and keeping an instant path back to blue if green stumbles.
Up: Blue-Green Extension Deployment — this page is the traffic-routing mechanics for that cutover, under the Automated Execution & Rollback Workflows area of the site.
Context & When This Applies
The blue-green deployment has staged green with upgraded extensions, synced it, and passed the health gate — now the traffic has to move. The routing layer is where a clean upgrade becomes a clean cutover or a messy one: switch too abruptly and in-flight transactions on blue are killed; switch reads and writes at different moments and you can serve a read from green that has not yet replicated a write still landing on blue. This page applies to PostgreSQL 12–17 behind any routable layer — PgBouncer, a service mesh, HAProxy, or DNS — where the goal is to move reads with zero lost queries and an instant fallback.
The reason reads deserve their own treatment is that a read-mostly workload can often cut reads over before writes, validating green under real read traffic while blue still owns writes — a lower-risk staged switch than flipping everything at once. Getting the drain-and-switch sequence right is what keeps the cutover invisible to users.
Concept: Quiesce, Drain, Switch — Never Kill
An abrupt router flip severs blue’s in-flight queries; the safe sequence is quiesce, drain, then switch. Quiesce stops the router sending new reads to blue while leaving existing connections alive — so no query is interrupted, and the blue read pool naturally empties. Drain waits for those in-flight reads to complete (bounded by a timeout so a runaway query cannot stall the cutover forever). Switch repoints the read target at green only once blue is idle for reads. Because green caught up via replication and passed the health gate, the switched reads immediately see consistent data. The whole move is graceful precisely because no connection is forcibly closed.
The second concept is read/write separation as staged risk. Reads and writes do not have to move together. Cutting reads over first — while blue still owns writes — lets green prove itself under real read load with a trivially instant fallback (revert the read target), before the higher-stakes write cutover. The one hazard this introduces is read-your-writes: a client that writes to blue and immediately reads from green can miss its own just-written row if replication lag exceeds the gap. Bounding replication lag under the gate threshold — the same lag check the blue-green deployment enforces — is what keeps the staged read cutover correct, and it composes with the window math in threshold tuning for downtime windows.
Runnable Implementation
The controller quiesces blue reads, drains with a bounded timeout, then switches the router’s read target to green — with an explicit revert function for instant fallback.
#!/usr/bin/env python3
"""Drain-and-switch read traffic from blue to green during a cutover."""
import time
import psycopg2
def active_read_backends(dsn: str) -> int:
with psycopg2.connect(dsn) as conn, conn.cursor() as cur:
# Count non-idle client backends still running read queries.
cur.execute("""
SELECT count(*) FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend'
AND query NOT ILIKE 'INSERT%' AND query NOT ILIKE 'UPDATE%'
AND query NOT ILIKE 'DELETE%'
""")
return cur.fetchone()[0]
def cutover_reads(router, blue_dsn: str, drain_timeout_s: int = 30) -> dict:
# 1. Quiesce: stop routing NEW reads to blue (router-specific call).
router.set_read_target("blue", accepting=False)
# 2. Drain: wait for in-flight blue reads to finish, bounded by a timeout.
deadline = drain_timeout_s
while deadline > 0 and active_read_backends(blue_dsn) > 0:
time.sleep(1)
deadline -= 1
drained = active_read_backends(blue_dsn) == 0
if not drained:
# Do NOT kill queries; re-open blue and abort the read cutover.
router.set_read_target("blue", accepting=True)
return {"status": "aborted", "reason": "blue reads did not drain in time"}
# 3. Switch: point the read target at green (already caught up + gated).
router.set_read_target("green", accepting=True)
return {"status": "reads_on_green", "drained": True}
def revert_reads(router) -> dict:
# Instant fallback: send reads back to blue.
router.set_read_target("blue", accepting=True)
return {"status": "reverted_to_blue"}Trigger cutover_reads only after the blue-green health gate passes, and keep revert_reads wired to your monitoring so a green regression flips reads back without human latency.
Expected Output & Verification
A clean read cutover reports the switch; a stalled drain aborts without killing anything:
{"status": "reads_on_green", "drained": true}Verify green is actually taking the read traffic and that read-your-writes holds under current lag:
-- On GREEN: read backends should now be arriving here.
SELECT count(*) AS green_read_backends
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend';
-- Replication lag must stay under the read-your-writes threshold.
SELECT application_name,
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;green_read_backends climbing and replay_lag_bytes under your threshold confirm a correct cutover. If lag exceeds the threshold, revert reads to blue until it drains — the staged approach makes that reversal free. Record the cutover outcome under version control and branching.
Edge Cases & Gotchas
Forcibly terminating blue reads defeats the graceful cutover. Calling pg_terminate_backend on blue’s in-flight reads to speed the drain kills user queries — the exact harm the sequence avoids. Let the drain time out and abort instead; a cutover that waits is better than one that errors users.
Read-your-writes breaks if reads move before lag is bounded. A client writing to blue then reading from green can miss its own row while replication catches up. Only cut reads over when replay_lag is under the round-trip a client could plausibly do, and prefer to keep a client’s session pinned to one environment for its lifetime.
A long-running read can hold the drain open indefinitely. An analytics query on blue may run for minutes, stalling the drain. Bound the drain with a timeout (as above) and decide policy: abort the cutover, or exclude known-long backends from the drain wait and let them finish on blue post-switch.
DNS-based routing switches slowly and unevenly. If the router is DNS, TTL caching means clients move over gradually, so blue and green serve reads simultaneously for a while. Ensure both are consistent for reads during that overlap (green caught up), or use a connection-level router (PgBouncer, mesh) for a crisper switch.