Set Up PostgreSQL High Availability with Patroni and HAProxy

The first time we powered off the Patroni leader in our lab, the application saw 40 seconds of failed writes and lost nothing. Both numbers matter, and most PostgreSQL high availability guides only promise the second one. Replication keeps a copy of your data; it does not decide who the primary is, and it does not give clients one address that follows the primary around. Patroni, etcd, HAProxy and keepalived split those jobs between them, and the gap between “the leader died” and “writes work again” is set by a handful of timers you control.

Original content from computingforgeeks.com - post 168641

This guide builds that stack end to end: a three-node Patroni cluster with synchronous replication, an etcd quorum holding the leader lock, HAProxy splitting writes from reads, and a keepalived VIP so the proxy tier has no single point of failure. Then it breaks the cluster on purpose, eleven times, and reports what the client saw. We rebuilt everything in October 2026 on Rocky Linux 10.2 and on Ubuntu 26.04 with proxies on Ubuntu 24.04, running PostgreSQL 18.6, Patroni 4.1.5 and etcd from each distribution’s usual source, and confirmed the install path on Debian 13 as well.

How the PostgreSQL HA cluster fits together

Five machines run one logical database: three database nodes that each carry PostgreSQL, Patroni and an etcd member; two proxy nodes carry HAProxy and keepalived. The application’s connection string points at the VIP and never changes:

Each component removes one specific failure mode, which is the easiest way to remember why it is there:

• Patroni runs on every database node, manages PostgreSQL, and races for a leader lock, and whichever node holds that lock is the primary. When the lock expires, Patroni promotes the synchronous standby, and when the old primary comes back it rewinds it into a replica.
• etcd stores that lock behind Raft consensus, so only a majority can grant it. With three members the cluster survives one failure; with two, a single loss freezes every failover decision.
• HAProxy never guesses who the primary is, because it asks the Patroni REST API on port 8008 of every node and routes port 5000 to the node answering 200 on /primary and port 5001 to nodes answering 200 on /replica.
• keepalived floats the VIP 10.0.1.10 between the two proxies with VRRP, so a dead proxy moves the address instead of taking the database offline.

Weigh the cost honestly, since this is five machines and four daemons to keep one database writable through a node loss. If a manual promotion during an outage is acceptable, plain PostgreSQL streaming replication is simpler and cheaper. We’d reach for the full stack when an unplanned primary failure has to heal in well under a minute with nobody awake.

Size the nodes before you build

The database nodes do the real work, so size them from the workload rather than from our lab. PostgreSQL wants shared_buffers at roughly a quarter of RAM with the operating system cache holding most of the rest, so a 12 GB working set points at 32 GB nodes or more. Disk is the dataset plus WAL, replication slot retention and about 30% headroom, on storage with fast synchronous writes, because synchronous replication makes every commit wait for the standby’s flush. A busy OLTP database usually lands between 16 and 64 GB of RAM per node.

All three database nodes must be specced identically, since any of them can become the primary and the cluster commits at the pace of the slowest synchronous member. Keep them in one data centre or on links under a millisecond, because both etcd elections and synchronous commits pay the round trip on every write. The proxy nodes are the cheap tier, because HAProxy in TCP mode barely touches CPU, and its real limit is maxconn and the file descriptor ceiling, so 2 vCPU and 2 GB is plenty.

Our lab ran every node at 2 vCPU and 2 GB RAM with a 20 GB disk. That is a floor for following along, not a production recommendation, and these are the addresses used throughout the guide:

Host	IP	Runs
pg1, pg2, pg3	10.0.1.11, 10.0.1.12, 10.0.1.13	PostgreSQL 18, Patroni, etcd member
ha1, ha2	10.0.1.21, 10.0.1.22	HAProxy, keepalived
VIP	10.0.1.10	Floats between ha1 and ha2
app01	any address on 10.0.1.0/24	Client used for the failover drills

Run chrony (Rocky) or systemd-timesyncd (Ubuntu) on every node, which both distributions enable by default, because Patroni’s lease timing assumes the clocks agree.

Set the cluster variables

The node addresses repeat across dozens of commands, so export them once per shell session on whichever node you are working on:

export PG1=10.0.1.11
export PG2=10.0.1.12
export PG3=10.0.1.13
export VIP=10.0.1.10
export SUBNET=10.0.1.0/24
export SUPERUSER_PWD='ChangeMe-Super#2026'

