Alta disponibilidad de PostgreSQL con Patroni, etcd, HAProxy y keepalived

PostgreSQL has a mature, production-grade high availability stack that costs nothing in licensing and is straightforward to operate once it is set up.

This lab builds a six-node HA cluster using four open-source components: Patroni for cluster management and automatic failover, etcd as the distributed consensus store, HAProxy for load balancing and connection routing, and keepalived for a floating virtual IP that survives HAProxy node failures.

The result is a cluster where a primary failure is detected and a new primary is elected in under 30 seconds, with no manual intervention required.

Switchovers are clean and zero data loss.

The entire stack is managed through a single CLI (patronictl) that makes day-to-day operations — switchover, failover, reinitialisation, configuration changes — simple commands rather than multi-step procedures.

This lab covers everything from scratch: TLS certificate generation, etcd cluster formation, Patroni configuration, HAProxy setup, keepalived VIP configuration, and full verification of failover and switchover.

Each step is explained with expected output and failure diagnosis so you know exactly what success looks like at every stage.

PostgreSQL High Availability with Patroni

Covers Patroni architecture, TLS setup, failover, switchover, and day-to-day operations.

Environment: Six servers total. etcd runs on the same nodes as PostgreSQL — no dedicated etcd servers.

1. Architecture

          +----------+   +----------+   +----------+
          |  etcd    |   |  etcd    |   |  etcd    |
          | Patroni  |   | Patroni  |   | Patroni  |
          |   +PG    |   |   +PG    |   |   +PG    |
          | node1    |   | node2    |   | node3    |
          | PRIMARY  |   | STANDBY  |   | STANDBY  |
          +----------+   +----------+   +----------+
                \              |              /
                 \             |             /
          +----------+   +----------+   +----------+
          | HAProxy  |   | HAProxy  |   | HAProxy  |
          |  node1   |   |  node2   |   |  node3   |
          | (MASTER) |   | (BACKUP) |   | (BACKUP) |
          +----------+   +----------+   +----------+
                \              |              /
                 \             |             /
              [keepalived VIP: 192.168.0.210:5432]
                              |
                        Applications

• etcd: distributed key-value store co-located on each PostgreSQL node. Holds cluster state (current leader, member list). Requires an odd number of nodes for quorum — 3 nodes tolerate 1 failure, 5 nodes tolerate 2.
• Patroni: daemon on each PostgreSQL node. Manages replication, monitors health, and coordinates failover through etcd.
• HAProxy: three dedicated nodes route application connections to the current primary by checking Patroni's REST API.
• keepalived: manages the VIP using VRRP. One HAProxy node holds the VIP at a time. If that node fails, the VIP moves to the next HAProxy node automatically.
• All communication is TLS-encrypted: etcd peer traffic, etcd client traffic, Patroni REST API, and PostgreSQL connections.

2. Prerequisites

Step 1 — Set the correct timezone on all nodes

Run on all 6 servers (postgres-01/02/03 and haproxy-01/02/03).

Servers default to UTC — set to your local timezone before anything else.

Mismatched or wrong timestamps cause confusion in logs and certificate validation.

sudo timedatectl set-timezone Europe/Madrid
timedatectl
# Expected: Time zone: Europe/Madrid (CET/CEST, +0100/+0200)
# NTP service should show: active
# System clock synchronized: yes

Step 2 — Confirm required ports are open

Before starting, confirm the following ports are open:

3. PostgreSQL Installation

Step 1 — Install PostgreSQL on all 3 PostgreSQL nodes

# On postgres-01, postgres-02, postgres-03
sudo apt update
sudo apt install -y postgresql-common
# postgresql-common: provides the PGDG repository setup script

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
# This script adds the official PostgreSQL apt repository (postgresql.org)
# and imports its GPG key — ensures you get the latest PostgreSQL version,
# not the older version bundled with Ubuntu

sudo apt update
sudo apt install -y postgresql-18 postgresql-contrib-18
# Install version 18 explicitly — the generic "postgresql" meta-package installs Ubuntu's
# default bundled version (16) in addition to the PGDG version, leaving two versions installed
# Always specify the version number to avoid this
# postgresql-contrib-18: additional modules including pg_rewind, which Patroni uses
# to resync the old primary after a failover without a full base backup

Step 2 — Stop and disable the PostgreSQL service

# On postgres-01, postgres-02, postgres-03
sudo systemctl stop postgresql
# Patroni manages PostgreSQL startup entirely
# If PostgreSQL is already running when Patroni starts, Patroni will fail with:
# "postmaster is already running"

sudo systemctl disable postgresql
# Prevents PostgreSQL from starting automatically on boot
# Patroni's own systemd service starts PostgreSQL when the node joins the cluster

4. etcd Installation

Step 1 — Install etcd on all 3 PostgreSQL nodes

# On postgres-01, postgres-02, postgres-03
sudo apt-get install -y wget curl

wget https://github.com/etcd-io/etcd/releases/download/v3.6.10/etcd-v3.6.10-linux-amd64.tar.gz
# Download the etcd binary directly from GitHub releases
# The apt package is often outdated — always install from the official releases
# Check https://github.com/etcd-io/etcd/releases for the latest stable version

tar xvf etcd-v3.6.10-linux-amd64.tar.gz
# xvf: extract (x), verbose (v), from file (f)

sudo mv etcd-v3.6.10-linux-amd64/etcd /usr/local/bin/
sudo mv etcd-v3.6.10-linux-amd64/etcdctl /usr/local/bin/
# etcd: the etcd server binary
# etcdctl: the etcd client CLI — used for health checks and inspecting cluster state

# Verify the installation
etcd --version
# Expected: etcd Version: 3.6.10
# If "etcd: command not found": /usr/local/bin is not in PATH — run: export PATH=$PATH:/usr/local/bin

etcdctl version
# Expected: etcdctl version: 3.6.10

Step 2 — Create the etcd system user

# On postgres-01, postgres-02, postgres-03
sudo useradd --system --home /var/lib/etcd --shell /bin/false etcd

# --system: creates a system account with no login shell by default
# --home /var/lib/etcd: etcd stores its data here
# --shell /bin/false: prevents interactive login — etcd runs as a daemon only

5. TLS Certificate Generation

All certificates are generated once on postgres-01 and then distributed to the other nodes.

The CA private key (ca.key) stays on postgres-01 after distribution is complete — do not copy it to other nodes.

Step 1 — Create the working directory

# On postgres-01
mkdir ~/certs && cd ~/certs
# All certificate files are created here before being copied to each node

Step 2 — Generate the Certificate Authority

# On postgres-01
openssl genrsa -out ca.key 2048
# genrsa: generate an RSA private key; 2048: key length in bits

openssl req -x509 -new -nodes -key ca.key -subj "/CN=etcd-ca" -days 7300 -out ca.crt
# req -x509: create a self-signed certificate (not a signing request)
# -new -nodes: new certificate, no passphrase on the private key
# -subj "/CN=etcd-ca": the certificate's Common Name — identifies this as the cluster CA
# -days 7300: valid for 20 years
# ca.crt: distributed to every node as the root of trust

Step 3 — Generate per-node etcd certificates

Each node gets its own certificate with its IP as a Subject Alternative Name (SAN). TLS hostname verification requires the server's IP to appear in the SAN — without it, connections will fail.

# On postgres-01 — generate all three node certificates here, then distribute

# Certificate for postgres-01 (192.168.0.203)
openssl genrsa -out etcd-node1.key 2048

