{"id":40,"date":"2026-09-02T14:49:24","date_gmt":"2026-09-02T14:49:24","guid":{"rendered":"https:\/\/metaintelligencesystems.com\/blog\/?p=40"},"modified":"2026-09-02T14:53:02","modified_gmt":"2026-09-02T14:53:02","slug":"40-2","status":"publish","type":"post","link":"https:\/\/metaintelligencesystems.com\/blog\/40-2\/","title":{"rendered":"PostgreSQL High Availability with Patroni, etcd and HAProxy"},"content":{"rendered":"\n<h1 class=\"wp-block-heading\"><\/h1>\n\n\n\n<p class=\"wp-block-paragraph\"><a>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.<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Overview<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The environment is built from four components, each with one job.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>PostgreSQL 16.4<\/strong> is the database engine. It stores the data, runs the queries, and ships its write ahead log to the replicas.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Patroni 3.3.2<\/strong> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>etcd 3.5.15<\/strong> is the distributed configuration store. Patroni keeps the leader lock and the cluster state there.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>HAProxy 2.9<\/strong> is the connection router. It gives clients one stable address and sends them to whichever node is currently the primary.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Everything runs under Docker Compose on a single Docker host.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a><u>Two honest caveats about that evidence.<\/u><\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>The scripts and the captured run are not an exact match<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>What the test showed<\/a><\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Question<\/td><td>Result from this run<\/td><\/tr><\/thead><tbody><tr><td>Did the cluster fail over on its own?<\/td><td>Yes. Node pg-node-1 promoted itself with nobody touching it.<\/td><\/tr><tr><td>How long until a new primary existed?<\/td><td>About 33 seconds. Thirty of those seconds were the leader lock timeout.<\/td><\/tr><tr><td>How long until write traffic followed it?<\/td><td>About 36 seconds from the moment the primary was stopped.<\/td><\/tr><tr><td>Did the application connection string change?<\/td><td>No. It stayed as port 5000 on localhost throughout.<\/td><\/tr><tr><td>Was any committed data lost?<\/td><td>No. Every row survived. The PostgreSQL timeline moved from 1 to 2.<\/td><\/tr><tr><td>Did the failed primary rejoin?<\/td><td>Yes, in about 1.5 seconds, as a replica with zero lag. It did not need pg_rewind, and Section 14 explains why.<\/td><\/tr><tr><td>Did losing a replica cause a failover?<\/td><td>No, and writes carried on without a pause.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a>What this document covers<\/a><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Which component solves which problem, and which problems each component does not solve.<\/li>\n\n\n\n<li>Why the leader lock is kept in etcd rather than inside PostgreSQL.<\/li>\n\n\n\n<li>Why a healthy HAProxy dashboard is supposed to show some servers as DOWN.<\/li>\n\n\n\n<li>What happens to connections that are already open when a failover starts. The honest answer catches people out.<\/li>\n\n\n\n<li>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.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Who this is for<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">An important limit on scope<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">This is a laboratory build. Five containers on one Docker host, with a single etcd node, is a good way to learn the mechanics.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Architecture<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This section covers the components, the ports, and the paths between them. It is worth reading before the operational sections.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>The problem this architecture solves<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There are four separate problems to solve here:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Keeping more than one copy of the data.<\/strong> This is done by PostgreSQL streaming replication.<\/li>\n\n\n\n<li><strong>Noticing that the primary has died, and choosing a replacement.<\/strong> This is done by Patroni together with etcd.<\/li>\n\n\n\n<li><strong>Actually promoting the chosen replica.<\/strong> This is done by Patroni.<\/li>\n\n\n\n<li><strong>Sending application traffic to whichever node is currently the primary.<\/strong> This is done by HAProxy.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Each component handles one of these. There is no overlap, which is useful when something breaks, because the split tells you where to look.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Component overview<\/a><\/h3>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"751\" height=\"1024\" src=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-37-751x1024.png\" alt=\"\" class=\"wp-image-57\" style=\"aspect-ratio:0.7342657342657343;width:482px;height:auto\" srcset=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-37-751x1024.png 751w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-37-220x300.png 220w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-37-768x1046.png 768w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-37-1127x1536.png 1127w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-37-1503x2048.png 1503w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-37-scaled.png 1879w\" sizes=\"auto, (max-width: 751px) 100vw, 751px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a>PostgreSQL, the database engine<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">What PostgreSQL does here:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stores the application data.<\/li>\n\n\n\n<li>Processes SQL from the application.<\/li>\n\n\n\n<li>Maintains the write ahead log, which is a sequential record of every change made to the database.<\/li>\n\n\n\n<li>Sends those log records to the replicas over streaming replication. This is how the replicas keep up.<\/li>\n\n\n\n<li>On a replica, replays incoming log records so the same changes are applied locally.<\/li>\n\n\n\n<li>Runs replicas in hot standby mode, which means they can answer read only queries while they are still receiving replication data.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">What PostgreSQL does not do:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>It does not decide which node is the primary. It has no idea that a cluster exists.<\/li>\n\n\n\n<li>It does not fail over. If the primary dies, PostgreSQL on a replica simply waits for more log data. It will not promote itself.<\/li>\n\n\n\n<li>It does not route connections. It listens on port 5432 and serves whatever connects.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Port<\/td><td>Protocol<\/td><td>Purpose<\/td><\/tr><\/thead><tbody><tr><td>5432<\/td><td>PostgreSQL wire protocol over TCP<\/td><td>Database connections<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Some PostgreSQL terms that appear throughout this document:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Write ahead log, usually shortened to WAL in the tools themselves.<\/strong> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Timeline.<\/strong> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Log sequence number.<\/strong> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The pg_is_in_recovery function.<\/strong> Returns true on a replica and false on the primary. This is how you check a node&#8217;s role from SQL.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The pg_stat_replication view.<\/strong> A view on the primary that lists every connected replica, its state, and how far behind it is.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The pg_rewind tool.<\/strong> Rewinds a former primary&#8217;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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Patroni, the high availability manager<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">What Patroni does:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>First time setup.<\/strong> 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&#8217;s data with a base backup, and start PostgreSQL as replicas.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Leader lock management.<\/strong> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Health monitoring.<\/strong> Patroni watches the local PostgreSQL process. If PostgreSQL stops, Patroni tries to restart it. If it cannot, Patroni gives up the leader lock.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Election and promotion.<\/strong> When the leader lock expires, the surviving Patroni instances race to take it. The winner promotes its local PostgreSQL from replica to primary.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Demotion and recovery.<\/strong> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A small HTTP API.<\/strong> Patroni answers on port 8008 and reports the node&#8217;s current role. HAProxy uses this for health checks.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Configuration management.<\/strong> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">What Patroni does not do:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>It does not store application data. It is a management process, not a database.<\/li>\n\n\n\n<li>It does not route client connections. Applications never connect to Patroni.<\/li>\n\n\n\n<li>It does not provide the consensus itself. It relies on etcd for that.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Port<\/td><td>Protocol<\/td><td>Purpose<\/td><\/tr><\/thead><tbody><tr><td>8008<\/td><td>HTTP<\/td><td>Health check endpoint for HAProxy, and cluster status queries<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The API endpoints that matter in this build:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Endpoint<\/td><td>Returns 200 when<\/td><td>Returns 503 when<\/td><td>Used by<\/td><\/tr><\/thead><tbody><tr><td>GET \/primary<\/td><td>This node is the primary<\/td><td>This node is a replica<\/td><td>The HAProxy primary backend<\/td><\/tr><tr><td>GET \/replica<\/td><td>This node is a replica<\/td><td>This node is the primary<\/td><td>The HAProxy replica backend<\/td><\/tr><tr><td>GET \/health<\/td><td>PostgreSQL is running and healthy<\/td><td>PostgreSQL is down<\/td><td>The Docker health check<\/td><\/tr><tr><td>GET \/patroni<\/td><td>Always, returning full node state<\/td><td>Not applicable<\/td><td>Diagnostics<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The Patroni settings used here:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Setting<\/td><td>Value<\/td><td>What it means<\/td><\/tr><\/thead><tbody><tr><td>scope<\/td><td>pg-ha-cluster<\/td><td>The cluster name. Every Patroni instance with the same scope belongs to the same cluster.<\/td><\/tr><tr><td>name<\/td><td>pg-node-1, pg-node-2, pg-node-3<\/td><td>A unique name for each node. It must differ on every node.<\/td><\/tr><tr><td>namespace<\/td><td>\/service\/<\/td><td>The key prefix Patroni uses inside etcd.<\/td><\/tr><tr><td>ttl<\/td><td>30 seconds<\/td><td>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.<\/td><\/tr><tr><td>loop_wait<\/td><td>10 seconds<\/td><td>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.<\/td><\/tr><tr><td>retry_timeout<\/td><td>10 seconds<\/td><td>How long Patroni keeps retrying etcd and PostgreSQL operations before giving up.<\/td><\/tr><tr><td>maximum_lag_on_failover<\/td><td>1048576 bytes, which is 1 MB<\/td><td>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.<\/td><\/tr><tr><td>use_pg_rewind<\/td><td>True<\/td><td>When a former primary rejoins, use pg_rewind instead of a full base backup. Recovery is far faster.<\/td><\/tr><tr><td>use_slots<\/td><td>True<\/td><td>Use replication slots, so the primary does not throw away log segments that a replica still needs.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a>etcd, the distributed configuration store<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">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?<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">What Patroni keeps in etcd, under the key prefix for this cluster:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Key<\/td><td>Content<\/td><td>Purpose<\/td><\/tr><\/thead><tbody><tr><td>leader<\/td><td>The name of the current leader<\/td><td>The leader lock. Whichever node created this key is the primary. It expires after the timeout.<\/td><\/tr><tr><td>initialize<\/td><td>A cluster initialisation token<\/td><td>Stops two nodes from creating a data directory at the same time during first startup.<\/td><\/tr><tr><td>config<\/td><td>Cluster wide PostgreSQL configuration<\/td><td>Shared settings, so all nodes agree.<\/td><\/tr><tr><td>members\/pg-node-1<\/td><td>Connection details, state, timeline and log position for that node<\/td><td>Each node registers itself so the others know how to reach it.<\/td><\/tr><tr><td>members\/pg-node-2<\/td><td>The same, for node 2<\/td><td>&nbsp;<\/td><\/tr><tr><td>members\/pg-node-3<\/td><td>The same, for node 3<\/td><td>&nbsp;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">How the leader lock actually works:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>A Patroni node starts and checks etcd for an existing leader key.<\/li>\n\n\n\n<li>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.<\/li>\n\n\n\n<li>etcd allows only one of those creates to succeed. The node that wins becomes the leader.<\/li>\n\n\n\n<li>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.<\/li>\n\n\n\n<li>If the leader stops renewing, because it crashed or lost the network or PostgreSQL died, the key expires at the thirty second mark.<\/li>\n\n\n\n<li>The other nodes notice the missing key on their next pass through the loop and race to create a new one.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Why production uses an odd number of etcd nodes.<\/strong> 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.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Port<\/td><td>Protocol<\/td><td>Purpose<\/td><\/tr><\/thead><tbody><tr><td>2379<\/td><td>HTTP<\/td><td>Client API. This is where Patroni connects.<\/td><\/tr><tr><td>2380<\/td><td>HTTP<\/td><td>Peer API, for etcd to etcd traffic. Unused with a single node.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a>HAProxy, the connection router<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">What HAProxy does:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Listens on port 5000 for write traffic and port 5001 for read traffic.<\/li>\n\n\n\n<li>Every three seconds, sends an HTTP health check to each node&#8217;s Patroni API on port 8008.<\/li>\n\n\n\n<li>For the primary backend it calls GET \/primary. Only the current primary answers 200, so write traffic goes there.<\/li>\n\n\n\n<li>For the replica backend it calls GET \/replica. Only replicas answer 200, and read traffic is spread across them.<\/li>\n\n\n\n<li>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.<\/li>\n\n\n\n<li>When HAProxy marks a server as down, it closes any TCP connections that were still open to that server.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">What HAProxy does not do:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>It does not decide who the primary is. It only reports what Patroni tells it.<\/li>\n\n\n\n<li>It does not keep existing connections alive across a failover. Connections to the old primary are broken.<\/li>\n\n\n\n<li>It does not replicate data, manage PostgreSQL, or talk to etcd.<\/li>\n\n\n\n<li>It does not understand SQL. It forwards raw bytes. As far as PostgreSQL is concerned, the connection comes from HAProxy, not from the application.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Port<\/td><td>Protocol<\/td><td>Reachable from the host<\/td><td>Purpose<\/td><\/tr><\/thead><tbody><tr><td>5000<\/td><td>TCP, PostgreSQL wire protocol<\/td><td>Yes<\/td><td>The read and write endpoint<\/td><\/tr><tr><td>5001<\/td><td>TCP, PostgreSQL wire protocol<\/td><td>Yes<\/td><td>The read only endpoint<\/td><\/tr><tr><td>7000<\/td><td>HTTP<\/td><td>Yes<\/td><td>The HAProxy statistics page<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Reading the HAProxy status page without being alarmed.<\/strong> 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.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"851\" src=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-36-1024x851.png\" alt=\"\" class=\"wp-image-56\" style=\"aspect-ratio:1.2034383954154728;width:699px;height:auto\" srcset=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-36-1024x851.png 1024w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-36-300x249.png 300w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-36-767x638.png 767w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-36.png 1472w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Communication map<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">This diagram shows every path between the components, with the port numbers.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"731\" src=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-39-1024x731.png\" alt=\"\" class=\"wp-image-59\" style=\"aspect-ratio:1.4;width:983px;height:auto\" srcset=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-39-1024x731.png 1024w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-39-300x214.png 300w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-39-767x548.png 767w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-39-1536x1097.png 1536w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-39-2048x1463.png 2048w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Every port in one table:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Port<\/td><td>Listener<\/td><td>Protocol<\/td><td>Who connects to it<\/td><td>Reachable from the host<\/td><\/tr><\/thead><tbody><tr><td>5000<\/td><td>HAProxy<\/td><td>TCP<\/td><td>Application or psql<\/td><td>Yes. This is the write endpoint.<\/td><\/tr><tr><td>5001<\/td><td>HAProxy<\/td><td>TCP<\/td><td>Application or psql<\/td><td>Yes. This is the read endpoint.<\/td><\/tr><tr><td>7000<\/td><td>HAProxy<\/td><td>HTTP<\/td><td>A browser<\/td><td>Yes<\/td><\/tr><tr><td>5432<\/td><td>PostgreSQL on each node<\/td><td>TCP<\/td><td>HAProxy, and the other PostgreSQL nodes for replication<\/td><td>No. Internal only.<\/td><\/tr><tr><td>8008<\/td><td>Patroni on each node<\/td><td>HTTP<\/td><td>HAProxy for health checks, and the Patroni command line tool<\/td><td>No. Internal only.<\/td><\/tr><tr><td>2379<\/td><td>etcd<\/td><td>HTTP<\/td><td>Patroni on all three nodes<\/td><td>No. Internal only.<\/td><\/tr><tr><td>2380<\/td><td>etcd<\/td><td>HTTP<\/td><td>Other etcd nodes. Unused here.<\/td><td>No. Internal only.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Note that PostgreSQL port 5432 and the Patroni port 8008 are deliberately not published to the host. All client access goes through HAProxy.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>What each node runs inside<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Every database container runs two processes side by side. Patroni is the parent, and it starts and manages PostgreSQL.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"272\" src=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-35-1024x272.png\" alt=\"\" class=\"wp-image-55\" style=\"aspect-ratio:3.7837837837837838;width:1100px;height:auto\" srcset=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-35-1024x272.png 1024w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-35-300x80.png 300w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-35-768x204.png 768w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-35-1536x408.png 1536w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-35-2048x544.png 2048w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">When a container starts:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>The entrypoint script writes the Patroni configuration file, filling in the values that differ between nodes.<\/li>\n\n\n\n<li>Patroni starts as the main process.<\/li>\n\n\n\n<li>Patroni asks etcd whether a leader exists and whether the cluster has been initialised.<\/li>\n\n\n\n<li>If this is the first node, Patroni creates the data directory, starts PostgreSQL, and takes the leader lock.<\/li>\n\n\n\n<li>If another node is already the leader, Patroni copies that node&#8217;s data with a base backup and starts PostgreSQL as a replica.<\/li>\n\n\n\n<li>Patroni then enters its main loop, running every ten seconds.<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><a>How the data stays in step<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"604\" src=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-34-1024x604.png\" alt=\"\" class=\"wp-image-54\" style=\"aspect-ratio:1.6935483870967742;width:1009px;height:auto\" srcset=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-34-1024x604.png 1024w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-34-767x452.png 767w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-34-300x177.png 300w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-34-1536x906.png 1536w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-34-2048x1208.png 2048w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">This cluster uses asynchronous replication, which the tools report as a synchronisation state of async. That means:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The primary commits a transaction and tells the client it succeeded straight away.<\/li>\n\n\n\n<li>The log records travel to the replicas in the background.<\/li>\n\n\n\n<li>There is a very short window, usually a few milliseconds, in which the primary has committed something the replicas have not received yet.<\/li>\n\n\n\n<li>If the primary is destroyed inside that window, that transaction is lost, because no replica ever saw it.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The health indicators to watch:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Indicator<\/td><td>Meaning<\/td><td>Healthy value<\/td><\/tr><\/thead><tbody><tr><td>sent position<\/td><td>How far the primary has sent to this replica<\/td><td>Matches the primary&#8217;s current position<\/td><\/tr><tr><td>write position<\/td><td>How far the replica has written to disk<\/td><td>Matches the sent position<\/td><\/tr><tr><td>flush position<\/td><td>How far the replica has flushed to disk<\/td><td>Matches the write position<\/td><\/tr><tr><td>replay position<\/td><td>How far the replica has applied to its data files<\/td><td>Matches the flush position<\/td><\/tr><tr><td>state<\/td><td>Connection state<\/td><td>The value streaming means healthy<\/td><\/tr><tr><td>sync state<\/td><td>Synchronisation mode<\/td><td>The value async in this build<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">When all four positions match, the replication lag is zero.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>The states the cluster moves through<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">This diagram shows the transitions that were actually observed during the test.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"354\" height=\"1024\" src=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-42-354x1024.png\" alt=\"\" class=\"wp-image-62\" style=\"aspect-ratio:0.34517766497461927;width:321px;height:auto\" srcset=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-42-354x1024.png 354w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-42-104x300.png 104w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-42-531x1536.png 531w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-42-768x2223.png 768w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-42-707x2048.png 707w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-42-scaled.png 884w\" sizes=\"auto, (max-width: 354px) 100vw, 354px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a>What this architecture does not protect against<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">This setup has real limits, and it is better to be clear about them.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Situation<\/td><td>Protected<\/td><td>Why<\/td><\/tr><\/thead><tbody><tr><td>One PostgreSQL node fails<\/td><td>Yes<\/td><td>Patroni promotes a replica<\/td><\/tr><tr><td>Application needs a stable address<\/td><td>Yes<\/td><td>HAProxy provides one<\/td><\/tr><tr><td>Loss of committed transactions<\/td><td>Mostly<\/td><td>Replicas are very close to real time, but asynchronous replication leaves a tiny window<\/td><\/tr><tr><td>All nodes fail together<\/td><td>No<\/td><td>Every container runs on one Docker host<\/td><\/tr><tr><td>etcd fails<\/td><td>No<\/td><td>There is only one etcd node, so no election can happen<\/td><\/tr><tr><td>Disk corruption<\/td><td>No<\/td><td>Corruption is replicated to the replicas along with everything else<\/td><\/tr><tr><td>Somebody drops a table by accident<\/td><td>No<\/td><td>The change is replicated to all nodes immediately<\/td><\/tr><tr><td>Data centre failure<\/td><td>No<\/td><td>Everything is on one machine<\/td><\/tr><tr><td>Network partition creating two primaries<\/td><td>Partly<\/td><td>The lock in etcd stops two primaries existing, because a leader that loses the lock demotes itself<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<!--nextpage-->\n\n\n\n<h2 class=\"wp-block-heading\"><a>Prerequisites<\/a><\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Software<\/td><td>Purpose<\/td><td>Version<\/td><\/tr><\/thead><tbody><tr><td>Docker Desktop or Docker Engine<\/td><td>Container runtime<\/td><td>Version 4 or later<\/td><\/tr><tr><td>Docker Compose<\/td><td>Service orchestration<\/td><td>Version 2, which ships with Docker Desktop<\/td><\/tr><tr><td>Git<\/td><td>To clone the repository<\/td><td>Any recent version<\/td><\/tr><tr><td>psql<\/td><td>PostgreSQL client, optional<\/td><td>Any version<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The client is optional because you can run psql inside any of the containers instead of installing it on the host.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The environment runs five containers: one etcd, three PostgreSQL nodes and one HAProxy. Give Docker at least 4 GB of memory. The default allocation is usually enough.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This build was tested on Docker Desktop on Windows, where Linux containers run inside a small virtual machine and all Docker commands work normally from PowerShell. The same files work on macOS and Linux.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Component Versions<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">These versions were tested together on 29 August 2026 and worked.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Component<\/td><td>Version<\/td><td>Image<\/td><\/tr><\/thead><tbody><tr><td>PostgreSQL<\/td><td>16.4<\/td><td>postgres:16.4-bookworm<\/td><\/tr><tr><td>Patroni<\/td><td>3.3.2<\/td><td>Installed with pip into a custom image<\/td><\/tr><tr><td>etcd<\/td><td>3.5.15<\/td><td>quay.io\/coreos\/etcd:v3.5.15<\/td><\/tr><tr><td>HAProxy<\/td><td>2.9<\/td><td>haproxy:2.9-alpine<\/td><\/tr><tr><td>Python<\/td><td>3.11<\/td><td>Included in the Debian base image<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">PostgreSQL 16.4 was the current stable release in the 16 series. Patroni 3.3.2 supports PostgreSQL 16 and includes the etcd version 3 client. etcd 3.5.15 is a stable release providing the version 3 API that this Patroni client needs. HAProxy 2.9 is a stable release.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">One version detail caused a real problem during the build. etcd 3.5 has the older version 2 API switched off by default, so Patroni has to be installed and configured for the version 3 API. The section on problems hit while building this explains what went wrong and how it was fixed.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Configuration Summary<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The repository holds the actual files. This section explains what they set up and why, so you can read them with some context.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>How the containers are wired together<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">All five services sit on one Docker bridge network and find each other by service name, so there are no hard coded IP addresses anywhere.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">etcd starts first. The three database nodes wait for the etcd health check to pass before they start. HAProxy starts after the three database containers have been created, although it does not wait for them to be healthy, because it runs its own health checks anyway.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Each database node has its own Docker volume for its data directory. Volumes are never shared between nodes. Three nodes means three independent copies of the data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Only three ports are published to the host: 5000 for writes, 5001 for reads and 7000 for the statistics page.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Every service is set to restart unless it has been stopped deliberately. This matters during the test, because it is the reason a stopped container stays stopped until it is started again by hand.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"661\" src=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-38-1024x661.png\" alt=\"\" class=\"wp-image-58\" style=\"aspect-ratio:1.5441176470588236;width:525px;height:auto\" srcset=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-38-1024x661.png 1024w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-38-300x194.png 300w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-38-768x496.png 768w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-38.png 1158w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The three database nodes start at the same time once etcd is ready. Whichever one takes the leader lock first creates the database, and the other two copy it.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Credentials<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The repository includes an example environment file. Copy it to a real one before starting the cluster, and change the values. The example file is deliberately committed with obvious lab only values, and the real file is excluded from version control.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The variables define three separate accounts, which is a habit worth keeping even in a lab.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Variable<\/td><td>Used by<\/td><td>Purpose<\/td><\/tr><\/thead><tbody><tr><td>Superuser name and password<\/td><td>Patroni<\/td><td>The PostgreSQL superuser Patroni uses to manage the cluster<\/td><\/tr><tr><td>Replication user and password<\/td><td>Patroni and PostgreSQL<\/td><td>Authenticates streaming replication between nodes<\/td><\/tr><tr><td>Application user and password<\/td><td>Your application<\/td><td>The account the application connects with<\/td><\/tr><tr><td>Application database name<\/td><td>Your application<\/td><td>The database the application uses<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">No values are printed in this document on purpose. Do not reuse the ones in the repository. Generate your own, and in production keep them in a secrets manager rather than a file on disk.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">One practical note if you fork this repository. Patroni prints its rendered configuration at startup, so the captured container logs under test-results contain those lab passwords in plain text. That is fine for a disposable lab whose credentials are already public, but it is worth remembering before you point the same setup at anything you care about.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>The PostgreSQL settings Patroni applies<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">One template is shared by all three nodes, and the entrypoint script fills in the values that differ. The settings below are the ones that matter for high availability.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Setting<\/td><td>Value<\/td><td>Why it is set<\/td><\/tr><\/thead><tbody><tr><td>wal_level<\/td><td>replica<\/td><td>Required for streaming replication<\/td><\/tr><tr><td>hot_standby<\/td><td>on<\/td><td>Lets replicas answer read queries<\/td><\/tr><tr><td>wal_log_hints<\/td><td>on<\/td><td>Required for pg_rewind to work<\/td><\/tr><tr><td>max_wal_senders<\/td><td>5<\/td><td>How many replication connections the primary will accept<\/td><\/tr><tr><td>max_replication_slots<\/td><td>5<\/td><td>How many replication slots can exist<\/td><\/tr><tr><td>max_connections<\/td><td>100<\/td><td>The PostgreSQL default. A replica will refuse to start if this is lower than on the primary.<\/td><\/tr><tr><td>max_worker_processes<\/td><td>8<\/td><td>Background worker limit, with the same constraint as max_connections<\/td><\/tr><tr><td>hot_standby_feedback<\/td><td>on<\/td><td>Replicas tell the primary which rows their running queries still need, so cleanup on the primary does not cancel queries on the replica<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Three of these deserve more explanation, because they are the ones people get wrong.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The wal_log_hints setting is not optional in this design.<\/strong> The pg_rewind tool has to identify every page that the diverged server changed. Without hint logging, or without data checksums, that information is not in the log and pg_rewind refuses to run. A failed primary would then only be able to rejoin by copying the whole database. On a large database that is the difference between a few seconds and several hours.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Replication slots protect replicas, and can fill your disk.<\/strong> Without a slot, a primary will happily delete log segments that a disconnected replica still needs, and that replica can never catch up. With a slot, the primary keeps those segments. The risk is the mirror image: a slot belonging to a replica that is never coming back will hold log files forever and eventually fill the disk. Watch the replication slot view and remove slots for decommissioned nodes.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The maximum lag setting is a data loss limit, not a performance setting.<\/strong> A replica more than 1 MB behind is not allowed to be promoted, on the grounds that promoting it would quietly throw away more data than you agreed to lose. Set it too high and you trade durability for availability. Set it to zero and you may find no candidate is eligible at all.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Two settings only apply the very first time the cluster is created. The data directory is created with UTF8 encoding and with data checksums switched on. Checksums let PostgreSQL notice silent disk corruption when it reads a page, at a small cost in CPU. They can only be turned on at creation time, so this is the only chance you get. They also satisfy the pg_rewind page tracking requirement on their own.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A word about anything under the bootstrap section of the Patroni configuration.<\/strong> Those values are used once, when the cluster is first created. Editing them later and restarting changes nothing, because Patroni has already copied them into etcd. To change them afterwards you use the Patroni command line tool to edit the live configuration. This is the most common reason people believe Patroni is ignoring their configuration file.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Two settings to change before production<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The template permits password authenticated connections from any address. That is acceptable on an isolated Docker network and unacceptable anywhere real. Restrict the allowed source ranges, and prefer the scram-sha-256 password method over the older md5 method.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The template also logs every statement and the duration of every statement. That is exactly what you want in a lab where the point is to watch what the cluster does. In production it is both a performance problem and a security problem, because it produces enormous log volume under load and writes query parameters, which may include personal data, to disk in plain text. Log only slow statements, for example anything over one second, and reduce statement logging to schema changes only.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>How HAProxy decides where to send traffic<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">HAProxy has two listeners. The one on port 5000 sends traffic to whichever node Patroni reports as the primary, checking the GET \/primary endpoint. The one on port 5001 sends traffic to nodes that report themselves as replicas, checking GET \/replica. Both check port 8008 rather than the database port, so they test the actual role rather than whether a port happens to be open.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">No load balancing algorithm is set for the replica pool, so HAProxy uses its default, which is round robin across the healthy replicas.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Two details in the server options set the real detection speed:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A server is only marked down after three consecutive failed checks. With checks every three seconds, a genuinely dead node takes about nine seconds to be removed. That delay is deliberate, and it stops one dropped packet from moving traffic around.<\/li>\n\n\n\n<li>A recovering server has to pass two consecutive checks before it is used again.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">In the failover recorded here, the old primary was marked down by a connection timeout while the new primary was marked up by a successful check on the very next pass, which is why routing changed in about three seconds rather than nine.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There is also an option that closes existing sessions to a server when that server is marked down. This is a deliberate choice to fail quickly. Clients get an immediate error they can retry, instead of a connection that looks alive but will never answer.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>One caution about the read port.<\/strong> Reading from replicas is genuinely useful, but this cluster replicates asynchronously. A client that writes on port 5000 and immediately reads on port 5001 may not see its own write. Send anything that needs to read its own writes to port 5000, and keep port 5001 for reporting and dashboards that can tolerate data a few milliseconds old.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>The Commands, Start to Finish<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Every command below is taken from the scripts and the README in the repository, in the order you would run them. Read the previous note about the scripts and the captured run before treating this as a transcript.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The scripts expect to be run from the root of the postgres-patroni-ha folder.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Build the environment<\/a><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>git clone https:\/\/github.com\/rameshsraj\/pgandpatroni.git<br>cd pgandpatroni\/postgres-patroni-ha<br><br>cp .env.example .env&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <em># then edit .env and change every password<\/em><br>docker compose build<br>docker compose up -d<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Give it 15 to 30 seconds before the next step.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Check the cluster came up<\/a><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>docker exec pg-node-1 patronictl -c \/etc\/patroni\/patroni.yml list<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">There is also a wrapper that prints the Patroni list, the container status, the etcd health and the HAProxy backend states in one go:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>bash scripts\/cluster-status.sh<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Create the application database and sample data<\/a><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>bash scripts\/database-test.sh<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">That script connects through HAProxy on port 5000, so it always reaches whichever node is currently the primary. It creates the database, grants the application user its privileges, creates the three tables, and inserts the sample rows.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Take a snapshot before breaking anything<\/a><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>bash scripts\/capture-state.sh before-failover<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The label becomes the folder name under test-results. The script collects the container status, the Patroni list, the etcd health and key contents, the HAProxy statistics, the role of each node, the replication view, the row counts, the sample rows, the connection details, and the last hundred log lines from all five containers.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Run the failover test<\/a><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>bash scripts\/failover-test.sh<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This is the interesting one, and it is worth knowing what it does rather than treating it as a black box:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Finds the current leader by parsing the Patroni list, rather than assuming which node it is.<\/li>\n\n\n\n<li>Captures the before state.<\/li>\n\n\n\n<li>Records the time, then stops the leader&#8217;s container with docker stop.<\/li>\n\n\n\n<li>Polls the Patroni list every two seconds, for up to sixty seconds, until a different node reports itself as leader.<\/li>\n\n\n\n<li>Retries a connection through HAProxy on port 5000 until it succeeds, which is how the routing delay is observed.<\/li>\n\n\n\n<li>Prints the node it reached, the row counts, and the marker rows.<\/li>\n\n\n\n<li>Inserts a new marker row to prove the new primary accepts writes.<\/li>\n\n\n\n<li>Captures the after state and prints a summary with the two intervals.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">The point of step 1 is that the first leader is chosen by a race and is not predictable. In the captured run it was node 2.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Bring the failed node back<\/a><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>bash scripts\/recovery-test.sh pg-node-2<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Pass the name of the node that was stopped. The script starts the container, waits for the Patroni health endpoint to answer, prints the cluster list, confirms the node reports itself as a replica, shows the replication view from the primary, and verifies the data.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Connect via psql<\/a><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>psql -h localhost -p 5000 -U appuser -d appdb&nbsp;&nbsp;&nbsp; <em># writes, always the primary<\/em><br>psql -h localhost -p 5001 -U appuser -d appdb&nbsp;&nbsp;&nbsp; <em># reads, spread across replicas<\/em><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The HAProxy statistics page is at http:\/\/localhost:7000 in a browser.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Useful one liners while poking at it<\/a><\/h3>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><em># Which node am I actually connected to, and is it a replica?<\/em><br>psql -h localhost -p 5000 -U appuser -d appdb \\<br>&nbsp;&nbsp;&nbsp;&nbsp; -c &#8220;SELECT inet_server_addr(), pg_is_in_recovery();&#8221;<br><br><em># Replication state, from the primary<\/em><br>psql -h localhost -p 5000 -U postgres -d postgres \\<br>&nbsp;&nbsp;&nbsp;&nbsp; -c &#8220;SELECT application_name, client_addr, state, sync_state, replay_lsn FROM pg_stat_replication;&#8221;<br><br><em># What Patroni has stored in etcd<\/em><br>docker exec etcd etcdctl get \/service\/pg-ha-cluster &#8211;prefix &#8211;print-value-only<br><br><em># Ask a single node directly what role it thinks it has<\/em><br>docker exec pg-node-1 curl -s http:\/\/localhost:8008\/patroni<br><br><em># Follow one node&#8217;s Patroni log<\/em><br>docker logs -f pg-node-1<\/p>\n<\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Tear it down<\/a><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>docker compose down&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <em># keeps the data volumes<\/em><br>docker compose down -v&nbsp;&nbsp;&nbsp;&nbsp; <em># deletes the volumes and all data<\/em><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">A note on the scripts and passwords. Each script reads .env if it is present and otherwise falls back to the lab defaults that are committed in the repository. That fallback is convenient and it is also why you should change the values in .env rather than relying on the defaults.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Starting the Cluster<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This section covers what actually happened when they were run, and how long each part took.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The build step creates an image based on the official PostgreSQL 16.4 image with Patroni 3.3.2 added, and the start step brings up all five containers.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The cluster needs roughly 15 to 30 seconds to become ready. During that time etcd starts and becomes healthy, the three database containers start, one node wins the election and creates the database, the other two copy it, replication begins, and HAProxy finds its healthy backends.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The captured logs show how quickly the database part actually happened. All three Patroni instances connected to etcd at 14:49:09. Node 2 won the race and recorded that it had initialised a new cluster at 14:49:13.279. Nodes 1 and 3 failed to acquire the initialise lock, saw that node 2 held it, and copied its data. Node 1 reported that the replica had been created using a base backup at 14:49:14.312, and was following the leader by 14:49:16.360. From first contact with etcd to a working three node cluster took about six and a half seconds.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The containers themselves, and the health checks that gate them, account for the rest of the time.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The container state once it had settled:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>docker compose ps<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Result:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IMAGE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SERVICE&nbsp;&nbsp;&nbsp;&nbsp; STATUS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PORTS<br>etcd&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; quay.io\/coreos\/etcd:v3.5.15&nbsp;&nbsp;&nbsp;&nbsp; etcd&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Up 2 minutes (healthy)&nbsp;&nbsp; 2379-2380\/tcp<br>haproxy&nbsp;&nbsp;&nbsp;&nbsp; haproxy:2.9-alpine&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; haproxy&nbsp;&nbsp;&nbsp;&nbsp; Up 2 minutes (healthy)&nbsp;&nbsp; 0.0.0.0:5000-5001-&gt;5000-5001\/tcp, 0.0.0.0:7000-&gt;7000\/tcp<br>pg-node-1&nbsp;&nbsp; postgres-patroni-ha-pg-node-1&nbsp;&nbsp; pg-node-1&nbsp;&nbsp; Up 2 minutes (healthy)&nbsp;&nbsp; 5432\/tcp, 8008\/tcp<br>pg-node-2&nbsp;&nbsp; postgres-patroni-ha-pg-node-2&nbsp;&nbsp; pg-node-2&nbsp;&nbsp; Up 2 minutes (healthy)&nbsp;&nbsp; 5432\/tcp, 8008\/tcp<br>pg-node-3&nbsp;&nbsp; postgres-patroni-ha-pg-node-3&nbsp;&nbsp; pg-node-3&nbsp;&nbsp; Up 2 minutes (healthy)&nbsp;&nbsp; 5432\/tcp, 8008\/tcp<\/p>\n<\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\">HAProxy publishes 5000, 5001 and 7000 to the host. The three database nodes show their PostgreSQL and Patroni ports with no host mapping, and etcd likewise, so none of them can be reached from outside the Docker network.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Where to check the etcd side<\/a><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>docker exec etcd etcdctl endpoint health --endpoints=http:\/\/localhost:2379<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">etcd reported itself healthy:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">http:\/\/localhost:2379 is healthy: successfully committed proposal: took = 2.037062ms<\/p>\n<\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Where to check the cluster and node roles<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The Patroni command line tool, run inside any of the containers, is the equivalent of a node list. It reads its answer from etcd, so it does not matter which container you run it in:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>docker exec pg-node-1 patronictl -c \/etc\/patroni\/patroni.yml list<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The columns are the member name, its host, its role, its state, the timeline it is on, and how far behind it is in megabytes. Blank lag on the leader is normal, because the leader is not behind anything.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To ask a single node what it thinks its own role is, without going through etcd, use its HTTP interface instead:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>docker exec pg-node-1 curl -s http:\/\/localhost:8008\/patroni<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">That returns a block of JSON with the role, the timeline, the log positions and the Patroni version, which is the same information HAProxy uses for its health checks.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There is also the SQL route, which is the one to use from an application:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT pg_is_in_recovery();&nbsp;&nbsp; <em>-- false on the primary, true on a replica<\/em><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This is the output of the cluster listing from the test run:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">+ Cluster: pg-ha-cluster (7679465736627556386) &#8212;-+&#8212;&#8212;&#8212;&#8211;+<br>| Member&nbsp;&nbsp;&nbsp; | Host&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Role&nbsp;&nbsp;&nbsp; | State&nbsp;&nbsp;&nbsp;&nbsp; | TL | Lag in MB |<br>+&#8212;&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8211;+&#8212;-+&#8212;&#8212;&#8212;&#8211;+<br>| pg-node-1 | pg-node-1 | Replica | streaming |&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |<br>| pg-node-2 | pg-node-2 | Leader&nbsp; | running&nbsp;&nbsp; |&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>| pg-node-3 | pg-node-3 | Replica | streaming |&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |<br>+&#8212;&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8211;+&#8212;-+&#8212;&#8212;&#8212;&#8211;+<\/p>\n<\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\">Node pg-node-2 won the first election and became the leader. Nodes pg-node-1 and pg-node-3 copied their data from it and are streaming replicas with zero lag on timeline 1.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Notice that the leader is not node 1. Which node wins the first election is a race, and it is not predictable. Any script or document that assumes node 1 is always the primary is wrong. This matters later, because the node that was killed in the failover test was node 2, not node 1.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If the containers do not come up, read the logs of a database node and of etcd. The section on problems hit while building this covers the failures that actually happened here.<\/p>\n\n\n\n<!--nextpage-->\n\n\n\n<h2 class=\"wp-block-heading\"><a>Database Setup<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">With the cluster healthy, an application database was created through HAProxy on port 5000, which means it was created on whichever node was the primary at the time. Nothing here connects to a node directly, which is the habit worth forming: if you can only reach the database through the load balancer, you cannot accidentally write to a replica.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>The schema<\/a><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE IF NOT EXISTS customers (<br>&nbsp;&nbsp;&nbsp; id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SERIAL PRIMARY KEY,<br>&nbsp;&nbsp;&nbsp; name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR(100) NOT NULL,<br>&nbsp;&nbsp;&nbsp; email&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR(150) NOT NULL UNIQUE,<br>&nbsp;&nbsp;&nbsp; created_at&nbsp; TIMESTAMPTZ DEFAULT now()<br>);<br><br>CREATE TABLE IF NOT EXISTS orders (<br>&nbsp;&nbsp;&nbsp; id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SERIAL PRIMARY KEY,<br>&nbsp;&nbsp;&nbsp; customer_id INTEGER NOT NULL REFERENCES customers(id),<br>&nbsp;&nbsp;&nbsp; amount&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMERIC(10,2) NOT NULL,<br>&nbsp;&nbsp;&nbsp; status&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR(20) NOT NULL DEFAULT 'pending',<br>&nbsp;&nbsp;&nbsp; created_at&nbsp; TIMESTAMPTZ DEFAULT now()<br>);<br><br>CREATE TABLE IF NOT EXISTS ha_test (<br>&nbsp;&nbsp;&nbsp; id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SERIAL PRIMARY KEY,<br>&nbsp;&nbsp;&nbsp; message&nbsp;&nbsp;&nbsp;&nbsp; TEXT NOT NULL,<br>&nbsp;&nbsp;&nbsp; created_at&nbsp; TIMESTAMPTZ DEFAULT now()<br>);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The ha_test table exists only for the test. Each row is a marker recording when it was written relative to the failover, which is how the data was proved to have survived.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>The sample data<\/a><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO customers (name, email) VALUES<br>&nbsp;&nbsp;&nbsp; ('Alice Johnson',&nbsp;&nbsp; 'alice@example.com'),<br>&nbsp;&nbsp;&nbsp; ('Bob Smith',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'bob@example.com'),<br>&nbsp;&nbsp;&nbsp; ('Charlie Brown',&nbsp;&nbsp; 'charlie@example.com'),<br>&nbsp;&nbsp;&nbsp; ('Diana Prince',&nbsp;&nbsp;&nbsp; 'diana@example.com'),<br>&nbsp;&nbsp;&nbsp; ('Edward Norton',&nbsp;&nbsp; 'edward@example.com')<br>ON CONFLICT (email) DO NOTHING;<br><br>INSERT INTO orders (customer_id, amount, status) VALUES<br>&nbsp;&nbsp;&nbsp; (1, 150.00, 'completed'),<br>&nbsp;&nbsp;&nbsp; (1,&nbsp; 75.50, 'shipped'),<br>&nbsp;&nbsp;&nbsp; (2, 200.00, 'completed'),<br>&nbsp;&nbsp;&nbsp; (3,&nbsp; 45.99, 'pending'),<br>&nbsp;&nbsp;&nbsp; (4, 320.00, 'completed'),<br>&nbsp;&nbsp;&nbsp; (4,&nbsp; 89.99, 'shipped'),<br>&nbsp;&nbsp;&nbsp; (5, 110.00, 'pending')<br>ON CONFLICT DO NOTHING;<br><br>INSERT INTO ha_test (message) VALUES<br>&nbsp;&nbsp;&nbsp; ('record-created-before-failover-1'),<br>&nbsp;&nbsp;&nbsp; ('record-created-before-failover-2'),<br>&nbsp;&nbsp;&nbsp; ('record-created-before-failover-3')<br>ON CONFLICT DO NOTHING;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">That gives five customers, seven orders and three marker rows, which are the counts quoted throughout the rest of this document. The conflict clauses make the script safe to run twice.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Checking which node you reached<\/a><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT inet_server_addr() AS server_addr,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; inet_server_port() AS server_port,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; current_database() AS database,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; current_user&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AS user,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; pg_is_in_recovery() AS is_replica,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; pg_current_wal_lsn() AS current_lsn;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This is the single most useful query in the whole lab. Because HAProxy forwards the connection, the client has no idea which node answered, and this query asks the server itself. The pg_is_in_recovery column returning false proves you are on the primary. Run the same query against port 5001 and it returns true, because that endpoint serves replicas.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Note that pg_current_wal_lsn only works on a primary. On a replica you need pg_last_wal_replay_lsn instead, which is a small detail that trips people up when they point a monitoring query at the read endpoint.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Connecting through HAProxy confirmed which node the connection had reached:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&nbsp;&nbsp; server&nbsp;&nbsp; | is_replica |&nbsp; db<br>------------+------------+-------<br>&nbsp;172.18.0.3 | f&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | appdb<br>(1 row)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The false value in the replica column confirms the connection landed on the primary. The address is the container address of pg-node-2 on the Docker network at that time. Container addresses are assigned by Docker and will differ on your machine, so treat them as evidence from this run rather than as fixed values.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The three container addresses in this run were 172.18.0.3 for pg-node-2, 172.18.0.4 for pg-node-3 and 172.18.0.5 for pg-node-1. They are worth noting because they appear again in the replication output and after the failover, and they are how you can tell from the captured evidence which node a connection actually reached.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The row counts at this point:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT 'customers' AS tbl, count(*) FROM customers<br>UNION ALL SELECT 'orders',&nbsp; count(*) FROM orders<br>UNION ALL SELECT 'ha_test', count(*) FROM ha_test;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Result:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<pre class=\"wp-block-code\"><code>&nbsp;&nbsp;&nbsp; tbl&nbsp;&nbsp;&nbsp; | count<br>-----------+-------<br>&nbsp;customers |&nbsp;&nbsp;&nbsp;&nbsp; 5<br>&nbsp;orders&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 7<br>&nbsp;ha_test&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 3<br>(3 rows)<\/code><\/pre>\n<\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Replication Validation<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Replication was checked on the primary before anything was broken.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT pid, application_name, client_addr, state, sync_state,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sent_lsn, write_lsn, flush_lsn, replay_lsn<br>FROM pg_stat_replication;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This view exists only on the primary. Query it on a replica and you get zero rows, which is itself a quick way to tell the two apart. Each row is one connected replica, and the four position columns are how far that replica has got: what the primary has sent, what the replica has written, what it has flushed to disk, and what it has actually applied.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is the output:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Both replicas are streaming, both are asynchronous, and all four log positions are identical across both rows. Identical positions mean the replicas have received, written, flushed and applied everything the primary has produced. The replication lag is zero.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&nbsp;pid | application_name | client_addr |&nbsp;&nbsp; state&nbsp;&nbsp; | sync_state | sent_lsn&nbsp; | write_lsn | flush_lsn | replay_lsn<br>-----+------------------+-------------+-----------+------------+-----------+-----------+-----------+------------<br>&nbsp; 75 | pg-node-1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 172.18.0.5&nbsp; | streaming | async&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0\/4484770 | 0\/4484770 | 0\/4484770 | 0\/4484770<br>&nbsp; 76 | pg-node-3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 172.18.0.4&nbsp; | streaming | async&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0\/4484770 | 0\/4484770 | 0\/4484770 | 0\/4484770<br>(2 rows)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Each replica was then queried directly rather than through HAProxy, bypassing the load balancer so there is no doubt which node answered. The captured result records the role and the row count together, so the query was equivalent to this, although this exact statement is a reconstruction rather than a line from one of the scripts:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT pg_is_in_recovery(), count(*) AS ha_test_count FROM ha_test;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>pg-node-1: pg_is_in_recovery = t, ha_test_count = 3<br>pg-node-3: pg_is_in_recovery = t, ha_test_count = 3<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Both report that they are in recovery, which is how a replica describes itself, and both hold all three marker rows that were inserted on the primary. This is the baseline the rest of the test is measured against.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>How the Failover Was Tested<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The approach.<\/strong> Rather than simulating a failure or asking Patroni to hand over cleanly, the primary container was stopped. Stopping the container sends a termination signal first and then kills the process if it has not exited. From the point of view of the surviving nodes, the primary simply disappeared and stopped renewing its lock. This is closer to a real crash than a planned switchover, and it is the harder case, because nothing gets a chance to hand over gracefully.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Which node was stopped.<\/strong> The primary was identified from Patroni rather than assumed. At that point the leader was pg-node-2, so pg-node-2 was the container that was stopped.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>When.<\/strong> The container was stopped at 14:51:58 UTC.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What was recorded.<\/strong> Before the failover, a full snapshot of the cluster was captured to files: the container status, the Patroni cluster list, the etcd health, the replication view, the row counts and sample rows, and the logs of all five containers. The same set was captured after the failover and again after recovery. The timeline in the next section is built from those captured container logs, not from memory.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What was checked afterwards.<\/strong> Four things. Whether a new primary appeared without help. Whether HAProxy followed it on the unchanged connection string. Whether all the data written before the failover was still present. Whether the new primary would accept a new write.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Three separate failure cases were run<\/strong>, in this order: the primary was stopped, then the old primary was restarted to see whether it could rejoin, then a replica was stopped and restarted to confirm that losing a replica behaves differently from losing a primary.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The scripts that perform the failover test, capture the state and run the recovery are in the repository.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Failover Event Timeline<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Every line below is taken from the container logs in test-results\/failover-events, except the first, which is from the command output. Times are UTC, to the millisecond where the log provides it.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Time<\/td><td>Component<\/td><td>Event<\/td><td>Source<\/td><\/tr><\/thead><tbody><tr><td>14:51:43.327<\/td><td>Patroni on pg-node-2<\/td><td>Last recorded lock renewal by the leader before the snapshot was taken<\/td><td>Log<\/td><\/tr><tr><td>14:51:58<\/td><td>Docker<\/td><td>The primary container pg-node-2 was stopped<\/td><td>Command output, not a log<\/td><\/tr><tr><td>14:52:03.969<\/td><td>Patroni on pg-node-1<\/td><td>Still following pg-node-2 as leader<\/td><td>Log<\/td><\/tr><tr><td>14:52:13.879<\/td><td>Patroni on pg-node-1<\/td><td>Still following pg-node-2<\/td><td>Log<\/td><\/tr><tr><td>14:52:23.837<\/td><td>Patroni on pg-node-1<\/td><td>Queried pg-node-3 and got back a state showing the cluster had no valid leader lock<\/td><td>Log<\/td><\/tr><tr><td>14:52:31.829<\/td><td>Patroni on pg-node-1<\/td><td>Could no longer resolve the old primary&#8217;s hostname<\/td><td>Log<\/td><\/tr><tr><td>14:52:31.928<\/td><td>Patroni on pg-node-1<\/td><td>Reported that it could not open a hardware watchdog device<\/td><td>Log<\/td><\/tr><tr><td>14:52:31.929<\/td><td>Patroni on pg-node-3<\/td><td>Tried for the lock and lost<\/td><td>Log<\/td><\/tr><tr><td>14:52:31.950<\/td><td>Patroni on pg-node-3<\/td><td>Followed the new leader after failing to obtain the lock<\/td><td>Log<\/td><\/tr><tr><td>14:52:31.951<\/td><td>Patroni on pg-node-3<\/td><td>Recorded that the lock owner was now pg-node-1<\/td><td>Log<\/td><\/tr><tr><td>14:52:31.980<\/td><td>Patroni on pg-node-1<\/td><td><strong>Promoted itself to leader by acquiring the session lock<\/strong><\/td><td>Log<\/td><\/tr><tr><td>14:52:32.087<\/td><td>Patroni on pg-node-1<\/td><td>Updated the leader lock during promotion<\/td><td>Log<\/td><\/tr><tr><td>Not timestamped<\/td><td>PostgreSQL on pg-node-1<\/td><td>Reported the server promoting<\/td><td>Log, written without a timestamp<\/td><\/tr><tr><td>14:52:36.416<\/td><td>Patroni on pg-node-3<\/td><td>Recorded the new primary timeline as 2, with the history branching at 0\/4489F20<\/td><td>Log<\/td><\/tr><tr><td>No timestamp<\/td><td>HAProxy<\/td><td>Marked pg-node-2 down in the primary pool, by connection timeout after 3003 ms<\/td><td>Log, no timestamps available<\/td><\/tr><tr><td>No timestamp<\/td><td>HAProxy<\/td><td>Marked pg-node-1 up in the primary pool, health check passed with code 200 in 8 ms<\/td><td>Log, no timestamps available<\/td><\/tr><tr><td>No timestamp<\/td><td>HAProxy<\/td><td>Marked pg-node-1 down in the replica pool, code 503 in 11 ms, which is correct because it is now the primary<\/td><td>Log, no timestamps available<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Two things in that timeline are worth pausing on.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The log lines at 14:52:03 and 14:52:13 show pg-node-1 still calmly following a primary that had already been dead for several seconds. Nothing is wrong there. The replicas cannot tell the difference between a primary that is dead and a primary that is briefly slow, so they wait for the lock in etcd to expire and let the timeout make the decision.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The timing of the expiry is worth working through, because it is not thirty seconds after the container stopped. The lock lives for thirty seconds from its <strong>last renewal<\/strong>, not from the moment of failure. The captured leader log shows renewals every ten seconds, the last visible one at 14:51:43.327, so the next would have been around 14:51:53. The container stopped at 14:51:58, five seconds after that renewal, which left the lock valid until about 14:52:23. That matches the log exactly: at 14:52:23.837 node 1 saw a state reporting the cluster as unlocked.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The practical consequence is that the delay before an election is not fixed. It varies between the loop interval and the full timeout, depending on where in the renewal cycle the failure happens. A primary that dies immediately after renewing costs you the full thirty seconds. One that dies just before a renewal was due costs you closer to twenty.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Failover Timing and Tuning<\/a><\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Measurement<\/td><td>Value<\/td><td>How it is known<\/td><\/tr><\/thead><tbody><tr><td>Primary stopped<\/td><td>14:51:58<\/td><td>Command output<\/td><\/tr><tr><td>Leader lock seen as expired<\/td><td>14:52:23.837<\/td><td>Log<\/td><\/tr><tr><td>New leader lock acquired<\/td><td>14:52:31.980<\/td><td>Log<\/td><\/tr><tr><td>Time to a new primary<\/td><td>About 34 seconds<\/td><td>Difference of the two above<\/td><\/tr><tr><td>HAProxy routing to the new primary<\/td><td>Within about 3 seconds of promotion<\/td><td>Derived from the check interval, not measured<\/td><\/tr><tr><td>Total time to working writes<\/td><td>About 36 to 37 seconds<\/td><td>Derived<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The first figure and the last are the two soft ones. The stop time came from the terminal rather than a log, and the HAProxy delay is inferred from its three second check interval, because the captured HAProxy log carries no timestamps. The middle figures are exact.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Where the time actually went<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Breaking the total down matters, because only part of it can be tuned.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Phase<\/td><td>Measured here<\/td><td>What controls it<\/td><\/tr><\/thead><tbody><tr><td>Waiting for the leader lock to expire<\/td><td>About 25 seconds, being 30 seconds from the last renewal<\/td><td>The lock timeout. This is the bulk of it.<\/td><\/tr><tr><td>Surviving nodes noticing, then acting on the next loop pass<\/td><td>About 8 seconds<\/td><td>How often the Patroni loop runs<\/td><\/tr><tr><td>Promoting the winner<\/td><td>Around 100 milliseconds<\/td><td>Database size and how much log there is to replay<\/td><\/tr><tr><td>HAProxy noticing the change<\/td><td>Up to 3 seconds<\/td><td>The health check interval<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"398\" src=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-40-1024x398.png\" alt=\"\" class=\"wp-image-60\" style=\"aspect-ratio:2.576687116564417;width:741px;height:auto\" srcset=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-40-1024x398.png 1024w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-40-300x117.png 300w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-40-766x298.png 766w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-40.png 1527w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The hatched block on the right is the HAProxy check, drawn differently because it is the one segment that was not measured. The captured HAProxy log has no timestamps, so its width comes from the configured three second interval.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Around ninety percent of the outage was the cluster deliberately waiting, and none of it was PostgreSQL being slow. The promotion itself took about a tenth of a second, from acquiring the session lock at 14:52:31.980 to the leader lock being updated at 14:52:32.087. The cluster was waiting to be sure the primary was really gone.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The eight second gap between noticing at 14:52:23.837 and promoting at 14:52:31.980 is the part people do not expect. Patroni had already seen that the cluster was unlocked, but it does not act in the middle of a loop pass. It finished the pass it was in, which included checking the other replica&#8217;s log position to confirm it was a suitable candidate, and acted on the next one ten seconds later.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>The trade-off if you want it faster<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The obvious reaction is to cut the timeout to ten seconds. It is worth resisting that, because this is not a performance setting. It is a choice about which kind of failure you would rather have.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A shorter timeout gives faster failover but makes it more likely that a brief network problem, a long garbage collection pause, or an overloaded etcd is mistaken for a dead primary. A false failover is a real outage. Connections drop, transactions in flight are lost, and a node gets promoted that did not need promoting.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A longer timeout gives fewer false alarms and longer real outages.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Patroni requires the timeout to be comfortably larger than the loop interval plus twice the retry timeout. With the values used here, ten and ten, the minimum workable timeout is thirty, which is exactly why thirty was chosen. If you lower the timeout you have to lower the other two as well, or Patroni will reject the configuration.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A reasonably aggressive but still safe set of values on a reliable network is a timeout of 15 with a loop interval and retry timeout of 5. That brings failover to roughly 15 to 20 seconds. Going lower than that needs a genuinely fast and stable network between the nodes and etcd.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">These numbers come from one run on one Docker host. Treat them as an illustration of where the time goes, not as a service level you can promise. Measure your own environment.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Post-Failover Verification<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Connecting with the same unchanged connection string, and asking the server to identify itself:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT inet_server_addr()&nbsp; AS server,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; pg_is_in_recovery() AS is_replica,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; current_database()&nbsp; AS db;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">HAProxy was now routing to a different node:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&nbsp;&nbsp; server&nbsp;&nbsp; | is_replica<br>------------+------------<br>&nbsp;172.18.0.5 | f<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The address changed from the old primary to the new one, and the replica flag is still false, meaning this is a primary. The client connection details did not change at all.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">All the data written before the failover was present:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM ha_test ORDER BY id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&nbsp;id |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; message&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; created_at<br>----+----------------------------------+-------------------------------<br>&nbsp; 1 | record-created-before-failover-1 | 2026-08-29 14:50:16.460916+00<br>&nbsp; 2 | record-created-before-failover-2 | 2026-08-29 14:50:16.460916+00<br>&nbsp; 3 | record-created-before-failover-3 | 2026-08-29 14:50:16.460916+00<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The counts were confirmed with:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT 'customers' AS tbl, count(*) FROM customers<br>UNION ALL SELECT 'orders',&nbsp; count(*) FROM orders<br>UNION ALL SELECT 'ha_test', count(*) FROM ha_test;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Row counts were unchanged: five customers, seven orders and three marker rows.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Replication at this point shows the cluster running on two nodes:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&nbsp;pid | application_name | client_addr |&nbsp;&nbsp; state&nbsp;&nbsp; | sync_state | sent_lsn&nbsp; | write_lsn | flush_lsn | replay_lsn<br>-----+------------------+-------------+-----------+------------+-----------+-----------+-----------+------------<br>&nbsp;423 | pg-node-3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 172.18.0.4&nbsp; | streaming | async&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0\/4499AB0 | 0\/4499AB0 | 0\/4499AB0 | 0\/4499AB0<br>(1 row)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Only one replica is listed, because pg-node-2 was still stopped. The new primary is streaming to the one replica it has left, with zero lag. This is the degraded state the cluster runs in after a failover and before the failed node is brought back: still serving reads and writes, but with no spare node to fail over to.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A new row was then inserted through HAProxy to prove the new primary accepts writes:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO ha_test (message) VALUES ('record-created-AFTER-failover');<br><br>SELECT * FROM ha_test ORDER BY id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The message text above is the one that is actually in the captured table. The failover script in the repository appends a timestamp to that string, so running it today produces a slightly different value. This is one of the specific divergences noted earlier between the scripts and this run.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&nbsp;id |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; message&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; created_at<br>----+----------------------------------+-------------------------------<br>&nbsp; 1 | record-created-before-failover-1 | 2026-08-29 14:50:16.460916+00<br>&nbsp; 2 | record-created-before-failover-2 | 2026-08-29 14:50:16.460916+00<br>&nbsp; 3 | record-created-before-failover-3 | 2026-08-29 14:50:16.460916+00<br>&nbsp;34 | record-created-AFTER-failover&nbsp;&nbsp;&nbsp; | 2026-08-29 14:57:10.552122+00<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The write succeeded. The new row has an unexpected identifier, which the next section explains.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Why the Row ID Jumped<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The identifier went from 3 to 34. This looks alarming and is not. No rows were lost. The table still holds exactly the three rows from before the failover, plus the new one.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The cause is the way PostgreSQL records sequences in its log. For performance, a sequence does not write a log record every single time it hands out a value. Instead it records a value thirty two increments ahead, then hands out that batch from memory without touching the log again. The constant is named SEQ_LOG_VALS in the PostgreSQL source, and the comment beside it states the consequence plainly: in the event of a crash, as many values as were pre-logged can be skipped over.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So after a crash or a promotion, the sequence carries on from the last value that reached the log, not from the last value actually handed out. The values allocated in memory are abandoned on purpose, because that is what guarantees a restarted server can never hand out an identifier it has already given to somebody else.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The arithmetic lines up exactly. The old primary had recorded ahead to 33, the newly promoted primary carried on from there, and the next value was 34.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The lesson is worth taking away from the failover entirely. <strong>PostgreSQL sequences guarantee that values are unique and roughly increasing. They never guarantee that there are no gaps.<\/strong> Any application that treats an auto generated identifier as a gapless counter, for example using it for invoice numbers or reading the highest value to count rows, will eventually be wrong, with or without a failover.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Old Primary Recovery<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The old primary container was started again about seven minutes after it was stopped. The first Patroni line in the captured log is at 14:58:58.362 UTC.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What happened is not what most write ups of Patroni would lead you to expect, and it is worth being precise about it.<\/strong> The node rejoined as a replica without pg_rewind running at all.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The way to watch this is the container log, since Patroni narrates what it decides:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>docker logs pg-node-2<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This is the captured log, from test-results\/after-recovery\/logs-pg-node-2-rejoin.txt:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>2026-08-29 14:58:58,362 INFO: Lock owner: pg-node-1; I am pg-node-2<br>2026-08-29 14:58:58,363 INFO: starting as a secondary<br>2026-08-29 14:58:58.638 UTC &#91;51] LOG:&nbsp; redirecting log output to logging collector process<br>2026-08-29 14:58:58,642 INFO: postmaster pid=51<br>localhost:5432 - rejecting connections<br>localhost:5432 - rejecting connections<br>localhost:5432 - accepting connections<br>2026-08-29 14:58:59,761 INFO: Lock owner: pg-node-1; I am pg-node-2<br>2026-08-29 14:58:59,824 INFO: no action. I am (pg-node-2), a secondary, and following a leader (pg-node-1)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Reading that in order: Patroni checked etcd, saw that pg-node-1 held the lock, and decided immediately to start as a secondary. PostgreSQL came up, refused connections for a moment while it completed recovery, then began accepting them. By 14:58:59.824 the node was a replica following the new leader.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>The whole rejoin took about 1.5 seconds<\/strong>, from the first Patroni line to following the leader.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Why pg_rewind was not needed<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The reason is the way the container was stopped. Stopping a container sends a termination signal first, and Patroni handles that signal by shutting PostgreSQL down cleanly. A clean shutdown means the old primary wrote everything it had accepted and recorded a proper shutdown checkpoint. Its log position did not run ahead of the point where the new primary&#8217;s history branched, which the captured log from pg-node-3 records as 0\/4489F20 on timeline 1.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">With no divergence to undo, there is nothing for pg_rewind to do. PostgreSQL can simply follow the new timeline from where it left off, which is exactly what it did.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Before starting PostgreSQL, Patroni inspected the local data directory. That inspection appears as a block of settings at the top of the same captured log file, and it is the output of the PostgreSQL control data tool, which you can run yourself:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>docker exec pg-node-2 pg_controldata \/var\/lib\/postgresql\/data\/pgdata<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Three lines in it are the ones that matter for recovery:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&nbsp; wal_level setting: replica<br>&nbsp; wal_log_hints setting: on<br>&nbsp; Data page checksum version: 1<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Hint logging is on and data checksums are enabled. Those are the two prerequisites for pg_rewind. It was not needed on this occasion, but the cluster was correctly built so that it <strong>could<\/strong> have run.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>When you would actually see pg_rewind<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">pg_rewind becomes necessary when the old primary really did diverge, which happens when it accepted a write that the new primary never received and then died without a clean shutdown. Pull the power on the machine, kill the container with an immediate kill rather than a polite stop, or lose the network to a primary that carries on accepting writes for a few seconds, and you get a genuine divergence.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In that case Patroni runs crash recovery on the local data directory, compares timelines, finds the point where the two histories split, and rewinds the data directory back to the last checkpoint both servers still agree on. PostgreSQL then replays forward from the new primary. Any transaction that existed only on the old primary is discarded, because keeping it would mean two conflicting versions of history.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is the case that makes wal_log_hints worth setting from day one. Without it, and without data checksums, pg_rewind refuses to run and the only way to bring the node back is to copy the whole database again.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"428\" height=\"1024\" src=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-43-428x1024.png\" alt=\"\" class=\"wp-image-63\" style=\"aspect-ratio:0.4179357021996616;width:348px;height:auto\" srcset=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-43-428x1024.png 428w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-43-125x300.png 125w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-43-768x1837.png 768w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-43-642x1536.png 642w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-43-856x2048.png 856w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-43-scaled.png 1070w\" sizes=\"auto, (max-width: 428px) 100vw, 428px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a>The cluster after recovery<\/a><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>docker exec pg-node-1 patronictl -c \/etc\/patroni\/patroni.yml list<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+ Cluster: pg-ha-cluster (7679465736627556386) ----+-----------+<br>| Member&nbsp;&nbsp;&nbsp; | Host&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Role&nbsp;&nbsp;&nbsp; | State&nbsp;&nbsp;&nbsp;&nbsp; | TL | Lag in MB |<br>+-----------+-----------+---------+-----------+----+-----------+<br>| pg-node-1 | pg-node-1 | Leader&nbsp; | running&nbsp;&nbsp; |&nbsp; 2 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>| pg-node-2 | pg-node-2 | Replica | streaming |&nbsp; 2 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |<br>| pg-node-3 | pg-node-3 | Replica | streaming |&nbsp; 2 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |<br>+-----------+-----------+---------+-----------+----+-----------+<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">All three nodes are healthy, all three are on timeline 2, and both replicas have zero lag. Note that the old primary did not take its role back. Patroni does not fail back automatically, and the node that was promoted stays the leader.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Replication from the new primary, now feeding both replicas again. This is the same pg_stat_replication query as before, ordered by name:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT application_name, client_addr, state, sync_state,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sent_lsn, write_lsn, flush_lsn, replay_lsn<br>FROM pg_stat_replication<br>ORDER BY application_name;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&nbsp;pid&nbsp; | application_name | client_addr |&nbsp;&nbsp; state&nbsp;&nbsp; | sync_state | sent_lsn&nbsp; | write_lsn | flush_lsn | replay_lsn<br>------+------------------+-------------+-----------+------------+-----------+-----------+-----------+------------<br>&nbsp;1210 | pg-node-3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 172.18.0.4&nbsp; | streaming | async&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0\/4499E60 | 0\/4499E60 | 0\/4499E60 | 0\/4499E60<br>&nbsp;1066 | pg-node-2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 172.18.0.3&nbsp; | streaming | async&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 0\/4499E60 | 0\/4499E60 | 0\/4499E60 | 0\/4499E60<br>(2 rows)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Both replicas are at the same log position, so the lag is zero. The process identifiers are new because both connections were established after the failover. The address 172.18.0.3 is the node that used to be the primary, now appearing as a client of the node that replaced it.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Querying the rejoined node directly, connecting to the container rather than through HAProxy, confirmed it holds everything including the row written while it was switched off:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM ha_test ORDER BY id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&nbsp;id |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; message&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; created_at<br>----+----------------------------------+-------------------------------<br>&nbsp; 1 | record-created-before-failover-1 | 2026-08-29 14:50:16.460916+00<br>&nbsp; 2 | record-created-before-failover-2 | 2026-08-29 14:50:16.460916+00<br>&nbsp; 3 | record-created-before-failover-3 | 2026-08-29 14:50:16.460916+00<br>&nbsp;34 | record-created-AFTER-failover&nbsp;&nbsp;&nbsp; | 2026-08-29 14:57:10.552122+00<br>&nbsp;35 | record-during-replica-failure&nbsp;&nbsp;&nbsp; | 2026-08-29 15:00:23.081974+00<br>(5 rows)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Row 35 needs a word of explanation, because it was written at 15:00:23, which is after this recovery. The after-recovery snapshot in the repository was taken at the very end of the session, once the replica failure test in the next section had also finished. That is why five rows appear here rather than four. Rows 1 to 34 are the state at the moment of the rejoin.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Replica Failure Test<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">With all three nodes healthy again, a replica was stopped rather than the primary, to confirm that the two cases behave differently.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Node pg-node-3 was stopped. The primary stayed the primary, no election happened, and the application kept working through HAProxy. A write performed during the outage succeeded:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO ha_test (message) VALUES ('record-during-replica-failure');<br><br>SELECT * FROM ha_test ORDER BY id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&nbsp;id |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; message&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; created_at<br>----+----------------------------------+-------------------------------<br>&nbsp; 1 | record-created-before-failover-1 | 2026-08-29 14:50:16.460916+00<br>&nbsp; 2 | record-created-before-failover-2 | 2026-08-29 14:50:16.460916+00<br>&nbsp; 3 | record-created-before-failover-3 | 2026-08-29 14:50:16.460916+00<br>&nbsp;34 | record-created-AFTER-failover&nbsp;&nbsp;&nbsp; | 2026-08-29 14:57:10.552122+00<br>&nbsp;35 | record-during-replica-failure&nbsp;&nbsp;&nbsp; | 2026-08-29 15:00:23.081974+00<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Then the container was started again and the cluster listed once more:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>docker start pg-node-3<br>docker exec pg-node-1 patronictl -c \/etc\/patroni\/patroni.yml list<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">It rejoined as a replica within seconds:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+ Cluster: pg-ha-cluster (7679465736627556386) ----+-----------+<br>| Member&nbsp;&nbsp;&nbsp; | Host&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Role&nbsp;&nbsp;&nbsp; | State&nbsp;&nbsp;&nbsp;&nbsp; | TL | Lag in MB |<br>+-----------+-----------+---------+-----------+----+-----------+<br>| pg-node-1 | pg-node-1 | Leader&nbsp; | running&nbsp;&nbsp; |&nbsp; 2 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>| pg-node-2 | pg-node-2 | Replica | streaming |&nbsp; 2 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |<br>| pg-node-3 | pg-node-3 | Replica | streaming |&nbsp; 2 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |<br>+-----------+-----------+---------+-----------+----+-----------+<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Note that this recovery needed no pg_rewind either. A replica never accepts writes of its own, so its history cannot diverge. It simply carried on streaming from where it left off.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So neither recovery in this test required a rewind: the replica because it could not diverge, and the old primary because it was shut down cleanly. That is the normal outcome for planned container stops. A rewind is what you get after an unplanned, abrupt loss of a primary.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Losing a replica is a much smaller event than losing a primary. The primary carries on, the other replica carries on, writes are never interrupted, and HAProxy just takes the missing node out of the read pool and puts it back when it returns.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Final Cluster State<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">After all three tests the cluster was healthy:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>pg-node-1 is the leader, running on timeline 2.<\/li>\n\n\n\n<li>pg-node-2 is a replica, streaming on timeline 2, with zero lag.<\/li>\n\n\n\n<li>pg-node-3 is a replica, streaming on timeline 2, with zero lag.<\/li>\n\n\n\n<li>etcd is healthy.<\/li>\n\n\n\n<li>HAProxy routes port 5000 to pg-node-1 and port 5001 to the two replicas.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"517\" src=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-41-1024x517.png\" alt=\"\" class=\"wp-image-61\" style=\"aspect-ratio:1.9811320754716981;width:1058px;height:auto\" srcset=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-41-1024x517.png 1024w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-41-300x151.png 300w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-41-766x387.png 766w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-41.png 1414w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The chart is drawn from the three Patroni cluster listings in the captured evidence. A primary existed at every checkpoint, which is why the application kept working. What disappeared in the middle column is not availability but redundancy: for the seven minutes the old primary stayed down, the cluster was one node away from having nothing to fail over to. That is the window you are trying to keep short in production, and it lasts as long as it takes somebody to bring the failed node back.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The data:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Table<\/td><td>Rows<\/td><\/tr><\/thead><tbody><tr><td>customers<\/td><td>5<\/td><\/tr><tr><td>orders<\/td><td>7<\/td><\/tr><tr><td>ha_test<\/td><td>5<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The marker table holds five rows: three from before the failover, one written after the failover, and one written while a replica was down. Every row is present and consistent on all three nodes.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Connection Path Explained<\/a><\/h2>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"536\" src=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-44-1024x536.png\" alt=\"\" class=\"wp-image-64\" style=\"aspect-ratio:1.9090909090909092;width:748px;height:auto\" srcset=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-44-1024x536.png 1024w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-44-300x157.png 300w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-44-1536x805.png 1536w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-44-767x402.png 767w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-44-2048x1073.png 2048w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The application connects to HAProxy. The application does not connect to Patroni. The Patroni API exists for HAProxy to health check, and nothing else. The application never needs to know which node is the primary.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When the primary changes, the next health check finds out, and new connections go to the new primary.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Failover Internals<\/a><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><a>The sequence of events<\/a><\/h3>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"1003\" src=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-46-1024x1003.png\" alt=\"\" class=\"wp-image-66\" style=\"aspect-ratio:1.0218978102189782;width:747px;height:auto\" srcset=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-46-1024x1003.png 1024w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-46-300x294.png 300w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-46-768x752.png 768w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-46-1536x1504.png 1536w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-46-2048x2005.png 2048w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a>The same thing as a state machine<\/a><\/h3>\n\n\n\n<figure class=\"wp-block-image aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"387\" src=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-45-1024x387.png\" alt=\"\" class=\"wp-image-65\" style=\"aspect-ratio:2.641509433962264;width:964px;height:auto\" srcset=\"https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-45-1024x387.png 1024w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-45-300x113.png 300w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-45-765x289.png 765w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-45-1536x580.png 1536w, https:\/\/metaintelligencesystems.com\/blog\/wp-content\/uploads\/2026\/09\/image-45-2048x773.png 2048w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Walking through what happened, step by step:<\/p>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><strong>The primary fails.<\/strong> The PostgreSQL process stops, or the container stops, or the network becomes unreachable.<\/li>\n\n\n\n<li><strong>The failure becomes visible.<\/strong> On the failed node itself, Patroni can no longer talk to PostgreSQL. What matters to the other nodes, though, is simply that the leader lock in etcd has stopped being renewed.<\/li>\n\n\n\n<li><strong>The lock expires.<\/strong> With a thirty second timeout and a ten second loop, the lock survives up to three missed renewals before etcd drops it. In this test the key expired around thirty seconds after the primary was stopped. The surviving nodes then noticed on their next pass and the winner held the lock three seconds later. Those extra seconds are the loop interval, not the timeout, because Patroni only acts when its loop next wakes up.<\/li>\n\n\n\n<li><strong>The election happens.<\/strong> The surviving nodes try to create the leader key. Only one can succeed, because etcd makes that operation atomic. The winner becomes the leader.<\/li>\n\n\n\n<li><strong>The winner is promoted.<\/strong> Patroni promotes the local PostgreSQL from read only standby to read write primary. PostgreSQL increases the timeline, in this case from 1 to 2.<\/li>\n\n\n\n<li><strong>The loser follows.<\/strong> The node that lost sees the new leader in etcd and starts streaming from it instead.<\/li>\n\n\n\n<li><strong>HAProxy catches up.<\/strong> Its health check finds that the old primary no longer answers and the new one does, so it moves the traffic.<\/li>\n\n\n\n<li><strong>New connections work.<\/strong> Clients connecting to port 5000 reach the new primary. The connection string never changed.<\/li>\n\n\n\n<li><strong>The old primary comes back later.<\/strong> Patroni sees that somebody else holds the lock, rewinds the diverged data directory, and rejoins as a replica.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Existing Connections During Failover<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This is the part that catches people out, so it is worth stating bluntly. <strong>HAProxy does not move existing connections to the new primary.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When the primary fails:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Any connection that was already open to the old primary is broken. The client sees an error or a timeout.<\/li>\n\n\n\n<li>HAProxy closes those broken sessions promptly rather than letting them hang, because of the option that shuts down sessions on a server marked down.<\/li>\n\n\n\n<li>New connections made after HAProxy has noticed the new primary are routed correctly.<\/li>\n\n\n\n<li>Transactions that were in progress are lost. They are not replayed. The application has to notice the error and retry.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">So applications need connection retry with a backoff, transaction retry so a failed transaction is run again on a new connection, and a connection pool that health checks and discards stale connections.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">HAProxy gives you a stable address. It does not give you unbroken sessions. High availability at the infrastructure layer is necessary but not sufficient, and the application has to play its part.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Problems Hit While Building This<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">These are the failures that actually happened during this build, not a general list of things that might go wrong.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>The etcd version 2 API returned 404<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Patroni logged a failure to get the list of machines from the etcd version 2 endpoint, with a response of 404 page not found.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The cause is that etcd 3.5 has the older version 2 API disabled by default, while the plain Patroni etcd package uses that version 2 client.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The fix has two parts. Install the Patroni variant that speaks the version 3 API, and configure the version 3 section in the Patroni configuration rather than the version 2 section. After the fix, Patroni logs that it has selected an etcd server, with no version 2 errors.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>The initialisation step refused to run as root<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Creating the data directory failed with an error saying it cannot be run as root, and a hint suggesting logging in as the unprivileged user that will own the server process.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The cause is that the custom image ran as root, and PostgreSQL refuses to create a data directory as root for safety.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The fix is to switch the image to the postgres user, and to make sure the directory holding the Patroni configuration is owned by that user so it remains writable.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>The environment variable quietly overrode the configuration file<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Even with the version 3 section present in the configuration file, Patroni still used the version 2 protocol.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The cause is that Patroni reads environment variables whose names map onto configuration sections, and those variables win over the file. A variable naming the version 2 hosts therefore selected the version 2 client no matter what the file said.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The fix is to name the environment variable so that it maps to the version 3 section instead.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This one is worth remembering as a general point. With Patroni, an environment variable will silently beat the configuration file, so if a setting appears to be ignored, check the environment before doubting the file.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Log messages that look like problems and are not<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The captured logs contain three recurring messages that will worry you the first time and can be ignored in this environment.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A watchdog device warning during promotion.<\/strong> At the exact moment of promotion, Patroni logged that it could not activate a Linux watchdog device, because \/dev\/watchdog does not exist. Patroni can use a hardware watchdog as a last line of defence, so that a node which loses its lock but somehow fails to demote itself gets forcibly rebooted. Containers have no watchdog device, so the feature is unavailable. The promotion went ahead normally. In production on real hardware this is worth configuring, because it closes a genuine gap.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A watchprefix protocol error against etcd.<\/strong> Every node logged a broken connection error mentioning an invalid chunk length, at startup and again during the failover. This is Patroni&#8217;s long lived watch on etcd being cut and re established, which is normal behaviour when the key being watched changes or the connection is recycled. The cluster carried on working through every one of these.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Connection reset tracebacks from the Patroni API.<\/strong> Several nodes logged full Python tracebacks ending in a connection reset by peer, for requests coming from the HAProxy container address. This is HAProxy closing a health check connection as soon as it has the status code it needs, before Patroni has finished writing the response body. It is noisy and harmless. If it bothers you, the volume drops if you lengthen the health check interval.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">None of these three affected the outcome, and all three appear in a cluster that is working correctly. The reason to know about them is so you do not go looking for a fault that is not there.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/h2>\n\n\n\n<h2 class=\"wp-block-heading\"><a>Validation Checklist<\/a><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Every item below was confirmed during the run.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><td>Check<\/td><td>Result<\/td><\/tr><\/thead><tbody><tr><td>Compose configuration valid<\/td><td>Passed<\/td><\/tr><tr><td>All containers start<\/td><td>Passed<\/td><\/tr><tr><td>etcd healthy<\/td><td>Passed<\/td><\/tr><tr><td>Patroni healthy on all nodes<\/td><td>Passed<\/td><\/tr><tr><td>One primary exists<\/td><td>Passed<\/td><\/tr><tr><td>Two replicas exist<\/td><td>Passed<\/td><\/tr><tr><td>Streaming replication working with zero lag<\/td><td>Passed<\/td><\/tr><tr><td>Application database and sample data present<\/td><td>Passed<\/td><\/tr><tr><td>Client connects through the HAProxy write port<\/td><td>Passed<\/td><\/tr><tr><td>Primary failure tested by stopping the container<\/td><td>Passed<\/td><\/tr><tr><td>New primary elected without intervention<\/td><td>Passed, pg-node-1 after about 34 seconds<\/td><\/tr><tr><td>New primary accepts writes<\/td><td>Passed<\/td><\/tr><tr><td>Data written before the failover survives<\/td><td>Passed<\/td><\/tr><tr><td>HAProxy routes to the new primary<\/td><td>Passed. Order and reason codes confirmed in the log, though that log has no timestamps<\/td><\/tr><tr><td>Old primary rejoins the cluster<\/td><td>Passed, in about 1.5 seconds, without needing pg_rewind<\/td><\/tr><tr><td>Three node cluster restored on timeline 2<\/td><td>Passed<\/td><\/tr><tr><td>Replica failure tested separately<\/td><td>Passed, no failover and no interruption to writes<\/td><\/tr><tr><td>All figures in this document taken from captured output<\/td><td>Passed<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Few things worth taking away<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Each component solves exactly one problem.<\/strong> PostgreSQL replicates, etcd arbitrates, Patroni decides and acts, HAProxy routes. No component covers for another, and when something breaks that split tells you where to look first.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Failover time is a policy decision, not a measure of speed.<\/strong> Thirty of the thirty three seconds were the cluster deliberately waiting out a timeout to be sure the primary was really gone. The promotion took under a second. You can make it faster, but you pay for that speed with a greater risk of failing over when nothing was actually wrong.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A stable address is not an unbroken session.<\/strong> The connection string never changed, and every open connection still died. Applications need retry logic.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>How the primary dies changes how it comes back.<\/strong> The rejoin here took 1.5 seconds and needed no pg_rewind, because stopping the container let Patroni shut PostgreSQL down cleanly. An abrupt loss would have diverged the node and forced a rewind. Hint logging and data checksums were enabled at creation time, so that rewind would have been possible. Neither can be added after an incident, and checksums can only be set when the data directory is first created.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A cluster you have not deliberately broken is not a tested cluster.<\/strong> Everything here worked, and that is only known because the primary was actually killed rather than assumed to be survivable.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Being honest about what this protects<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">This build defends against exactly one thing: the loss of a single PostgreSQL node. It does nothing about the single etcd node, the single host, disk corruption that replicates to every replica, or a mistaken delete that reaches all three nodes in milliseconds.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Replication keeps three copies of your data in step, including the copy of an accident. A real backup strategy, with restores that have actually been tested, belongs at the top of the production list.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Reasonable next steps<\/a><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Turn on synchronous replication and measure what it costs in write latency against what it buys in durability.<\/li>\n\n\n\n<li>Replace the single etcd node with three, then break the network to one of them.<\/li>\n\n\n\n<li>Put a connection pooler in front of HAProxy and watch how pooling changes what the application sees during a failover.<\/li>\n\n\n\n<li>Run a planned switchover with the Patroni command line tool and compare it with the unplanned failover measured here. It is far quicker, because nothing has to wait for a timeout to expire.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><a>Source code<\/a><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">All configuration files and test scripts referred to in this document are in the public repository:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/github.com\/rameshsraj\/pgandpatroni\">https:\/\/github.com\/rameshsraj\/pgandpatroni<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The Patroni build is in the postgres-patroni-ha folder. This document explains what each piece does and what happened when it was tested. It does not reproduce the files themselves, so please read them in the repository if you plan to adapt them.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,8],"tags":[15,13,16,14,11,5,12],"class_list":["post-40","post","type-post","status-publish","format-standard","hentry","category-blog","category-postgres","tag-etcd","tag-ha","tag-haproxy","tag-high-availability","tag-patroni","tag-postgresql","tag-replication"],"_links":{"self":[{"href":"https:\/\/metaintelligencesystems.com\/blog\/wp-json\/wp\/v2\/posts\/40","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/metaintelligencesystems.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/metaintelligencesystems.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/metaintelligencesystems.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/metaintelligencesystems.com\/blog\/wp-json\/wp\/v2\/comments?post=40"}],"version-history":[{"count":3,"href":"https:\/\/metaintelligencesystems.com\/blog\/wp-json\/wp\/v2\/posts\/40\/revisions"}],"predecessor-version":[{"id":261,"href":"https:\/\/metaintelligencesystems.com\/blog\/wp-json\/wp\/v2\/posts\/40\/revisions\/261"}],"wp:attachment":[{"href":"https:\/\/metaintelligencesystems.com\/blog\/wp-json\/wp\/v2\/media?parent=40"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/metaintelligencesystems.com\/blog\/wp-json\/wp\/v2\/categories?post=40"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/metaintelligencesystems.com\/blog\/wp-json\/wp\/v2\/tags?post=40"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}