Automated Failover Orchestration for Sharded Databases

Automated failover orchestration keeps every shard of a partitioned database writable when its primary node dies, without a human on the promotion path. This guide is part of the Partition Monitoring & Failover Automation section; where partition skew detection and replication lag alerting tell you a shard is degrading, failover orchestration is the machinery that acts when a shard actually fails. It covers leader election with Patroni and etcd for PostgreSQL, orchestrator and GTID-based promotion for MySQL, fencing and split-brain prevention, and — the part most teams get wrong — how the shard routing layer learns about a new primary.

Problem framing

A payments platform runs 16 PostgreSQL shards, each a three-node replication group: one primary and two streaming replicas. With a single database, the operations team could tolerate a manual failover runbook — page the on-call DBA, verify the primary is dead, promote a replica, repoint the application. At 16 shards, that model collapses arithmetically. If a single primary has a 1% chance of failing in a given month, the fleet has roughly a 15% chance that some shard loses its primary that month. Every hardware refresh, kernel patch cycle, and cloud availability-zone wobble now multiplies across 16 independent failure surfaces, and the mean time between “some shard needs a failover” shrinks to weeks.

Sharding also makes the blast radius asymmetric and confusing. When shard 11 loses its primary, 1/16th of customers see write errors while the other 15/16ths are fine. Dashboards show aggregate error rates of ~6%, which reads like a minor degradation rather than a total outage for an unlucky tenant slice. On-call engineers waste critical minutes confirming that the problem is one shard’s primary rather than a network issue, a bad deploy, or skewed load on a hot partition.

Manual failover at fleet scale therefore fails on three axes: frequency (too many events), speed (humans add 10–30 minutes of write unavailability), and correctness (a tired operator promoting the more lagged of two replicas silently loses committed transactions). The fix is per-shard failover automation: each shard runs its own leader-election group, an external consensus store arbitrates which node holds the primary role, and a router-update path propagates every role change to the query layer within seconds.

Architecture overview

Each shard is an independent high-availability unit. For PostgreSQL, every node in the shard runs a Patroni agent; all agents across all shards share one etcd cluster as the distributed configuration store (DCS). The current primary holds a leader key with a time-to-live (TTL) lease and renews it every loop cycle. If the primary dies or is partitioned away from etcd, the lease expires, the surviving replicas race to acquire it, and the healthiest candidate — filtered by replication lag — promotes itself. The routing tier (pgbouncer, HAProxy, or an application-side shard map) discovers the new primary either by polling each node’s Patroni REST API or by reacting to a callback that fires on role change.

Three properties make this architecture safe. First, the DCS is the single source of truth: a node is primary if and only if it holds the leader key, so two nodes can never both believe they are primary while etcd has quorum. Second, election is lag-aware: a replica that is too far behind refuses to stand for election, bounding data loss. Third, the router consumes the same source of truth as the election, so traffic follows the leader key rather than a stale static config.

Implementation walkthrough

1. Leader election with Patroni and etcd (PostgreSQL)

Patroni turns each PostgreSQL shard into a self-healing replication group. Every node runs the Patroni daemon, which manages the local PostgreSQL process, publishes health over a REST API on port 8008, and participates in leader election through etcd. One etcd cluster serves the whole fleet; each shard gets its own scope, which namespaces its keys.

# /etc/patroni/patroni.yml on node pg-shard04-a
scope: shard_04                # one Patroni cluster per shard
name: pg-shard04-a

etcd3:
  hosts:
    - etcd-1.internal:2379
    - etcd-2.internal:2379
    - etcd-3.internal:2379

bootstrap:
  dcs:
    ttl: 30                    # leader lease lifetime (seconds)
    loop_wait: 10              # seconds between agent cycles
    retry_timeout: 10          # DCS/PostgreSQL operation retry budget
    maximum_lag_on_failover: 1048576   # bytes; laggier replicas won't promote
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        max_wal_senders: 10
        hot_standby: "on"