cat > temp.cnf <<EOF
[ req ]
distinguished_name = req_distinguished_name
req_extensions = v3_req
[ req_distinguished_name ]
[ v3_req ]
subjectAltName = @alt_names
[ alt_names ]
IP.1 = 192.168.0.203
IP.2 = 127.0.0.1
EOF

# temp.cnf: OpenSSL config that adds the node IP as a SAN
# IP.2 = 127.0.0.1: allows etcdctl to connect locally without specifying a remote address

openssl req -new -key etcd-node1.key -out etcd-node1.csr \
  -subj "/CN=etcd-node1" \
  -config temp.cnf
# req -new: generate a certificate signing request (CSR)
# -subj: the certificate's identity — CN identifies the node in logs

openssl x509 -req -in etcd-node1.csr -CA ca.crt -CAkey ca.key \
  -CAcreateserial -out etcd-node1.crt -days 7300 \
  -sha256 -extensions v3_req -extfile temp.cnf
# x509 -req: sign the CSR with the CA to produce a certificate
# -CAcreateserial: creates ca.srl to track serial numbers across certificates
# -extensions v3_req -extfile temp.cnf: embed the SANs into the signed certificate

openssl x509 -in etcd-node1.crt -text -noout | grep -A1 "Subject Alternative Name"
# Verify the SAN was embedded — Expected: IP Address:192.168.0.203, IP Address:127.0.0.1
# If the SAN is missing: the -extensions and -extfile flags were not applied correctly

rm temp.cnf

# Certificate for postgres-02 (192.168.0.204)
openssl genrsa -out etcd-node2.key 2048

cat > temp.cnf <<EOF
[ req ]
distinguished_name = req_distinguished_name
req_extensions = v3_req
[ req_distinguished_name ]
[ v3_req ]
subjectAltName = @alt_names
[ alt_names ]
IP.1 = 192.168.0.204
IP.2 = 127.0.0.1
EOF

openssl req -new -key etcd-node2.key -out etcd-node2.csr \
  -subj "/CN=etcd-node2" -config temp.cnf

openssl x509 -req -in etcd-node2.csr -CA ca.crt -CAkey ca.key \
  -CAcreateserial -out etcd-node2.crt -days 7300 \
  -sha256 -extensions v3_req -extfile temp.cnf

openssl x509 -in etcd-node2.crt -text -noout | grep -A1 "Subject Alternative Name"
# Expected: IP Address:192.168.0.204, IP Address:127.0.0.1
rm temp.cnf


# Certificate for postgres-03 (192.168.0.205)
openssl genrsa -out etcd-node3.key 2048

cat > temp.cnf <<EOF
[ req ]
distinguished_name = req_distinguished_name
req_extensions = v3_req
[ req_distinguished_name ]
[ v3_req ]
subjectAltName = @alt_names
[ alt_names ]
IP.1 = 192.168.0.205
IP.2 = 127.0.0.1
EOF

openssl req -new -key etcd-node3.key -out etcd-node3.csr \
  -subj "/CN=etcd-node3" -config temp.cnf

openssl x509 -req -in etcd-node3.csr -CA ca.crt -CAkey ca.key \
  -CAcreateserial -out etcd-node3.crt -days 7300 \
  -sha256 -extensions v3_req -extfile temp.cnf

openssl x509 -in etcd-node3.crt -text -noout | grep -A1 "Subject Alternative Name"
# Expected: IP Address:192.168.0.205, IP Address:127.0.0.1
rm temp.cnf

Step 4 — Generate the PostgreSQL server certificate

One shared certificate covers all PostgreSQL nodes.

It is used for both PostgreSQL connections and the Patroni REST API.

# On postgres-01
openssl genrsa -out server.key 2048

openssl req -new -key server.key -out server.req
# You will be prompted for certificate details — the Common Name is not critical
# since connections are verified by IP SAN, not CN
# Warning "No -copy_extensions given" is harmless — the server cert does not need SANs

openssl req -x509 -key server.key -in server.req -out server.crt -days 7300
# Self-signed server certificate — signed directly with server.key, not the CA
# Patroni and PostgreSQL use this certificate to identify themselves to clients

Step 5 — Distribute certificates to postgres-02 and postgres-03

postgres-01 keeps its own certs in ~/certs — no scp needed for it.

# On postgres-01
scp ~/certs/ca.crt ~/certs/etcd-node2.crt ~/certs/etcd-node2.key \
  ~/certs/server.crt ~/certs/server.key fernando@192.168.0.204:/tmp/

scp ~/certs/ca.crt ~/certs/etcd-node3.crt ~/certs/etcd-node3.key \
  ~/certs/server.crt ~/certs/server.key fernando@192.168.0.205:/tmp/

Step 6 — Install certificates on each PostgreSQL node

All certificates live in /etc/etcd/certs/ on every node.

The directory is owned by etcd:etcd so the etcd daemon can read its certs.

En postgres user gets read access via ACL so Patroni can connect to etcd.

Important: set file permissions before locking down the directory.

After chmod 700 the shell cannot expand globs inside the directory as a non-root user — use explicit filenames.

# On postgres-01, postgres-02, postgres-03
sudo mkdir -p /etc/etcd/certs
sudo apt-get install -y acl
# acl: provides setfacl — needed to grant postgres user access without changing ownership

On postgres-01 — copy from ~/certs (files were never in /tmp on this node):

sudo cp ~/certs/ca.crt /etc/etcd/certs/
sudo cp ~/certs/etcd-node1.crt /etc/etcd/certs/
sudo cp ~/certs/etcd-node1.key /etc/etcd/certs/
sudo cp ~/certs/server.crt /etc/etcd/certs/
sudo cp ~/certs/server.key /etc/etcd/certs/

On postgres-02 — move from /tmp:

sudo mv /tmp/ca.crt /etc/etcd/certs/
sudo mv /tmp/etcd-node2.crt /etc/etcd/certs/
sudo mv /tmp/etcd-node2.key /etc/etcd/certs/
sudo mv /tmp/server.crt /etc/etcd/certs/
sudo mv /tmp/server.key /etc/etcd/certs/

On postgres-03 — move from /tmp:

sudo mv /tmp/ca.crt /etc/etcd/certs/
sudo mv /tmp/etcd-node3.crt /etc/etcd/certs/
sudo mv /tmp/etcd-node3.key /etc/etcd/certs/
sudo mv /tmp/server.crt /etc/etcd/certs/
sudo mv /tmp/server.key /etc/etcd/certs/

On all three nodes — set permissions then lock the directory:

etcd certs must be owned by etcd — the etcd daemon runs as this user.

Server certs must be owned by postgres — PostgreSQL enforces that its SSL private key is owned by the database user or root.

Utilizando etcd ownership will cause PostgreSQL to refuse to start with: “private key file must be owned by the database user or root”.

# Set file permissions first — must happen before chmod 700 on the directory
# After chmod 700, the shell cannot expand globs as a non-root user

# etcd certs: owned by etcd
# On postgres-01:
sudo chown etcd:etcd /etc/etcd/certs/etcd-node1.crt /etc/etcd/certs/etcd-node1.key /etc/etcd/certs/ca.crt
sudo chmod 600 /etc/etcd/certs/etcd-node1.key
sudo chmod 644 /etc/etcd/certs/etcd-node1.crt /etc/etcd/certs/ca.crt

# On postgres-02:
sudo chown etcd:etcd /etc/etcd/certs/etcd-node2.crt /etc/etcd/certs/etcd-node2.key /etc/etcd/certs/ca.crt
sudo chmod 600 /etc/etcd/certs/etcd-node2.key
sudo chmod 644 /etc/etcd/certs/etcd-node2.crt /etc/etcd/certs/ca.crt

