Building a High-Availability PostgreSQL Cluster (Patroni + etcd + HAProxy + Keepalived)

PostgreSQL on its own is a single point of failure: if the server goes down, the application goes down with it. This article shows how to stand up a 3-node cluster that survives the loss of a machine with no human intervention and without changing the connection string on the application side.

The goal isn't to copy-paste commands blindly, but to understand why each building block exists, so you can adapt the whole thing to your own network.

The result: a single virtual IP (the VIP) that applications connect to as if it were a regular database. Behind it, three servers watch each other continuously; if the primary fails, a replica is promoted within seconds and the VIP fails over automatically.

Part 1 — The architecture and why this stack

The high-availability problem

For a database to stay available when a server goes down, you have to solve four distinct problems:

1. Store the data more than once → PostgreSQL replication (one primary writes, replicas follow).
2. Decide who the primary is, and change it without conflict when it fails → an orchestrator (Patroni).
3. Agree on that decision across several nodes, even during a network partition → a consensus store (etcd).
4. Route clients to the right server without them knowing who is primary → a router (HAProxy) + a single address that follows the service (VIP via Keepalived).

Each component solves exactly one of these problems. That's why there are four of them.

The four components

Why three nodes, and not two

etcd (like any consensus system) decides by majority. With 3 nodes, the majority is 2: the cluster keeps working if one node goes down. With 2 nodes, losing one node loses the majority — nobody can decide who the primary is anymore, and the cluster freezes as a safety measure (to avoid split-brain, two primaries diverging).

Simple rule: an odd number of nodes. Three is the useful minimum.

The path of a request

Application
    │  connects to the VIP (e.g. 192.168.11.50:5432)
    ▼
Keepalived  ── the VIP is mounted on the healthy, prioritized node
    ▼
HAProxy     ── probes all 3 nodes, only forwards to the PRIMARY
    ▼
PostgreSQL (primary)  ←── replicate ──  PostgreSQL (replicas ×2)
    ▲
Patroni     ── monitors, and via etcd decides who is primary

Key point: HAProxy does not guess who the primary is. It queries Patroni's REST API on each node (GET /primary). Only the node that is actually primary answers 200. Replicas answer something else and are discarded. When a failover happens, it's the former replica — now primary — that starts answering 200, and HAProxy redirects traffic automatically, with no reconfiguration.

Security: TLS everywhere

All internal traffic is encrypted and authenticated with X.509 certificates:

• etcd requires mutual certificates (peer-to-peer and client). A private CA signs one certificate per node.
• PostgreSQL uses a shared certificate whose SANs cover the VIP + the 3 IPs + localhost, so the connection is valid no matter which node is serving.
• pg_hba only allows hostssl connections (never plaintext), restricted to the cluster's subnet.

Part 2 — Preparation: network, configuration, preflight

The entire deployment comes down to two scripts and one configuration file:

• cluster.env — all the variables (IPs, ports, secrets, PG version). It's the only file you edit.
• preflight.sh — checks the machine is ready, writes nothing.
• deploy.sh — installs and configures everything. Idempotent: re-runnable without breaking an existing cluster.

All three files live in the blog's labs repo, under the postgresql-ha-cluster subfolder. Grab them:

git clone https://github.com/denisakp/dev-life-labs
cd dev-life-labs/postgresql-ha-cluster

This is the folder you'll later copy to each node (see Part 3).

Network prerequisites

First and foremost, you need:

• 3 servers running Debian/Ubuntu on the same subnet, with static IPs.
• One free IP in that subnet for the VIP — check it:

ping -c2 192.168.11.50    # must NOT respond → the VIP is free

• The network interface name of each server (often ens3, enp0s3, eth0…):

ip link show

If the interface (IFACE) is wrong, the VIP will never come up. That's mistake number one.

The configuration file

Start from the template:

cp cluster.env.example cluster.env && chmod 600 cluster.env

The essential fields:

# Identity
CLUSTER_NAME="postgresql-cluster"
PG_VERSION="18"
ETCD_VERSION="v3.6.12"

# Network
SUBNET="192.168.11.0/24"      # cluster subnet
VIP="192.168.11.50"           # virtual IP (the applications' entry point)
IFACE="ens3"                  # interface that will carry the VIP
APP_SUBNET="192.168.11.0/24"  # where the clients come from

# Nodes
DB1_NAME="db1"; DB1_IP="192.168.11.45"   # initial primary, VRRP priority 100
DB2_NAME="db2"; DB2_IP="192.168.11.46"   # standby, priority 90
DB3_NAME="db3"; DB3_IP="192.168.11.47"   # standby, priority 80

# Secrets — generate each with:  openssl rand -base64 24
PG_SUPERUSER_PASSWORD="..."
PG_REPLICATOR_PASSWORD="..."
PATRONI_RESTAPI_PASSWORD="..."
VRRP_AUTH_PASS="........"     # WARNING: max 8 characters (VRRP truncates beyond that)

Two traps that come up every time:

• VRRP_AUTH_PASS ≤ 8 characters. The VRRP protocol truncates silently; two nodes with passwords that differ after truncation won't see each other, and the VIP can split in two.
• cluster.env must be identical on all 3 servers, secrets included. Certificates and authentication rely on it.

VRRP priorities

DB1=100 > DB2=90 > DB3=80. Keepalived mounts the VIP on the healthy node with the highest priority. db1 has it by default; if it goes down, db2 takes over, then db3. When db1 comes back, it reclaims the VIP (highest priority). These priorities are independent of the PostgreSQL role: the VIP follows HAProxy health, not the primary — but since HAProxy only routes to the primary, the result is consistent end to end.

