The cluster has three PostgreSQL nodes managed by Patroni, one etcd node holding the leader lock, and HAProxy giving the application one address to connect to. Everything runs in Docker on a single machine.
Every timestamp, log line, WAL position and row count in this document comes from a real test run. Nothing has been made up or filled in for illustration. Where a number is specific to this environment, the text says so.
Overview
The environment is built from four components, each with one job.
PostgreSQL 16.4 is the database engine. It stores the data, runs the queries, and ships its write ahead log to the replicas.
Patroni 3.3.2 is the high availability manager. It runs next to PostgreSQL on every node and decides which node is the primary. It starts PostgreSQL, promotes it, demotes it, and rebuilds replicas when needed.
etcd 3.5.15 is the distributed configuration store. Patroni keeps the leader lock and the cluster state there.
HAProxy 2.9 is the connection router. It gives clients one stable address and sends them to whichever node is currently the primary.
Everything runs under Docker Compose on a single Docker host.
Two honest caveats about that evidence.
The captured HAProxy log has no timestamps, because HAProxy writes these particular messages without them. The order of events and the exact reason codes are therefore verified, but the few seconds attributed to HAProxy noticing the change are derived from the configured three second check interval rather than measured.
The moment the primary container was stopped, given below as 14:51:58, comes from the command output at the time. It is the one figure in the failover timeline that is not in a log file. Everything after it is.
The scripts and the captured run are not an exact match
The failover script in the repository writes a marker row whose text ends with a timestamp, but the captured row has no timestamp in it. The recovery script writes a marker row of its own, and no such row exists in the captured table at all. The row that does exist for the replica test is not written by any script in the repository. On top of that, the state capture script writes sixteen files per snapshot, while the committed snapshots contain ten, one of which no script produces.
The conclusion is that the scripts were revised after this run. The captured output is genuine, and it agrees with itself everywhere it can be cross checked. What cannot be claimed is that running these scripts today produces exactly the files shown here.
What the test showed
| Question | Result from this run |
| Did the cluster fail over on its own? | Yes. Node pg-node-1 promoted itself with nobody touching it. |
| How long until a new primary existed? | About 33 seconds. Thirty of those seconds were the leader lock timeout. |
| How long until write traffic followed it? | About 36 seconds from the moment the primary was stopped. |
| Did the application connection string change? | No. It stayed as port 5000 on localhost throughout. |
| Was any committed data lost? | No. Every row survived. The PostgreSQL timeline moved from 1 to 2. |
| Did the failed primary rejoin? | Yes, in about 1.5 seconds, as a replica with zero lag. It did not need pg_rewind, and Section 14 explains why. |
| Did losing a replica cause a failover? | No, and writes carried on without a pause. |
What this document covers
- Which component solves which problem, and which problems each component does not solve.
- Why the leader lock is kept in etcd rather than inside PostgreSQL.
- Why a healthy HAProxy dashboard is supposed to show some servers as DOWN.
- What happens to connections that are already open when a failover starts. The honest answer catches people out.
- When a failed primary needs pg_rewind to rejoin, and when it does not. In this run it did not, which is the more common case than the write ups suggest.
Who this is for
Anyone who runs PostgreSQL and has been asked what happens if the database goes down. You need to be comfortable with Docker, a terminal and basic SQL. No previous Patroni experience is needed.
An important limit on scope
This is a laboratory build. Five containers on one Docker host, with a single etcd node, is a good way to learn the mechanics.
Architecture
This section covers the components, the ports, and the paths between them. It is worth reading before the operational sections.
The problem this architecture solves
This architecture removes that single point of failure. Several copies of the database stay in step with each other. If the primary stops, one of the copies takes over on its own, with no manual work and no change to the connection string on the application side.
There are four separate problems to solve here:
- Keeping more than one copy of the data. This is done by PostgreSQL streaming replication.
- Noticing that the primary has died, and choosing a replacement. This is done by Patroni together with etcd.
- Actually promoting the chosen replica. This is done by Patroni.
- Sending application traffic to whichever node is currently the primary. This is done by HAProxy.
Each component handles one of these. There is no overlap, which is useful when something breaks, because the split tells you where to look.
Component overview
PostgreSQL, the database engine
PostgreSQL is the database itself: tables, indexes, data, SQL processing, transactions and constraints. It runs on all three nodes, but only one node accepts writes at any moment. The other two are read only replicas.
What PostgreSQL does here:
- Stores the application data.
- Processes SQL from the application.
- Maintains the write ahead log, which is a sequential record of every change made to the database.
- Sends those log records to the replicas over streaming replication. This is how the replicas keep up.
- On a replica, replays incoming log records so the same changes are applied locally.
- Runs replicas in hot standby mode, which means they can answer read only queries while they are still receiving replication data.
What PostgreSQL does not do:
- It does not decide which node is the primary. It has no idea that a cluster exists.
- It does not fail over. If the primary dies, PostgreSQL on a replica simply waits for more log data. It will not promote itself.
- It does not route connections. It listens on port 5432 and serves whatever connects.
| Port | Protocol | Purpose |
| 5432 | PostgreSQL wire protocol over TCP | Database connections |
Some PostgreSQL terms that appear throughout this document:
Write ahead log, usually shortened to WAL in the tools themselves. Every insert, update, delete and schema change is written to this log before it is applied to the data files. Replication is built on top of it, because a replica receives and replays exactly the same records.
Timeline. A counter that increases by one every time a replica is promoted. Timeline 1 is the original primary. Timeline 2 means one promotion has happened. This test moved from timeline 1 to timeline 2.
Log sequence number. A position in the log stream. Comparing the sent, written, flushed and replayed positions between primary and replica tells you how far behind a replica is.
The pg_is_in_recovery function. Returns true on a replica and false on the primary. This is how you check a node’s role from SQL.
The pg_stat_replication view. A view on the primary that lists every connected replica, its state, and how far behind it is.
The pg_rewind tool. Rewinds a former primary’s data directory back to the point before it diverged from the current primary. This lets a failed primary rejoin as a replica without copying the entire database again.
Patroni, the high availability manager
Patroni is a Python program that runs next to PostgreSQL on every node. It stores no data and runs no SQL of its own. Its job is to manage the PostgreSQL instance and to coordinate with the other nodes through etcd.
What Patroni does:
First time setup. When the cluster starts for the first time, one Patroni instance wins a lock in etcd and creates the PostgreSQL data directory. The other instances see that a leader already exists, copy the leader’s data with a base backup, and start PostgreSQL as replicas.
Leader lock management. The current leader has to renew its lock in etcd every ten seconds. If it fails to renew before the thirty second timeout expires, the lock becomes available to the other nodes.
Health monitoring. Patroni watches the local PostgreSQL process. If PostgreSQL stops, Patroni tries to restart it. If it cannot, Patroni gives up the leader lock.
Election and promotion. When the leader lock expires, the surviving Patroni instances race to take it. The winner promotes its local PostgreSQL from replica to primary.
Demotion and recovery. When a former primary comes back, Patroni sees that another node holds the lock. It runs pg_rewind to bring the local data directory back in line, then starts PostgreSQL as a replica of the new leader.
A small HTTP API. Patroni answers on port 8008 and reports the node’s current role. HAProxy uses this for health checks.
Configuration management. Patroni writes the PostgreSQL configuration files, the host based authentication file, and the standby signal files. You do not edit those by hand, because Patroni will overwrite them.
What Patroni does not do:
- It does not store application data. It is a management process, not a database.
- It does not route client connections. Applications never connect to Patroni.
- It does not provide the consensus itself. It relies on etcd for that.
| Port | Protocol | Purpose |
| 8008 | HTTP | Health check endpoint for HAProxy, and cluster status queries |
The API endpoints that matter in this build:
| Endpoint | Returns 200 when | Returns 503 when | Used by |
| GET /primary | This node is the primary | This node is a replica | The HAProxy primary backend |
| GET /replica | This node is a replica | This node is the primary | The HAProxy replica backend |
| GET /health | PostgreSQL is running and healthy | PostgreSQL is down | The Docker health check |
| GET /patroni | Always, returning full node state | Not applicable | Diagnostics |
The Patroni settings used here:
| Setting | Value | What it means |
| scope | pg-ha-cluster | The cluster name. Every Patroni instance with the same scope belongs to the same cluster. |
| name | pg-node-1, pg-node-2, pg-node-3 | A unique name for each node. It must differ on every node. |
| namespace | /service/ | The key prefix Patroni uses inside etcd. |
| ttl | 30 seconds | How long the leader lock stays valid. If the leader does not renew inside this window, the lock expires and other nodes may take it. |
| loop_wait | 10 seconds | How often the Patroni main loop runs. Each pass checks PostgreSQL, renews the lock if this node is the leader, and looks for changes in etcd. |
| retry_timeout | 10 seconds | How long Patroni keeps retrying etcd and PostgreSQL operations before giving up. |
| maximum_lag_on_failover | 1048576 bytes, which is 1 MB | A replica must be within this many bytes of the primary to be allowed to take over. This stops a badly lagging replica from being promoted and losing data. |
| use_pg_rewind | True | When a former primary rejoins, use pg_rewind instead of a full base backup. Recovery is far faster. |
| use_slots | True | Use replication slots, so the primary does not throw away log segments that a replica still needs. |
etcd, the distributed configuration store
etcd is a key value store with strong consistency guarantees. In this build it answers one question that matters more than any other: which node is the leader?
Patroni nodes do not talk to each other to elect a leader. They all talk to etcd instead. etcd guarantees that only one node can hold the leader lock at a time, because creating the key is an atomic operation.
What Patroni keeps in etcd, under the key prefix for this cluster:
| Key | Content | Purpose |
| leader | The name of the current leader | The leader lock. Whichever node created this key is the primary. It expires after the timeout. |
| initialize | A cluster initialisation token | Stops two nodes from creating a data directory at the same time during first startup. |
| config | Cluster wide PostgreSQL configuration | Shared settings, so all nodes agree. |
| members/pg-node-1 | Connection details, state, timeline and log position for that node | Each node registers itself so the others know how to reach it. |
| members/pg-node-2 | The same, for node 2 | |
| members/pg-node-3 | The same, for node 3 |
How the leader lock actually works:
- A Patroni node starts and checks etcd for an existing leader key.
- If there is no leader key, or the old one has expired, the node tries to create one with its own name and a thirty second lifetime.
- etcd allows only one of those creates to succeed. The node that wins becomes the leader.
- The leader has to renew the key before it expires. It renews every ten seconds, so it gets three attempts inside the thirty second window.
- If the leader stops renewing, because it crashed or lost the network or PostgreSQL died, the key expires at the thirty second mark.
- The other nodes notice the missing key on their next pass through the loop and race to create a new one.
There is one more safety behaviour worth knowing. If the current leader loses its lock, for example because it can no longer reach etcd, Patroni on that node demotes PostgreSQL straight away. It will not keep accepting writes without a valid lock. That is what stops two nodes from both believing they are the primary.
Why production uses an odd number of etcd nodes. etcd uses the Raft consensus algorithm, which needs a majority of nodes to agree before any write is accepted. Three etcd nodes survive one failure. Five survive two. An even number buys you nothing, because four nodes still only survive one failure, the majority being three either way. A single etcd node survives nothing at all. If it fails, no election can happen and the PostgreSQL cluster is frozen in whatever state it was in. This lab uses one etcd node for simplicity and accepts that limit.
| Port | Protocol | Purpose |
| 2379 | HTTP | Client API. This is where Patroni connects. |
| 2380 | HTTP | Peer API, for etcd to etcd traffic. Unused with a single node. |
HAProxy, the connection router
HAProxy is a TCP load balancer. It gives the application one address that never changes: port 5000 for writes and port 5001 for reads. HAProxy works out which PostgreSQL node is the right one and forwards the TCP connection there.
What HAProxy does:
- Listens on port 5000 for write traffic and port 5001 for read traffic.
- Every three seconds, sends an HTTP health check to each node’s Patroni API on port 8008.
- For the primary backend it calls GET /primary. Only the current primary answers 200, so write traffic goes there.
- For the replica backend it calls GET /replica. Only replicas answer 200, and read traffic is spread across them.
- After a failover, the next health check finds that the old primary no longer answers 200 and a different node now does, so the routing is updated. In this test that took about three seconds.
- When HAProxy marks a server as down, it closes any TCP connections that were still open to that server.
What HAProxy does not do:
- It does not decide who the primary is. It only reports what Patroni tells it.
- It does not keep existing connections alive across a failover. Connections to the old primary are broken.
- It does not replicate data, manage PostgreSQL, or talk to etcd.
- It does not understand SQL. It forwards raw bytes. As far as PostgreSQL is concerned, the connection comes from HAProxy, not from the application.
| Port | Protocol | Reachable from the host | Purpose |
| 5000 | TCP, PostgreSQL wire protocol | Yes | The read and write endpoint |
| 5001 | TCP, PostgreSQL wire protocol | Yes | The read only endpoint |
| 7000 | HTTP | Yes | The HAProxy statistics page |
Reading the HAProxy status page without being alarmed. There are two separate backend pools, and each one health checks a different Patroni endpoint. A node marked DOWN in one pool is not broken. It simply is not playing the role that the pool is looking for.
In a healthy three node cluster you will always see one server up and two down in the primary pool, and two up and one down in the replica pool. The down entries are not faults. They are proof that role based routing is working.
Communication map
This diagram shows every path between the components, with the port numbers.
Every port in one table:
| Port | Listener | Protocol | Who connects to it | Reachable from the host |
| 5000 | HAProxy | TCP | Application or psql | Yes. This is the write endpoint. |
| 5001 | HAProxy | TCP | Application or psql | Yes. This is the read endpoint. |
| 7000 | HAProxy | HTTP | A browser | Yes |
| 5432 | PostgreSQL on each node | TCP | HAProxy, and the other PostgreSQL nodes for replication | No. Internal only. |
| 8008 | Patroni on each node | HTTP | HAProxy for health checks, and the Patroni command line tool | No. Internal only. |
| 2379 | etcd | HTTP | Patroni on all three nodes | No. Internal only. |
| 2380 | etcd | HTTP | Other etcd nodes. Unused here. | No. Internal only. |
Note that PostgreSQL port 5432 and the Patroni port 8008 are deliberately not published to the host. All client access goes through HAProxy.
What each node runs inside
Every database container runs two processes side by side. Patroni is the parent, and it starts and manages PostgreSQL.
When a container starts:
- The entrypoint script writes the Patroni configuration file, filling in the values that differ between nodes.
- Patroni starts as the main process.
- Patroni asks etcd whether a leader exists and whether the cluster has been initialised.
- If this is the first node, Patroni creates the data directory, starts PostgreSQL, and takes the leader lock.
- If another node is already the leader, Patroni copies that node’s data with a base backup and starts PostgreSQL as a replica.
- Patroni then enters its main loop, running every ten seconds.
How the data stays in step
Streaming replication is what keeps the replicas synchronised. It works at the level of the write ahead log, not at the level of SQL statements. A replica receives and replays exactly the same low level changes that the primary made.
This cluster uses asynchronous replication, which the tools report as a synchronisation state of async. That means:
- The primary commits a transaction and tells the client it succeeded straight away.
- The log records travel to the replicas in the background.
- There is a very short window, usually a few milliseconds, in which the primary has committed something the replicas have not received yet.
- If the primary is destroyed inside that window, that transaction is lost, because no replica ever saw it.
Synchronous replication is possible and Patroni supports it, but it adds latency to every write, because the primary has to wait for a replica to confirm before it can commit. This lab uses asynchronous replication for simplicity.
The health indicators to watch:
| Indicator | Meaning | Healthy value |
| sent position | How far the primary has sent to this replica | Matches the primary’s current position |
| write position | How far the replica has written to disk | Matches the sent position |
| flush position | How far the replica has flushed to disk | Matches the write position |
| replay position | How far the replica has applied to its data files | Matches the flush position |
| state | Connection state | The value streaming means healthy |
| sync state | Synchronisation mode | The value async in this build |
When all four positions match, the replication lag is zero.
The states the cluster moves through
This diagram shows the transitions that were actually observed during the test.
What this architecture does not protect against
This setup has real limits, and it is better to be clear about them.
| Situation | Protected | Why |
| One PostgreSQL node fails | Yes | Patroni promotes a replica |
| Application needs a stable address | Yes | HAProxy provides one |
| Loss of committed transactions | Mostly | Replicas are very close to real time, but asynchronous replication leaves a tiny window |
| All nodes fail together | No | Every container runs on one Docker host |
| etcd fails | No | There is only one etcd node, so no election can happen |
| Disk corruption | No | Corruption is replicated to the replicas along with everything else |
| Somebody drops a table by accident | No | The change is replicated to all nodes immediately |
| Data centre failure | No | Everything is on one machine |
| Network partition creating two primaries | Partly | The lock in etcd stops two primaries existing, because a leader that loses the lock demotes itself |
The important line in that table is the one about dropping a table. Replication is not a backup. Three synchronised copies of a mistake are still a mistake.