CloudTadaInsights
Return to Articles

Building a Highly Available PostgreSQL Cluster with Patroni, etcd, and PgBouncer

DevOps & Development

Architecture Diagram

Running PostgreSQL in production isn't just about having a single server — it's about high availability (HA), automatic failover, and connection pooling. With the right setup, you can ensure your databases survive node failures without downtime.

In this guide, we'll build a 3-node PostgreSQL HA cluster using Patroni, etcd, and PgBouncer, combined with HAProxy + Keepalived for load balancing and a virtual IP.

etcd: Distributed key-value store used by Patroni for consensus and leader election.

Patroni: Orchestrates PostgreSQL, manages replication, failover, and recovery.

PgBouncer: Connection pooler to reduce overhead from many client connections.

HAProxy + Keepalived: Provides a VIP for clients, always pointing to the active PostgreSQL leader.

Step 1. Preparing the Environment

For this setup, I'm running on Ubuntu 22.04.5 LTS (Jammy Jellyfish).

Update /etc/hosts for easier resolution:

TEXT
192.168.50.11  patroni1
192.168.50.12  patroni2
192.168.50.13  patroni3
192.168.50.21  haproxy1
192.168.50.22  haproxy2
192.168.50.100 pg-vip

Directory Layout and Tablespaces

I created dedicated directories for PostgreSQL data, WAL, logs, and optional tablespaces:

BASH
sudo mkdir -p /pg/pgdata /pg/pgwal /pg/pglogs
sudo mkdir -p /tablespaces/TEST_TBS /tablespaces/TEST_TBS
sudo chown -R postgres:postgres /pg /tablespaces
  • /pg/pgdata → main PostgreSQL data directory
  • /pg/pgwal → separate storage for WAL files (recommended for performance and crash recovery)
  • /pg/pglogs → PostgreSQL logs
  • /tablespaces/… → optional directories for custom tablespaces Tablespaces let you place different databases or schemas on different storage volumes.

Important: The same directory structure (/pg/pgdata, /pg/pgwal, /pg/pglogs, and any /tablespaces/…) must exist on all Patroni nodes.

(Optional) To create a tablespace in PostgreSQL, you'd run:

SQL
CREATE TABLESPACE db1_tbs LOCATION '/tablespaces/TEST_TBS';
CREATE DATABASE mydb TABLESPACE db1_tbs;

Install required packages on all Patroni nodes:

source: https://www.postgresql.org/download/linux/ubuntu/

BASH
# Import the repository signing key:
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
# Create the repository configuration file:
. /etc/os-release
sudo sh -c "echo 'deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $VERSION_CODENAME-pgdg main' > /etc/apt/sources.list.d/pgdg.list"
# Update the package lists:
sudo apt update
# Install the latest version of PostgreSQL:
# If you want a specific version, use 'postgresql-17' or similar instead of 'postgresql'
sudo apt -y install postgresql-17

Install etcd, PgBouncer, and Patroni:

BASH
sudo apt-get install postgresql-common patroni etcd python3-psycopg2 pgbouncer

Step 2. Configuring etcd

On each etcd node, configure /etc/default/etcd:

Example for patroni1:

BASH
ETCD_NAME="patroni1"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_LISTEN_PEER_URLS="http://192.168.50.11:2380"
ETCD_LISTEN_CLIENT_URLS="http://192.168.50.11:2379,http://127.0.0.1:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.50.11:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.50.11:2379"
ETCD_INITIAL_CLUSTER="patroni1=http://192.168.50.11:2380,patroni2=http://192.168.50.12:2380,patroni3=http://192.168.50.13:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="pg-ha-etcd"

Change etcd default config path:

BASH
sudo mkdir -p /etc/systemd/system/etcd.service.d
sudo tee /etc/systemd/system/etcd.service.d/override.conf >/dev/null <<'EOF'
[Service]
Environment=ETCD_DATA_DIR=/var/lib/etcd
EOF
sudo systemctl daemon-reload
sudo systemctl restart etcd
systemctl show etcd -p Environment | grep ETCD_DATA_DIR

Repeat with node-specific IPs and names.

Show etcd cluster health:

BASH
ETCDCTL_API=3 etcdctl --endpoints=192.168.50.11:2379,192.168.50.12:2379,192.168.50.13:2379 endpoint health
etcdctl member list

Step 3. Configuring Patroni

Add Patroni system service:

BASH
cat <<'EOF' | sudo tee /etc/systemd/system/patroni.service
[Unit]
Description=Patroni PostgreSQL HA Cluster
After=network-online.target
Wants=network-online.target
[Service]
User=postgres
Group=postgres
Type=simple
ExecStart=/usr/bin/patroni /etc/patroni/patroni.yml
Restart=on-failure
LimitNOFILE=102400
[Install]
WantedBy=multi-user.target
EOF