The preflight

Run on each node before deployment:

sudo ./preflight.sh db1

It checks, without modifying anything: secrets filled in (no CHANGE_ME), VIP within the subnet, interface present, node IP correct, other nodes reachable, ports free, enough RAM/disk, clock synchronized (NTP) — critical: clock drift between nodes makes failover unstable.

• [FAIL] = blocking, fix before continuing.
• [WARN] = worth checking, but deployment can still proceed.

Part 3 — Deployment, verification, failover

The order is mandatory: db1 → certificate distribution → db2 → db3. db1 generates the CA and everyone's certificates; db2 and db3 cannot start without receiving them.

Step 1 — Deploy db1

sudo ./preflight.sh db1
sudo ./deploy.sh db1 --no-upgrade

--no-upgrade skips the global apt upgrade. Recommended if the VMs host other services (you don't want to upgrade the whole system along the way). Drop it for a fresh, dedicated server.

What deploy.sh does on db1, in order: targeted update → package installation (PostgreSQL, Patroni, HAProxy, Keepalived) + the etcd binary → generation of the CA and all certificates → etcd configuration and start → Patroni, HAProxy, Keepalived configuration → scheduled daily etcd backup.

At the end, db1 is not primary yet: etcd is waiting for quorum (≥ 2 nodes). That's expected. The script produces two archives, certs-db2.tar.gz and certs-db3.tar.gz.

Step 2 — Distribute the certificates

The deployment does no automatic SSH (by design: no keys to propagate, authentication stays interactive). You copy the archives yourself:

scp certs-db2.tar.gz user@192.168.11.46:/tmp/
scp certs-db3.tar.gz user@192.168.11.47:/tmp/
# and cluster.env, identical:
scp cluster.env user@192.168.11.46:~/dev-life-labs/postgresql-ha-cluster/
scp cluster.env user@192.168.11.47:~/dev-life-labs/postgresql-ha-cluster/

Step 3 — Deploy db2 then db3

On db2 (then, identically, on db3) — clone the repo, then deploy:

git clone https://github.com/denisakp/dev-life-labs
cd dev-life-labs/postgresql-ha-cluster
chmod 600 cluster.env
sudo ./preflight.sh db2
sudo ./deploy.sh db2 --no-upgrade

deploy.sh detects the archive in /tmp, extracts the certificates, configures the node and joins the cluster. As soon as db2 starts, etcd quorum forms (2 nodes), Patroni elects db1 as primary and db2 syncs as a replica. db3 then joins the same way.

Step 4 — Verify

From any node:

# 1 Leader + 2 Replicas streaming
patronictl -c /etc/patroni/config.yml list

+ Cluster: postgresql-cluster ------+---------+----+-----------+
| Member | Host          | Role    | State   | TL | Lag in MB |
+--------+---------------+---------+---------+----+-----------+
| db1    | 192.168.11.45 | Leader  | running |  1 |           |
| db2    | 192.168.11.46 | Replica | running |  1 |         0 |
| db3    | 192.168.11.47 | Replica | running |  1 |         0 |
+--------+---------------+---------+---------+----+-----------+

# etcd: 3 healthy members
etcdctl --endpoints=https://192.168.11.45:2379,https://192.168.11.46:2379,https://192.168.11.47:2379 \
  --cacert=/etc/etcd/ssl/ca.crt --cert=/etc/etcd/ssl/etcd-db1.crt --key=/etc/etcd/ssl/etcd-db1.key \
  endpoint health

# Connection via the VIP → must return 'f' (primary, read/write)
PGPASSWORD='<superuser>' psql -h 192.168.11.50 -U postgres -c 'SELECT pg_is_in_recovery();'

# The VIP is indeed mounted on the primary
ip addr show ens3 | grep 192.168.11.50

pg_is_in_recovery() returning f confirms the VIP leads to the writable primary.

Step 5 — Firewall

On all 3 nodes, restrict the cluster ports to its subnet:

sudo ufw allow from 192.168.11.0/24 to any port 2379,2380,5432,5433,8008 proto tcp
# VRRP between the nodes:
sudo ufw allow from 192.168.11.0/24 to any proto vrrp 2>/dev/null || \
  sudo ufw allow from 192.168.11.0/24 to 224.0.0.18

The test that validates everything: failover

This is the whole point of the cluster. We simulate the primary going down:

# Identify the leader
patronictl -c /etc/patroni/config.yml list

# On the leader node: stop Patroni
sudo systemctl stop patroni

# A few seconds later, on another node:
patronictl -c /etc/patroni/config.yml list   # → a replica has become Leader

What happens within seconds, with no intervention: Patroni loses the leader lease in etcd → a replica nominates itself and is elected → it is promoted to primary → its API now answers 200 on /primary → HAProxy redirects traffic → the VIP stays on a node where HAProxy is healthy. Applications, connected to the VIP, see only a brief interruption.

Bring the former primary back in:

sudo systemctl start patroni   # it returns as a Replica and resyncs

Recap

To adapt this to your environment, you only touch one file (cluster.env): subnet, IPs, interface, secrets. The rest — certificates, configuration of the four services, backup scheduling — is generated by deploy.sh, which is idempotent: on error, fix it and re-run sudo ./deploy.sh dbX --no-upgrade.

Traps to remember:

• Mandatory order: db1 → certs → db2 → db3.
• Correct IFACE, otherwise the VIP won't come up.
• VRRP_AUTH_PASS ≤ 8 characters.
• cluster.env identical on all 3 nodes.
• Clock synchronized (NTP) on all three.
• Odd number of nodes (3 minimum).