We Open-Sourced a Complete PostgreSQL 17 HA Cluster: Patroni, etcd, HAProxy and Failover Slots

Everyone agrees PostgreSQL needs high availability. Far fewer teams have it, because the gap between "we have a replica" and "the application keeps writing when a machine dies" is filled with decisions nobody wants to make at 3 a.m.: who is the primary now, how does the application find it, and why did the reporting server's subscription break after the promote. We built that layer, ran it, and today we are releasing it.

The problem it solves

The scenario is ordinary: a site — a plant, a branch, a data center — must keep accepting writes if one database machine dies. Headquarters needs an up-to-date copy of that data for reporting, without ever writing into the site cluster.

With two PostgreSQL servers, native streaming replication and a manual promote, three things go wrong at exactly the worst moment:

• Someone has to decide who the primary is. A human decision, under pressure, with incomplete information — and if two people decide differently, you get split brain.
• The application has to discover the new address. Connection strings pinned to an IP do not fail over; they fail.
• The reporting server's logical subscription breaks. The replication slot lived on the old primary. It did not move. You rebuild the subscription and re-copy the data, on the day you least want to.

The repository ties those pieces together with components that already exist and are well understood, wired in the specific way that makes them work as one system:

Architecture at a glance

One HA model: Patroni. There is no manual promote path, no hand-rolled pg_basebackup rebuild, no dual-mode "sometimes automatic, sometimes not". Patroni elects the primary, promotes the replica, and rebuilds a broken member.

            application (read + write)          headquarters (read-only)
              |                                  |
           VIP :5000  ── HAProxy ── :5001        |
              |            |                     |
  ┌───────────┴──┐      ┌──┴───────────┐         |
  |   db1        |◄────►|   db2        |         |
  | PG 17        | sync | PG 17        |         |
  | Patroni+etcd | WAL  | Patroni+etcd |         |
  └──────┬───────┘      └───────┬──────┘         |
         |     etcd quorum      |                |
         └────────┬─────────────┘                |
                  |                              |
             ┌────┴─────┐                  ┌─────┴──────┐
             | witness  |                  |  central   |
             | etcd only|                  | PG 17 sub  |
             └──────────┘                  └────────────┘
                           logical replication
                           slot site_001_slot (failover = true)

The witness is the part people try to skip, and it is the part that makes the difference between a cluster and a coin flip. With two voters, a network partition gives you 1 vs 1: neither side can prove it holds the majority, so neither can safely be primary. The third etcd — on a tiny VM, with no database on it — breaks the tie deterministically.

Two replication layers that must not be confused

This is where most home-grown setups go wrong, so it is worth being explicit. There are two entirely separate replication mechanisms in play, and Patroni is responsible for only one of them.

Layer 1 — physical, synchronous, managed by Patroni

• db1 and db2 form a single Patroni scope (pg-ha).
• The leader accepts writes; the standby applies WAL through a physical slot named after the member.
• synchronous_mode: true and synchronous_mode_strict: true: COMMIT waits for the synchronous replica.
• Ubuntu watchdog: if Patroni loses the lock and cannot demote PostgreSQL in time, the node reboots itself.

Layer 2 — logical, asynchronous, plain PostgreSQL

Patroni does not replicate anything to headquarters. That copy is native PostgreSQL logical replication, pointed at the HAProxy writer port:

• Central subscribes to a publication on the application database (site_001_pub on appdb).
• The subscription is created with failover = true and slot_name = site_001_slot.
• The standby keeps that slot synchronized (sync_replication_slots, hot_standby_feedback).
• The primary lists the replica's physical slot in synchronized_standby_slots, so the subscriber can never run ahead of the standby.
• Central connects to VIP:5000 — never to db1 or db2 directly.

This is the piece PostgreSQL 17 finally made possible. Before failover slots, a logical subscriber pointed at a cluster that promotes was a scheduled outage: the slot existed only on the old primary, so after promotion the subscription had nothing to consume and had to be recreated — meaning a full re-copy of every table. With failover = true, the slot already exists on the new primary, at the right position, and the stream simply continues.

Try it in five minutes: the Docker lab

The whole cluster — three etcd nodes, two Patroni/PostgreSQL nodes, HAProxy and the central subscriber — starts on one machine with Docker Compose. It exists so you can break things on purpose before you own them in production.

git clone https://github.com/johnvithera01/postgresql-patroni-ha.git
cd postgresql-patroni-ha

./scripts/lab/reset-lab.sh        # build and start the whole cluster
./scripts/lab/status.sh           # who is the leader, who is sync standby
./scripts/lab/setup-logical.sh    # publication, failover slot, subscription

Requirements are only Docker Compose v2 and Bash. What comes up:

psql "host=127.0.0.1 port=15000 user=postgres dbname=appdb"        # writer
psql "host=127.0.0.1 port=15001 user=postgres dbname=appdb"        # reader
psql "host=127.0.0.1 port=15002 user=postgres dbname=site_001_db"  # central

Now break it on purpose

Reading about failover teaches you nothing. Watching a leader die while a writer session is open teaches you a lot:

./scripts/lab/test-switchover.sh          # planned, controlled promotion
./scripts/lab/test-failover.sh           # kill the leader, watch the election
./scripts/lab/test-logical-continuity.sh # the full run: switchover + kill + verify central

