--- title: "How to Set Up PostgreSQL High Availability with Patroni" description: "Build a three-node PostgreSQL 16 cluster on Ubuntu 24.04 with Patroni and etcd, test automatic failover and route clients to the leader with HAProxy." category: "Database Advanced" language: en source: https://cubepath.com/docs/database-advanced/postgresql-high-availability-with-patroni updated: 2026-09-25 --- # How to Set Up PostgreSQL High Availability with Patroni Patroni is a cluster manager for PostgreSQL. It runs next to each PostgreSQL instance, stores the cluster state in a distributed configuration store such as etcd, and uses that store to elect a single leader, configure streaming replication on the replicas and promote a replica automatically when the leader fails. In this tutorial you will build a three-node PostgreSQL 16 cluster on Ubuntu 24.04 with Patroni and etcd, test failover and switchover, and put HAProxy in front so applications always connect to the current leader. ## Prerequisites To follow this tutorial, you will need: - Three servers running Ubuntu 24.04 LTS, for example three CubePath VPS, each with at least 2 GB of RAM. - A non-root user with `sudo` privileges on every server. - A private network between the three servers. This guide uses the following addresses; replace them with yours everywhere they appear: | Hostname | Private IP | |----------|------------| | `node1` | `10.0.0.11` | | `node2` | `10.0.0.12` | | `node3` | `10.0.0.13` | - Optionally, a fourth server for HAProxy. You can also run HAProxy on one of the database nodes while testing. > Important: etcd and the Patroni REST API in this guide use plain HTTP. Keep ports 2379, 2380 and 8008 reachable only over the private network, as configured in Step 1. Unless a step says otherwise, run each command on all three nodes, changing the IP address and node name for each one. ## Step 1 - Opening the cluster ports The nodes talk to each other on four TCP ports: 2379 (etcd clients), 2380 (etcd peers), 5432 (PostgreSQL) and 8008 (Patroni REST API). Allow them only from the private subnet: ```bash sudo ufw allow OpenSSH sudo ufw allow from 10.0.0.0/24 proto tcp to any port 2379,2380,5432,8008 sudo ufw enable ``` Check the rules: ```bash sudo ufw status ``` ```output Status: active To Action From -- ------ ---- OpenSSH ALLOW Anywhere 2379,2380,5432,8008/tcp ALLOW 10.0.0.0/24 OpenSSH (v6) ALLOW Anywhere (v6) ``` ## Step 2 - Installing etcd, PostgreSQL and Patroni Ubuntu 24.04 ships PostgreSQL 16, Patroni and etcd 3.4 in its main repositories, so no third-party repository is needed: ```bash sudo apt update sudo apt install etcd-server etcd-client postgresql-16 patroni ``` The PostgreSQL package creates and starts a default cluster called `16/main`. Patroni must own the data directory and start PostgreSQL itself, so stop the default service, disable it and drop that cluster: ```bash sudo systemctl disable --now postgresql sudo pg_dropcluster --stop 16 main ``` Confirm that no PostgreSQL cluster is left: ```bash pg_lsclusters ``` ```output Ver Cluster Port Status Owner Data directory Log file ``` The etcd package also starts a single-node etcd with default settings. Stop it and remove its data so the node can join the three-node cluster you configure next: ```bash sudo systemctl stop etcd sudo rm -rf /var/lib/etcd/default ``` ## Step 3 - Configuring the etcd cluster etcd is where Patroni keeps the leader lock and the cluster configuration. It needs a majority of its members (two out of three) to be available, which is why you run it on all three nodes. On Ubuntu, the etcd service reads its settings from `/etc/default/etcd`. Open the file on `node1`: ```bash sudo nano /etc/default/etcd ``` Add the following lines at the end of the file: ```ini ETCD_NAME="node1" ETCD_DATA_DIR="/var/lib/etcd/default" ETCD_LISTEN_PEER_URLS="http://10.0.0.11:2380" ETCD_LISTEN_CLIENT_URLS="http://10.0.0.11:2379,http://127.0.0.1:2379" ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.0.0.11:2380" ETCD_ADVERTISE_CLIENT_URLS="http://10.0.0.11:2379" ETCD_INITIAL_CLUSTER="node1=http://10.0.0.11:2380,node2=http://10.0.0.12:2380,node3=http://10.0.0.13:2380" ETCD_INITIAL_CLUSTER_STATE="new" ETCD_INITIAL_CLUSTER_TOKEN="pg-etcd-cluster" ``` On `node2` and `node3`, use the same block but change `ETCD_NAME` to `node2` or `node3` and replace `10.0.0.11` with that node's IP in the four `*_URLS` lines. `ETCD_INITIAL_CLUSTER` is identical on every node. Start etcd on all three nodes within a short time of each other, since the first member waits for the others before it becomes ready: ```bash sudo systemctl enable --now etcd ``` When all three are running, check the membership from any node: ```bash etcdctl --endpoints=http://10.0.0.11:2379,http://10.0.0.12:2379,http://10.0.0.13:2379 endpoint health ``` ```output http://10.0.0.11:2379 is healthy: successfully committed proposal: took = 2.1ms http://10.0.0.12:2379 is healthy: successfully committed proposal: took = 2.4ms http://10.0.0.13:2379 is healthy: successfully committed proposal: took = 2.6ms ``` If a node reports as unhealthy, look at its log with `sudo journalctl -u etcd -n 50`. A typical cause is a typo in one of the URLs. ## Step 4 - Writing the Patroni configuration Patroni is configured with a YAML file. The Ubuntu `patroni.service` unit runs as the `postgres` user and reads `/etc/patroni/config.yml`. The configuration contains three passwords: one for the `postgres` superuser, one for the `replicator` user that replicas use to stream WAL, and one for the `rewind_user` that `pg_rewind` uses to resynchronize a former leader. Generate strong values once and use the same three on every node, for example with: ```bash openssl rand -base64 24 ``` Create the configuration file on `node1`: ```bash sudo nano /etc/patroni/config.yml ``` ```yaml scope: pg-cluster namespace: /service/ name: node1 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 use_slots: true parameters: wal_level: replica hot_standby: "on" wal_log_hints: "on" max_connections: 100 max_wal_senders: 10 max_replication_slots: 10 initdb: - encoding: UTF8 - data-checksums postgresql: listen: 10.0.0.11,127.0.0.1:5432 connect_address: 10.0.0.11:5432 data_dir: /var/lib/postgresql/16/main bin_dir: /usr/lib/postgresql/16/bin pgpass: /var/lib/postgresql/.pgpass_patroni authentication: superuser: username: postgres password: your_superuser_password replication: username: replicator password: your_replication_password rewind: username: rewind_user password: your_rewind_password parameters: unix_socket_directories: /var/run/postgresql pg_hba: - local all all peer - host all all 127.0.0.1/32 scram-sha-256 - host replication replicator 10.0.0.0/24 scram-sha-256 - host all all 10.0.0.0/24 scram-sha-256 tags: nofailover: false noloadbalance: false clonefrom: false nosync: false ``` The important sections are: - `scope`: the cluster name. It must be the same on all nodes. - `name`, `restapi` and `postgresql.listen`/`connect_address`: specific to each node. - `etcd3`: Patroni talks to etcd through the v3 API. etcd 3.4 disables the old v2 API by default, so do not use the `etcd:` section here. - `bootstrap.dcs`: written to etcd once, when the cluster is first initialized. `ttl` is how long the leader lock lasts, so a failed leader is replaced after roughly 30 seconds. `maximum_lag_on_failover` (in bytes) prevents a replica that is too far behind from being promoted. - `postgresql.authentication`: Patroni creates these users when it bootstraps the cluster. - `pg_hba`: Patroni writes `pg_hba.conf` from this list. Adjust the subnet if yours differs. Replace the three placeholder passwords. On `node2` and `node3`, change `name` and the four lines that contain `10.0.0.11` (`restapi.listen`, `restapi.connect_address`, `postgresql.listen` and `postgresql.connect_address`) to that node's name and IP. The file contains passwords, so make it readable only by `postgres`: ```bash sudo chown postgres:postgres /etc/patroni/config.yml sudo chmod 600 /etc/patroni/config.yml ``` Validate the syntax before starting the service: ```bash sudo -u postgres patroni --validate-config /etc/patroni/config.yml && echo OK ``` ```output OK ``` ## Step 5 - Starting the Patroni cluster Start Patroni on `node1` first. Because the cluster does not exist yet in etcd, this node takes the leader lock, runs `initdb` and creates the users from the configuration: ```bash sudo systemctl enable --now patroni ``` Follow the log until the node reports itself as the leader: ```bash sudo journalctl -u patroni -f ``` ```output ... INFO: no action. I am (node1), the leader with the lock ``` Press `Ctrl+C` to stop following the log, then start Patroni on `node2` and `node3`: ```bash sudo systemctl enable --now patroni ``` These nodes find an existing leader in etcd, clone its data with `pg_basebackup` and start as streaming replicas. Check the cluster from any node with `patronictl`: ```bash sudo -u postgres patronictl -c /etc/patroni/config.yml list ``` ```output + Cluster: pg-cluster (7412345678901234567) ---+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +--------+-----------+---------+-----------+----+-----------+ | node1 | 10.0.0.11 | Leader | running | 1 | | | node2 | 10.0.0.12 | Replica | streaming | 1 | 0 | | node3 | 10.0.0.13 | Replica | streaming | 1 | 0 | +--------+-----------+---------+-----------+----+-----------+ ``` ## Step 6 - Verifying replication Create a table on the leader and read it back from a replica. On `node1`: ```bash sudo -u postgres psql -c "CREATE TABLE ha_test (id serial PRIMARY KEY, note text);" sudo -u postgres psql -c "INSERT INTO ha_test (note) VALUES ('written on node1');" ``` On `node2`: ```bash sudo -u postgres psql -c "SELECT * FROM ha_test;" ``` ```output id | note ----+------------------ 1 | written on node1 (1 row) ``` Replicas are read-only. Trying to write on `node2` fails: ```bash sudo -u postgres psql -c "INSERT INTO ha_test (note) VALUES ('x');" ``` ```output ERROR: cannot execute INSERT in a read-only transaction ``` ## Step 7 - Testing automatic failover and switchover To simulate a leader failure, stop Patroni on the current leader, `node1`. Stopping the service also shuts down PostgreSQL on that node: ```bash sudo systemctl stop patroni ``` Wait about 30 seconds (the `ttl` value) and list the cluster from `node2`: ```bash sudo -u postgres patronictl -c /etc/patroni/config.yml list ``` ```output + Cluster: pg-cluster (7412345678901234567) ---+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +--------+-----------+---------+-----------+----+-----------+ | node2 | 10.0.0.12 | Leader | running | 2 | | | node3 | 10.0.0.13 | Replica | streaming | 2 | 0 | +--------+-----------+---------+-----------+----+-----------+ ``` One replica was promoted and the timeline (`TL`) increased. Start Patroni again on `node1`: ```bash sudo systemctl start patroni ``` Patroni notices that a newer leader exists, uses `pg_rewind` if needed and brings `node1` back as a replica. Run `patronictl list` again to confirm it shows as `streaming`. For planned maintenance, such as a kernel update on the leader, use a switchover instead. It moves the leader role to a healthy replica in a controlled way: ```bash sudo -u postgres patronictl -c /etc/patroni/config.yml switchover pg-cluster --candidate node1 --force ``` ```output Successfully switched over to "node1" ``` Without `--force`, `patronictl` asks for confirmation and lets you schedule the switchover. ## Step 8 - Changing PostgreSQL settings Settings under `bootstrap.dcs` are stored in etcd after the first start, so editing `config.yml` no longer changes them. Use `patronictl edit-config`, which updates etcd and applies the change on every node: ```bash sudo -u postgres patronictl -c /etc/patroni/config.yml edit-config --pg work_mem=16MB --force ``` Some parameters, such as `max_connections` or `shared_buffers`, only take effect after a restart. `patronictl list` then shows a `Pending restart` column. Restart the members one at a time with: ```bash sudo -u postgres patronictl -c /etc/patroni/config.yml restart pg-cluster --pending --force ``` Do not use `systemctl restart postgresql` or `pg_ctl` on these nodes: Patroni manages PostgreSQL and would treat an unexpected stop as a failure. ## Step 9 - Routing clients with HAProxy Applications need a single address that always points to the leader. Patroni's REST API answers `GET /primary` with HTTP 200 only on the leader and `GET /replica` with 200 only on healthy replicas, so HAProxy can use it as a health check. Verify this from any node: ```bash curl -s -o /dev/null -w '%{http_code}\n' http://10.0.0.11:8008/primary curl -s -o /dev/null -w '%{http_code}\n' http://10.0.0.12:8008/primary ``` ```output 200 503 ``` On the server that will run HAProxy, install it: ```bash sudo apt install haproxy ``` Open the configuration file: ```bash sudo nano /etc/haproxy/haproxy.cfg ``` Add these two sections at the end, after the existing `global` and `defaults` blocks: ```haproxy listen postgres_primary bind *:5000 mode tcp option httpchk GET /primary http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server node1 10.0.0.11:5432 check port 8008 server node2 10.0.0.12:5432 check port 8008 server node3 10.0.0.13:5432 check port 8008 listen postgres_replicas bind *:5001 mode tcp 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 node1 10.0.0.11:5432 check port 8008 server node2 10.0.0.12:5432 check port 8008 server node3 10.0.0.13:5432 check port 8008 ``` Port 5000 always leads to the leader for reads and writes, and port 5001 balances read-only queries across the replicas. `on-marked-down shutdown-sessions` closes existing connections to a node as soon as it stops being the leader, so clients reconnect to the new one. Check the syntax and reload HAProxy: ```bash sudo haproxy -c -f /etc/haproxy/haproxy.cfg sudo systemctl reload haproxy ``` ```output Configuration file is valid ``` If HAProxy runs on a separate server, allow it through UFW on the database nodes (for example `sudo ufw allow from haproxy_private_ip proto tcp to any port 5432,8008`) and allow ports 5000 and 5001 on the HAProxy server only from your application servers. Connect through HAProxy and ask PostgreSQL whether it is a replica: ```bash psql -h haproxy_ip -p 5000 -U postgres -c "SELECT inet_server_addr(), pg_is_in_recovery();" ``` ```output inet_server_addr | pg_is_in_recovery ------------------+------------------- 10.0.0.11 | f (1 row) ``` `f` means you reached the leader. Repeat the query after a switchover and the address changes to the new leader. ## Troubleshooting - **Replicas stay in `starting` or `stopped` state**: check `sudo journalctl -u patroni -n 100` on the replica. `no pg_hba.conf entry for replication connection` means the `pg_hba` subnet does not match the node's IP; `password authentication failed` means the replication password differs between nodes. - **`patronictl` shows no leader and logs mention etcd timeouts**: etcd has lost quorum. Run `etcdctl endpoint health` against the three endpoints; at least two must be healthy. - **`Failed to get list of machines from etcd3`**: the node cannot reach etcd on port 2379. Check `ETCD_LISTEN_CLIENT_URLS` and the UFW rules. - **A former leader does not rejoin after failover**: if `pg_rewind` cannot run, reinitialize it from the current leader with `sudo -u postgres patronictl -c /etc/patroni/config.yml reinit pg-cluster node1`. This deletes the node's data directory and clones it again. ## Conclusion You now have a three-node PostgreSQL 16 cluster managed by Patroni, with etcd holding the leader lock, automatic failover in about 30 seconds, controlled switchovers for maintenance and HAProxy sending clients to the current leader. As next steps, remove HAProxy as a single point of failure by running it on two servers with a floating IP managed by keepalived, enable TLS for etcd, the Patroni REST API and PostgreSQL, and set up regular backups with a tool such as pgBackRest.