How to Set Up a Load-Balanced PostgreSQL Cluster with Patroni on RHEL 7

How to Set Up a Load-Balanced PostgreSQL Cluster with Patroni on RHEL 7

Running a single PostgreSQL instance is a liability in any production environment — it is a single point of failure that, when it goes down, takes your entire application with it. Patroni is an open-source, Python-based high-availability solution for PostgreSQL that uses a Distributed Configuration Store (DCS) — such as etcd, Consul, or ZooKeeper — to coordinate a leader election among PostgreSQL nodes. When the primary (leader) node fails, Patroni detects the outage through the DCS heartbeat, promotes the most up-to-date standby automatically, and updates the cluster state so that HAProxy (or any other load balancer) redirects writes to the new primary within seconds. This guide walks through installing and configuring a two-node Patroni cluster backed by etcd on RHEL 7, with HAProxy providing read/write split for application clients.

Prerequisites

• At least two RHEL 7 servers for PostgreSQL nodes (e.g., pg1: 192.168.1.11, pg2: 192.168.1.12)
• A third server or the same nodes for etcd (e.g., 192.168.1.10)
• A fourth server or virtual IP for HAProxy (e.g., 192.168.1.20)
• RHEL 7 with EPEL and SCL (Software Collections) repositories enabled
• Root or sudo access on all nodes
• All nodes can reach each other on the required ports (2379, 2380 for etcd; 5432 for PostgreSQL; 8008 for Patroni REST API)

Step 1: Install etcd as the Distributed Configuration Store

Install and configure etcd on the dedicated DCS node (or as a cluster). For this guide, a single etcd node is used for simplicity. In production, run a three-node etcd cluster for quorum-based fault tolerance.

# On the etcd node (192.168.1.10)
sudo yum install -y epel-release
sudo yum install -y etcd

# Configure etcd
sudo tee /etc/etcd/etcd.conf <<'EOF'
ETCD_NAME=etcd0
ETCD_DATA_DIR=/var/lib/etcd/default.etcd
ETCD_LISTEN_CLIENT_URLS="http://192.168.1.10:2379,http://127.0.0.1:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.1.10:2379"
ETCD_LISTEN_PEER_URLS="http://192.168.1.10:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.1.10:2380"
ETCD_INITIAL_CLUSTER="etcd0=http://192.168.1.10:2380"
ETCD_INITIAL_CLUSTER_TOKEN="patroni-etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
EOF

sudo systemctl enable etcd
sudo systemctl start etcd

# Verify etcd is healthy
etcdctl --endpoints=http://192.168.1.10:2379 cluster-health
# Output: cluster is healthy

Step 2: Install Python 3 and PostgreSQL on the Database Nodes

Patroni requires Python 3. On RHEL 7, Python 3 is available via the Software Collections Library (SCL). PostgreSQL 12 or 14 is available via the PostgreSQL YUM repository.

# On BOTH pg1 and pg2 nodes — run all commands on each

# Install Python 3 via SCL
sudo yum install -y centos-release-scl 2>/dev/null || 
  sudo yum install -y scl-utils
sudo yum install -y rh-python36 rh-python36-python-devel 
  rh-python36-python-pip

# Make Python 3 available as 'python3' in the current shell
scl enable rh-python36 bash

# Add PostgreSQL YUM repository
sudo yum install -y 
  https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL 14
sudo yum install -y postgresql14-server postgresql14-contrib postgresql14

# Add PostgreSQL binaries to PATH
echo 'export PATH=$PATH:/usr/pgsql-14/bin' | 
  sudo tee /etc/profile.d/pgsql.sh
source /etc/profile.d/pgsql.sh

# Do NOT initdb — Patroni will handle cluster initialization
# Verify psql is accessible
psql --version

Step 3: Install Patroni

# On BOTH pg1 and pg2 nodes
# Install Patroni with etcd support using pip3
source /opt/rh/rh-python36/enable

pip3 install --upgrade pip
pip3 install patroni[etcd]

# Verify installation
patroni --version
# Output: patroni 3.x.x

# Install psycopg2 (PostgreSQL Python adapter, required by Patroni)
pip3 install psycopg2-binary

# Create directories
sudo mkdir -p /etc/patroni
sudo mkdir -p /data/patroni
sudo useradd --system --home /var/lib/pgsql 
  --shell /bin/bash postgres 2>/dev/null || true
sudo chown postgres:postgres /data/patroni
sudo chmod 700 /data/patroni

Step 4: Configure Patroni

Each node needs its own patroni.yml with node-specific values for name, connect_address, and listen. The configuration below is for pg1; adjust IP addresses for pg2.

# /etc/patroni/patroni.yml — on pg1 (192.168.1.11)
sudo tee /etc/patroni/patroni.yml <<'EOF'
scope: postgres-cluster
namespace: /service/
name: pg1

restapi:
  listen: 192.168.1.11:8008
  connect_address: 192.168.1.11:8008

etcd:
  hosts: 192.168.1.10:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: logical
        hot_standby: "on"
        max_connections: 200
        max_wal_senders: 5
        max_replication_slots: 5
        wal_keep_segments: 8

  initdb:
    - encoding: UTF8
    - data-checksums

  pg_hba:
    - host replication replicator 192.168.1.0/24 md5
    - host all all 0.0.0.0/0 md5

  users:
    admin:
      password: "AdminPassword1!"
      options:
        - createrole
        - createdb