postgresql:
  listen: 0.0.0.0:5432
  connect_address: pg-shard04-a.internal:5432
  data_dir: /var/lib/postgresql/16/shard_04

The election loop is simple: every loop_wait seconds, the primary renews its leader key with a ttl-second lease. If the primary process crashes, the host dies, or it cannot reach etcd for longer than the TTL, the key expires. Each surviving replica then checks its own replication position, compares it against maximum_lag_on_failover, and attempts an atomic compare-and-set on the leader key. Exactly one wins, promotes its PostgreSQL instance with pg_promote(), and begins renewing the lease. The full node-by-node build — etcd endpoints, bootstrap settings, tags, and callbacks — is covered in Automating PostgreSQL Shard Failover with Patroni.

Use Patroni tags to shape election behavior per node. A reporting replica that must never take writes gets nofailover: true; a node in a distant region gets failover_priority: 0 so it is only promoted as a last resort:

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: true
  failover_priority: 1

2. Leader election with orchestrator and GTID (MySQL)

MySQL fleets get equivalent behavior from orchestrator, which takes a topology-wide rather than per-node view: a central orchestrator service (itself run as a raft group of three for its own availability) continuously discovers every replication topology, detects dead primaries, and repoints replicas. GTID-based replication is what makes automated promotion safe — because every transaction carries a globally unique identifier, orchestrator can compute which replica is most advanced and realign the others under it with CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION = 1 without manually reconciling binlog file/offset pairs.

{
  "RaftEnabled": true,
  "RaftNodes": ["orc-1.internal", "orc-2.internal", "orc-3.internal"],
  "RecoverMasterClusterFilters": ["shard_.*"],
  "FailureDetectionPeriodBlockMinutes": 10,
  "RecoveryPeriodBlockSeconds": 300,
  "DelayMasterPromotionIfSQLThreadNotUpToDate": true,
  "PreventCrossDataCenterMasterFailover": true,
  "PostMasterFailoverProcesses": [
    "/usr/local/bin/update-shard-map.sh {failureClusterAlias} {successorHost} {successorPort}"
  ]
}

Two settings deserve attention. RecoveryPeriodBlockSeconds enforces a cooldown so a flapping shard cannot trigger promotion loops. DelayMasterPromotionIfSQLThreadNotUpToDate makes orchestrator wait for the candidate to apply its already-received relay logs before promotion — the MySQL analog of Patroni’s lag gate. Pair orchestrator with semi-synchronous replication (rpl_semi_sync_source_wait_for_replica_count = 1) if the business cannot tolerate losing acknowledged transactions.

3. Tuning health checks and failure detection

Detection speed is a budget you allocate between two failure costs: real outages that last longer because detection is slow, and false failovers triggered by transient blips. The core Patroni relationship is:

worst-case detection time ≈ ttl
safe constraint:            ttl ≥ loop_wait + 2 × retry_timeout

With the defaults (ttl: 30, loop_wait: 10, retry_timeout: 10), a hard primary crash is detected within 30 seconds and promotion completes a few seconds later. Halving everything (ttl: 15, loop_wait: 5, retry_timeout: 5) halves detection time but means any 15-second etcd unavailability or network stall demotes a perfectly healthy primary. On a sharded fleet the flapping cost is multiplied: a 20-second network wobble in one rack can demote a dozen primaries simultaneously, which is far worse than any single slow failover.

Health checking must also distinguish “PostgreSQL is slow” from “PostgreSQL is dead.” Patroni’s liveness comes from its ability to renew the lease and to query the local instance; a primary drowning in a lock pileup still renews its key. That is correct behavior — failover does not fix overload — and it is why failover automation must be paired with the capacity signals from replication lag and capacity alerting rather than treated as a universal remediation.

4. Fencing and split-brain prevention

Split brain — two nodes accepting writes for the same shard — is the catastrophic failure mode, because it forks the data and no promotion can merge it back. Orchestrated failover prevents it with three layers:

1. Lease-based demotion. A Patroni primary that cannot renew its leader key demotes itself before the TTL expires, restarting PostgreSQL read-only. By the time a replica can win the election, the old primary has already stopped accepting writes — provided the Patroni process itself is alive to do the demotion.
2. Watchdog fencing. If the Patroni process is frozen (SIGSTOP, OOM thrashing, blocked I/O) it cannot demote anything. Enabling the Linux softdog watchdog closes this hole: Patroni arms /dev/watchdog and the kernel hard-resets the machine if Patroni fails to pet it in time.

watchdog:
  mode: required        # refuse to run as primary without a watchdog
  device: /dev/watchdog
  safety_margin: 5      # reset at least 5s before the lease could expire

3. Rejoin via pg_rewind. When the old primary comes back, its timeline has diverged from the new one. With use_pg_rewind: true, Patroni rewinds the returning node to the divergence point and reattaches it as a replica instead of letting it rejoin as a second primary or forcing a slow full re-clone.

For MySQL, the equivalent fencing is to run the old primary with read_only = ON and super_read_only = ON as the default state, letting only orchestrator’s promotion hook flip a node writable — combined with PreventCrossDataCenterMasterFailover to stop a partitioned minority site from promoting locally.

5. Router integration: teaching the shard map about the new primary

Promotion is worthless until traffic follows it. The router update is the step teams most often leave manual, and it shows up as “the database failed over in 30 seconds but the app was down for 20 minutes.” There are three production-grade patterns.

Health-checked routing (HAProxy). Patroni’s REST API returns HTTP 200 on /primary only from the current leader. HAProxy probes every node of the shard and sends write traffic to whichever answers 200 — no config change needed at failover time:

# haproxy.cfg — one backend per shard, primary-only routing
listen shard_04_primary
    bind *:5404
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 2s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-shard04-a pg-shard04-a.internal:5432 check port 8008
    server pg-shard04-b pg-shard04-b.internal:5432 check port 8008
    server pg-shard04-c pg-shard04-c.internal:5432 check port 8008

The on-marked-down shutdown-sessions directive matters: it kills established connections to a demoted node so pooled sessions do not keep writing to a read-only ex-primary. Building this tier out fully is covered in building a custom query router with HAProxy.

Callback-driven shard map updates. Patroni fires on_role_change callbacks on every transition. The callback rewrites the authoritative shard map — a database table, a Consul key, or a pgbouncer config — and reloads consumers. This is the right pattern when routing lives in application-level sharding logic rather than a proxy.

DCS watch. A small sidecar watches the etcd leader keys directly (etcdctl watch --prefix /service/) and regenerates router configuration on change. This removes the callback’s dependency on the database host being healthy enough to run scripts, at the cost of one more moving part.

Whichever pattern you choose, make it idempotent and converging: the router state should be recomputable from the DCS at any time, so a missed event heals on the next reconciliation pass rather than persisting until a human notices.

6. Cascading failure protection

On a sharded fleet, the dangerous scenario is correlated failure: an availability zone dies and eight shards need failover at once, or the etcd cluster itself loses quorum. Guard against three cascade paths:

• DCS quorum loss. By default, a Patroni primary that cannot reach etcd demotes itself — safe for one shard, catastrophic when every shard demotes simultaneously because etcd is down. Patroni’s DCS failsafe mode (failsafe_mode: true in the DCS config) lets an existing primary keep running if it can still reach all its own replicas’ REST APIs, converting a total-fleet write outage into a temporary loss of failover capability.
• Promotion stampedes. Stagger recovery: orchestrator’s RecoveryPeriodBlockSeconds and Patroni’s per-scope elections both bound work per shard, but your callback layer (shard-map rewrites, pgbouncer reloads) is shared. Rate-limit it and queue role-change events rather than executing eight config rewrites concurrently.
• Reconnect thundering herd. After promotion, thousands of application connections re-establish at once, and a cold new primary (empty shared buffers, cold OS cache) faces its worst latency at its moment of peak connection churn. Enforce jittered backoff in connection pools and keep default_pool_size caps in pgbouncer so the new primary is not flattened in its first 60 seconds.