They last for the current shell only, so re-export them after reconnecting or switching to a root shell. Pick your own passwords; the same three accounts (superuser, replication, rewind) appear in the Patroni config later.

Install PostgreSQL, Patroni and etcd

This is the one step where the distribution families genuinely diverge, both in where the packages come from and in what they do on install, while everything after it is identical. Run it on pg1, pg2 and pg3.

Rocky Linux and AlmaLinux

Add the PGDG repository and EPEL first, because in our build EPEL supplied python3-click, which patronictl needs:

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-10-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-10.noarch.rpm

etcd ships in the PGDG extras repository, which is disabled by default, so enable it and install the whole stack in one go:

sudo dnf config-manager --set-enabled pgdg-rhel10-extras
sudo dnf install -y postgresql18-server postgresql18-contrib etcd patroni patroni-etcd

The patroni-etcd package pulls in the etcd driver Patroni needs, so confirm which versions the repositories gave you:

etcd --version | head -1; patroni --version; /usr/pgsql-18/bin/postgres --version

On our Rocky Linux 10.2 nodes this printed the following three lines:

etcd Version: 3.7.2
patroni 4.1.5
postgres (PostgreSQL) 18.6

Leave initdb and the postgresql-18 service alone, because Patroni creates, owns and starts the data directory itself.

Ubuntu and Debian

Add the PGDG apt repository; the codename variable resolves to resolute, noble or trixie on its own, and PGDG publishes all three:

sudo apt-get update
sudo apt-get install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -fsSL -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc
. /etc/os-release
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt ${VERSION_CODENAME}-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt-get update

Install PostgreSQL and Patroni from PGDG, and etcd from the distribution archive:

sudo apt-get install -y postgresql-18 patroni etcd-server etcd-client python3-etcd

Do not skip python3-etcd, because the Debian Patroni package depends on the Consul driver and only suggests etcd, so without it Patroni dies at startup with Can not find suitable configuration of distributed configuration store.

Two things start on their own here that Patroni needs out of the way. The postgresql-18 package creates and starts a cluster called main on port 5432, and etcd-server starts a one-member etcd cluster with its data under /var/lib/etcd/default. Drop the first and clear the second on every node:

sudo pg_dropcluster --stop 18 main
sudo systemctl stop etcd
sudo rm -rf /var/lib/etcd/default

We left the etcd directory in place on the first pass and the new three-member cluster still formed, but every start logged found invalid file under data directory, which removing the stale directory clears up.

What actually differs between the families

Every name and path below comes from the lab nodes and appears again in the config files:

Item	Rocky Linux 10	Ubuntu 26.04 / 24.04, Debian 13
PostgreSQL package	postgresql18-server (PGDG)	postgresql-18 (PGDG)
etcd package and version	etcd 3.7.2 (PGDG extras)	etcd-server: 3.5.16 on 26.04 and Debian 13, 3.4.30 on 24.04
etcd driver for Patroni	patroni-etcd	python3-etcd
Auto-created on install	nothing	PostgreSQL cluster main, one-member etcd
PostgreSQL binaries	/usr/pgsql-18/bin	/usr/lib/postgresql/18/bin
Data directory	/var/lib/pgsql/18/data	/var/lib/postgresql/18/main
etcd config file	/etc/etcd/etcd.conf	/etc/default/etcd (absent on 26.04 until you create it)
Patroni config file	/etc/patroni/patroni.yml	/etc/patroni/config.yml
HAProxy	3.0.5	2.8.16 on 24.04
Firewall	firewalld (install it on cloud images)	ufw
MAC	SELinux enforcing, one boolean on the proxies	AppArmor, no denials for this stack

Mixing etcd 3.4, 3.5 and 3.7 across distributions is fine for Patroni, which talks to etcd through the v3 API, but keep all members of one quorum on the same release.

Open the database ports

The database nodes listen on 5432 (PostgreSQL), 8008 (Patroni REST API), 2379 (etcd clients) and 2380 (etcd peers). The Rocky GenericCloud image ships without firewalld, so on those nodes run sudo dnf install -y firewalld && sudo systemctl enable --now firewalld first. Then open all four ports with firewalld:

sudo firewall-cmd --permanent --add-port={5432,8008,2379,2380}/tcp
sudo firewall-cmd --reload

