World Programming Society

Deploying a PostgreSQL Cluster with Patroni and HAProxy on Ubuntu 24.04

A Patroni cluster needs an odd number of nodes to maintain quorum — with 3 nodes, losing 1 still...

Dev.to

Dev.to · Jul 31, 2026

0

Deploying a PostgreSQL Cluster with Patroni and HAProxy on Ubuntu 24.04

A Patroni cluster needs an odd number of nodes to maintain quorum — with 3 nodes, losing 1 still leaves a majority, so the cluster keeps running. This guide builds a 3-node PostgreSQL cluster on Ubuntu 24.04 with Patroni handling replication and automatic failover, etcd as the coordination store, and HAProxy load-balancing client connections — all secured with TLS.

Prerequisites: three Ubuntu 24.04 servers (2 vCPU / 4GB RAM minimum) with PostgreSQL installed, non-root sudo access, and a domain with three A records: node1.example.com, node2.example.com, node3.example.com. Replace these placeholders with your actual subdomains throughout.

Install Dependencies

Run on all three nodes unless noted otherwise.

1. Install packages:

$ sudo apt update
$ sudo apt install haproxy certbot pipx -y
$ sudo pip3 install --break-system-packages 'patroni[etcd3]' psycopg2-binary psycopg

Enter fullscreen mode Exit fullscreen mode

2. Install etcd:

$ wget https://github.com/etcd-io/etcd/releases/download/v3.6.4/etcd-v3.6.4-linux-amd64.tar.gz
$ tar -xvf etcd-v3.6.4-linux-amd64.tar.gz
$ sudo mv etcd-v3.6.4-linux-amd64/etcd etcd-v3.6.4-linux-amd64/etcdctl /usr/local/bin/

Enter fullscreen mode Exit fullscreen mode

3. Open firewall ports — 80 (Certbot), 2379/2380 (etcd), 5432/5433 (PostgreSQL + Patroni-managed PostgreSQL), 8008/8009 (Patroni REST API):

$ sudo ufw allow 80,2379,2380,5432,5433,8008,8009/tcp
$ sudo ufw reload
$ sudo ufw status

Enter fullscreen mode Exit fullscreen mode

Configure SSL Certificates

1. Request a certificate per node (run on each node for its own subdomain):

$ sudo certbot certonly --standalone -d node1.example.com -m [email protected] --agree-tos --no-eff

Enter fullscreen mode Exit fullscreen mode