# On postgres-03:
sudo chown etcd:etcd /etc/etcd/certs/etcd-node3.crt /etc/etcd/certs/etcd-node3.key /etc/etcd/certs/ca.crt
sudo chmod 600 /etc/etcd/certs/etcd-node3.key
sudo chmod 644 /etc/etcd/certs/etcd-node3.crt /etc/etcd/certs/ca.crt

# Server certs: owned by postgres (all three nodes — same files on each)
sudo chown postgres:postgres /etc/etcd/certs/server.crt /etc/etcd/certs/server.key
sudo chmod 600 /etc/etcd/certs/server.key
sudo chmod 644 /etc/etcd/certs/server.crt

# Lock the directory — run this last, after all file permissions are set
sudo chown etcd:etcd /etc/etcd/certs
sudo chmod 700 /etc/etcd/certs

# Grant the postgres user read access to the directory and all files inside it
# Patroni needs to read the etcd certs to connect with TLS
sudo setfacl -R -m u:postgres:rX /etc/etcd/certs
# -R: apply recursively to all files; rX: read + execute on directories (to traverse)

Step 7 — Create the combined PEM file for Patroni

Patroni's restapi.certfile expects a single file containing both the certificate and the private key.

# On postgres-01, postgres-02, postgres-03
sudo sh -c 'cat /etc/etcd/certs/server.crt /etc/etcd/certs/server.key \
  > /etc/etcd/certs/server.pem'
# Concatenates certificate then key into one file
sudo chown postgres:postgres /etc/etcd/certs/server.pem
sudo chmod 600 /etc/etcd/certs/server.pem
# server.pem contains the private key — PostgreSQL requires 0600 and postgres ownership

# Verify the PEM file is valid
sudo openssl x509 -in /etc/etcd/certs/server.pem -text -noout
# Expected: certificate details including validity dates and subject
# If "unable to load certificate": the PEM file is malformed — recreate it

# Verify final permissions on all cert files
sudo ls -la /etc/etcd/certs/
# Expected output (postgres-01 shown — node number differs on 02/03):
#   drwx------+  2 etcd     etcd      ca.crt etcd-node1.crt etcd-node1.key server.crt server.key server.pem
#   -rw-r--r--+  1 etcd     etcd      ca.crt
#   -rw-r--r--+  1 etcd     etcd      etcd-node1.crt
#   -rw-------+  1 etcd     etcd      etcd-node1.key
#   -rw-r--r--+  1 postgres postgres  server.crt
#   -rw-------+  1 postgres postgres  server.key   ← must be 0600, postgres-owned
#   -rw-------+  1 postgres postgres  server.pem   ← must be 0600, postgres-owned
# PostgreSQL will refuse to start if server.key or server.pem is group- or world-readable

6. etcd Configuration

Step 1 — Create the etcd data directory

# On postgres-01, postgres-02, postgres-03
sudo mkdir -p /var/lib/etcd
sudo chown -R etcd:etcd /var/lib/etcd
# etcd stores its WAL and snapshot data here — must be owned by the etcd user

Step 2 — Create the etcd environment file on each node

etcd is configured via environment variables loaded by the systemd service.

Only the node-specific values differ between nodes.

# /etc/etcd/etcd.env — postgres-01 (192.168.0.203)

ETCD_NAME="postgresql-01"
# ETCD_NAME: unique identifier for this member within the cluster

ETCD_DATA_DIR="/var/lib/etcd"
# ETCD_DATA_DIR: where etcd stores its WAL and snapshots

ETCD_INITIAL_CLUSTER="postgresql-01=https://192.168.0.203:2380,postgresql-02=https://192.168.0.204:2380,postgresql-03=https://192.168.0.205:2380"
# ETCD_INITIAL_CLUSTER: all members at bootstrap time — must be identical on all three nodes

ETCD_INITIAL_CLUSTER_STATE="new"
# new: this is a fresh cluster bootstrap
# Important: change to "existing" after the cluster is running (see section 9 step 3)

ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
# ETCD_INITIAL_CLUSTER_TOKEN: prevents nodes from accidentally joining the wrong cluster

ETCD_INITIAL_ADVERTISE_PEER_URLS="https://192.168.0.203:2380"
# ETCD_INITIAL_ADVERTISE_PEER_URLS: address this node advertises to other etcd members for peer traffic

ETCD_LISTEN_PEER_URLS="https://0.0.0.0:2380"
# ETCD_LISTEN_PEER_URLS: address etcd listens on for peer connections from other etcd members

ETCD_LISTEN_CLIENT_URLS="https://0.0.0.0:2379"
# ETCD_LISTEN_CLIENT_URLS: address etcd listens on for client connections (Patroni connects here)

ETCD_ADVERTISE_CLIENT_URLS="https://192.168.0.203:2379"
# ETCD_ADVERTISE_CLIENT_URLS: address this node advertises to clients — must be reachable from Patroni

# TLS for client connections (Patroni → etcd)
ETCD_CLIENT_CERT_AUTH="true"
# ETCD_CLIENT_CERT_AUTH: require clients to present a valid certificate (mutual TLS)
ETCD_TRUSTED_CA_FILE="/etc/etcd/certs/ca.crt"
# ETCD_TRUSTED_CA_FILE: CA certificate used to verify client certificates
ETCD_CERT_FILE="/etc/etcd/certs/etcd-node1.crt"
# ETCD_CERT_FILE: certificate presented to clients connecting to this node
ETCD_KEY_FILE="/etc/etcd/certs/etcd-node1.key"
# ETCD_KEY_FILE: private key for the above certificate

# TLS for peer connections (etcd node ↔ etcd node)
ETCD_PEER_CLIENT_CERT_AUTH="true"
# ETCD_PEER_CLIENT_CERT_AUTH: require peer nodes to present a valid certificate
ETCD_PEER_TRUSTED_CA_FILE="/etc/etcd/certs/ca.crt"
ETCD_PEER_CERT_FILE="/etc/etcd/certs/etcd-node1.crt"
ETCD_PEER_KEY_FILE="/etc/etcd/certs/etcd-node1.key"

For postgres-02 (192.168.0.204): change ETCD_NAME a postgresql-02, both IP addresses to 192.168.0.204, and cert/key filenames to etcd-node2.crt / etcd-node2.key.

For postgres-03 (192.168.0.205): change ETCD_NAME a postgresql-03, both IP addresses to 192.168.0.205, and cert/key filenames to etcd-node3.crt / etcd-node3.key.

Step 3 — Create the etcd systemd service file

# On postgres-01, postgres-02, postgres-03
# Create /etc/systemd/system/etcd.service with the following content:

[Unit]
Description=etcd key-value store
Documentation=https://github.com/etcd-io/etcd
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
# Type=notify: systemd waits for etcd to send a readiness signal before marking it as started
WorkingDirectory=/var/lib/etcd
EnvironmentFile=/etc/etcd/etcd.env
# EnvironmentFile: loads all ETCD_* variables from the file created in step 2
ExecStart=/usr/local/bin/etcd
Restart=always
# Restart=always: systemd restarts etcd if it exits for any reason
RestartSec=10s
LimitNOFILE=40000
# LimitNOFILE: raise the open file descriptor limit — etcd opens many files under load
User=etcd
Group=etcd

[Install]
WantedBy=multi-user.target

Step 4 — Start etcd on all 3 nodes

# On postgres-01, postgres-02, postgres-03
sudo systemctl daemon-reload
# daemon-reload: required after creating or modifying a systemd unit file

sudo systemctl enable etcd
# enable: start etcd automatically on boot

sudo systemctl start etcd
# start: start the etcd service now

