Haute disponibilité PostgreSQL avec Patroni, etcd, HAProxy et keepalived

          +----------+   +----------+   +----------+
          |  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

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

# 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

# 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

# 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

# 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

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

# 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

# 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

# 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

# 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/

# 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

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/

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/

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/

# 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)

# 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

# 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

# /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"

# 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

# 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

# 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

# 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

# /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

# 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

# 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)

# 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

# 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

# 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

# /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

# 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

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

# 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)

# /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

# 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

# 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

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

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

# 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

# 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

# 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

# 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 -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

# On postgres-01
sudo systemctl start patroni

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

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

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

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 — 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

# 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

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

# 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

# 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

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

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

# 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

# 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;"

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

# 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

# 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

# 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

# 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
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
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

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

ETCD_INITIAL_CLUSTER_STATE="existing"

ETCD_INITIAL_CLUSTER_STATE="new"

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

# 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

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

# 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

# On postgres-02
sudo systemctl start patroni

# On postgres-03
sudo systemctl start patroni

# 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