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
ttlseconds after the last renewal. On their next loop, the replicas race for the key, and the healthiest one promotes itself. Expect up tottlplus oneloop_waitwith 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 atloop_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_waitandretry_timeoutmust satisfyloop_wait + 2 * retry_timeout <= ttl. A lowerttldetects a dead node sooner. Too low, and a slow DCS response demotes a healthy primary.maximum_lag_on_failovercaps 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_timeouttrades restart time against failover. With async replication, a restart in place keeps every committed transaction, so I keep the default there.synchronous_mode: onmakes 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 setsynchronous_mode_strict.failsafe_mode: truekeeps the primary running through a DCS outage, as long as it can still reach every other member over the REST API.use_pg_rewindandwal_log_hintslet the old primary rejoin as a replica without a full copy. Without them, it needs a freshpg_basebackup.use_slotskeeps WAL on the primary until each replica has received it. Alert on stale slots, as in the postgres_exporter recipe.watchdogresets the node if Patroni hangs while it still holds the key. It needs thesoftdogmodule or a hardware watchdog, and write access to/dev/watchdogfor 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=5in libpq orconnectTimeoutin 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_pingand 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
COMMITmay 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:
- Planned switchover.
patronictl -c /etc/patroni/patroni.yml switchover app-pg --leader pg-1 --candidate pg-2 --force - Postgres crash.
kill -9the postmaster on the primary. Patroni restarts it in place, which tests yourprimary_start_timeoutchoice. - Node loss. Power off the primary’s VM.
systemctl stop patroniis a gentler version, since Patroni shuts Postgres down in order and releases the key. - 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
Leaderinpatronictl listat 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 transactionerrors 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
- PostgreSQL Autovacuum Tuning for Bloat and Wraparound
Give autovacuum the budget and per-table thresholds your write rate needs, and clear what blocks it, so tables stop bloating and wraparound never stops writes.
- PostgreSQL Load Testing with pgbench
Use pgbench for PostgreSQL load testing at fixed, rising rates with your own queries, so you find the database's breaking point before your users do.
- Monitor PostgreSQL with postgres_exporter
Run postgres_exporter with the right collectors and alert on PostgreSQL replication lag, connections and stuck transactions in Prometheus before users notice.
- PostgreSQL Connection Pooling with PgBouncer
PgBouncer in transaction mode lets hundreds of app connections share a small pool of Postgres connections, so scaling out pods stops hitting max_connections.