postgresql:
  listen: 192.168.1.11:5432
  connect_address: 192.168.1.11:5432
  data_dir: /data/patroni
  bin_dir: /usr/pgsql-14/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: "ReplicatorPassword1!"
    superuser:
      username: postgres
      password: "PostgresPassword1!"
    rewind:
      username: rewind_user
      password: "RewindPassword1!"

  parameters:
    unix_socket_directories: '/var/run/postgresql'

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false
EOF

sudo chown postgres:postgres /etc/patroni/patroni.yml
sudo chmod 600 /etc/patroni/patroni.yml

On pg2, create the same file but set name: pg2, restapi.listen: 192.168.1.12:8008, restapi.connect_address: 192.168.1.12:8008, postgresql.listen: 192.168.1.12:5432, and postgresql.connect_address: 192.168.1.12:5432.

Step 5: Create and Start the Patroni systemd Service

# On BOTH pg1 and pg2
sudo tee /etc/systemd/system/patroni.service <<'EOF'
[Unit]
Description=Patroni PostgreSQL High Availability
After=syslog.target network.target

[Service]
Type=simple
User=postgres
Group=postgres
# Load Python 3 SCL environment
Environment=PATH=/opt/rh/rh-python36/root/usr/bin:/usr/pgsql-14/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
ExecStart=/opt/rh/rh-python36/root/usr/bin/patroni 
  /etc/patroni/patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=process
TimeoutStopSec=30
Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable patroni

# Start pg1 FIRST (it will initdb and become the primary)
# On pg1:
sudo systemctl start patroni
sudo systemctl status patroni

# Wait ~30 seconds for pg1 to initialize, then start pg2
# On pg2:
sudo systemctl start patroni
sudo systemctl status patroni

Step 6: Verify the Cluster with patronictl

# Check cluster status from either node
source /opt/rh/rh-python36/enable

patronictl -c /etc/patroni/patroni.yml list
# Output:
# + Cluster: postgres-cluster (xxxxxxxxxxxxxxxx) +----+-----------+
# | Member | Host           | Role    | State   | TL | Lag in MB |
# +--------+----------------+---------+---------+----+-----------+
# | pg1    | 192.168.1.11:5432 | Leader  | running |  1 |           |
# | pg2    | 192.168.1.12:5432 | Replica | running |  1 |         0 |
# +--------+----------------+---------+---------+----+-----------+

# Test a manual switchover (promotes pg2, demotes pg1)
patronictl -c /etc/patroni/patroni.yml switchover 
  postgres-cluster --master pg1 --candidate pg2 --force

# Verify the new leader
patronictl -c /etc/patroni/patroni.yml list

Step 7: Configure HAProxy for Read/Write Split

HAProxy uses Patroni’s REST health endpoint to determine which node is the primary (for writes) and which nodes are replicas (for reads). The primary returns HTTP 200 on /primary and replicas return HTTP 200 on /replica.

# On the HAProxy node (192.168.1.20)
sudo yum install -y haproxy

sudo tee /etc/haproxy/haproxy.cfg <<'EOF'
global
    maxconn 100
    log     /dev/log local0

defaults
    log     global
    mode    tcp
    retries 2
    timeout connect 4s
    timeout client  30m
    timeout server  30m
    timeout check   5s
    option  tcplog

# Primary (read/write) — only the Patroni leader responds 200 to /primary
listen postgres-primary
    bind *:5000
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg1 192.168.1.11:5432 check port 8008
    server pg2 192.168.1.12:5432 check port 8008

# Replicas (read-only) — healthy replicas respond 200 to /replica
listen postgres-replicas
    bind *:5001
    balance roundrobin
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg1 192.168.1.11:5432 check port 8008
    server pg2 192.168.1.12:5432 check port 8008

# HAProxy stats page
listen stats
    bind *:7000
    mode  http
    stats enable
    stats uri /
    stats refresh 10s
EOF

sudo systemctl enable haproxy
sudo systemctl start haproxy

Applications that need read/write access connect to 192.168.1.20:5000. Read-only replicas (analytics, reporting) connect to 192.168.1.20:5001. HAProxy polls the Patroni REST API every 3 seconds, so failover is detected and traffic is rerouted within a few seconds of the primary going offline.

Step 8: Testing Automatic Failover

# Open a psql session through the HAProxy primary port
psql -h 192.168.1.20 -p 5000 -U postgres -c "SELECT pg_is_in_recovery();"
# Output: f  (false = this is the primary)

# Simulate a primary failure by stopping Patroni on pg1
# On pg1:
sudo systemctl stop patroni

# Back on the HAProxy node, watch the cluster recover
# (allow ~15-30 seconds for leader election)
patronictl -c /etc/patroni/patroni.yml list

# Re-test the connection through HAProxy — pg2 should now be primary
psql -h 192.168.1.20 -p 5000 -U postgres -c "SELECT pg_is_in_recovery();"
# Output: f  (pg2 is now the leader)

# Restart pg1 — it will rejoin as a replica automatically
# On pg1:
sudo systemctl start patroni

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

A Patroni-managed PostgreSQL cluster on RHEL 7 eliminates the manual intervention that single-instance PostgreSQL setups demand during failures. By combining etcd for distributed leader election, Patroni for automated failover and cluster state management, and HAProxy for transparent read/write routing, you achieve a high-availability database tier that can survive node failures with minimal downtime and zero application code changes. As your requirements grow, extend this architecture by adding a third PostgreSQL node for a synchronous replica, deploying a three-node etcd cluster for quorum resilience, and using pg_rewind (already enabled in this configuration) to quickly reintegrate a former primary that rejoins after a network partition. The patronictl command gives you full cluster visibility and safe manual switchover capabilities that make maintenance windows straightforward and predictable.