That opens the four ports to every source; in production, scope them to the cluster subnet with a firewalld rich rule or a dedicated zone, as the ufw rules below do. On Ubuntu and Debian, ufw is installed but inactive, so allow SSH before enabling it, or you lock yourself out of the node, then scope the cluster ports to the subnet:

sudo ufw allow OpenSSH
sudo ufw allow from ${SUBNET} proto tcp to any port 5432,8008,2379,2380
sudo ufw --force enable

Bootstrap the etcd quorum

etcd comes first, because Patroni has nowhere to keep the leader lock without it. Both families read the same environment variables, only from different files, so start by opening the file on pg1:

sudo vim /etc/etcd/etcd.conf

On Ubuntu and Debian the path is /etc/default/etcd instead, and on 26.04 the file does not exist until you create it. Replace any contents with:

ETCD_NAME=pg1
ETCD_DATA_DIR=/var/lib/etcd
ETCD_LISTEN_PEER_URLS=http://10.0.1.11:2380
ETCD_LISTEN_CLIENT_URLS=http://10.0.1.11:2379,http://127.0.0.1:2379
ETCD_INITIAL_ADVERTISE_PEER_URLS=http://10.0.1.11:2380
ETCD_ADVERTISE_CLIENT_URLS=http://10.0.1.11:2379
ETCD_INITIAL_CLUSTER=pg1=http://10.0.1.11:2380,pg2=http://10.0.1.12:2380,pg3=http://10.0.1.13:2380
ETCD_INITIAL_CLUSTER_STATE=new
ETCD_INITIAL_CLUSTER_TOKEN=pg-etcd

On pg2 and pg3, change ETCD_NAME and the four URLs that carry the node’s own address. ETCD_INITIAL_CLUSTER stays identical on all three nodes. Start etcd on all three nodes within a few seconds of each other so the first election succeeds:

sudo systemctl enable --now etcd

Then check from any node that every member of the quorum answers:

etcdctl --endpoints=http://${PG1}:2379,http://${PG2}:2379,http://${PG3}:2379 endpoint health -w table

All three should report true, and the screenshot after the Patroni start shows the table from our Rocky nodes.

Configure Patroni

Patroni reads one YAML file per node, and only name and the two connect_address lines change between nodes, so open it on pg1:

sudo vim /etc/patroni/patroni.yml

The block below is the exact file pg1 ran on Rocky Linux. On Ubuntu and Debian, save it as /etc/patroni/config.yml and change data_dir to /var/lib/postgresql/18/main and bin_dir to /usr/lib/postgresql/18/bin:

scope: pg-cluster
namespace: /service/
name: pg1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.1.11:8008

etcd3:
  hosts:
    - 10.0.1.11:2379
    - 10.0.1.12:2379
    - 10.0.1.13:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    synchronous_mode: true
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 256MB
        wal_log_hints: "on"
  initdb:
    - encoding: UTF8
    - data-checksums
  pg_hba:
    - host all all 127.0.0.1/32 scram-sha-256
    - host all all 10.0.1.0/24 scram-sha-256
    - host replication replicator 10.0.1.0/24 scram-sha-256

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.1.11:5432
  data_dir: /var/lib/pgsql/18/data
  bin_dir: /usr/pgsql-18/bin
  authentication:
    superuser:
      username: postgres
      password: 'ChangeMe-Super#2026'
    replication:
      username: replicator
      password: 'ChangeMe-Repl#2026'
    rewind:
      username: rewind_user
      password: 'ChangeMe-Rewind#2026'
  parameters:
    password_encryption: scram-sha-256

watchdog:
  mode: automatic
  device: /dev/watchdog
  safety_margin: 5

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

Two settings carry most of the weight here, starting with synchronous_mode: true, which keeps one standby in lockstep and lets Patroni promote only that standby; that is why none of our drills lost an acknowledged write. use_pg_rewind: true lets a failed primary rejoin as a replica without a full re-clone. The ttl: 30 value is the one that sets your failover time, as the drills below show.

The file holds passwords, so give it to the postgres user only, then let Patroni validate it as that user (on Ubuntu and Debian, use /etc/patroni/config.yml in all three commands):