Also protect the failover decision from load-induced false positives: a replica saturated by cross-shard rebalancing traffic may lag past maximum_lag_on_failover on every shard at once, leaving no eligible candidates exactly when you need one. Coordinate large data movements — see zero-downtime resharding workflows — with failover thresholds in mind.

7. Failover drills

Untested failover automation is a rumor, not a capability. Run two kinds of drills on a schedule:

# Planned switchover drill — one shard per week, rotating through the fleet
patronictl -c /etc/patroni/patroni.yml switchover shard_04 \
  --leader pg-shard04-a --candidate pg-shard04-b --force

# Unplanned failure drill (staging, then production game days)
ssh pg-shard04-a 'sudo systemctl kill --signal=SIGKILL postgresql'

Measure four numbers every drill and trend them: detection time (primary death to leader-key expiry), promotion time (expiry to pg_promote completing), propagation time (promotion to router serving the new primary), and client recovery time (first successful application write). Alert if any drill exceeds its budget — drift here means a config regression somewhere in the chain. Rotate drills across all shards; per-shard differences in data volume, replica hardware, and WAL churn produce genuinely different failover behavior, and the shard you never drilled is the one that will misbehave.

Configuration reference

Operational contrast

The three guides in this section form a detection-to-action pipeline, and it matters which tool owns which problem. Partition skew detection and monitoring addresses chronic imbalance: one shard growing hotter or larger than its siblings over days and weeks. Its remedy is rebalancing or key redesign — failing over a skewed shard changes nothing, because the new primary inherits the same hot data. Replication lag and capacity alerting addresses degradation: replicas falling behind, disks filling, connection pools saturating over minutes and hours. It feeds this guide directly — lag alerts are your early warning that maximum_lag_on_failover would block promotion, meaning a shard is silently running without failover cover.

Failover orchestration, by contrast, handles discrete failure on a timescale of seconds, and it is the only one of the three that takes destructive automated action. That asymmetry drives the design bias: skew and lag monitoring can afford sensitive thresholds and noisy alerts because a false positive costs an engineer a glance at a dashboard, while failover must be tuned conservatively because a false positive kills connections, promotes a replica, and rewrites the shard map fleet-wide.

Failure modes

Common mistakes

• Tuning ttl for the demo, not the fleet. A 10-second TTL looks impressively fast in a test environment and then demotes twelve healthy primaries during the first real network wobble. Detection aggressiveness must be divided by the number of shards that share each failure domain.
• Automating promotion but not propagation. If updating pgbouncer configs or the application shard map still requires a deploy or a human, your real recovery time is the human’s response time and the database automation is theater. The router path deserves the same testing rigor as the election.
• Skipping fencing because “etcd handles it.” The DCS prevents two nodes from holding the lease; it does not stop a frozen ex-primary’s PostgreSQL from accepting writes from clients with cached connections. Watchdog plus shutdown-sessions at the router closes the gap the DCS cannot.
• Drilling switchover and calling failover tested. A clean switchover exercises none of the ugly paths: lease expiry, lag-gated candidate selection, pg_rewind on the returning node, callback execution during partial outage. Kill processes and partition networks in staging regularly, and run production game days on low-traffic shards.

FAQ

Related

• Automating PostgreSQL Shard Failover with Patroni — hands-on walkthrough building the Patroni + etcd setup for one shard, including callbacks and threshold tuning
• Partition Monitoring & Failover Automation — parent overview tying failover automation to skew detection and capacity alerting
• Replication Lag & Capacity Alerting — the lag signals that determine whether a replica is even eligible to be promoted
• Proxy Routing Architectures — the routing tier that must converge on every new primary for failover to be complete