PostgreSQL Mastery lessons / Module 36: High availability

High availability and failover

Level 7: Operations and HA25 min

PostgreSQL provides replication and promotion; it does not decide on its own when to fail over. A cluster manager such as Patroni or a Kubernetes operator watches health, elects a new primary through a consensus store, fences the old one and repoints clients.

This lesson is interactive. Run every example on a real PostgreSQL 18 server in your browser, read EXPLAIN ANALYZE plans, and take the quiz.

Open the interactive lesson

You will learn to

Describe how Patroni and Kubernetes operators manage failover
Explain split-brain, fencing, and how clients find the new primary
Plan switchovers and rejoin an old primary with pg_rewind

conceptThe concept

Patroni runs next to each PostgreSQL instance and keeps a leader key in a distributed configuration store (etcd, Consul or ZooKeeper). The leader renews its key; if it expires, healthy replicas race to acquire it, and the winner (the one with the least lag that meets the rules) is promoted with pg_promote(). A node that loses the key demotes itself, which prevents two primaries (split-brain).

Kubernetes operators (CloudNativePG, Crunchy PGO, Zalando, Percona) do the same with the Kubernetes API as the consensus store, plus Services that always point to the current primary (-rw) and replicas (-ro). Clients reach the primary through a virtual IP, DNS, HAProxy or a libpq multi-host string with target_session_attrs=read-write.

After failover the old primary may have WAL the new primary never received. pg_rewind rewinds it to the divergence point so it can rejoin as a replica without a full re-clone. A planned switchover (patronictl switchover) demotes cleanly and loses nothing.

whyWhy it matters

Manual failover at 3 a.m. is slow and error-prone, and a split-brain (two primaries accepting writes) is much worse than a short outage. Automated, fenced failover turns hardware failures into a 10-30 second blip.

Use caseReal-world scenario

A three-node Patroni cluster across three zones with synchronous quorum replication: losing any one node or zone fails over automatically with no data loss, and the application reconnects through HAProxy.

Try itExamples

Run and edit these in the interactive lesson.

Patroni operations

patronictl -c /etc/patroni.yml list
# + Cluster: shop -------+---------+-----------+----+-----------+
# | Member | Host     | Role    | State     | TL | Lag in MB |
# | pg-a   | 10.0.1.1 | Leader  | running   |  7 |           |
# | pg-b   | 10.0.2.1 | Sync Standby | streaming | 7 | 0 |
# | pg-c   | 10.0.3.1 | Replica | streaming |  7 |         0 |

patronictl -c /etc/patroni.yml switchover --leader pg-a --candidate pg-b
patronictl -c /etc/patroni.yml edit-config   # change DCS-managed postgresql parameters

Client-side failover with libpq

# try hosts in order, connect only to the one that accepts writes
postgresql://app@pg-a:5432,pg-b:5432,pg-c:5432/shop?target_session_attrs=read-write&connect_timeout=3

# read-only traffic prefers standbys (PostgreSQL 14+ client)
postgresql://report@pg-a,pg-b,pg-c/shop?target_session_attrs=prefer-standby

Timeline and promotion state

SELECT pg_is_in_recovery() AS in_recovery,
       timeline_id,
       pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file
FROM pg_control_checkpoint();

Every promotion starts a new timeline; the WAL file name begins with the timeline id. pg_rewind uses timelines to find where an old primary diverged.

internalsUnder the hood

Failover time is detection (Patroni ttl, default 30 s, and loop_wait) plus promotion (seconds) plus client reconnection. Connection pools must detect dead connections quickly; TCP keepalives and short pool health-check intervals matter as much as the failover itself.

Avoid / preferDo this, not that

Avoid

-- a cron script promotes the standby when ping fails

Prefer

-- Patroni, or a Kubernetes operator, with a consensus store and fencing

Home-made failover cannot tell a dead primary from a network partition, so it promotes a second primary: split brain and divergent data.

Avoid

host=10.0.1.15 dbname=shop  # the current primary's IP

Prefer

host=pg-a,pg-b,pg-c dbname=shop target_session_attrs=read-write

After a failover the application keeps writing to the old address. Multi-host connection strings or a service name follow the primary.

PitfallsCommon mistakes

Building failover with a script that promotes a replica but does not fence the old primary. When the old primary comes back, you have two.
Running a two-node cluster with no third voter. Neither node can tell a network split from a crash.
Testing failover only in staging. Schedule regular switchovers in production so the path is exercised.

LimitsWhen not to use it

Two nodes without a third witness cannot fail over safely: neither side can know whether the other is dead or just unreachable. Use three members in the consensus store.

SecuritySecurity notes

Patroni's REST API and the etcd or Consul cluster control who is primary; protect them with TLS and authentication. Anyone who can write to the consensus store can trigger a failover.

ChallengeTry it yourself

Write the query a load balancer health check would use to decide whether this node accepts writes, including uptime.

Show one solution

One solution

SELECT NOT pg_is_in_recovery() AS accepts_writes,
       current_setting('default_transaction_read_only') AS default_read_only,
       now() - pg_postmaster_start_time() AS uptime;

InterviewQuestions you should be able to answer

How does Patroni prevent split-brain?

The primary must hold a leader lock (key with a TTL) in a consensus store such as etcd. It renews the key every loop; if it cannot reach the DCS or loses the key, it demotes itself to read-only. Replicas only promote after acquiring the expired key, which only one can do. Optional watchdog devices reboot a node that cannot demote in time. Quorum in the DCS (three or more members) is what makes the decision safe during network partitions.

What does pg_rewind do?

After a failover, the old primary may contain WAL that the new primary never received, so it cannot simply follow the new timeline. pg_rewind compares the two data directories from the point of divergence (using WAL and timeline history), copies only the changed blocks from the new primary, and lets the old primary restart as a standby without a full base backup. It requires wal_log_hints = on or data checksums.

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

← Replication in 17 and 18: failover slots, pg_createsubscriber and incremental backup PostgreSQL on Kubernetes: CloudNativePG, Crunchy PGO and Zalando →