Each node has a Patroni config (/etc/patroni/patroni.yml). Example (for patroni1)

YAML
scope: pg-ha
namespace: /service/
name: patroni1
restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.50.11:8008
etcd:
  hosts: 192.168.50.11:2379,192.168.50.12:2379,192.168.50.13:2379
bootstrap:
  initdb:
    - encoding: UTF8
    - locale: en_US.UTF-8
    - data-checksums
    - waldir: /pg/pgwal/pg_wal
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    synchronous_mode: true
    synchronous_mode_strict: false
    synchronous_node_count: 1
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        shared_buffers: '8GB'
        effective_cache_size: '16GB'
        work_mem: '32MB'
        maintenance_work_mem: '1GB'
        wal_level: replica
        wal_compression: on
        wal_log_hints: on
        max_wal_senders: 10
        max_replication_slots: 10
        hot_standby: on
        hot_standby_feedback: on
        wal_keep_size: '1024MB'
        max_wal_size: '8GB'
        min_wal_size: '1GB'
        checkpoint_timeout: '15min'
        checkpoint_completion_target: 0.9
        fsync: on
        full_page_writes: on
        synchronous_commit: on
        archive_mode: on
        archive_command: 'pgbackrest --stanza=main archive-push %p'
        logging_collector: on
        log_directory: '/pg/pglogs'
        log_filename: 'postgresql-%Y-%m-%d_%H%M%S.log'
        log_truncate_on_rotation: on
        log_rotation_age: '1d'
        log_min_duration_statement: '500ms'
  users:
    replicator:
      password: "ReplPass123"
      options: [replication]
    admin:
      password: "AdminPass123"
      options: [createrole, createdb]
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.50.11:5432
  data_dir: /pg/pgdata
  bin_dir: /usr/lib/postgresql/17/bin
  authentication:
    superuser:
      username: postgres
      password: "SuperSecurePass123"
    replication:
      username: replicator
      password: "ReplPass123"
  pg_hba:
    - host replication replicator 192.168.50.0/24 md5
    - host all all 0.0.0.0/0 md5
watchdog:
  mode: off

patroni2 — /etc/patroni/patroni.yml

(same as above but name, connect_address, and IPs updated to 192.168.50.12)

patroni3 — /etc/patroni/patroni.yml

(same as above but name, connect_address, and IPs updated to 192.168.50.13)

Run Patroni as a systemd service:

BASH
sudo systemctl daemon-reload
sudo systemctl enable patroni
sudo systemctl start patroni
patronictl -c /etc/patroni/patroni.yml list
sudo patronictl -c /etc/patroni/config.yml show-config

Step 4. Setting up PgBouncer

Each node runs PgBouncer at port 6432:

/etc/pgbouncer/pgbouncer.ini

INI
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb user=appuser pool_size=20 reserve_pool_size=5
* = host=10.157.8.23 port=5432 user=postgres pool_mode=session
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = plain
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
pool_mode = transaction
max_client_conn = 1000
max_db_connections = 100
reserve_pool_size = 5
stats_users = postgres

/etc/pgbouncer/users.txt

TEXT
"appdb" "myAppPassword"
"postgres" "myPostgresPassword"

Enable and start:

BASH
sudo systemctl enable pgbouncer
sudo systemctl restart pgbouncer

The /etc/pgbouncer/pgbouncer.ini and /etc/pgbouncer/userlist.txt files must be created and configured on each Patroni node (patroni1, patroni2, patroni3).

Transaction vs Session Pooling

Choosing the right pool mode is critical:

  • transaction mode (recommended for microservices):
    • Each query/transaction borrows a connection and releases it immediately.
    • Great for stateless, API-driven apps.
    • GUI tools like DBeaver/pgAdmin may fail because they expect a stable session.
    • That's why I added a catch-all * rule with pool_mode=session, so I can still manage the cluster comfortably from GUI tools while keeping transaction mode as the default for microservices.
  • **session** mode (recommended for interactive tools / long-lived sessions):
    • Client gets the same backend connection for the entire session.
    • Required for tools that keep temporary tables, prepared statements, or cursors open.
    • Less efficient for microservices.

Authentication Best Practices

Local PgBouncer (same host as Postgres) → auth_type = plain is acceptable since traffic never leaves the machine.

Remote PgBouncer (dedicated pooler nodes) → Use md5 or scram-sha-256 for stronger security.

Step 5. HAProxy + Keepalived

Install HAProxy and KeepAlived:

BASH
sudo apt -y install haproxy keepalived
# Enable Binding to VIP
echo 'net.ipv4.ip_nonlocal_bind = 1' | sudo tee /etc/sysctl.d/60-nonlocal-bind.conf
sudo sysctl -p /etc/sysctl.d/60-nonlocal-bind.conf

HAProxy forwards traffic to Patroni leader via PgBouncer:

/etc/haproxy/haproxy.cfg

HAPROXY
global
    daemon
    maxconn 5000
    log /dev/log local0
defaults
    mode tcp
    log global
    timeout connect 5s
    timeout client 30s
    timeout server 30s
frontend pg_write
    bind *:5432
    default_backend pgsql-write
backend pgsql-write
    balance roundrobin
    option httpchk GET /master
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server patroni1 192.168.50.11:6432 check port 8008
    server patroni2 192.168.50.12:6432 check port 8008
    server patroni3 192.168.50.13:6432 check port 8008
listen stats
    bind *:8404
    mode http
    stats enable
    stats uri /haproxy?stats
    stats refresh 10s
BASH
sudo haproxy -c -f /etc/haproxy/haproxy.cfg
sudo systemctl reload haproxy
sudo ss -lntp | grep ':5432'     # should show haproxy LISTEN

repeat for haproxy1 and haproxy2.

HAProxy on each LB node is querying Patroni on all DB nodes (via the IPs and port 8008) to monitor their role. You can test this by visiting http://HAPROXY_IP

/master in a browser or with curl — the primary will return HTTP 200 and some JSON status, replicas return 503.

Keepalived Configuration

We’ll configure two HAProxy nodes (haproxy1 as MASTER, haproxy2 as BACKUP).

haproxy1 — /etc/keepalived/keepalived.conf

CONF
global_defs {
  script_user root
  enable_script_security
}
vrrp_script chk_haproxy {
    script "/usr/bin/systemctl is-active --quiet haproxy"
    interval 2
    weight -5
}
vrrp_instance VI_PG {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass MySecretPass
    }
    unicast_src_ip 192.168.50.21
    unicast_peer {
        192.168.50.22
    }
    virtual_ipaddress {
        192.168.50.100/32 dev eth0
    }
    track_script {
        chk_haproxy
    }
}

haproxy2 — /etc/keepalived/keepalived.conf

CONF
global_defs {
  script_user root
  enable_script_security
}
vrrp_script chk_haproxy {
    script "/usr/bin/systemctl is-active --quiet haproxy"
    interval 2
    weight -5
}
vrrp_instance VI_PG {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 50
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass MySecretPass
    }
    unicast_src_ip 192.168.50.22
    unicast_peer {
        192.168.50.21
    }
    virtual_ipaddress {
        192.168.50.100/32 dev eth0
    }
    track_script {
        chk_haproxy
    }
}
BASH
sudo systemctl restart keepalived
sudo journalctl -u keepalived -f

Testing the Cluster

Check Patroni status:

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

Connect via VIP:

BASH
psql -h 192.168.50.100 -U postgres -d appdb

Simulate failover by stopping Patroni on the leader and watching HAProxy switch:

BASH
sudo systemctl stop patroni
curl http://PATRONI_NODE_IP:8008/master

Conclusion

  • Patroni + etcd handles PostgreSQL replication and leader election.
  • PgBouncer provides lightweight connection pooling.
  • HAProxy + Keepalived ensures seamless client access through a single VIP.
Explore related topics:
#Database#PostgreSQL#Highly Available#Patroni#PgBouncer#etcd#Linux#Ubuntu

Share this article

You might also like

Browse all articles
Series

PostgreSQL Security Hardening Guide

Essential security features and hardening measures for PostgreSQL HA cluster deployment with Patroni, etcd, and PgBouncer. Follow this guide for production security best practices.

#Database#PostgreSQL#Security

Lesson 4: Infrastructure Preparation for PostgreSQL HA

Setting up the infrastructure for PostgreSQL High Availability with Patroni and etcd, including hardware requirements, network configuration, firewall, SSH keys, and time synchronization.

#Database#PostgreSQL#Infrastructure

Lesson 3: Introduction to Patroni and etcd

Understanding Patroni and etcd for PostgreSQL High Availability, including DCS, Raft consensus algorithm, leader election, and split-brain prevention mechanisms.

#Database#PostgreSQL#Patroni
Series

PostgreSQL HA Cluster - Monitoring Stack

Full monitoring stack with Prometheus and Grafana for PostgreSQL HA cluster. Includes pre-configured dashboards, alerting rules, and exporter configurations.

#Database#PostgreSQL#Monitoring