sudo systemctl status etcd
# Expected: Active: active (running)
# If "Active: failed": check journalctl -xeu etcd.service for details
# Common causes:
#   - cert not found: verify paths in etcd.env match files in /etc/etcd/certs/
#   - permission denied on key: run "sudo chown etcd:etcd /etc/etcd/certs/*.key"
#   - port in use: run "ss -tlnp | grep 237" to find what is on ports 2379/2380

Step 5 — Verify etcd cluster health

# On postgres-01
etcdctl endpoint health
# Expected:
#   127.0.0.1:2379 is healthy: successfully committed proposal: took = 2.3ms
# This checks only the local node — the full cluster check is below

# Full cluster health check with TLS credentials
sudo etcdctl \
  --endpoints=https://192.168.0.203:2379,https://192.168.0.204:2379,https://192.168.0.205:2379 \
  --cacert=/etc/etcd/certs/ca.crt \
  --cert=/etc/etcd/certs/etcd-node1.crt \
  --key=/etc/etcd/certs/etcd-node1.key \
  endpoint health
# --cacert: CA certificate to verify the server certificates
# --cert / --key: client certificate and key for mutual TLS
# Expected:
#   https://192.168.0.203:2379 is healthy: successfully committed proposal: took = 2.3ms
#   https://192.168.0.204:2379 is healthy: successfully committed proposal: took = 2.1ms
#   https://192.168.0.205:2379 is healthy: successfully committed proposal: took = 2.4ms
# If any node is unhealthy: check journalctl -u etcd on that node
# If all nodes unhealthy: check firewall on port 2380 between nodes

# Check leader election — one node should be the leader
sudo etcdctl \
  --endpoints=https://192.168.0.203:2379,https://192.168.0.204:2379,https://192.168.0.205:2379 \
  --cacert=/etc/etcd/certs/ca.crt \
  --cert=/etc/etcd/certs/etcd-node1.crt \
  --key=/etc/etcd/certs/etcd-node1.key \
  endpoint status --write-out=table
# Expected: a table with one node showing IS LEADER = true
# If no leader: quorum is not established — verify all three nodes are running

7. Patroni Installation and Configuration

Step 1 — Install Patroni

# On postgres-01, postgres-02, postgres-03
sudo apt install -y patroni
# On Ubuntu 22.04+, the apt package includes the etcd v3 client library
# If your distro's package does not include it: pip install patroni[etcd3]
# [etcd3]: selects the etcd v3 API backend — required for etcd 3.5+
# The older [etcd] flag uses the deprecated v2 HTTP API

sudo mkdir -p /etc/patroni/
# Patroni reads its configuration from a YAML file in this directory

Step 2 — Create patroni.yml on each node

Only nombre, restapi.connect_address, postgresql.connect_address, and the etcd cert/key paths differ between nodes.

# /etc/patroni/config.yml — postgres-01 (192.168.0.203)

scope: postgresql-cluster
# scope: cluster name — must be identical on all Patroni nodes
# Used as the key prefix in etcd to separate multiple Patroni clusters

namespace: /service/
# namespace: etcd key prefix — all cluster state is stored under /service/postgresql-cluster/

name: postgresql-01
# name: unique name for this node within the cluster

etcd3:
  hosts: 192.168.0.203:2379,192.168.0.204:2379,192.168.0.205:2379
  # etcd3: use the etcd v3 API (gRPC) — required for etcd 3.5+
  # The older "etcd:" key uses the v2 HTTP API which is deprecated
  protocol: https
  # protocol: https — Patroni connects to etcd over TLS
  cacert: /etc/etcd/certs/ca.crt
  # cacert: CA certificate to verify the etcd server certificates
  cert: /etc/etcd/certs/etcd-node1.crt
  # cert: client certificate presented to etcd for mutual TLS
  key: /etc/etcd/certs/etcd-node1.key
  # key: private key for the client certificate

restapi:
  listen: 0.0.0.0:8008
  # listen: Patroni's REST API listens on all interfaces on port 8008
  # HAProxy queries this API to determine which node is the current primary
  connect_address: 192.168.0.203:8008
  # connect_address: the address other nodes use to reach this node's REST API — must be the actual IP
  certfile: /etc/etcd/certs/server.pem
  # certfile: combined cert+key PEM file — enables TLS on the REST API
  # HAProxy uses check-ssl when querying /primary — the API must serve a certificate

bootstrap:
  dcs:
    ttl: 30
    # ttl: leader lease duration in seconds
    # If the primary does not renew within ttl seconds, it is considered failed
    # and a standby is promoted. Lower = faster failover; higher = more tolerance for slow networks.
    loop_wait: 10
    # loop_wait: how often Patroni checks cluster health (seconds)
    retry_timeout: 10
    # retry_timeout: how long Patroni retries a failed etcd or PostgreSQL operation before giving up
    maximum_lag_on_failover: 1048576
    # maximum_lag_on_failover: standbys more than 1MB behind the primary will not be promoted
    # Prevents promoting a very stale standby that would cause significant data loss
    postgresql:
      parameters:
        ssl: 'on'
        # ssl: enable TLS on PostgreSQL connections
        ssl_cert_file: /etc/etcd/certs/server.crt
        ssl_key_file: /etc/etcd/certs/server.key
      pg_hba:
        # pg_hba: Patroni writes this pg_hba.conf on bootstrap
        # hostssl: TLS is required — plain connections are rejected
        - hostssl replication replicator 127.0.0.1/32 md5
        - hostssl replication replicator 192.168.0.203/32 md5
        - hostssl replication replicator 192.168.0.204/32 md5
        - hostssl replication replicator 192.168.0.205/32 md5
        # Replication connections from all three PostgreSQL nodes
        - hostssl all all 127.0.0.1/32 md5
        - hostssl all all 0.0.0.0/0 md5
        # Application connections — TLS required, password authentication

  initdb:
    - encoding: UTF8
    - data-checksums
    # data-checksums: enables page-level checksums — required for pg_rewind
    # Detects corrupted blocks; slight write overhead (typically under 2%)

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.0.203:5432
  # connect_address: this node's actual IP — used by standbys to connect for replication
  data_dir: /var/lib/postgresql/data
  # data_dir: PostgreSQL data directory — Patroni manages this directory entirely
  bin_dir: /usr/lib/postgresql/18/bin
  # bin_dir: directory containing pg_ctl, pg_basebackup, pg_rewind, initdb
  # Adjust the version number to match your PostgreSQL installation
  authentication:
    superuser:
      username: postgres
      password: strongpassword
      # Patroni uses these credentials for internal management connections
      # Change before production use
    replication:
      username: replicator
      password: replpassword
      # Patroni creates this role automatically during bootstrap
      # Change before production use
  parameters:
    max_connections: 100
    shared_buffers: 256MB
    # shared_buffers: PostgreSQL's main memory cache — typically 25% of total RAM
    # 256MB is a conservative default; increase based on available memory

tags:
  nofailover: false
  # nofailover: set to true on a node you never want automatically promoted (e.g. a DR standby)
  noloadbalance: false
  # noloadbalance: set to true to exclude this node from read replica routing
  clonefrom: false
  nosync: false

ctl:
  insecure: true
  # insecure: skip TLS certificate verification when patronictl calls the Patroni REST API
  # Required for switchover and failover commands — without it patronictl fails with an SSL error
  # Read-only commands (patronictl list) work without this because they use etcd, not the REST API
  # Do NOT add cacert or certfile here — a server certfile causes a bad TLS handshake

For postgres-02 (192.168.0.204): change nombre a postgresql-02, both connect_address values to 192.168.0.204, and etcd cert/key to etcd-node2.crt / etcd-node2.key.