sudo chown postgres:postgres /etc/patroni/patroni.yml
sudo chmod 600 /etc/patroni/patroni.yml
sudo -u postgres patroni --validate-config /etc/patroni/patroni.yml

Silence means the file is valid, while running the validator as your own user fails with PermissionError: [Errno 13] Permission denied: '/etc/patroni/patroni.yml', which is the 600 mode doing its job, not a broken config.

Arm the watchdog

The etcd lock prevents two leaders at the cluster level, but a primary that hangs long enough to lose its lock can wake up and accept writes for a moment before it notices. A watchdog closes that gap by resetting the node when Patroni stops feeding it, and the softdog module provides one on any VM. The packaged units on both distributions ship the watchdog lines commented out, so add a drop-in on all three database nodes:

echo softdog | sudo tee /etc/modules-load.d/softdog.conf
sudo modprobe softdog
sudo install -d /etc/systemd/system/patroni.service.d
printf '[Service]\nExecStartPre=+/sbin/modprobe softdog\nExecStartPre=+/bin/chown postgres /dev/watchdog\n' | sudo tee /etc/systemd/system/patroni.service.d/watchdog.conf
sudo systemctl daemon-reload

With mode: automatic, Patroni arms the watchdog when it can and carries on when it cannot. Once you have seen it arm on every node, mode: required makes a node refuse leadership without it.

Start the cluster

Start pg1 alone first, so it runs initdb, creates the replication and rewind roles, and takes the lock:

sudo systemctl enable --now patroni

The journal should end with the leader claiming the lock and the watchdog armed:

2026-10-07 16:33:13,536 INFO: Software Watchdog activated with 25 second timeout, timing slack 15 seconds
2026-10-07 16:33:14,823 INFO: initialized a new cluster
2026-10-07 16:33:14,825 INFO: Lock owner: pg1; I am pg1
2026-10-07 16:33:15,458 INFO: Enabled synchronous replication
2026-10-07 16:33:15,772 INFO: no action. I am (pg1), the leader with the lock

Now start Patroni on pg2 and pg3 with the same command. They clone the leader with pg_basebackup and join as replicas, and one of them becomes the sync standby within one or two Patroni loops (about 20 seconds in our runs). Check from any node, using config.yml on Ubuntu and Debian:

sudo patronictl -c /etc/patroni/patroni.yml list

One leader, one sync standby and one async replica, all on timeline 1 with zero lag, behind a healthy etcd quorum:

The Sync Standby role is the visible proof that synchronous mode is on. That node holds every committed transaction the leader holds, which is why Patroni will only promote it.

Put HAProxy in front

HAProxy is what turns three database nodes into one address, and its health checks decide how quickly clients follow a leader change. Install it together with keepalived on both ha1 and ha2 (on Rocky GenericCloud images, add firewalld to this install and enable it, as on the database nodes):

sudo dnf install -y haproxy keepalived

On Ubuntu and Debian, the equivalent is sudo apt-get install -y haproxy keepalived, and either way the next step is the same config on both proxies:

sudo vim /etc/haproxy/haproxy.cfg

Replace everything in it with the file both proxies ran in the lab. Port 5000 carries writes, 5001 balances reads across the standbys, and 7000 serves the stats page:

global
    maxconn 2000
    log /dev/log local0

defaults
    log     global
    mode    tcp
    retries 2
    timeout client  30m
    timeout server  30m
    timeout connect 4s
    timeout check   5s

listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /
    stats refresh 5s

# Writes: only the Patroni leader answers 200 on /primary
listen postgres_primary
    bind *:5000
    option httpchk
    http-check send meth OPTIONS uri /primary
    http-check expect status 200
    default-server inter 1s fall 2 rise 2 on-marked-down shutdown-sessions
    server pg1 10.0.1.11:5432 maxconn 200 check port 8008
    server pg2 10.0.1.12:5432 maxconn 200 check port 8008
    server pg3 10.0.1.13:5432 maxconn 200 check port 8008

# Reads: every healthy standby answers 200 on /replica
listen postgres_replicas
    bind *:5001
    option httpchk
    http-check send meth OPTIONS uri /replica
    http-check expect status 200
    balance roundrobin
    default-server inter 1s fall 2 rise 2 on-marked-down shutdown-sessions
    server pg1 10.0.1.11:5432 maxconn 200 check port 8008
    server pg2 10.0.1.12:5432 maxconn 200 check port 8008
    server pg3 10.0.1.13:5432 maxconn 200 check port 8008

