Patroni PostgreSQL High Availability and Failover

Set up Patroni automatic failover with health-checked routing and test it under load, so a dead primary costs seconds of errors and no lost writes.

Published

Applies to Patroni 3.0 and later with etcd, Consul or Kubernetes; PostgreSQL 13 and later

Patroni keeps one PostgreSQL primary per cluster and promotes a replica when that primary dies. Your app also has to find the new primary, drop its dead connections and retry without losing a write. The test at the end proves all of it on staging.

What happens when the primary dies

Patroni runs next to each Postgres node and stores a leader key in a distributed config store (DCS): etcd, Consul, ZooKeeper or the Kubernetes API. The primary renews the key every loop_wait seconds. The key expires after ttl seconds without a renewal.

The defaults are ttl: 30, loop_wait: 10 and retry_timeout: 10. The detection time depends on what failed:

  • The whole node dies. The key expires up to ttl seconds after the last renewal. On their next loop, the replicas race for the key, and the healthiest one promotes itself. Expect up to ttl plus one loop_wait with no primary.
  • Postgres crashes, Patroni survives. Patroni keeps the leader key and tries to restart Postgres in place. It waits primary_start_timeout, 300 seconds by default, before it gives up and lets a replica take over. Patroni’s docs put the worst case at loop_wait + primary_start_timeout + loop_wait.
  • The primary loses the DCS. Patroni can’t renew the key, so it demotes its own Postgres before the key expires. That prevents two primaries. It also means a DCS outage takes your writes down unless you turn on failsafe_mode.

A replica only joins the race if its lag is within maximum_lag_on_failover, 1048576 bytes (1 MiB) by default. If every replica is further behind, nobody promotes and the cluster stays without a primary.

During the switch, your app logs some of these. Search for each:

FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
FATAL:  the database system is shutting down
FATAL:  the database system is starting up
ERROR:  cannot execute INSERT in a read-only transaction

The last one is the dangerous one. The client still talks to the old primary after it rejoined as a replica, or reached a replica through stale DNS. Every write from that client fails until it reconnects somewhere else.

Async replication adds a quieter failure. A transaction the old primary committed but hadn’t shipped is gone after the promotion. Your app got a success for it, and no error ever shows up.

The failover setup

Patroni settings. bootstrap.dcs seeds the cluster-wide settings the first time the cluster starts. Change them later with patronictl edit-config. This file shows one node, pg-1, with etcd. Replace the addresses and passwords:

# /etc/patroni/patroni.yml
scope: app-pg
name: pg-1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.1.11:8008

etcd3:
  hosts: 10.0.0.21:2379,10.0.0.22:2379,10.0.0.23:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    primary_start_timeout: 300
    synchronous_mode: false
    failsafe_mode: true
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        wal_log_hints: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 1GB

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.1.11:5432
  data_dir: /var/lib/postgresql/data
  authentication:
    superuser:
      username: postgres
      password: change-me
    replication:
      username: replicator
      password: change-me
    rewind:
      username: rewind_user
      password: change-me

watchdog:
  mode: automatic
  device: /dev/watchdog

Each setting, and what goes wrong when it’s off:

  • ttl, loop_wait and retry_timeout must satisfy loop_wait + 2 * retry_timeout <= ttl. A lower ttl detects a dead node sooner. Too low, and a slow DCS response demotes a healthy primary.
  • maximum_lag_on_failover caps how much WAL a promoted replica may be missing. A higher value lets a lagging replica take over and lose more writes. A lower one can leave nobody eligible.
  • primary_start_timeout trades restart time against failover. With async replication, a restart in place keeps every committed transaction, so I keep the default there.
  • synchronous_mode: on makes the primary wait for one replica to confirm each commit. A failover then loses nothing acknowledged. Writes get slower by one network round trip. If no sync replica is left, Patroni drops back to async unless you set synchronous_mode_strict.
  • failsafe_mode: true keeps the primary running through a DCS outage, as long as it can still reach every other member over the REST API.
  • use_pg_rewind and wal_log_hints let the old primary rejoin as a replica without a full copy. Without them, it needs a fresh pg_basebackup.
  • use_slots keeps WAL on the primary until each replica has received it. Alert on stale slots, as in the postgres_exporter recipe.
  • watchdog resets the node if Patroni hangs while it still holds the key. It needs the softdog module or a hardware watchdog, and write access to /dev/watchdog for the Patroni user.

For data where a lost commit costs money, such as payments or orders, I run three nodes with synchronous_mode: on. A failover then can’t drop an acknowledged write, and one replica can still fail without blocking writes.

Routing. Clients need one address that reaches whichever node is primary. Patroni’s REST API answers GET /primary with 200 on the leader and 503 everywhere else, and GET /replica the other way round. HAProxy turns that into two ports:

# changed lines only, add to haproxy.cfg
listen postgres_primary
    bind *:5000
    mode tcp
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-1 10.0.1.11:5432 maxconn 500 check port 8008
    server pg-2 10.0.1.12:5432 maxconn 500 check port 8008
    server pg-3 10.0.1.13:5432 maxconn 500 check port 8008

listen postgres_replicas
    bind *:5001
    mode tcp
    balance roundrobin
    option httpchk GET /replica?lag=10MB
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-1 10.0.1.11:5432 maxconn 500 check port 8008
    server pg-2 10.0.1.12:5432 maxconn 500 check port 8008
    server pg-3 10.0.1.13:5432 maxconn 500 check port 8008

on-marked-down shutdown-sessions closes every open client connection to a node once its check fails. Without it, clients keep their sockets to the old primary and get the read-only error after it rejoins. inter 3s fall 3 marks a node down after about nine seconds of failed checks. The lag=10MB check drops replicas that fall too far behind from the read pool. Run two HAProxy instances behind a virtual IP or your cloud load balancer, so the router isn’t a single point of failure itself.

Without HAProxy. libpq takes several hosts and picks the writable one:

postgresql://app@pg-1:5432,pg-2:5432,pg-3:5432/app?target_session_attrs=read-write&connect_timeout=5

The PostgreSQL JDBC driver does the same with targetServerType=primary. This only helps on a new connection, so your pool still has to drop the dead ones.

Client settings. Most of the errors above reach users because the app holds pooled connections to a node that’s gone.

  • Set a connect timeout, connect_timeout=5 in libpq or connectTimeout in JDBC, so a dial to a dead host fails fast.
  • Turn on TCP keepalives, or set tcp_user_timeout, so a socket to a powered-off host doesn’t hang for minutes.
  • Give pooled connections a maximum lifetime and test them on checkout. HikariCP, SQLAlchemy’s pool_pre_ping and most other pools have a setting for both.
  • Retry a transaction once or twice on connection errors, with a short backoff. Only retry work that’s safe to repeat. A failed COMMIT may have succeeded, so make those writes idempotent with a unique key.

If PgBouncer sits in front, point it at HAProxy’s port 5000, and it re-dials the new primary on its own. The PgBouncer recipe covers its settings.

Managed Postgres. You don’t run Patroni on a managed service. Amazon RDS Multi-AZ, Aurora, Azure Database for PostgreSQL, Cloud SQL and OCI Database with PostgreSQL each run their own failover. The provider moves a DNS name to the new primary. Your client settings above matter more there, since a client that caches DNS or holds old sockets keeps failing. Java caches DNS for the JVM’s lifetime under some security managers, so set networkaddress.cache.ttl to a few seconds. Force a test failover with aws rds reboot-db-instance --force-failover, aws rds failover-db-cluster for Aurora, gcloud sql instances failover or az postgres flexible-server restart --failover Forced. Check your OCI version’s docs for its equivalent.

On Kubernetes, the Zalando Postgres Operator runs Patroni in its Spilo image, with the Kubernetes API as the DCS. Pair it with a PodDisruptionBudget so a node drain can’t take two members at once, as the pod disruption budget recipe explains.

Test the failover

Run this on staging first, with the same Patroni settings, HAProxy and app pool settings as production. Write down your targets before you start: the seconds of write errors you accept, and zero lost acknowledged writes.

Create a probe table, then write to it through HAProxy in a loop. Each psql call opens a new connection, the same way a recovering pool does:

CREATE TABLE failover_probe (n bigint PRIMARY KEY, at timestamptz DEFAULT now());
DSN="postgresql://app:secret@haproxy.internal:5000/app?connect_timeout=3"
i=0
while true; do
  i=$((i+1))
  if psql "$DSN" -qtAc "INSERT INTO failover_probe (n) VALUES ($i)" >/dev/null 2>&1; then
    echo "$(date +%H:%M:%S.%3N) ok $i"
  else
    echo "$(date +%H:%M:%S.%3N) fail $i"
  fi
  sleep 0.2
done | tee probe.log

Put your normal load on the cluster too, with your app’s load test or pgbench at a fixed rate. Then fail the primary, one method per run, from gentle to harsh:

  1. Planned switchover. patronictl -c /etc/patroni/patroni.yml switchover app-pg --leader pg-1 --candidate pg-2 --force
  2. Postgres crash. kill -9 the postmaster on the primary. Patroni restarts it in place, which tests your primary_start_timeout choice.
  3. Node loss. Power off the primary’s VM. systemctl stop patroni is a gentler version, since Patroni shuts Postgres down in order and releases the key.
  4. Network partition. Block the primary from etcd with a firewall rule. The primary must demote itself, and only one node may accept writes.