For postgres-03 (192.168.0.205): change nombre a postgresql-03, both connect_address values to 192.168.0.205, and etcd cert/key to etcd-node3.crt / etcd-node3.key.

8. Starting the Cluster

Step 1 — Start Patroni on postgres-01 first

The intended primary must start first.

If a standby starts before a primary exists in etcd, it waits — it does not cause an error. Starting the primary first avoids a three-way leader election race.

# On postgres-01
sudo systemctl enable patroni
# enable: start Patroni automatically on boot
sudo systemctl restart patroni
# Patroni initialises the PostgreSQL data directory (initdb), starts PostgreSQL,
# acquires the leader lease in etcd, and configures itself as primary

journalctl -u patroni -f
# -f: follow — stream new log lines as they appear
# Watch for: "promoted self to leader" — confirms postgres-01 is the primary
# Press Ctrl+C to stop following once confirmed
# If "could not connect to etcd": verify etcd is running and TLS certs are correct

Step 2 — Verify postgres-01 is the leader

# On postgres-01
# patronictl reads ca.crt from /etc/etcd/certs/ — that directory is mode 700.
# Run with sudo, or it will fail with an SSL certificate load error.
sudo patronictl -c /etc/patroni/config.yml list
# Expected:
# + Cluster: postgresql-cluster +----+-----------+
# | Member        | Host              | Role   | State   | TL | Lag in MB |
# +---------------+-------------------+--------+---------+----+-----------+
# | postgresql-01 | 192.168.0.203    | Leader | running |  1 |           |
# +---------------+-------------------+--------+---------+----+-----------+
# If State is "start failed": check journalctl -u patroni for the PostgreSQL error
# If no Leader after 30 seconds: etcd health check (section 7 step 5)

Step 3 — Start Patroni on postgres-02 and postgres-03

# On postgres-02 and postgres-03
sudo systemctl enable patroni && sudo systemctl restart patroni
# Patroni detects the existing primary in etcd, runs pg_basebackup from postgres-01,
# and starts PostgreSQL as a streaming standby

# Verify all three nodes
sudo patronictl -c /etc/patroni/config.yml list
# Expected:
# + Cluster: postgresql-cluster -----+----+-----------+
# | Member        | Host           | Role    | State   | TL | Lag in MB |
# +---------------+----------------+---------+---------+----+-----------+
# | postgresql-01 | 192.168.0.203 | Leader  | running |  1 |           |
# | postgresql-02 | 192.168.0.204 | Replica | running |  1 |         0 |
# | postgresql-03 | 192.168.0.205 | Replica | running |  1 |         0 |
# +---------------+----------------+---------+---------+----+-----------+
# Lag in MB = 0: standbys are fully caught up with the primary
# If a node shows "stopped": check journalctl -u patroni on that node
# If pg_basebackup failed: verify port 5432 is open between nodes

Step 4 — Change initial-cluster-state to existing on all nodes

After successful bootstrap, initial-cluster-state must be changed from new a existing.

This prevents a node from accidentally bootstrapping a new cluster if it is restarted in isolation later.

# On postgres-01, postgres-02, postgres-03
# Edit /etc/etcd/etcd.env and change:
#   ETCD_INITIAL_CLUSTER_STATE="new"
# to:
#   ETCD_INITIAL_CLUSTER_STATE="existing"

sudo systemctl restart etcd
# Restart to apply the change
# Expected: etcd rejoins the existing cluster cleanly
# Verify: run the endpoint health command from section 7 step 5

9. HAProxy Setup

HAProxy routes all application connections to the current primary by querying Patroni's REST API.

Only the primary returns HTTP 200 on /primary — standbys return HTTP 503.

Three HAProxy nodes provide redundancy; keepalived (section 11) moves the VIP between them.

Step 1 — Install HAProxy

# On haproxy-01, haproxy-02, haproxy-03
sudo apt install -y haproxy

haproxy -v
# Expected: HAProxy version 2.x.x
# If an older version: add the HAProxy apt repository for the latest version

Step 2 — Configure HAProxy

The configuration is identical on all three HAProxy nodes.

# /etc/haproxy/haproxy.cfg

frontend postgres_frontend
    bind *:5432
    mode tcp
    # mode tcp: HAProxy forwards raw TCP — PostgreSQL is not an HTTP protocol
    timeout client 30s
    # timeout client belongs in the frontend only — HAProxy will warn and ignore it in a backend
    default_backend postgres_backend

backend postgres_backend
    mode tcp
    option tcp-check
    option httpchk GET /primary
    # httpchk: HAProxy queries this endpoint on each node's Patroni REST API
    # Only the current primary returns HTTP 200 on /primary; standbys return 503
    # This is how HAProxy knows which node to route write traffic to
    http-check expect status 200
    timeout connect 5s
    # timeout connect and timeout server belong in the backend only
    timeout server 30s
    server postgresql-01 192.168.0.203:5432 port 8008 check check-ssl verify none
    server postgresql-02 192.168.0.204:5432 port 8008 check check-ssl verify none
    server postgresql-03 192.168.0.205:5432 port 8008 check check-ssl verify none
    # port 8008: HAProxy checks the Patroni REST API port, not the PostgreSQL port
    # check-ssl: use TLS when querying the REST API (Patroni REST API has TLS enabled)
    # verify none: skip certificate CN verification — acceptable in a private cluster
    # where nodes are identified by IP

Step 3 — Validate config and reload HAProxy

Always validate the config before reloading.

HAProxy will refuse to reload if the config has errors.

# On haproxy-01, haproxy-02, haproxy-03
sudo haproxy -c -f /etc/haproxy/haproxy.cfg
# -c: check config only, do not start
# -f: path to config file
# Expected: Configuration file is valid
# If you see warnings about timeout client/server in the wrong section,
# check that timeout client is in the frontend and timeout connect/server are in the backend

# On haproxy-01, haproxy-02, haproxy-03
sudo systemctl reload haproxy
# reload: applies the new configuration without dropping existing connections
# Expected: no output on the terminal; confirm success in the logs below

sudo journalctl -u haproxy --since "1 minute ago"
# Expected lines (timestamps will differ):
#   systemd[1]: Reloading haproxy.service - HAProxy Load Balancer...
#   systemd[1]: Reloaded haproxy.service - HAProxy Load Balancer.
# If you see "Failed": check sudo haproxy -c output first

sudo tail -f /var/log/syslog | grep haproxy
# Watch the HAProxy logs to confirm it is checking the PostgreSQL nodes
# Expected: repeated health check lines; one node should show "UP" (the primary)
# If all nodes show "DOWN": HAProxy cannot reach port 8008 — check firewall

10. keepalived Setup

keepalived manages the VIP using VRRP.

One HAProxy node holds the VIP (the MASTER).

If the MASTER's health check fails or the node goes down, keepalived on a BACKUP node claims the VIP.

Applications always connect to the VIP — HAProxy node failures are transparent.

Step 1 — Install keepalived

# On haproxy-01, haproxy-02, haproxy-03
sudo apt update && sudo apt install -y keepalived

Step 2 — Create the HAProxy health check script

keepalived runs this script every 2 seconds.

A non-zero exit code tells keepalived this node should not hold the VIP.

# On haproxy-01, haproxy-02, haproxy-03
# Create /etc/keepalived/check_haproxy.sh with the following content:

#!/bin/bash

PORT=5432

if ! pidof haproxy > /dev/null; then
    # pidof haproxy: checks whether the HAProxy process is running
    echo "HAProxy is not running"
    exit 1
    # exit 1: non-zero tells keepalived this node is unhealthy — VIP moves to a BACKUP
