Draining a Read Replica for Maintenance Without Dropping Queries

Problem statement: you need to patch, resize, or restart one replica, and removing it from the pool with a health-check change kills every query currently running on it.

Draining is the difference between a maintenance window nobody notices and a burst of client errors that looks like an outage. The mechanics are simple; the parts that go wrong are the bounded wait and the reintroduction. This page covers both. The pool behaviour it depends on is described in load balancing reads across a replica pool.

Symptom Identification

You need a drain procedure, rather than the one you have, if maintenance produces any of these:

  • A burst of server closed the connection unexpectedly or connection reset by peer in application logs at exactly the moment the replica was taken out.
  • Query error rate spiking for a few seconds per maintenance action, once per replica, entirely predictably.
  • The remaining replicas’ latency climbing sharply as the drained node’s traffic lands on them all at once.
  • A second error spike when the node comes back, as a least-connections balancer sends it a disproportionate share while its caches are cold.
  • Maintenance windows that overrun because a long-running reporting query holds the drain open indefinitely.

The first and last symptoms have opposite causes — not waiting at all, and waiting without a bound — and a correct procedure has to solve both.

Root Cause Analysis

Removing a server has two distinct effects, and only one of them is wanted. Stopping new work from being assigned is the goal. Terminating existing work is collateral damage. Health-check-based removal conflates them: when a check fails, most balancers mark the server down and, with on-marked-down shutdown-sessions configured, close its sessions.

Drain separates the two. In the drained state a balancer assigns no new sessions to the server while leaving established ones untouched. Queries already running finish normally; the server empties as clients naturally close connections or as the pooler recycles them.

Draining is unbounded by default, and that is the second problem. A replica serving an analytical workload may have a query with twenty minutes left to run. Waiting for a true zero means the maintenance window is hostage to the longest query on the node. The resolution is a bounded wait plus a statement_timeout on the read role, so “wait for in-flight work” has a defined upper limit.

Reintroduction is where the second incident happens. After a restart the node has an empty buffer cache. A least-connections balancer sees a node with zero connections and finds it maximally attractive. The node receives a disproportionate burst while it is least able to serve it, its latency spikes, and it may be ejected again — the failure loop described in weighted vs least-connections balancing.

Three ways to remove a replica, by what happens to in-flight queries Three traces of the number of active queries on a replica after the removal is initiated. Marking the server down drops the count to zero instantly and every one of those queries becomes a client error. An unbounded drain decays quickly to a single long analytical query that continues for many minutes, holding the maintenance window open. A bounded drain decays the same way but terminates the remaining backends at a thirty second deadline, so at most one query is affected and the window is predictable. active queries time after removal initiated → set down every in-flight query becomes a client error unbounded drain — one 20-minute query holds it open 30 s deadline bounded drain — remainder terminated deliberately set down unbounded drain bounded drain — what you want The bounded drain trades one deliberately-killed long query for a predictable window and no error burst.

Step-by-Step Resolution

Step 1 — Confirm the pool can absorb the loss

promql
# Current utilisation of the replicas that will carry this node's share.
avg by (instance) (rate(node_cpu_seconds_total{mode!="idle",role="replica"}[5m]))

With three replicas at 55% utilisation, removing one puts the other two at roughly 82% — workable but tight. At 70% each, removing one takes the survivors past 100% and the maintenance window becomes an incident.

Inline verification: projected post-drain utilisation, computed as current × n / (n−1), stays under about 80%.

Step 2 — Drain, do not down

bash
# HAProxy: drain leaves in-flight sessions alone.
echo "set server pg_read/r3 state drain" | socat stdio /var/run/haproxy.sock

# Confirm the state took effect and no NEW sessions are arriving.
echo "show servers state pg_read" | socat stdio /var/run/haproxy.sock | grep r3
sql
-- ProxySQL: OFFLINE_SOFT is the drain equivalent. OFFLINE_HARD kills sessions.
UPDATE mysql_servers SET status = 'OFFLINE_SOFT'
 WHERE hostgroup_id = 20 AND hostname = 'r3';
LOAD MYSQL SERVERS TO RUNTIME;

Inline verification: on the replica, SELECT count(*) FROM pg_stat_activity WHERE backend_type='client backend' stops rising and begins to fall.

Step 3 — Wait for in-flight work, with a bound

