# 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.

```bash
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](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](http://10.0.0.11:2380)" ETCD\_INITIAL\_CLUSTER="db1=[http://10.0.0.11:2380](http://10.0.0.11:2380),db2=[http://10.0.0.12:2380](http://10.0.0.12:2380),db3=[http://10.0.0.13:2380](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](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.