Tag: High Availability

High Availability for a Sharded PostgreSQL Cluster

Using Patroni to make a Citus cluster survive losing a node

A sharded database spreads rows across machines so that no single machine has to hold everything. That solves scale. It does nothing at all for availability, and in fact makes it worse: where you once had one server to lose, you now have several, and losing any one of them takes away the slice of data it was holding.

This article is about closing that gap with Patroni, so the cluster both spreads its data and survives node loss.

What is designed here and what is measured

The two component pieces were built and tested separately. A three node Patroni cluster was broken on purpose and recovered on its own in about 34 seconds. A Citus cluster had a worker added and its shards rebalanced with no downtime and no rows lost. Figures from those runs are quoted below and are measured.

The combined architecture described here has not been built or measured. It is a design, assembled from the behaviour observed in those two labs and from the documented behaviour of Patroni’s Citus support. Where a number appears, the text says where it came from.

The Two Axes

Sharding and replication solve different problems, and the clearest way to see it is as a grid. Sharding divides the data sideways. Replication stacks copies underneath each division.

Sharding divides the data left to right, into shard ranges. Replication stacks copies top to bottom, within each range.

A sharded cluster with no replication is the top row on its own. Every column is a single point of failure. A replicated cluster with no sharding is one column on its own: it survives losing a node but can only ever hold what one machine holds.

Neither is a subset of the other, which is the point. Having one gives you no progress towards the other.

Capability	Replication alone	Sharding alone	Both
Survive losing a node	Yes	No	Yes
Hold more than one machine’s data	No	Yes	Yes
Spread write load	No	Yes	Yes
Stable address for the application	Yes	Only a fragile one	Yes
Add capacity while running	No	Yes	Yes

What Has to Be Protected

Three things, and it is worth separating them because they are protected differently.

The first two are solved the same way: run each of them as its own replicated cluster. A worker becomes a small Patroni cluster. The coordinator becomes another.

The coordinator is the one people skip, because it holds no table data and feels unimportant. It is the opposite. It is the only entry point and it holds the shard map, so losing it takes down the whole cluster even though every row is safe on the workers. Replicating it is the single most valuable change you can make, and it is cheap precisely because it stores no data.

The third item is the interesting one, and Section 3 is about it.

The One New Problem

The coordinator records the address of each worker. Replicating a worker means that address can change, because a promotion makes a different machine the writable one.

So every combined design has to answer one question: when a worker’s primary changes, how does the coordinator find out?

There are two established answers. Both work, and they differ in where the indirection sits.

Option A puts a load balancer in front of each worker group and records the proxy address. When the primary changes, the proxy follows it and the coordinator never notices. The appeal is that it reuses exactly the pattern the Patroni lab already proved.

Option B removes the proxy and has the thing that performs the failover also update the metadata. Patroni is what promotes the replica, so it already knows the new address and can write it in.

Consideration	Option A	Option B
Extra components per worker group	One load balancer	None
What the metadata records	A proxy address	The real primary address
Detection delay	Manager plus proxy health check	Manager only
Behaviour on planned switchover	Connections break	Traffic can be paused instead
Coupling between coordinator and workers	Loose	Shared cluster name and credentials

Option A suits you if worker groups are run by different teams or tooling, or if you already operate a proxy tier. Option B suits you if you want fewer moving parts.

The rest of this article works through Option B, because it has fewer components to describe. Where something applies only to that route, the text says so.

How Patroni Models a Sharded Cluster

Patroni has supported Citus natively since version 3.0. The model is that a sharded formation is a set of independent Patroni clusters sharing one cluster name, distinguished by a group number. The coordinator is group 0. Workers are groups 1, 2, 3 and so on.

Configuration on each node is two lines:

citus:
group: 0 # 0 for the coordinator, 1, 2, 3 and so on for workers
database: citus # must be identical on every node

The same values can be given as the environment variables PATRONI_CITUS_GROUP and PATRONI_CITUS_DATABASE.

That is the whole configuration. Everything else follows from it, and the important behaviour is this: the coordinator’s primary discovers each worker group’s current primary and registers it in the shard map itself, then keeps doing so as groups fail over.

That is what removes the need for a proxy in front of the workers. The metadata is maintained by the thing that performs the failover.

Two version requirements come with it. Citus 10.0 is the minimum, and 11.2 or later is recommended, because the near transparent switchover in Section 9 depends on it.

The Architecture

A three node coordinator group and three worker groups of three. This is the shape to aim for rather than the minimum.

Solid arrows are query traffic, dotted arrows are streaming replication inside a group. Every one of those twelve nodes runs its own Patroni process, and all of them talk to the same consensus store, which is left out here and shown in Section 6.

There is no load balancer in front of any worker group, because this is the Option B route. If you take Option A, add one per group between the coordinator and that group’s nodes and everything else stays the same. Either way the proxy in front of the coordinator stays, because that is the one place an external client needs an address that never changes.