The check timers are the part we changed after testing. Our first build used the widely copied inter 3s fall 3 rise 2, and a planned switchover then produced about 10 seconds of failed writes, including several cannot execute INSERT in a read-only transaction errors: the demoted leader stayed in the write pool for up to nine seconds while the new one had already joined it, and HAProxy balanced between them. With inter 1s fall 2 rise 2 the same switchover cost 4.7 seconds and no read-only errors. The checks are tiny HTTP requests, so six per second per proxy (three servers in each of two pools) cost nothing measurable. on-marked-down shutdown-sessions matters too, because it cuts existing connections to a node the moment it fails its check instead of leaving clients writing to a demoted primary.

Validate the file on both proxies before starting anything, since HAProxy refuses to start on a syntax error:

sudo haproxy -c -f /etc/haproxy/haproxy.cfg

On Rocky Linux, SELinux stops HAProxy from binding the stats and read ports, because 7000 and 5001 are labelled for other services. Our first start failed with this, and systemctl start just reported a failed control process:

[ALERT]    (5420) : Binding [/etc/haproxy/haproxy.cfg:16] for proxy stats: protocol tcpv4: cannot bind socket (Permission denied) for [0.0.0.0:7000].
[ALERT]    (5420) : Binding [/etc/haproxy/haproxy.cfg:34] for proxy postgres_replicas: protocol tcpv4: cannot bind socket (Permission denied) for [0.0.0.0:5001].

The audit log showed name_bind denials against gatekeeper_port_t (7000) and commplex_link_port_t (5001). One boolean lets the confined HAProxy bind and connect to any port. Set it, open the proxy ports, and start the service on both Rocky proxies:

sudo setsebool -P haproxy_connect_any 1
sudo firewall-cmd --permanent --add-port={5000,5001,7000}/tcp
sudo firewall-cmd --reload
sudo systemctl enable --now haproxy

Ubuntu needs no SELinux step at all, so just open the same ports with ufw and restart HAProxy, which the package already started with its default config:

sudo ufw allow OpenSSH
sudo ufw allow proto tcp to any port 5000,5001,7000
sudo ufw --force enable
sudo systemctl restart haproxy

The stats page at http://10.0.1.21:7000/ shows the routing decision live. In this capture pg3 had become the leader after an earlier drill, so it is the only green row in postgres_primary and the only red one in postgres_replicas:

Red rows are not errors here, because a standby is supposed to answer 503 on /primary and the leader is supposed to answer 503 on /replica.

Float the VIP with keepalived

keepalived gives the proxy tier the same treatment HAProxy gives the databases, starting with its config on ha1:

sudo vim /etc/keepalived/keepalived.conf

The file below is for ha1, the MASTER, and you should set interface to your NIC name from ip -br link; it was eth0 on both our Rocky and Ubuntu proxies:

vrrp_track_process track_haproxy {
    process haproxy
    weight 10
}

vrrp_instance VI_PG {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 101
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass cfgha26
    }
    virtual_ipaddress {
        10.0.1.10/24
    }
    track_process {
        track_haproxy
    }
}

On ha2, use the same file with state BACKUP and priority 100. We track the HAProxy process with vrrp_track_process rather than the usual vrrp_script running pidof haproxy. The script version worked, but keepalived logged SECURITY VIOLATION - scripts are being executed but script_security not enabled on every start and, on Rocky, SELinux logged a setattr denial against /usr/bin/pidof. Process tracking uses kernel process events, needs no script and produced neither. When HAProxy stops on ha1, its priority drops from 111 to 101, below ha2’s 110, and the VIP moves.

VRRP is IP protocol 112, not TCP or UDP, so the port rules above do not cover it. If the firewall drops it, each proxy decides the other is dead and both claim the VIP, which we reproduced on both distributions when ip -br a showed 10.0.1.10 on ha1 and ha2 at the same time. Allow the protocol on Rocky like this:

sudo firewall-cmd --permanent --add-rich-rule='rule protocol value=vrrp accept'
sudo firewall-cmd --reload

The ufw rule on Ubuntu takes the protocol name directly:

sudo ufw allow proto vrrp from ${SUBNET}

Check the config syntax and start keepalived on both proxies:

sudo keepalived -t -f /etc/keepalived/keepalived.conf
sudo systemctl enable --now keepalived

Within about four seconds ha1 logs (VI_PG) Entering MASTER STATE and ip -br a show eth0 lists 10.0.1.10 next to its own address, while ha2 sits in BACKUP. Stopping HAProxy on ha1 moved the VIP to ha2 three seconds later in our test, and starting it again moved the VIP back.

Test read and write routing through the VIP

Point a query at port 5000 and one at port 5001. pg_is_in_recovery() is false on the primary and true on a standby:

export PGPASSWORD="$SUPERUSER_PWD"
psql "host=${VIP} port=5000 user=postgres dbname=postgres" -c "SELECT inet_server_addr(), pg_is_in_recovery();"
for i in 1 2 3 4; do psql "host=${VIP} port=5001 user=postgres dbname=postgres" -Atc "SELECT inet_server_addr(), pg_is_in_recovery();"; done

Writes land on the leader and reads rotate across the two standbys, all through one address:

Your application now uses 10.0.1.10:5000 for writes and 10.0.1.10:5001 for reads, and neither string changes when a node fails.

Run a failover drill under load

A cluster that has never failed over in front of you is a cluster you should not trust yet, and patronictl list alone tells you nothing about what the application experiences, so start by creating a table to write into:

psql "host=${VIP} port=5000 user=postgres dbname=postgres" -c "CREATE DATABASE appdb;"
psql "host=${VIP} port=5000 user=postgres dbname=appdb" -c "CREATE TABLE t(id serial primary key, note text, at timestamptz default now(), served_by inet default inet_server_addr());"

On a client machine with psql (app01 in our lab), create a small write loop:

vim write-loop.sh

It inserts a row every 200 ms through the VIP and prints the time, the new row id and the server that took it, or the error:

export VIP=10.0.1.10
export PGPASSWORD='ChangeMe-Super#2026'
while true; do
  echo "$(date +%T.%3N) $(psql "host=${VIP} port=5000 user=postgres dbname=appdb connect_timeout=2" -Atqc "INSERT INTO t(note) VALUES ('drill') RETURNING id, inet_server_addr()" 2>&1 | head -1)"
  sleep 0.2
done

Run it with bash write-loop.sh, then hard power off the current leader (pull the virtual power, not systemctl stop, because a clean stop releases the lock early and flatters the result). We did it from the hypervisor with qm stop, and this is what app01 printed when we killed pg2, the leader at the time:

Three distinct phases show up in that output. First, connections time out while HAProxy still believes pg2 is alive. Four seconds in, HAProxy marks it DOWN and, with no server left in the write pool, closes every new connection immediately. Then nothing happens for about 24 seconds, because no standby may promote until the leader lock in etcd expires, and the lock lives for ttl: 30 seconds from its last refresh. This is the timeline we reconstructed from the HAProxy, Patroni and PostgreSQL logs of that drill:

The last phase surprised us, because between 29.3 and 33.1 seconds the loop got cannot execute INSERT in a read-only transaction from pg3, the node that had just been promoted. Patroni answers 200 on /primary as soon as it holds the leader lock and has asked PostgreSQL to promote, and on our lab disks PostgreSQL then needed four seconds to finish the end of recovery: its log shows selected new timeline ID: 10 at 17:08:54.8 and database system is ready to accept connections at 17:08:59.3. The window was one write long in one drill and four seconds in another. Your application’s retry logic has to treat that error exactly like a dropped connection.

We ran eleven drills across both labs and compared every row id the loop printed as committed against the table afterwards, and none were missing. The ids jump by anywhere from 0 to 32 across a failover (949 to 968 in the screenshot) because PostgreSQL logs sequence values to WAL 32 at a time and a promoted node starts after the last logged value, not because rows vanished. The gap below runs from the last successful write before the event to the first one after it:

Event	Settings	Write gap	Acknowledged rows lost
Leader powered off (Rocky)	ttl 30, checks inter 3s fall 3 rise 2	40.2 s	0
Leader powered off (Rocky, two runs)	ttl 30, checks inter 1s fall 2 rise 1	28.8 s, 35.7 s	0
Leader powered off (Rocky)	ttl 30, checks inter 1s fall 2 rise 2 (final config)	32.4 s	0
Leader powered off (Ubuntu 26.04)	ttl 30, checks inter 1s fall 2 rise 1	about 31 s	0
Leader powered off (Rocky)	ttl 20, loop_wait 5, retry_timeout 5	25.9 s	0
Planned switchover (two runs)	checks inter 3s fall 3 rise 2	10.4 s, 9.1 s, with read-only errors	0
Planned switchover (two runs)	checks inter 1s fall 2, rise 1 then rise 2	4.9 s, 4.7 s, no read-only errors	0
Active proxy ha1 powered off	keepalived, advert_int 1	6.1 s	0

For planned maintenance, use a switchover instead of stopping the leader. Under synchronous mode Patroni only accepts the sync standby as the candidate, so check which node that is in patronictl list first:

sudo patronictl -c /etc/patroni/patroni.yml switchover --candidate pg2 --force

Aiming it at the async replica fails with Switchover failed, details: 412, candidate name does not match with sync_standby. That is the zero data loss guarantee refusing to promote a node that might be behind.

Watch the old leader rejoin

Power the dead node back on and leave it alone. Patroni sees that its timeline is behind the cluster’s, runs pg_rewind against the new leader using the rewind_user account, and starts it as a replica. In the Rocky lab the old leader was streaming again 35 seconds after power on, boot included, with no re-clone. On Ubuntu 26.04 the rewind itself took just over a second:

While a member is down, patronictl prints a Failed to get list of machines from http://10.0.1.11:2379/v3beta error above its table when it happens to try the dead etcd member first. It falls through to a live member and the table that follows is correct, so it is noise rather than a fault.

Harden it before production traffic

The lab cluster works, but a few decisions separate it from one we’d put customer traffic on. The first knob most teams reach for is the lock TTL, which is cluster-wide and lives in etcd, so change it once from any node rather than in every YAML file:

sudo patronictl -c /etc/patroni/patroni.yml edit-config -s ttl=20 -s loop_wait=5 -s retry_timeout=5 --force

Patroni prints a diff of the dynamic config and applies it on the next loop. The rest of the list is about how much of that outage your users see:

• Choose your failover time deliberately, because the lock TTL dominates the outage. The edit-config change above cut our hard failover from about 30 seconds to 25.9. Twenty is the lowest TTL Patroni accepts, and it requires loop_wait + 2 * retry_timeout <= ttl. The cost is that a shorter network or etcd stall can now trigger a failover you did not need.
• Build retries into the application layer. Every drill produced client errors even though no data was lost. Treat connection errors and read-only transaction errors as retryable with backoff, and size the retry window to the 30 to 40 seconds measured above.
• Encrypt etcd traffic and replication. This guide runs etcd over plain HTTP for clarity, so give etcd TLS peer and client certificates, and require TLS on PostgreSQL connections, because the leader lock and the WAL stream both cross the network.
• Decide what happens when no sync standby is left. synchronous_mode: true drops back to async when the last sync standby disappears, which keeps writes flowing. Setting synchronous_mode_strict: true refuses writes in that situation instead. That is a durability versus availability choice only you can make for your data.
• Restrict the stats page and add connection pooling. Port 7000 exposes the topology without a password; bind it to a management address or add stats auth. HAProxy balances connections but does not pool them, so put PgBouncer in front of PostgreSQL if the application opens many short connections.
• Alert on timeline changes and etcd membership. Scrape Patroni’s /metrics on port 8008 for patroni_postgres_timeline, patroni_xlog_replayed_location and patroni_dcs_last_seen, and etcd’s own /metrics on port 2379 for etcd_server_has_leader, since Patroni does not report quorum health. A timeline bump you did not expect is a failover nobody noticed.

Before go-live, measure what synchronous commit costs your write throughput by running the same load against this cluster and a single server, using the method from our PostgreSQL vs MySQL vs MariaDB benchmark. If etcd will serve more than Patroni, the three-node etcd cluster guide covers running it as a standalone service. The same proxy and VIP pattern also fronts a MariaDB Galera cluster and a Percona MySQL cluster with ProxySQL, and our HAProxy and keepalived cluster on Rocky Linux 10 goes deeper on the proxy tier alone. For a single server to start from, see the PostgreSQL install guides for Rocky and AlmaLinux and Ubuntu and Debian. The Patroni REST API reference lists every health endpoint if you want HAProxy to check something other than /primary and /replica.