Database Engineering: Architecting a PostgreSQL High Availability Cluster with Patroni

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 severe 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 blueprint 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).

Architectural Overview & 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 baseline setup requires three distinct servers communicating over a private LAN.

• 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

Step 1: System Preparation

On all three nodes, update the system packages and configure the hosts file so the nodes can resolve each other via hostname.

apt update && apt upgrade -y

Edit /etc/hosts and append the cluster IPs:

Plaintext 10.0.0.11 db1 10.0.0.12 db2 10.0.0.13 db3 Step 2: Install PostgreSQL & Dependencies Install PostgreSQL, Python3 (required for Patroni), and etcd on all nodes.

Bash apt install -y postgresql postgresql-contrib python3-pip python3-venv etcd curl Because Patroni will manage the PostgreSQL lifecycle completely, you must stop and disable the default PostgreSQL service created by Ubuntu:

Bash systemctl stop postgresql systemctl disable postgresql Step 3: Configure etcd (The Consensus Store) etcd stores the state of your cluster and manages leader election. On Node 1 (db1), edit the configuration at /etc/default/etcd:

Ini, TOML 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](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 across the cluster:

Bash systemctl restart etcd systemctl enable etcd Step 4: Install & Configure Patroni On all nodes, install Patroni via pip. (Note: Using --break-system-packages is standard on modern Ubuntu environments unless using a strictly managed venv).

Bash pip3 install patroni[etcd3] psycopg2-binary --break-system-packages Create the Patroni configuration file at /etc/patroni.yml. Below is the configuration for Node 1 (adjust names and IPs for Node 2 and 3 accordingly):

YAML 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 configuration file:

Bash chown postgres:postgres /etc/patroni.yml Step 5: Create the Patroni Service On all nodes, create a systemd service for Patroni (/etc/systemd/system/patroni.service):

Ini, TOML [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):

Bash systemctl daemon-reload systemctl enable patroni systemctl start patroni Monitor cluster health by running patronictl -c /etc/patroni.yml list.

Step 6: Set Up HAProxy for Routing Your applications need a single IP address to connect to, dynamically routing to the active primary node. Install HAProxy on your application servers or a dedicated routing node:

Bash apt install -y haproxy Configure /etc/haproxy/haproxy.cfg to check the Patroni REST API (port 8008):

Code snippet 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, completely abstracted from underlying hardware failovers.