2. Create a cert-prep script on each node (set HOSTNAME to that node's subdomain):

$ sudo nano /usr/local/bin/prepare-ssl-certs.sh

Enter fullscreen mode Exit fullscreen mode

#!/bin/bash

HOSTNAME="node1.example.com"  # Update for each node
CERT_DIR="/etc/letsencrypt/live/$HOSTNAME"
ARCHIVE_DIR="/etc/letsencrypt/archive/$HOSTNAME"

getent group ssl-users >/dev/null || sudo groupadd ssl-users

for user in etcd patroni haproxy postgres; do
    if ! id "$user" >/dev/null 2>&1 && [[ "$user" == "etcd" || "$user" == "patroni" ]]; then
        getent group "$user" >/dev/null && sudo useradd -r -m -s /bin/bash -g "$user" "$user" || sudo useradd -r -m -s /bin/bash "$user"
    fi
    groups "$user" 2>/dev/null | grep -q ssl-users || sudo usermod -aG ssl-users "$user"
done

cat "$CERT_DIR/fullchain.pem" "$CERT_DIR/privkey.pem" > "$CERT_DIR/combined.pem"
sudo chmod 755 /etc/letsencrypt /etc/letsencrypt/live /etc/letsencrypt/archive
sudo chgrp ssl-users "$CERT_DIR" "$ARCHIVE_DIR"
sudo chmod 755 "$CERT_DIR" "$ARCHIVE_DIR"
sudo chown root:ssl-users "$ARCHIVE_DIR"/*.pem "$CERT_DIR/combined.pem"
sudo chmod 644 "$ARCHIVE_DIR"/cert*.pem "$ARCHIVE_DIR"/chain*.pem "$ARCHIVE_DIR"/fullchain*.pem
sudo chmod 640 "$ARCHIVE_DIR"/privkey*.pem "$CERT_DIR/combined.pem"

id "linuxuser" >/dev/null 2>&1 && ! groups linuxuser | grep -q ssl-users && sudo usermod -aG ssl-users linuxuser

echo "SSL certificate setup completed for $HOSTNAME"

Enter fullscreen mode Exit fullscreen mode

$ sudo chmod +x /usr/local/bin/prepare-ssl-certs.sh
$ sudo /usr/local/bin/prepare-ssl-certs.sh

Enter fullscreen mode Exit fullscreen mode

3. Create a renewal hook so certs stay combined and services reload after renewal:

$ sudo nano /usr/local/bin/prepare-renewed-ssl-certs.sh

Enter fullscreen mode Exit fullscreen mode

#!/bin/bash

HOSTNAME="node1.example.com"  # Update for each node
CERT_DIR="/etc/letsencrypt/live/$HOSTNAME"
ARCHIVE_DIR="/etc/letsencrypt/archive/$HOSTNAME"

cat "$CERT_DIR/fullchain.pem" "$CERT_DIR/privkey.pem" > "$CERT_DIR/combined.pem" 2>/dev/null

chmod 755 /etc/letsencrypt /etc/letsencrypt/live /etc/letsencrypt/archive 2>/dev/null
chgrp ssl-users "$CERT_DIR" "$ARCHIVE_DIR" 2>/dev/null
chmod 755 "$CERT_DIR" "$ARCHIVE_DIR" 2>/dev/null
chown root:ssl-users "$ARCHIVE_DIR"/*.pem "$CERT_DIR/combined.pem" 2>/dev/null
chmod 644 "$ARCHIVE_DIR"/cert*.pem "$ARCHIVE_DIR"/chain*.pem "$ARCHIVE_DIR"/fullchain*.pem 2>/dev/null
chmod 640 "$ARCHIVE_DIR"/privkey*.pem "$CERT_DIR/combined.pem" 2>/dev/null

systemctl is-active --quiet haproxy && systemctl reload haproxy 2>/dev/null
systemctl is-active --quiet postgresql && systemctl reload postgresql 2>/dev/null
systemctl is-active --quiet patroni && systemctl restart patroni 2>/dev/null
systemctl is-active --quiet etcd && { sleep $((RANDOM % 30)); systemctl restart etcd 2>/dev/null; }

Enter fullscreen mode Exit fullscreen mode

$ sudo chmod +x /usr/local/bin/prepare-renewed-ssl-certs.sh
$ echo 'renew_hook = /usr/local/bin/prepare-renewed-ssl-certs.sh' | sudo tee -a /etc/letsencrypt/renewal/node1.example.com.conf

Enter fullscreen mode Exit fullscreen mode

Repeat the renewal hook line for node2 and node3, each pointing at its own renewal conf.

Configure etcd

Patroni uses etcd as its distributed key-value store for leader election.

$ sudo nano /etc/systemd/system/etcd.service

Enter fullscreen mode Exit fullscreen mode

Node1's unit (adjust --name, peer/client URLs, and cert paths per node for node2/node3):

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

[Service]
User=etcd
Type=notify
ExecStart=/usr/local/bin/etcd \
  --name node1 \
  --data-dir /var/lib/etcd \
  --initial-cluster node1=https://node1.example.com:2380,node2=https://node2.example.com:2380,node3=https://node3.example.com:2380 \
  --initial-cluster-state new \
  --listen-peer-urls https://0.0.0.0:2380 \
  --initial-advertise-peer-urls https://node1.example.com:2380 \
  --listen-client-urls https://0.0.0.0:2379 \
  --advertise-client-urls https://node1.example.com:2379 \
  --cert-file /etc/letsencrypt/live/node1.example.com/fullchain.pem \
  --key-file /etc/letsencrypt/live/node1.example.com/privkey.pem \
  --peer-cert-file /etc/letsencrypt/live/node1.example.com/fullchain.pem \
  --peer-key-file /etc/letsencrypt/live/node1.example.com/privkey.pem \
  --client-cert-auth=false \
  --peer-client-cert-auth=false \
  --auto-tls=false \
  --peer-auto-tls=false
Restart=always
RestartSec=5
LimitNOFILE=40000

[Install]
WantedBy=multi-user.target

Enter fullscreen mode Exit fullscreen mode

$ sudo mkdir -p /var/lib/etcd /etc/etcd
$ sudo chown -R etcd:etcd /var/lib/etcd /etc/etcd
$ sudo chmod 700 /var/lib/etcd
$ sudo systemctl daemon-reload
$ sudo systemctl enable etcd
$ sudo systemctl start etcd
$ sudo systemctl status etcd

Enter fullscreen mode Exit fullscreen mode

Verify the cluster formed:

$ etcdctl --endpoints=https://node1.example.com:2379 member list

Enter fullscreen mode Exit fullscreen mode

693741ed87a9350d, started, node1, https://node1.example.com:2380, https://node1.example.com:2379, false
d9554ae165b5de0b, started, node3, https://node3.example.com:2380, https://node3.example.com:2379, false
fecdfaa2d1b8f35c, started, node2, https://node2.example.com:2380, https://node2.example.com:2379, false

Enter fullscreen mode Exit fullscreen mode

$ etcdctl --endpoints=https://node1.example.com:2379,https://node2.example.com:2379,https://node3.example.com:2379 endpoint health

Enter fullscreen mode Exit fullscreen mode

Configure PostgreSQL

1. SSL and memory settings (per node, adjust cert paths):

$ sudo nano /etc/postgresql/18/main/postgresql.conf

Enter fullscreen mode Exit fullscreen mode

ssl_cert_file = '/etc/letsencrypt/live/node1.example.com/fullchain.pem'
ssl_key_file = '/etc/letsencrypt/live/node1.example.com/privkey.pem'

shared_buffers = 512MB
work_mem = 8MB
effective_cache_size = 1536MB

Enter fullscreen mode Exit fullscreen mode

2. Enforce SSL connections:

$ sudo nano /etc/postgresql/18/main/pg_hba.conf

Enter fullscreen mode Exit fullscreen mode

hostssl all             all             0.0.0.0/0               scram-sha-256

Enter fullscreen mode Exit fullscreen mode

$ sudo systemctl restart postgresql
$ sudo -u postgres psql -c "SHOW ssl;"

Enter fullscreen mode Exit fullscreen mode

Configure Patroni

$ sudo nano /etc/patroni.yaml

Enter fullscreen mode Exit fullscreen mode

Node1's config (adjust name, connect_address, and cert paths for node2/node3):

scope: postgres
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: node1.example.com:8008
  certfile: /etc/letsencrypt/live/node1.example.com/fullchain.pem
  keyfile: /etc/letsencrypt/live/node1.example.com/privkey.pem

etcd3:
  hosts:
    - node1.example.com:2379
    - node2.example.com:2379
    - node3.example.com:2379
  protocol: https
  cacert: /etc/ssl/certs/ca-certificates.crt

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 5
    retry_timeout: 5
    maximum_lag_on_failover: 1048576
  initdb:
    - encoding: UTF8
    - data-checksums
  pg_hba:
    - host replication replicator 0.0.0.0/0 scram-sha-256
    - host all all 0.0.0.0/0 scram-sha-256

postgresql:
  listen: 0.0.0.0:5433
  connect_address: node1.example.com:5433
  data_dir: /var/lib/postgresql/18/main
  bin_dir: /usr/lib/postgresql/18/bin
  parameters:
    ssl: 'on'
    ssl_cert_file: '/etc/letsencrypt/live/node1.example.com/fullchain.pem'
    ssl_key_file: '/etc/letsencrypt/live/node1.example.com/privkey.pem'
  authentication:
    replication:
      username: replicator
      password: StrongPassword123!
    superuser:
      username: postgres
      password: StrongPassword123!

Enter fullscreen mode Exit fullscreen mode

Use identical authentication passwords across all three nodes.

Prepare directories and hand PostgreSQL off to Patroni:

$ sudo mkdir -p /var/run/postgresql
$ sudo chown patroni:patroni /var/run/postgresql
$ sudo chmod 755 /var/run/postgresql
$ echo 'd /var/run/postgresql 0755 patroni patroni -' | sudo tee /etc/tmpfiles.d/postgresql.conf
$ sudo mv /var/lib/postgresql/18 /var/lib/postgresql/18.bak-$(date +%s) || true
$ sudo mkdir -p /var/lib/postgresql/18/main
$ sudo chown -R patroni:patroni /var/lib/postgresql/18
$ sudo chmod -R 700 /var/lib/postgresql/18
$ sudo chmod o+rx /usr/lib/postgresql/18/bin/*
$ sudo systemctl stop postgresql

Enter fullscreen mode Exit fullscreen mode

Create the Patroni systemd unit:

$ sudo nano /etc/systemd/system/patroni.service

Enter fullscreen mode Exit fullscreen mode

[Unit]
Description=Patroni PostgreSQL cluster
After=network.target

[Service]
Type=simple
User=patroni
Group=patroni
ExecStart=/usr/local/bin/patroni /etc/patroni.yaml
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
TimeoutSec=30
Restart=always
RestartSec=5

[Install]
WantedBy=multi-user.target

Enter fullscreen mode Exit fullscreen mode

$ sudo systemctl daemon-reload
$ sudo systemctl enable patroni
$ sudo systemctl start patroni

Enter fullscreen mode Exit fullscreen mode

Verify cluster state:

$ patronictl -c /etc/patroni.yaml list

Enter fullscreen mode Exit fullscreen mode

+ Cluster: postgres (7561190282296399779) --+-----------+----+-------------+-----+------------+-----+
| Member | Host                   | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+--------+------------------------+---------+-----------+----+-------------+-----+------------+-----+
| node1  | node1.example.com:5433 | Leader  | running   |  1 |             |     |            |     |
| node2  | node2.example.com:5433 | Replica | streaming |  1 |   0/4000000 |   0 |  0/4000000 |   0 |
| node3  | node3.example.com:5433 | Replica | streaming |  1 |   0/4000000 |   0 |  0/4000000 |   0 |
+--------+------------------------+---------+-----------+----+-------------+-----+------------+-----+

Enter fullscreen mode Exit fullscreen mode

Configure HAProxy

Same config on all three nodes, load-balancing PostgreSQL connections across the cluster:

$ sudo rm /etc/haproxy/haproxy.cfg
$ sudo nano /etc/haproxy/haproxy.cfg

Enter fullscreen mode Exit fullscreen mode

global
    daemon
    maxconn 4096
    log stdout local0
    stats socket /var/lib/haproxy/stats mode 660 level admin

defaults
    mode tcp
    timeout connect 5000ms
    timeout client 50000ms
    timeout server 50000ms
    log global

frontend postgres_ssl
    bind *:5432
    default_backend postgres_backend

backend postgres_backend
    balance roundrobin
    server node1 node1.example.com:5433 check inter 5000ms rise 2 fall 3
    server node2 node2.example.com:5433 check inter 5000ms rise 2 fall 3
    server node3 node3.example.com:5433 check inter 5000ms rise 2 fall 3

listen stats
    bind *:8404
    mode http
    stats enable
    stats uri /stats
    stats refresh 30s

Enter fullscreen mode Exit fullscreen mode

$ sudo systemctl restart haproxy
$ sudo systemctl status haproxy

Enter fullscreen mode Exit fullscreen mode

Test the Cluster

1. Connect through HAProxy:

$ psql 'postgresql://postgres:[email protected]:5433,node2.example.com:5433,node3.example.com:5433/postgres?sslmode=require&target_session_attrs=read-write'

Enter fullscreen mode Exit fullscreen mode

2. Check the current primary:

postgres=# SELECT inet_server_addr() as ip,
       CASE WHEN pg_is_in_recovery() THEN 'Replica' ELSE 'Primary' END as role;
postgres=# \q

Enter fullscreen mode Exit fullscreen mode

3. Trigger a failover by stopping Patroni on the leader:

$ sudo systemctl stop patroni

Enter fullscreen mode Exit fullscreen mode

4. Confirm a new leader was elected from another node:

$ patronictl -c /etc/patroni.yaml list

Enter fullscreen mode Exit fullscreen mode

5. Verify the cluster is still reachable through the same connection string, then bring the stopped node back:

$ sudo systemctl start patroni

Enter fullscreen mode Exit fullscreen mode

Next Steps

The cluster now handles node failure automatically, load-balances client connections through HAProxy, and encrypts all traffic with TLS. From here:

Add Prometheus + Grafana to track replication lag, connection counts, and failover events
Tune maximum_lag_on_failover and ttl in patroni.yaml for your latency/consistency tradeoffs
Automate the per-node config differences (name, connect_address, cert paths) with Ansible or similar for easier scaling

For the full guide, visit the original article on Vultr Docs.

Read the original source Next news

#Ai #Backend

Publisher

Originally by Sanskriti Harmukh

0 Comments

Log in to join the conversation.

No comments yet. Be the first to share your thoughts.