fi

if ! ss -ltn | grep -q ":${PORT}"; then
    # ss -ltn: list listening TCP sockets; grep checks whether port 5432 is bound
    echo "HAProxy is not listening on port ${PORT}"
    exit 2
fi

exit 0
# exit 0: tells keepalived this node is healthy and should keep (or receive) the VIP

# On haproxy-01, haproxy-02, haproxy-03
sudo useradd -r -s /bin/false keepalived_script
# -r: system account; -s /bin/false: no interactive login
# keepalived runs health check scripts as this user when enable_script_security is active

sudo chmod +x /etc/keepalived/check_haproxy.sh
sudo chown keepalived_script:keepalived_script /etc/keepalived/check_haproxy.sh
sudo chmod 700 /etc/keepalived/check_haproxy.sh
# 700: owner execute-only — keepalived requires the script not be writable by other users
# when enable_script_security is active (configured in keepalived.conf below)

Step 3 — Configure keepalived

Each node has a different state y priority.

The MASTER (priority 100) holds the VIP initially.

If it fails, the BACKUP with the next highest priority (90) claims the VIP.

# /etc/keepalived/keepalived.conf — haproxy-01 (MASTER)

global_defs {
    enable_script_security
    # enable_script_security: prevents keepalived from running scripts owned by root
    # or writable by anyone — prevents privilege escalation through health check scripts
    script_user keepalived_script
    # script_user: the OS user keepalived uses to execute health check scripts
}

vrrp_script check_haproxy {
    script "/etc/keepalived/check_haproxy.sh"
    interval 2
    # interval: run the health check every 2 seconds
    fall 3
    # fall: mark this node as failed after 3 consecutive failing checks (6 seconds total)
    rise 2
    # rise: mark this node as recovered after 2 consecutive passing checks (4 seconds)
}

vrrp_instance VI_1 {
    state MASTER
    # MASTER: this node holds the VIP initially on startup
    interface enp0s3
    # interface: the network interface where the VIP is assigned
    # Run "ip link show" to find your interface name — may be ens3, enp0s3, eth0, etc.
    # In this lab the interface is enp0s3 (VirtualBox default)
    virtual_router_id 51
    # virtual_router_id: identifies this VRRP group — must be identical on all three nodes
    priority 100
    # priority: the node with the highest priority wins the VIP election
    # haproxy-01 wins by default (100 > 90 > 80)
    advert_int 1
    # advert_int: send VRRP advertisements every 1 second
    authentication {
        auth_type PASS
        auth_pass changeme123
        # auth_pass: shared password between all keepalived nodes — change before production use
    }
    virtual_ipaddress {
        192.168.0.210
        # The VIP — applications connect to this address on port 5432
    }
    track_script {
        check_haproxy
        # track_script: tie this VRRP instance to the HAProxy health check
        # If the script exits non-zero, this node's effective priority drops
        # below the BACKUPs and the VIP moves
    }
}

# /etc/keepalived/keepalived.conf — haproxy-02 (BACKUP, second priority)
# Identical to haproxy-01 except:
#   state BACKUP
#   priority 90

# /etc/keepalived/keepalived.conf — haproxy-03 (BACKUP, lowest priority)
# Identical to haproxy-01 except:
#   state BACKUP
#   priority 80

Step 4 — Start keepalived

# On haproxy-01, haproxy-02, haproxy-03
sudo systemctl enable --now keepalived

sudo journalctl -u keepalived -f
# Watch the keepalived logs — expected to see VRRP state transitions
# haproxy-01 should log: "(VI_1) Entering BACKUP STATE" then "(VI_1) Entering MASTER STATE"
#   (briefly enters BACKUP on startup, wins election after ~4 seconds due to priority 100)
# haproxy-02 and haproxy-03 should log: "(VI_1) Entering BACKUP STATE"
# Press Ctrl+C to stop following
#
# NOTE: "Truncating auth_pass to 8 characters" is a warning, not an error.
# keepalived only uses the first 8 characters of auth_pass regardless of what you set.
# This is fine as long as all nodes use the same password string — they will all truncate identically.

# Verify the VIP is active on haproxy-01
ping -c 3 192.168.0.210
# Expected: 3 packets transmitted, 3 received
# If 0 received: VIP is not assigned to any node — check keepalived logs on all three HAProxy nodes

11. Set the postgres Superuser Password

Patroni manages its own pg_hba.conf at /var/lib/postgresql/data/pg_hba.conf — not the default package location at /etc/postgresql/18/main/pg_hba.conf.

Patroni's file contains only hostssl entries and no local Unix socket entry.

This means sudo -u postgres psql will fail until a local entry is added.

# On postgres-01 — check Patroni's actual pg_hba.conf
sudo cat /var/lib/postgresql/data/pg_hba.conf
# Expected: "Do not edit this file manually! It will be overwritten by Patroni!"
# followed by hostssl entries only — no local socket entry

Add a temporary local entry so the postgres OS user can connect via socket:

# On postgres-01
echo "local all postgres peer" | sudo tee -a /var/lib/postgresql/data/pg_hba.conf

Reload the config using pg_ctl — pg_reload_conf() cannot be used because there is no socket connection yet:

# On postgres-01
sudo -u postgres /usr/lib/postgresql/18/bin/pg_ctl reload -D /var/lib/postgresql/data
# Expected: server signaled

Set the password:

# On postgres-01
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'strongpassword';"
# Expected: ALTER ROLE
# IMPORTANT: this password must match postgresql.authentication.superuser.password in config.yml
# If they differ, Patroni cannot connect to its local PostgreSQL instance and will report
# unknown LSN — the node will appear stuck after a switchover or restart
# The password is now stored in the database — it will survive Patroni overwriting pg_hba.conf

Patroni will overwrite pg_hba.conf on its next cycle and remove the local entry — that is expected and fine.

The password persists in the database catalog regardless.

12. Verify the Full Stack

Run these checks in order after completing all setup steps.

# Check 1: etcd cluster is healthy (run on postgres-01)
sudo etcdctl \
  --endpoints=https://192.168.0.203:2379,https://192.168.0.204:2379,https://192.168.0.205:2379 \
  --cacert=/etc/etcd/certs/ca.crt \
  --cert=/etc/etcd/certs/etcd-node1.crt \
  --key=/etc/etcd/certs/etcd-node1.key \
  endpoint health
# Expected: all three nodes report "is healthy"

# Check 2: Patroni cluster has a leader and two replicas (run on any PostgreSQL node)
sudo patronictl -c /etc/patroni/config.yml list
# Expected: one Leader, two Replica, all State = running, Lag = 0

# Check 3: VIP is responding
ping -c 3 192.168.0.210
# Expected: 3 packets received
# If 0 received: VIP not assigned — check keepalived on all HAProxy nodes

# Check 4: PostgreSQL is reachable through the VIP
psql -h 192.168.0.210 -U postgres -c "SELECT inet_server_addr(), pg_is_in_recovery();"
# Expected: inet_server_addr = 192.168.0.203 (the primary), pg_is_in_recovery = f
# pg_is_in_recovery = f: confirms this is the primary (standbys return t)
# If connection refused: HAProxy is not routing — check haproxy status and /primary endpoint

# Check 5: replication is streaming
psql -h 192.168.0.210 -U postgres -c "SELECT client_addr, replay_lag FROM pg_stat_replication;"
# Expected: two rows (one per standby), replay_lag = 00:00:00 or NULL (caught up)
# If no rows: standbys are not streaming — check Patroni logs on standby nodes

