Automating PostgreSQL Shard Failover with Patroni

This walkthrough sets up Patroni over etcd for a single PostgreSQL shard — one primary, two replicas — so the shard promotes a replica automatically when its primary dies, implementing the automated failover orchestration pattern from the Partition Monitoring & Failover Automation section. By the end you will have a self-healing shard, tuned detection thresholds, a passed kill test, and a callback that updates your application shard map on every role change.

Prerequisites

Step 1 — Verify the etcd endpoints

Patroni stores the shard’s leader key, member list, and dynamic configuration in etcd, so a healthy DCS is the foundation everything else stands on. Confirm quorum and per-endpoint health from one of the database hosts before touching any Patroni config:

export ETCDCTL_API=3
ENDPOINTS=etcd-1.internal:2379,etcd-2.internal:2379,etcd-3.internal:2379

etcdctl --endpoints=$ENDPOINTS endpoint health
# etcd-1.internal:2379 is healthy: successfully committed proposal: took = 4.1ms
# etcd-2.internal:2379 is healthy: successfully committed proposal: took = 5.3ms
# etcd-3.internal:2379 is healthy: successfully committed proposal: took = 4.8ms

etcdctl --endpoints=$ENDPOINTS endpoint status -w table   # exactly one IS LEADER=true

Operational note: Run this check from the database hosts, not your workstation — you are validating the exact network path Patroni will use for lease renewals. A firewall rule that only breaks host-to-etcd traffic would otherwise stay invisible until it demotes your primary in production.

SRE tip: Alert on round-trip times in this output. Lease renewals that normally take 5 ms but occasionally spike past retry_timeout are the classic precursor to spurious demotions; trending etcd latency gives you weeks of warning.

Step 2 — Write patroni.yml for the first node

Each node gets a patroni.yml differing only in name, connect_address, and listen values. The scope names this shard’s Patroni cluster and namespaces its keys in etcd, so every shard in your fleet uses a distinct scope against the same etcd cluster:

# /etc/patroni/patroni.yml — node pg-shard04-a
scope: shard_04
namespace: /service/
name: pg-shard04-a

restapi:
  listen: 0.0.0.0:8008
  connect_address: pg-shard04-a.internal:8008

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

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        max_wal_senders: 10
        max_replication_slots: 10
        hot_standby: "on"
  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: pg-shard04-a.internal:5432
  data_dir: /var/lib/postgresql/16/shard_04
  authentication:
    superuser: {username: postgres, password: "s3cr3t-pg"}
    replication: {username: replicator, password: "s3cr3t-repl"}

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: true

Operational note: Everything under bootstrap.dcs is written to etcd only once, when the first node initializes the shard. After that, etcd is authoritative — editing ttl or maximum_lag_on_failover in the YAML file does nothing on an existing shard. Change them with patronictl edit-config (Step 4) instead.

DBA tip: Enable data-checksums at initdb time here; it cannot be turned on later without a full rewrite, and checksums are how you distinguish “replica diverged” from “replica has silent disk corruption” during failover forensics. Set tags.nofailover: true only on nodes that must never be promoted, such as a dedicated reporting replica.

Step 3 — Start Patroni and bring up the replicas

Run Patroni under systemd so it restarts with the host. Start node a first — it acquires the leader key and runs initdb — then start b and c, which discover the leader in etcd and clone themselves automatically with pg_basebackup:

sudo tee /etc/systemd/system/patroni.service > /dev/null <<'EOF'
[Unit]
Description=Patroni (shard_04)
After=network-online.target

[Service]
User=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
KillMode=process
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable --now patroni    # run on a, then b, then c

Operational note: KillMode=process is deliberate: it makes systemd stop only the Patroni supervisor and leave the PostgreSQL child running, so a systemctl restart patroni for a config tweak does not bounce the database or trigger an election.

SRE tip: Watch the first replica clone with journalctl -u patroni -f. On a shard of real size, pg_basebackup takes time, and starting all three nodes simultaneously means two concurrent base backups off a brand-new primary — stagger them on large shards to avoid saturating its disk and network.

Step 4 — Tune failover thresholds

The defaults detect a dead primary in at most 30 seconds (ttl) and refuse to promote any replica more than 1 MiB of WAL behind. Adjust the live values through the DCS, keeping the invariant ttl >= loop_wait + 2 * retry_timeout:

patronictl -c /etc/patroni/patroni.yml edit-config shard_04 --force \
  -s ttl=30 \
  -s loop_wait=10 \
  -s retry_timeout=10 \
  -s maximum_lag_on_failover=1048576 \
  -s postgresql.parameters.wal_keep_size=2GB

patronictl -c /etc/patroni/patroni.yml show-config shard_04

Operational note: maximum_lag_on_failover is your data-loss ceiling under asynchronous replication: a failover may lose up to that much WAL. But setting it too low interacts badly with real replica lag — if both replicas routinely lag past the threshold, the shard silently has no eligible candidate. Track eligibility with the lag signals described in replication lag and capacity alerting.

SRE tip: Resist tuning ttl down aggressively on a sharded fleet. Detection time improvements apply per shard, but false-positive demotions during a transient etcd or network stall apply to every shard sharing that failure domain simultaneously. 30 seconds is a sound production default; earn anything lower with months of clean etcd latency data.

Step 5 — Test failover with a controlled kill

Simulate a hard primary crash — not a clean shutdown — because SIGKILL exercises lease expiry, lag-gated election, and timeline switching the way a real failure does:

# On the current leader (pg-shard04-a):
sudo systemctl kill --signal=SIGKILL --kill-whom=all patroni

# From another node, watch the election:
watch -n 2 patronictl -c /etc/patroni/patroni.yml list shard_04

# When done, restart the killed node; it should rejoin as a replica:
sudo systemctl start patroni

Within one TTL window the leader key expires, the healthier of b/c wins the election, promotes itself, and the member list shows a new leader on a new timeline. When you restart node a, pg_rewind realigns it to the new timeline and it reappears as a streaming replica.

Operational note: Time each phase with timestamps from journalctl -u patroni: kill to key expiry (should be ≤ ttl), expiry to promoted self to leader, and promotion to your application’s first successful write. That last number includes your router propagation path — if it dwarfs the others, your shard map update (Step 6) is the bottleneck, not the database.

SRE tip: Make this drill a scheduled, rotating exercise across all shards rather than a one-time setup test, and always drill through the full application path. A failover that succeeds at the database layer but strands application traffic on the old primary counts as a failed drill.

Step 6 — Wire a role-change callback into the shard map

Promotion only matters once the routing layer knows about it. Patroni’s on_role_change callback fires on every role transition with three arguments — the action, the node’s new role, and the scope — which is exactly enough to update an application shard-map table and reload pgbouncer:

#!/usr/bin/env bash
# /etc/patroni/on_role_change.sh <action> <role> <scope>
# Register in patroni.yml on every node:
#   postgresql:
#     callbacks:
#       on_role_change: /etc/patroni/on_role_change.sh
set -euo pipefail
ACTION="$1"; ROLE="$2"; SCOPE="$3"
SELF="pg-shard04-a.internal"          # templated per node by config management

if [[ "$ROLE" == "master" || "$ROLE" == "primary" ]]; then
  # 1. Point the authoritative shard map at this node (idempotent UPSERT)
  psql "host=shardmap-db.internal dbname=routing user=shardmap_writer" <<SQL
    INSERT INTO shard_map (shard, primary_host, primary_port, updated_at)
    VALUES ('${SCOPE}', '${SELF}', 5432, now())
    ON CONFLICT (shard) DO UPDATE
      SET primary_host = EXCLUDED.primary_host,
          primary_port = EXCLUDED.primary_port,
          updated_at   = now();
SQL
  # 2. Rewrite the pgbouncer entry for this shard and reload without dropping pools
  sed -i "s|^shard_04 = .*|shard_04 = host=${SELF} port=5432 dbname=app|" \
      /etc/pgbouncer/pgbouncer.ini
  psql "host=127.0.0.1 port=6432 dbname=pgbouncer user=pgbouncer_admin" -c "RELOAD;"
  logger -t patroni-callback "shard_map updated: ${SCOPE} -> ${SELF} (${ACTION})"
fi
exit 0

Deploy the script to all three nodes (each with its own SELF), make it executable, and add the callbacks block to patroni.yml before restarting Patroni node by node.