While it runs, watch the cluster and the router:

watch -n 1 patronictl -c /etc/patroni/patroni.yml list

Keep the HAProxy stats page open and tail the Patroni log on each node. On the new leader you should see promoted self to leader by acquiring session lock. Afterwards, patronictl history lists the switch with its timeline.

Then check the probe:

grep -c ' ok ' probe.log
psql "$DSN" -qtAc "SELECT count(*) FROM failover_probe"
awk '$2 == "fail"' probe.log | sed -n '1p;$p'

The first two numbers must match. A lower row count means Postgres lost writes it had acknowledged, which async replication allows. The first and last failed lines give your write outage window.

A clean failover meets all of these:

  • One node, and only one, shows as Leader in patronictl list at any time.
  • The write gap stays inside the target you wrote down.
  • Every acknowledged write is in the table.
  • Your app logs no read-only transaction errors once the new leader is up.
  • The old primary rejoins as a replica, through pg_rewind, without a manual rebuild.

If the app takes much longer to recover than the probe, its pool holds dead connections. Fix the pool lifetime and checkout test before you tune Patroni.

Keep it healthy

Prometheus scrapes Patroni’s /metrics on the REST API port, 8008 here. Add postgres_exporter for replication bytes. These rules catch a missing leader, a split brain, paused automation, a failover and replicas that can’t take over:

groups:
  - name: patroni
    rules:
      - alert: PatroniNoLeader
        expr: max by (scope) (patroni_primary) == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "Patroni cluster {{ $labels.scope }} has no primary"
          description: "Writes fail. Run patronictl list. If every replica is past maximum_lag_on_failover, promote the most current one with patronictl failover after checking its lag."
      - alert: PatroniMultipleLeaders
        expr: sum by (scope) (patroni_primary) > 1
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "Patroni cluster {{ $labels.scope }} reports more than one primary"
          description: "Possible split brain. Check that every node reaches the same DCS, stop the node that doesn't hold the leader key, and check HAProxy sends writes to one node."
      - alert: PatroniClusterUnlocked
        expr: max by (scope) (patroni_cluster_unlocked) == 1
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "No member of {{ $labels.scope }} holds the leader lock"
          description: "Check etcd or Consul health first, then the Patroni log on each node for errors renewing the key."
      - alert: PatroniPaused
        expr: max by (scope) (patroni_is_paused) == 1
        for: 1h
        labels:
          severity: warning
        annotations:
          summary: "Automatic failover is paused on {{ $labels.scope }}"
          description: "Someone ran patronictl pause and didn't resume. If maintenance is over, run patronictl resume, or the next primary failure needs a manual failover."
      - alert: PatroniFailoverHappened
        expr: max by (scope) (patroni_postgres_timeline) > max by (scope) (patroni_postgres_timeline offset 10m)
        for: 0m
        labels:
          severity: warning
        annotations:
          summary: "The primary of {{ $labels.scope }} changed in the last 10 minutes"
          description: "Run patronictl history and read the Patroni log on the old primary to find the cause. Check the old primary rejoined as a replica."
      - alert: PatroniReplicaNotStreaming
        expr: patroni_replica == 1 and on (scope, name) patroni_postgres_streaming == 0
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Replica {{ $labels.name }} in {{ $labels.scope }} isn't streaming from the primary"
          description: "It falls behind and drops out of the failover race. Check the replica's Postgres log for connection or WAL errors, and the primary's slots."
      - alert: PostgresReplicaPastFailoverLag
        expr: max by (instance, application_name) (pg_stat_replication_pg_wal_lsn_diff) > 1048576
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Standby {{ $labels.application_name }} is past maximum_lag_on_failover"
          description: "Patroni won't promote this standby while it stays this far behind. Check disk and network on the standby; if all standbys show this, failover is at risk."

PatroniFailoverHappened uses for: 0m because a failover is finished by the time it fires. Set the lag threshold in the last rule to the maximum_lag_on_failover you configured.

Roll back

Save the current dynamic config before you change it:

patronictl -c /etc/patroni/patroni.yml show-config > patroni-dcs-before.yaml

Change one setting at a time with patronictl edit-config --set, for example --set ttl=30. To roll back, apply the saved values the same way. Patroni pushes the change to every member within a loop, with no restart for these keys.

If a change causes false failovers or write errors, roll it back now. A ttl set too low is the usual cause. Revert other changes, such as turning synchronous_mode back off, in a quiet hour. That one changes the commit path under load. During a risky maintenance window, patronictl pause stops automatic failover, and patronictl resume turns it back on. The PatroniPaused rule reminds you if you forget the second command.

More recipes

Further reading