# Check 6: insert data on primary, read it from both standbys
psql -h 192.168.0.210 -U postgres -c "CREATE TABLE test (id serial, val text);"
psql -h 192.168.0.210 -U postgres -c "INSERT INTO test (val) VALUES ('replication works');"
psql -h 192.168.0.204 -U postgres -c "SELECT * FROM test;"
# Expected: one row with val = 'replication works'
# Connects directly to postgres-02 (a standby) to confirm the data replicated
# If "relation does not exist": replication is not running — check pg_stat_replication
psql -h 192.168.0.205 -U postgres -c "SELECT * FROM test;"
# Expected: same row — confirms postgres-03 is also replicating
psql -h 192.168.0.210 -U postgres -c "DROP TABLE test;"
# Clean up the test table

13. Switchover (Planned)

A switchover moves the primary role to a specific standby with zero data loss.

Patroni waits for the candidate standby to be fully caught up before promoting.

Prerequisite — ctl: section in config.yml: En ctl: section must be present with insecure: true before running switchover.

Without it, patronictl cannot authenticate to the Patroni REST API and the switchover will fail with an SSL error.

Read-only commands like patronictl list do not require the REST API (they use etcd) — so the missing section is not obvious until you attempt a write operation.

# Verify the ctl section is present on all PostgreSQL nodes before attempting switchover
sudo tail -5 /etc/patroni/config.yml
# Expected:
#   ctl:
#     insecure: true
# If missing: add it and reload patroni (sudo systemctl reload patroni)
# NOTE: do not add cacert or certfile to the ctl section — only insecure: true
# Adding a server certfile causes a bad TLS handshake and switchover still fails

# On any PostgreSQL node
sudo patronictl -c /etc/patroni/config.yml switchover postgresql-cluster \
  --leader postgresql-01 \
  # --leader: the current primary being demoted
  # NOTE: older Patroni versions used --master here — newer versions use --leader
  --candidate postgresql-02
  # --candidate: the standby being promoted
# patronictl will show the current topology and ask for confirmation — press Enter for "now", then y

# Patroni performs these steps automatically:
# 1. Pauses writes on the primary (checkpoint)
# 2. Waits for the candidate to confirm it has applied all WAL
# 3. Demotes the current primary to standby
# 4. Promotes the candidate to primary
# 5. Reconfigures the old primary as a standby using pg_rewind

sudo patronictl -c /etc/patroni/config.yml list
# Expected: postgresql-02 now shows Role = Leader on a new timeline; postgresql-01 shows Role = Replica
# postgresql-01 may briefly show "stopped" — this is pg_rewind running; wait 10 seconds and recheck
# If postgresql-01 stays "stopped": pg_rewind failed
#   Fix: sudo patronictl -c /etc/patroni/config.yml reinit postgresql-cluster postgresql-01

14. Failover (Unplanned)

When the primary fails, Patroni detects it automatically:

1. The primary fails to renew its leader lease in etcd within ttl seconds (30 by default)
2. Patroni on the remaining nodes holds an election in etcd
3. The standby with the least lag that is within maximum_lag_on_failover is elected and promoted
4. Other standbys reattach to the new primary using pg_rewind

Simulate a primary failure

Stop Patroni on the current primary to simulate a crash.

Patroni manages PostgreSQL — stopping Patroni also stops PostgreSQL on that node.

# On postgres-01 (the current primary)
sudo systemctl stop patroni
# This simulates a primary crash — PostgreSQL stops and the leader lease expires

On postgres-02 or postgres-03, watch the automatic failover happen:

# On postgres-02 or postgres-03
watch -n2 "sudo patronictl -c /etc/patroni/config.yml list"
# Expected sequence over ~30 seconds (ttl):
# 1. postgresql-01 disappears or shows "stopped"
# 2. One of the remaining nodes shows Leader on a new timeline
# 3. The other remaining node shows Replica streaming
# Press Ctrl+C when the new leader is confirmed

Bring postgres-01 back:

# On postgres-01
sudo systemctl start patroni

Check the cluster — postgres-01 should rejoin as a replica:

sudo patronictl -c /etc/patroni/config.yml list
# Expected: postgres-01 shows Replica streaming, Lag = 0
# If postgres-01 shows "start failed": check sudo journalctl -u patroni -n 30 --no-pager

Switch back to postgres-01 as primary when ready:

sudo patronictl -c /etc/patroni/config.yml switchover postgresql-cluster \
  --leader <new-leader> \
  --candidate postgresql-01

Manual failover — use only when automatic failover has not triggered

sudo patronictl -c /etc/patroni/config.yml failover postgresql-cluster \
  --leader postgresql-01 \
  --candidate postgresql-02 \
  --force
  # --force: skip the confirmation prompt
  # Use only when the primary is confirmed down
  # Without --force, patronictl prompts you to confirm before proceeding

15. Day-to-Day Operations

Check cluster status

sudo patronictl -c /etc/patroni/config.yml list
# Shows all members, roles (Leader/Replica), state (running/stopped/start failed),
# timeline, and replication lag in MB
# Run this first whenever diagnosing any cluster issue

Pause and resume automatic failover

# Pause — Patroni will not promote any standby while paused
# Use during planned maintenance to prevent accidental failover
sudo patronictl -c /etc/patroni/config.yml pause postgresql-cluster

# Resume — restore automatic failover
sudo patronictl -c /etc/patroni/config.yml resume postgresql-cluster

Restart PostgreSQL on a node

# Always restart PostgreSQL through patronictl — never directly via systemctl
# Running "systemctl restart postgresql" bypasses Patroni and causes inconsistent state
sudo patronictl -c /etc/patroni/config.yml restart postgresql-cluster postgresql-02

Reload configuration

# Apply postgresql.conf changes across all nodes without a restart
sudo patronictl -c /etc/patroni/config.yml reload postgresql-cluster

Edit cluster DCS configuration

# Edit settings stored in etcd (ttl, maximum_lag_on_failover, synchronous_mode, etc.)
sudo patronictl -c /etc/patroni/config.yml edit-config postgresql-cluster
# Opens the current configuration in your $EDITOR
# Changes take effect immediately after saving — no restart required

Reinitialise a failed standby

# If pg_rewind fails after a failover, wipe and rebuild the standby from the primary
sudo patronictl -c /etc/patroni/config.yml reinit postgresql-cluster postgresql-03
# Wipes data_dir on postgresql-03 and runs pg_basebackup from the current primary
# Wipes the standby and rebuilds it from the current primary

16. Synchronous Mode (Zero Data Loss)

sudo patronictl -c /etc/patroni/config.yml edit-config postgresql-cluster

Add or update:

synchronous_mode: true
# true: commits on the primary wait for at least one standby to confirm before returning
# Patroni sets synchronous_standby_names automatically — no manual configuration needed

synchronous_mode_strict: false
# false (default): if no synchronous standby is available, the primary continues writing
# true: if no synchronous standby is available, the primary stops accepting writes entirely
#       use true only when zero data loss is mandatory and availability can be sacrificed

17. Monitoring

Patroni REST API

# Check HTTP status code only — this is what HAProxy checks internally
curl -k -o /dev/null -w "%{http_code}\n" https://192.168.0.203:8008/primary
# Expected on the primary: 200
# Expected on a replica: 503
# -k: skip certificate verification; -o /dev/null: discard body; -w: print status code only

curl -k -o /dev/null -w "%{http_code}\n" https://192.168.0.203:8008/replica
# Expected on a replica: 200
# Expected on the primary: 503

# Check full JSON response (useful for debugging)
curl -k https://192.168.0.203:8008/primary
# Response includes: role, server_version, timeline, replication state of each standby