Operational note: The callback runs on every node whose role changes — the demoted ex-primary fires it too, with role replica. The role guard above ensures only the promotion rewrites routing state. Keep the script fast and non-blocking: Patroni runs callbacks asynchronously, but a callback that hangs on an unreachable shard-map database must not be able to wedge, hence set -euo pipefail and no retries inline.

SRE tip: Event-driven updates miss events — a callback can fail while the shard map database is briefly down. Pair the callback with a converging reconciler: a cron job that reads each shard’s /primary REST endpoint (or the etcd leader keys) every minute and repairs any shard-map row that disagrees. Alert when the reconciler makes a correction; that means a callback was lost.

Verification

Confirm steady state from any node:

patronictl -c /etc/patroni/patroni.yml list shard_04

Expected output after the Step 5 drill — node b leading on timeline 2, node a rejoined as a replica, lag near zero:

+ Cluster: shard_04 (7321459812345678901) -------+----+-----------+
| Member       | Host                 | Role    | State     | TL | Lag in MB |
+--------------+----------------------+---------+-----------+----+-----------+
| pg-shard04-a | pg-shard04-a.internal| Replica | streaming |  2 |         0 |
| pg-shard04-b | pg-shard04-b.internal| Leader  | running   |  2 |           |
| pg-shard04-c | pg-shard04-c.internal| Replica | streaming |  2 |         0 |
+--------------+----------------------+---------+-----------+----+-----------+

Then verify the routing side agrees with the DCS:

# REST API: 200 only from the leader
curl -s -o /dev/null -w "%{http_code}\n" http://pg-shard04-b.internal:8008/primary   # 200
curl -s -o /dev/null -w "%{http_code}\n" http://pg-shard04-a.internal:8008/primary   # 503

# Shard map row matches the leader
psql "host=shardmap-db.internal dbname=routing" \
  -c "SELECT shard, primary_host, updated_at FROM shard_map WHERE shard = 'shard_04';"

The shard_map row must name pg-shard04-b.internal with an updated_at within seconds of the promotion timestamp in the Patroni journal. If patronictl list and the shard map disagree, the callback path — not the failover — is broken.

Failure mode table

Failure mode Root cause SRE mitigation
Primary demotes itself with no failure (demoted self because DCS is not accessible) etcd latency spikes or a network stall exceeded retry_timeout, so the lease could not renew inside ttl Keep ttl >= loop_wait + 2 * retry_timeout; monitor etcd round-trip times from the database hosts and fix the network before lowering thresholds; enable DCS failsafe mode on multi-shard fleets
No leader after primary loss; replicas log not the healthiest node Both replicas lag beyond maximum_lag_on_failover, so neither is an eligible candidate Alert when replica lag approaches half the threshold; pause bulk loads and resharding jobs that saturate replication; if stuck, verify the least-lagged replica manually, then promote with patronictl failover
Failover succeeds but the application keeps writing to the old primary until pools recycle Callback failed or pgbouncer reload was missed, leaving a stale shard-map entry; old primary still accepts reads from cached connections Run the reconciler from Step 6 on a 60-second loop and alert on corrections; configure the router to health-check the /primary endpoint so stale backends eject even when config rewrites fail

FAQ

Does the on_role_change callback run on every node or only the new primary?

It runs on every node whose role changes, with that node’s new role passed as an argument. During a failover the promoted replica fires with role primary and the demoted or rewound ex-primary later fires with role replica. Guard your shard-map update so only the primary transition rewrites routing state, and make the script idempotent because Patroni may invoke it more than once for the same transition.

What happens to the old primary after the controlled kill test?

When the host comes back, Patroni compares the local timeline with the current leader’s. Because use_pg_rewind is enabled, it rewinds the data directory to the divergence point and starts the node as a streaming replica of the new primary, usually within seconds. Without pg_rewind, Patroni would need to re-clone the node with pg_basebackup, which on a large shard can take hours of reduced redundancy.

Can I lose committed transactions with this setup?

With asynchronous streaming replication, yes: transactions committed on the primary but not yet received by the promoted replica are lost, bounded by maximum_lag_on_failover (1 MiB of WAL in this walkthrough). If the shard carries data where that is unacceptable, enable synchronous_mode so a commit only returns after a standby confirms receipt; the cost is added write latency and blocked writes if all standbys are down.

Back to Automated Failover Orchestration