Two levels of routing

The two layers answer different questions and neither knows about the other. HAProxy uses the Patroni health endpoint, exactly as measured in the Patroni lab. The coordinator uses the hash of the distribution column, exactly as measured in the sharding lab. Keeping them distinct is what makes the design tractable.

Where the Cluster State Lives

The grouping is visible in the key layout, and understanding it makes the failure behaviour obvious.

Three things follow.

Every group has its own leader lock. Group 1 electing a new leader has no effect on group 2, because those are different keys. Failures are contained to a group.

One recursive read fetches the whole formation. This is why the hierarchy exists. The coordinator needs every group’s current primary and can get it in a single request.

Reads are asymmetric. Only coordinator nodes read the whole tree, because only they need to discover workers. A worker reads its own subtree. Adding groups therefore does not multiply consensus traffic the way it might appear.

What Patroni Sets Up For You

Once the group setting is present, Patroni does all of this without being asked, which removes most of the manual setup a plain Citus cluster needs.

What it does	Why it matters
Adds the Citus library to the preload list	Otherwise the extension does not function
Creates the database and the extension	Otherwise this is a script looping over every node
Sets prepared transactions to twice the connection limit	This defaults to zero and makes the rebalancer fail with an unclear error
Sets synchronous replication to quorum mode	Changes the durability guarantee, discussed below
Corrects the local hostname setting	The default assumes localhost, which is often wrong
Writes the superuser credentials into the cross node authentication table	This is what lets nodes authenticate to each other
Registers each worker group’s primary in the shard map	Replaces manual node registration entirely
Keeps the shard map correct across every failover	This is the whole point of the design

Two of those defaults deserve attention.

Quorum synchronous replication is a real change. The Patroni lab ran asynchronous replication, which is why its results note that a transaction committed in the final milliseconds before a crash can be lost. Quorum mode means a commit is not acknowledged until enough replicas confirm it. That is a stronger guarantee and it costs write latency. This article cannot quote a number, because it was never measured and it depends heavily on the network between nodes. It is the first thing to measure in a real deployment.

The prepared transactions default closes a real trap. The sharding lab hit this: the setting defaults to zero, Citus needs it for two phase commit during shard moves, and the error does not obviously point at the cause.

What You Have to Get Right

Four things are your responsibility, and getting any of them wrong produces confusing failures rather than clear ones.

The cluster name must be identical on every node. Coordinator and workers share one name, and the group number distinguishes them. Giving a worker its own name produces a separate cluster that the coordinator will never discover, and nothing reports an error.

The extension must be installed on every node, including replicas that will never serve a query until promoted. A replica missing it is a promotion that fails at the worst possible moment.

Superuser credentials must match across all nodes, and host based authentication must permit access between them. This is how cross node queries authenticate. Those credentials get written into the cross node authentication table, so rotating the password later means updating that table too. A rotation that misses it breaks cross node communication at some later, apparently unrelated moment.

The Patroni API must be reachable from workers to the coordinator. Worker nodes notify the coordinator during a switchover, and that is the mechanism that makes the switch nearly transparent. If a firewall blocks it the cluster still works, but you quietly lose the transparency and get the abrupt behaviour instead.

That last one passes every test in a lab, where everything sits on one network, and then fails in a production environment with real segmentation. Verify it explicitly.

Losing a Worker

A worker group losing its primary is the failure this whole architecture exists to handle. The planned and unplanned cases behave very differently.

A planned switchover

The coordinator can hold traffic destined for one worker group while that group changes primary, then release it.

The sequence is from the worked example in the Patroni documentation, where the pause lasted a little under three seconds. That is their figure from their environment, not a measurement of this design. Your own depends on how long the old primary takes to stop and the new one to promote.

The important word is paused, not failed. Queries that would have gone to that group wait rather than erroring. Contrast that with the Patroni lab, where a failover broke every open connection and the application had to retry.

This matters because most primary changes in a healthy system are planned ones: patching, resizing, moving a node between hosts. If your maintenance windows are frequent, it is worth weighing when choosing between the two routes.

An unplanned failure

That transparency depends on the old primary being able to announce its intent. A machine that loses power announces nothing.

Then the sequence is the familiar one from the Patroni lab. The group’s lock stops being renewed, expires, the surviving replicas compete, one is promoted, and the shard map is updated so the coordinator learns the new address. Queries in flight to the dead worker fail and must be retried.

The Patroni lab measured about 34 seconds for that cycle with a 30 second lock timeout, and established a more useful lesson: the delay is not fixed. The lock expires 30 seconds after its last renewal, not 30 seconds after the failure, so where in the renewal cycle the machine died changes the answer. In that run the lock had about 25 seconds left when the node stopped.