curl -k https://192.168.0.203:8008/cluster | python3 -m json.tool
# Full cluster status in formatted JSON
# Expected: all members listed with roles, state, and lag

curl -k https://192.168.0.203:8008/health
# Expected: HTTP 200 if the node is running normally

Replication lag

Connect directly to the primary node — pg_stat_replication only has rows on the primary, not on replicas.

HAProxy also routes port 5432 on the VIP to the primary, so either works.

# Option 1 — direct to primary
psql -h 192.168.0.203 -U postgres -c "SELECT client_addr, replay_lag, sync_state FROM pg_stat_replication;"

# Option 2 — through VIP (HAProxy routes all connections on port 5432 to the primary)
psql -h 192.168.0.210 -p 5432 -U postgres -c "SELECT client_addr, replay_lag, sync_state FROM pg_stat_replication;"

Expected output:

  client_addr  | replay_lag | sync_state
---------------+------------+------------
 192.168.0.204 |            | async
 192.168.0.205 |            | async
(2 rows)

• One row per connected standby
• replay_lag = NULL (empty): standby is fully caught up — normal at rest
• replay_lag growing: standby is falling behind — check network and standby Patroni logs
• 0 rows: no standbys streaming — check patronictl list to confirm replica states; if replicas show running, check primary_conninfo in postgresql.auto.conf on each standby

18. Full Reset

Use this procedure to rebuild the entire cluster from scratch — for example after a lab failure, a misconfiguration that cannot be recovered, or to re-run the lab from Section 9. TLS certificates are preserved on all nodes.

Only etcd cluster state and PostgreSQL data are wiped.

Run all commands in this section on postgres-01, postgres-02, and postgres-03 unless labelled otherwise.

Step 1 — Stop Patroni and etcd on all PostgreSQL nodes

Patroni must stop before etcd so it can cleanly release its leader lock.

If etcd is stopped first, Patroni loses its DCS connection and may hang.

On postgres-01:

# On postgres-01
sudo systemctl stop patroni
# Stops PostgreSQL gracefully via Patroni — do not use systemctl stop postgresql directly

sudo systemctl stop etcd
# Stops the etcd member on this node

Repeat on postgres-02 and postgres-03 before continuing.

Step 2 — Wipe etcd and PostgreSQL data directories

# On postgres-01, postgres-02, postgres-03
sudo rm -rf /var/lib/etcd/
# Removes all etcd WAL and snapshot data — the etcd cluster will re-bootstrap from scratch

sudo rm -rf /var/lib/postgresql/data/
# Removes all PostgreSQL data files — Patroni will re-initialise via pg_basebackup on standbys

Step 3 — Recreate directories with correct ownership

rm -rf removes the directory itself, not just its contents.

The directories must be recreated before etcd and Patroni can write to them.

# On postgres-01, postgres-02, postgres-03
sudo mkdir -p /var/lib/etcd/
sudo chown etcd:etcd /var/lib/etcd/
# etcd runs as the etcd user — it must own its data directory

sudo mkdir -p /var/lib/postgresql/data
sudo chown postgres:postgres /var/lib/postgresql/data
# Patroni runs as the postgres user — it must own the PostgreSQL data directory

Step 4 — Restore ACL permissions on etcd certificates

rm -rf on the data directory does not affect /etc/etcd/certs/, but if you also wiped the certs directory during troubleshooting, the postgres user will have lost read access to the etcd certificates.

Run this step to restore those permissions.

On postgres-01:

# On postgres-01
sudo setfacl -m u:postgres:r /etc/etcd/certs/ca.crt
sudo setfacl -m u:postgres:r /etc/etcd/certs/etcd-node1.crt
sudo setfacl -m u:postgres:r /etc/etcd/certs/etcd-node1.key
# Grants the postgres OS user read access to the etcd TLS files
# Required because Patroni (running as postgres) connects to etcd over TLS

On postgres-02:

# On postgres-02
sudo setfacl -m u:postgres:r /etc/etcd/certs/ca.crt
sudo setfacl -m u:postgres:r /etc/etcd/certs/etcd-node2.crt
sudo setfacl -m u:postgres:r /etc/etcd/certs/etcd-node2.key

On postgres-03:

# On postgres-03
sudo setfacl -m u:postgres:r /etc/etcd/certs/ca.crt
sudo setfacl -m u:postgres:r /etc/etcd/certs/etcd-node3.crt
sudo setfacl -m u:postgres:r /etc/etcd/certs/etcd-node3.key

Step 5 — Reset ETCD_INITIAL_CLUSTER_STATE to “new”

After the first bootstrap, etcd.env on all nodes was changed to existing.

For a full reset, it must be set back to new so etcd treats this as a fresh cluster formation.

On each node, back up and edit /etc/etcd/etcd.env:

On postgres-01:

# On postgres-01
sudo cp /etc/etcd/etcd.env /etc/etcd/etcd.env.$(date +%Y%m%d)
sudo vi /etc/etcd/etcd.env

Change:

ETCD_INITIAL_CLUSTER_STATE="existing"

To:

ETCD_INITIAL_CLUSTER_STATE="new"

Repeat on postgres-02 and postgres-03.

Step 6 — Start etcd on all nodes

etcd must be running on all three nodes before Patroni starts.

Start etcd on all three nodes before continuing to Step 7.

# On postgres-01, postgres-02, postgres-03
sudo systemctl start etcd

Verify all three members are healthy before starting Patroni:

# On postgres-01
ETCDCTL_API=3 etcdctl \
  --cacert=/etc/etcd/certs/ca.crt \
  --cert=/etc/etcd/certs/etcd-node1.crt \
  --key=/etc/etcd/certs/etcd-node1.key \
  --endpoints=https://192.168.0.203:2379,https://192.168.0.204:2379,https://192.168.0.205:2379 \
  endpoint health
# Expected: all three endpoints report "is healthy"
# If any node is unhealthy: check journalctl -u etcd on that node before starting Patroni

Step 7 — Start Patroni on all nodes

Start Patroni on postgres-01 first.

Patroni on postgres-01 will bootstrap a new PostgreSQL primary.

Only after postgres-01 is running and shows as Leader should postgres-02 and postgres-03 be started — they will join as replicas via pg_basebackup.

# On postgres-01 — start first
sudo systemctl start patroni

Wait until postgres-01 shows as Leader:

# On postgres-01
patronictl -c /etc/patroni/config.yml list
# Expected: postgresql-01 shows as Leader, state running
# Wait for this before starting postgres-02 and postgres-03

Then start Patroni on the remaining nodes:

# On postgres-02
sudo systemctl start patroni

# On postgres-03
sudo systemctl start patroni

Final verification:

# On postgres-01
patronictl -c /etc/patroni/config.yml list
# Expected:
# + Cluster: postgres-cluster --------+----+-----------+
# | Member        | Host            | Role    | State   | TL | Lag in MB |
# +---------------+-----------------+---------+---------+----+-----------+
# | postgresql-01 | 192.168.0.203:5432 | Leader | running |  1 |           |
# | postgresql-02 | 192.168.0.204:5432 | Replica | running |  1 |         0 |
# | postgresql-03 | 192.168.0.205:5432 | Replica | running |  1 |         0 |
# +---------------+-----------------+---------+---------+----+-----------+
# TL 1: fresh cluster, timeline resets to 1 on full reset
# If a node shows "start failed": check journalctl -u patroni on that node

Continue from Section 9 Step 3 to re-verify the cluster and set ETCD_INITIAL_CLUSTER_STATE=existing.

19. Common Issues

20. Key Commands Reference