High-Availability Data: Setting Up a PostgreSQL Cluster with Patroni on Bare Metal

For enterprise applications, SaaS platforms, and large e-commerce sites, a database outage means catastrophic revenue loss. Relying on a single database server is a single point of failure.

To ensure true 100% uptime, you need a PostgreSQL High Availability (HA) Cluster. In this architecture, multiple database servers run in sync. If the primary master server crashes, the system automatically detects the failure and promotes a standby server to take over in seconds, with zero manual intervention.

This tutorial covers setting up a robust, automated PostgreSQL HA cluster on Ubuntu 24.04 bare-metal servers using Patroni (for failover management), etcd (for distributed consensus), and HAProxy (for routing client traffic).

Step 1: Architecture & Prerequisites

To prevent a "split-brain" scenario where two servers both think they are the primary, a highly available cluster requires an odd number of nodes to maintain a quorum. Therefore, this setup requires three bare-metal servers.

• Node 1 (db1): 10.0.0.11
• Node 2 (db2): 10.0.0.12
• Node 3 (db3): 10.0.0.13
• OS: Ubuntu 24.04 LTS on all nodes
• Networking: All nodes must be able to communicate over a private LAN.

Step 2: System Preparation

Run these commands on all three nodes. First, update the system and configure the hosts file so the nodes can resolve each other.

apt update && apt upgrade -y
                            

Edit the /etc/hosts file and add the IP addresses of your cluster:

10.0.0.11 db1
10.0.0.12 db2
10.0.0.13 db3
                            

Step 3: Install PostgreSQL & Dependencies

On all three nodes, install PostgreSQL, Python3 (for Patroni), and etcd.

apt install -y postgresql postgresql-contrib python3-pip python3-venv etcd curl
                            

Because Patroni manages PostgreSQL completely, you must stop and disable the default PostgreSQL service created by Ubuntu:

systemctl stop postgresql
systemctl disable postgresql
                            

Step 4: Configure etcd (The Consensus Store)

etcd stores the state of your cluster and manages leader election. On Node 1 (db1), edit the etcd configuration: nano /etc/default/etcd

ETCD_NAME="db1"
ETCD_DATA_DIR="/var/lib/etcd/default.etcd"
ETCD_LISTEN_PEER_URLS="http://10.0.0.11:2380"
ETCD_LISTEN_CLIENT_URLS="http://localhost:2379,http://10.0.0.11:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.0.0.11:2380"
ETCD_INITIAL_CLUSTER="db1=http://10.0.0.11:2380,db2=http://10.0.0.12:2380,db3=http://10.0.0.13:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="postgres-ha-cluster"
ETCD_ADVERTISE_CLIENT_URLS="http://10.0.0.11:2379"
                            

Note: Repeat this configuration on Node 2 and Node 3, changing ETCD_NAME and the local IP addresses to match the respective node. Leave the ETCD_INITIAL_CLUSTER line identical on all nodes.

Restart etcd on all nodes:

systemctl restart etcd
systemctl enable etcd
                            

Step 5: Install & Configure Patroni

On all nodes, install Patroni via pip. (Using --break-system-packages is required on modern Ubuntu environments unless using a strictly managed venv).

pip3 install patroni[etcd3] psycopg2-binary --break-system-packages
                            

Create the Patroni configuration file: nano /etc/patroni.yml (Below is for Node 1. Change name and IPs for Node 2 and 3).

scope: postgres-cluster
namespace: /db/
name: db1

restapi:
  listen: 10.0.0.11:8008
  connect_address: 10.0.0.11:8008

etcd3:
  hosts: 10.0.0.11:2379,10.0.0.12:2379,10.0.0.13:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
  initdb:
    - auth-host: md5
    - auth-local: trust
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 10.0.0.11:5432
  connect_address: 10.0.0.11:5432
  data_dir: /var/lib/postgresql/16/main
  bin_dir: /usr/lib/postgresql/16/bin
  authentication:
    replication:
      username: replicator
      password: strong_replica_password
    superuser:
      username: postgres
      password: strong_admin_password
                            

Ensure the postgres user owns this file:

chown postgres:postgres /etc/patroni.yml
                            

Step 6: Create the Patroni Service & Start Cluster

On all nodes, create a systemd service for Patroni: nano /etc/systemd/system/patroni.service

[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target etcd.service

[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni.yml
Restart=no

[Install]
WantedBy=multi-user.target
                            

Reload systemd and start Patroni on all nodes (Start Node 1 first, then Node 2 and 3):

systemctl daemon-reload
systemctl enable patroni
systemctl start patroni
                            

You can monitor the health of your cluster by running patronictl -c /etc/patroni.yml list. You will see one node elected as the Leader, and the others as Replicas.

Step 7: Set Up HAProxy for Routing

Your applications need a single IP address to connect to. If the primary node fails, the application shouldn't need to be updated. Install HAProxy on a separate lightweight node or directly on your application servers.

apt install -y haproxy
                            

Configure HAProxy to check the Patroni REST API (port 8008) to detect the primary database. Add this to /etc/haproxy/haproxy.cfg:

listen postgres_cluster
    bind *:5432
    option httpchk GET /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server db1 10.0.0.11:5432 maxconn 100 check port 8008
    server db2 10.0.0.12:5432 maxconn 100 check port 8008
    server db3 10.0.0.13:5432 maxconn 100 check port 8008
                            

Restart HAProxy. Your application can now connect to the HAProxy IP on port 5432, and it will automatically route queries to the active primary node.

Why Bare Metal Matters for Database Clusters

When setting up a high-availability database, network latency and storage Input/Output Operations Per Second (IOPS) are critical. Cloud environments throttle database writes due to virtualized storage arrays.

By deploying your Patroni cluster on eServers Bare Metal Dedicated Servers, your database gets 100% direct access to NVMe SSDs and unmetered private networking. If a node fails, Patroni instantly promotes a replica without data loss, ensuring your enterprise applications remain online 24/7.