bash
#!/usr/bin/env bash
# wait-drained.sh — poll until quiet or the deadline, then report which.
set -euo pipefail
HOST="${1:?replica host}"; DEADLINE="${2:-30}"

for (( i = 0; i < DEADLINE; i++ )); do
  active=$(psql -h "$HOST" -Atc "
      SELECT count(*) FROM pg_stat_activity
       WHERE backend_type = 'client backend' AND state = 'active'")
  if [[ "$active" -eq 0 ]]; then
    echo "drained cleanly after ${i}s"; exit 0
  fi
  sleep 1
done

echo "deadline reached with ${active} active quer(ies) — terminating deliberately" >&2
psql -h "$HOST" -c "
  SELECT pg_terminate_backend(pid) FROM pg_stat_activity
   WHERE backend_type = 'client backend' AND state = 'active'"

Terminating at a deadline is a deliberate decision, not a failure. The alternative — waiting indefinitely — hands control of your maintenance window to whichever query happens to be running.

Inline verification: the script prints either drained cleanly with a duration, or the number of queries it terminated. Both are acceptable outcomes; a hang is not.

Step 4 — Cap query duration on the read role

sql
-- Set once, permanently, on the role used for pooled reads. This bounds how
-- long any drain can possibly take and protects against runaway reads generally.
ALTER ROLE app_read SET statement_timeout = '30s';

-- Reporting workloads that genuinely need longer get their own role,
-- and are drained separately or scheduled outside maintenance windows.
ALTER ROLE app_reporting SET statement_timeout = '15min';

Inline verification: SELECT rolname, rolconfig FROM pg_roles WHERE rolname LIKE 'app_%' shows the timeout on each role.

Step 5 — Reintroduce through a ramp

bash
# Bring the server back into rotation. slowstart on the backend does the ramp.
echo "set server pg_read/r3 state ready" | socat stdio /var/run/haproxy.sock

# Watch the effective weight climb rather than jumping to full.
watch -n5 'echo "show servers state pg_read" | socat stdio /var/run/haproxy.sock \
           | awk "/r3/ {print \$4, \$6, \$9}"'

If the maintenance restarted the database, consider pre-warming before reintroduction so the ramp starts from a warmer cache:

sql
-- Optional: pull the hottest relations into cache before taking traffic.
-- pg_prewarm must be installed; do this while the node is still drained.
SELECT pg_prewarm('orders'), pg_prewarm('orders_pkey'), pg_prewarm('users');

Inline verification: during the ramp, the node’s p99 query latency stays within the fleet’s normal band rather than spiking above it.

Configuration Snippet

text
# The backend settings that make drain and reintroduction safe.
backend pg_read
    balance leastconn
    option pgsql-check user haproxy_check

    default-server inter 2s fall 3 rise 5 \
                   slowstart 60s \
                   maxconn 120 \
                   on-marked-down shutdown-sessions

    server r1 10.0.1.11:5432 check weight 100
    server r2 10.0.1.12:5432 check weight 100
    server r3 10.0.1.13:5432 check weight 100

on-marked-down shutdown-sessions looks contradictory on a page about not dropping queries, and it is deliberate: it applies when a server is marked down, which means the node is unreachable or failing checks. In that situation the sessions are already broken and closing them promptly returns clients to a healthy node instead of leaving them blocked on a dead socket. It has no effect on the drain state, which is the one this procedure uses. Keeping both behaviours — clean drain for planned work, fast shutdown for genuine failure — is the correct configuration.

ini
# PgBouncer, if it fronts the replicas. PAUSE is the pooler-level drain.
# Run against the admin console on the pooler that targets this replica.
;   PAUSE app_read;      -- stop assigning server connections, let txns finish
;   SHOW POOLS;          -- wait for sv_active to reach 0
;   RESUME app_read;     -- after maintenance

# Bounded connection lifetime means direct application pools also age out,
# so a drain does not depend on clients voluntarily reconnecting.
server_lifetime = 300
server_idle_timeout = 60
Full drain-and-return sequence with the two risk windows marked A left-to-right sequence of seven stages: verify headroom, set drain, bounded wait for in-flight queries, perform the maintenance, pre-warm the cache, reintroduce with a slow-start ramp, and verify the distribution. Two brackets mark the windows where client errors are possible: the moment the deadline expires and remaining backends are terminated, and the reintroduction ramp if slow-start is not configured. Everything else in the sequence is error-free. 1 · headroom n/(n−1) check 2 · drain not down 3 · wait bounded, 30 s 4 · maintain patch / resize 5 · pre-warm optional 6 · ramp in slowstart 60 s 7 · verify share recovers where client errors are possible only at the deadline, only for over-running queries only if slowstart is missing — cold cache under full load Every other stage is error-free. Both risk windows are configuration, not fate.

Verification and Rollback

Confirm the drain is complete before starting the maintenance:

sql
-- On the replica: no client backends at all, active or idle.
SELECT state, count(*) FROM pg_stat_activity
 WHERE backend_type = 'client backend' GROUP BY state;
-- Expect zero rows.

An idle count that will not fall means clients are holding connections without using them. That is harmless for most maintenance (a restart will close them) but matters for an in-place operation that needs exclusive access.

Confirm no client errors were produced:

promql
# Should be flat across the whole maintenance window.
sum(rate(app_db_errors_total{kind=~"connection_reset|server_closed"}[1m]))

Confirm the reintroduction went cleanly:

promql
# The returning node's share should climb smoothly to parity over slowstart,
# and its p99 latency should never exceed the fleet's.
sum by (instance) (rate(pg_stat_database_xact_commit{role="replica"}[2m]))

Rollback. At every stage before the maintenance itself, rollback is a single command:

bash
# Abort the drain and restore the node to service immediately.
echo "set server pg_read/r3 state ready" | socat stdio /var/run/haproxy.sock

Because draining never terminated anything, aborting mid-drain leaves no trace — the node simply resumes taking new sessions. If you have already terminated backends at the deadline, restoring the node is still safe; the terminated queries were client-visible errors that have already happened and will not be undone by returning the node to service.

Edge Cases and Gotchas

A transaction-mode pooler drains almost instantly, and that can mislead you

With PgBouncer in transaction mode, server connections are released back to the pool at every transaction boundary, so sv_active on the pooler reaches zero within milliseconds. It is tempting to read that as “the replica is drained”. It is not — the pooler still holds idle server connections, and if the maintenance requires the database to have no connections at all, you need PAUSE followed by waiting for sv_used and sv_idle to clear as well.

Long-running reads on a replica may be cancelled anyway

A replica applying a heavy WAL backlog can cancel long-running queries through recovery conflicts, entirely independently of your drain. If maintenance follows a period of high write volume on the primary, the replica may be replaying a backlog and cancelling queries while you are carefully avoiding cancelling them. Check pg_stat_database_conflicts before concluding that your drain caused an error — the mechanism is covered in detecting hot standby query cancellations on Postgres replicas.

Draining the last healthy replica

The drain command does not know it is removing your last serving node. If the other replicas are already down or drained, this one leaves the read pool empty, and whatever your empty-pool policy is takes effect immediately. Guard the procedure with the headroom check in step 1 as a hard precondition rather than an advisory one, and consider a wrapper script that refuses to drain when fewer than two servers are up.

FAQ

What is the difference between drain and down?

Draining stops the balancer assigning new sessions to the server while leaving existing connections and in-flight queries alone, so work already started completes normally. Marking a server down closes its sessions immediately, which produces client errors for anything running. Drain is the correct state for planned maintenance; down is for a node that is genuinely unreachable or returning errors.

How long should I wait for a replica to drain?

Long enough for a normal query to finish and no longer — typically your p99 read latency plus a wide margin, so tens of seconds rather than minutes. Waiting indefinitely means one reporting query can block a maintenance window. Set a bounded wait, and when it expires, terminate the remaining backends deliberately rather than allowing the drain to hang.

Do pooled connections drain automatically?

Not by themselves. A transaction-mode pooler keeps its server connections open and simply stops assigning them, so the pooler drains quickly. A session-mode pooler or a direct application pool holds connections until they are closed or expire, so draining requires either a connection lifetime that ages them out or an explicit terminate. Check which mode you are in before assuming the drain has completed.

Is a warm-up needed when bringing the replica back?

Yes, whenever the maintenance restarted the database or the machine. The buffer cache and the OS page cache are empty, so the node’s first queries are far slower than normal, and a least-connections balancer will preferentially select it because it has no connections. A slow-start ramp of about sixty seconds covers most working sets; large ones need longer.

← Back to Load Balancing Reads Across a Replica Pool