The tuning trade-off carries over and now applies per group. A shorter timeout speeds every group’s recovery and raises the chance that a transient network problem is mistaken for a dead worker. A false failover on a worker group is a real outage for the shards it holds.

Losing the Coordinator

The coordinator group is an ordinary Patroni cluster, so it behaves exactly as measured in the Patroni lab.

Two things worth drawing out.

The workers are unaffected. They keep their data and their locks. The coordinator holds metadata, not table data, so what is being recovered is the map rather than the territory.

The new coordinator rebuilds its view from the consensus store. It reads the tree, finds each group’s primary and reconciles the shard map. This is the same discovery it does at startup, which is why the recovery needs no special handling.

What It Costs

Counted from the topology rather than measured:

Topology	PostgreSQL nodes	Consensus nodes	Total
Sharded, no replication	4	0	4
Replicated, no sharding	3	1	4
Both, minimum	8	3	11
Both, recommended	12	3	15

The multiplication is in the workers and cannot be avoided, because every group needs its own replicas. Adding a shard group later adds a whole group, not a node.

Two nodes per group tolerates one failure, and while that failure is unrepaired the group has no redundancy at all. The Patroni lab showed this exactly: after its failover and before the old primary rejoined, it ran for about seven minutes with a primary and a single replica, which is a working cluster with nothing left to fall back on. Three per group gives you room to lose one and still be safe.

Operating It

The day to day experience changes in one important way: the coordinator and each worker group are separate clusters and cannot generally be managed as one thing.

The Patroni command line reflects this. Listing the cluster shows every group at once with a group column, so one command gives the whole picture. Most other commands need to be told which group they apply to, and switchover and member removal require it explicitly, because doing either to the wrong group would be an unpleasant surprise.

The pattern to internalise is one view, many control surfaces. You can see everything in one place, and you act on one group at a time.

A few consequences worth planning for.

Rebalancing and failover should not overlap. A rebalance moves data using logical replication and takes a lock at cutover, while a failover changes which node is primary underneath it. The sharding lab established that a worker failing during a rebalance causes the rebalance to abort with the shard left on the source and no data lost, which is the safe outcome. Even so, running a long rebalance when failovers are likely invites aborted work. Prefer the background form of the rebalance so the job is not tied to your session.

Adding capacity is now two steps. Provision a whole new group, let the coordinator discover it, then rebalance. The rebalance itself is unchanged: the sharding lab measured four of twelve shard groups moving with no rows lost and no downtime.

Major version upgrades are per group. Each cluster is upgraded independently, so an upgrade becomes a sequence of operations rather than one. Restarts should go through Patroni rather than the service manager, so it does not interpret the restart as a failure.

Failure is no longer all or nothing. Three groups healthy and one unavailable means queries touching the missing group’s shards fail while queries confined to other groups succeed. A point lookup for one customer may work while an aggregate across all customers does not. Monitoring and application code both need to handle partial availability.

Notes

The building block is a group, not a node. A sharded, highly available cluster is a set of independent replicated clusters: one for the coordinator and one per shard range. Thinking in groups makes the failure behaviour, the node count and the operational model all easier to reason about.

Keeping the routing metadata correct is the central problem. Everything else is two familiar mechanisms side by side. The one genuinely new question is how the coordinator learns that a worker group has a new primary, and the two answers are a proxy per group or a manager that writes the metadata itself. Pick one deliberately.

Replicate the coordinator. It holds no table data, which is exactly why it gets forgotten, and it is the one failure that takes down everything.

Planned and unplanned failures differ a lot. A deliberate switchover can pause traffic rather than break it, making routine maintenance nearly invisible. An abrupt loss still costs a lock timeout and broken connections. Designing maintenance to use the first path is a real operational win.

Sharding and high availability stay separate concerns even when combined. One layer answers which coordinator is writable, the other answers which worker holds a row, and neither knows about the other.

Being honest about this

This is a design grounded in two tested labs and in documented behaviour, not a tested architecture. No cluster was built and no timing was captured. The one figure quoted for combined behaviour, the sub three second switchover pause, comes from published documentation rather than any run of ours.

The two most likely places for reality to differ are the write latency under quorum synchronous replication, which depends entirely on the network between nodes, and the behaviour when a coordinator failover and a worker failover overlap. Both deserve measuring before anyone depends on this.

Where to start

Build the coordinator group first and confirm it elects a leader. Then add a single worker group and check that the coordinator discovers it with no manual registration. That is the step that proves the whole design works. If the shard map populates itself, everything else is repetition. If it does not, the usual causes are a mismatched cluster name, missing credentials, or a blocked path from worker to coordinator.

September 2, 2026

PostgreSQL High Availability with Patroni, etcd and HAProxy

A single PostgreSQL server is one power cut away from an outage. This guide builds the usual open source answer to that problem, then breaks it on purpose to find out whether it really works.

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.

September 2, 2026