That last one is the interesting test, and the one the CI pipeline runs on every commit. It writes to the cluster, forces a planned switchover, kills the new leader, waits for the election, writes again, and then verifies that headquarters received every row — with the same subscription, the same slot, and no manual intervention. If that assertion fails, the design is broken and the build says so.

Production on Ubuntu: four machines

Production is four VMs, and each script is idempotent enough to be run from a checklist rather than from memory:

Decisions already baked into the scripts, so you do not re-litigate them under pressure: PostgreSQL 17 with Patroni 4.1+, synchronous_mode_strict (RPO zero), TLS on etcd and PostgreSQL, watchdog required, and a clean install plus restore rather than an in-place conversion of an existing Debian cluster.

# on db1, db2 and witness
sudo ./ubuntu/install-env.sh ubuntu/env.example
sudo nano /etc/pg-patroni-ha/env
sudo bash ubuntu/patroni/setup-etcd.sh

# witness
sudo bash ubuntu/patroni/setup-witness.sh

# db1 and db2
sudo bash ubuntu/patroni/setup-patroni-node.sh
sudo bash ubuntu/patroni/bootstrap-cluster.sh   # first node only
sudo bash ubuntu/patroni/join-replica.sh        # second node
sudo bash ubuntu/patroni/setup-haproxy.sh
sudo bash ubuntu/patroni/setup-keepalived.sh
sudo bash ubuntu/patroni/setup-failover-slots.sh site_001

# central
sudo bash ubuntu/central/setup-server.sh
sudo bash ubuntu/central/setup-subscription.sh site_001

The full runbook is docs/production.md; the reasoning behind the layers, the quorum and the explicit non-goals is in docs/architecture.md.

Day-to-day operations

sudo bash ubuntu/patroni/status.sh            # cluster state, lag, who leads
sudo bash ubuntu/patroni/switchover.sh db2   # planned, in a maintenance window
sudo bash ubuntu/patroni/failover.sh db2     # forced, when the leader is gone
sudo bash ubuntu/patroni/reinit-member.sh db1 # rebuild a member from the leader
sudo bash ubuntu/monitor/check-all.sh        # the cron checks

Two operational rules are worth memorizing, because both are counterintuitive and both are expensive to get wrong.

After a healthy failover, do not DROP SUBSCRIPTION on Central. The instinct is to rebuild it, and rebuilding it triggers a full re-copy of every table. It is not needed: the failover slot already exists on the new primary and Central is connected through HAProxy, so it reconnects to the writer and keeps consuming the same site_001_slot.

When a rejoined node cannot sync the failover slot, reinit it — do not rewind it again. After a crash the old leader may come back through pg_rewind. If its local catalog xmin has already moved too far, PostgreSQL 17's slot-sync worker refuses to copy the failover slot and says so plainly:

Synchronization could lead to data loss

That message is the slot-sync worker protecting you: copying the slot at that point would let the subscriber skip changes it never received. The correct fix is a clean clone from the current primary:

sudo bash ubuntu/patroni/reinit-member.sh db1

The limits, stated up front

An HA design that does not tell you where it breaks is marketing. These are deliberate trade-offs, documented in the repository itself:

• RPO zero stalls writes when there is no synchronous standby. Availability of writes is traded for never losing a confirmed commit.
• Losing the witness and one database node at once loses quorum. The survivor holds no lock and will not promote itself. This is intentional — a lone node that cannot prove it is alone is exactly how split brain starts.
• Logical replication is not a backup. A DELETE replicates faithfully and instantly. Use pgBackRest, or an equivalent, for PITR.
• Keepalived/VRRP needs a real L2 network. The Docker lab does not exercise the VIP; validate it on the actual VMs.
• HAProxy terminates TCP, so PostgreSQL sees the proxy's IP, not the client's. Restrict the application CIDR at the firewall and in HAProxy, not only in pg_hba.conf.
• It does not migrate an existing production cluster in place. It bootstraps a new cluster, restores a validated backup on the leader, then joins the replica.

Automatic failover is not monitoring

A Patroni cluster survives the failure. It does not tell you why it happened, and it will happily survive the same failure every week without anyone noticing that a disk is filling, a replica has been rebuilt three times this month, or that the sync standby is falling behind under afternoon load.

Worse, the parts of this design that protect you are also the parts that hurt quietly. A stalled write under synchronous_mode_strict looks like "the application is slow", not like "the standby is gone". An abandoned replication slot retains WAL until pg_wal fills the filesystem and the whole cluster stops — the most common way a perfectly healthy PostgreSQL server goes down. Replication lag that spikes at 3 a.m. and recovers by 7 is invisible to anyone who only looks after a complaint.

That is the layer PG Monitoring covers: continuous tracking of replication lag and slot retention, WAL growth, autovacuum and wraparound risk, query regressions, connection saturation and lock waits — with history, so after a failover you can prove what actually happened instead of guessing. Use the open-source cluster to stay up; use monitoring so you are not the last to know why you almost did not.

Use it, break it, ask us

The repository is MIT licensed. Clone it, fork it, run the lab, tear it apart, adapt the scripts to your environment. Issues and pull requests are welcome — CI validates Bash and ShellCheck, checks the Compose file and runs the full logical-continuity test, so a change that breaks failover does not get merged quietly.

We answer questions about the repository whether or not you are a PG Monitoring customer. Designing HA badly is expensive enough that we would rather you got it right.

References: the PostgreSQL manual documents logical replication failover and slot synchronization, synchronous replication, pg_rewind and replication slots. Patroni's own replication modes documentation explains synchronous_mode_strict in detail.