PostgreSQL High Availability with Patroni on VPS

Why PostgreSQL high availability on VPS — and not RDS

Amazon RDS Multi-AZ handles failover well, but its pricing model is designed so that the bill grows non-linearly with usage: storage, provisioned IOPS, simultaneous connections, and Multi-AZ replication itself are billed separately. For a SaaS with a growing database, the extra cost quickly exceeds what a dedicated VPS would cost.

On three root-access VPS instances, Patroni provides the same service: a single leader accepts writes, two synchronous or asynchronous replicas follow it, and etcd holds the quorum. If the leader goes down, Patroni detects the absence via the etcd lease (configurable TTL, typically 30 seconds) and promotes the most up-to-date replica. No human intervention, no manual DNS update needed if a load balancer like HAProxy points to Patroni's health endpoint.

What this cluster gives you

Automatic failover under 30 seconds — Patroni detects leader loss via etcd lease expiry and promotes without intervention.
Configurable synchronous replication — synchronous_mode: true guarantees no committed transaction is lost if the leader crashes.
Built-in REST API — GET /leader, GET /health, POST /switchover: the cluster can be queried and managed without a PostgreSQL client.
Fixed, predictable cost — three fixed-resource VPS instances, no billing surprises tied to traffic.
Free extensions — pg_hba.conf, postgresql.conf, pgvector, PostGIS: no restrictions imposed by a managed service.
Centralised backups — pgBackRest 2.59 integrates natively with Patroni for incremental backups from the replica.

Cluster architecture: three nodes, one quorum

The cluster rests on three layers:

etcd holds the distributed configuration store (DCS). It owns the leader lease. If the Patroni leader does not renew this lease within the TTL, etcd releases it and standbys run for election. With three etcd nodes (one per VPS), the quorum tolerates the loss of one node without losing availability.

Patroni runs on each VPS alongside PostgreSQL. It handles cluster initialisation, postgresql.conf and pg_hba.conf configuration, replication lag tracking, and failover. It exposes a REST API on port 8008.

PostgreSQL is managed entirely by Patroni — never edit postgresql.conf directly; all changes go through patronictl edit-config to stay synchronised across all three nodes.

The replication flow: the leader receives writes as WAL, replicas connect via pg_basebackup on first start then follow the WAL stream continuously. In synchronous mode, the leader waits for at least one replica's acknowledgement before returning COMMIT to the client.

Prerequisites: resources and network

This guide was written with Patroni 4.1.5, etcd 3.6.6, and PostgreSQL 17 on Debian 12.

Minimum recommended resources per node

2 vCPU / 4 GB RAM — sufficient to start; plan for 8 GB RAM once the database exceeds a few GB of shared_buffers.
NVMe SSD — WAL replication is sensitive to write latency; a spinning disk degrades the replication lag.
Private network between the three nodes — etcd-to-etcd and Patroni-to-PostgreSQL communication must not transit over the internet.
Dedicated IPv4 — for external client access and the pg_hba.conf of replicas.
NTP synchronised (chrony) — drift < 1 s — etcd refuses quorum if a node's clock drifts by more than one second. This is the most common pitfall on VPS.

Installation: from scratch to a running cluster

Synchronise the clock on all three nodes

On each node, install and enable chrony before anything else:

apt install -y chrony
systemctl enable --now chronyd
chronyc tracking

Verify that System time offset is under 0.1 seconds. A drift above 1 second causes etcd timeouts and election loops.

Install etcd 3.6 on all three nodes

Set the environment variables specific to each node (replace NODE1_IP, NODE2_IP, NODE3_IP with private IPs):

ETCD_VER=v3.6.6
curl -L https://github.com/etcd-io/etcd/releases/download/${ETCD_VER}/etcd-${ETCD_VER}-linux-amd64.tar.gz \
  | tar xz -C /usr/local/bin --strip-components=1 etcd-${ETCD_VER}-linux-amd64/etcd \
                                                   etcd-${ETCD_VER}-linux-amd64/etcdctl

Create /etc/etcd/etcd.conf.yml on each node (example for pg-node1):

name: pg-node1
data-dir: /var/lib/etcd
listen-peer-urls: http://NODE1_IP:2380
listen-client-urls: http://NODE1_IP:2379,http://127.0.0.1:2379
initial-advertise-peer-urls: http://NODE1_IP:2380
advertise-client-urls: http://NODE1_IP:2379
initial-cluster: pg-node1=http://NODE1_IP:2380,pg-node2=http://NODE2_IP:2380,pg-node3=http://NODE3_IP:2380
initial-cluster-token: pg-cluster-token
initial-cluster-state: new

Create the systemd unit, enable and start etcd on all three nodes before moving to the next step.

Verify etcd quorum

On any node:

etcdctl --endpoints=http://NODE1_IP:2379,http://NODE2_IP:2379,http://NODE3_IP:2379 \
  endpoint status --write-out=table

Wait until three rows appear — one showing IS LEADER true, the other two false — and ERRORS is empty. If a node is missing, check the firewall on ports 2379 and 2380.

Install PostgreSQL and Patroni

On all three nodes:

# PostgreSQL from the official PGDG repository
apt install -y curl ca-certificates
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
echo "deb https://apt.postgresql.org/pub/repos/apt bookworm-pgdg main" > /etc/apt/sources.list.d/pgdg.list
apt update && apt install -y postgresql-17

# Patroni and the etcd driver
pip3 install patroni[etcd3] psycopg2-binary

Stop PostgreSQL — Patroni takes over cluster initialisation:

systemctl stop postgresql
systemctl disable postgresql

Configure Patroni on each node

Create /etc/patroni/patroni.yml (example for pg-node1):

scope: pg-cluster
namespace: /service/
name: pg-node1

restapi:
  listen: NODE1_IP:8008
  connect_address: NODE1_IP:8008

etcd3:
  hosts: NODE1_IP:2379,NODE2_IP:2379,NODE3_IP:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    synchronous_mode: true
    synchronous_node_count: 1
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        wal_keep_size: 128MB
        max_wal_senders: 10
        max_replication_slots: 10

  initdb:
    - encoding: UTF8
    - data-checksums

  pg_hba:
    - host replication replicator 0.0.0.0/0 scram-sha-256
    - host all all 0.0.0.0/0 scram-sha-256

postgresql:
  listen: NODE1_IP:5432
  connect_address: NODE1_IP:5432
  data_dir: /var/lib/postgresql/17/main
  bin_dir: /usr/lib/postgresql/17/bin
  authentication:
    replication:
      username: replicator
      password: 'YOUR_REPLICATION_PASSWORD'
    superuser:
      username: postgres
      password: 'YOUR_POSTGRES_PASSWORD'

Adapt NODE1_IP for each node.

Start Patroni and initialise the cluster

Create the systemd unit /etc/systemd/system/patroni.service:

[Unit]
Description=Patroni PostgreSQL HA
After=network.target etcd.service
Requires=etcd.service

[Service]
Type=simple
User=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target

Start on pg-node1 first (this node runs initdb and becomes the leader), then on the other two with a few seconds gap:

systemctl daemon-reload
systemctl enable --now patroni

Follow the initialisation:

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

Verify the initial cluster state

Expected output after full initialisation:

+ Cluster: pg-cluster (7234567890123456789) +---------+----+-----------+
| Member    | Host             | Role         | State   | TL | Lag in MB |
+-----------+------------------+--------------+---------+----+-----------+
| pg-node1  | NODE1_IP:5432    | Leader       | running |  1 |           |
| pg-node2  | NODE2_IP:5432    | Sync Standby | running |  1 | 0         |
| pg-node3  | NODE3_IP:5432    | Replica      | running |  1 | 0         |
+-----------+------------------+--------------+---------+----+-----------+

pg-node2 shows as Sync Standby: every committed transaction on the leader is guaranteed to be on this node before COMMIT is returned to the client.

Configure pg_hba.conf via Patroni

Never edit pg_hba.conf directly. Use patronictl edit-config to add access rules in the pg_hba section — Patroni propagates the configuration across all nodes and reloads PostgreSQL automatically:

patronictl -c /etc/patroni/patroni.yml edit-config

Add your rules in the pg_hba YAML block. On VPS, host all all 0.0.0.0/0 scram-sha-256 is a starting point to refine based on your private network.

Failover and switchover: demonstration

Simulated failover — abrupt leader shutdown.

Before stopping, note the cluster state:

patronictl -c /etc/patroni/patroni.yml list
# → pg-node1 is Leader, pg-node2 is Sync Standby

Stop Patroni on the leader:

systemctl stop patroni   # on pg-node1

Watch the promotion on one of the standbys:

patronictl -c /etc/patroni/patroni.yml list
# → (after 10 to 30 seconds)
# pg-node2 : Leader  | running | TL 2
# pg-node3 : Replica | running | TL 2 | 0 MB
# pg-node1 : stopped

Patroni waits for the etcd lease to expire (TTL = 30 s), then pg-node2 (the sync standby) acquires the lease and promotes itself. The actual delay is typically between 10 and 30 seconds depending on loop_wait.

Planned switchover — zero-downtime switch.

For scheduled maintenance, prefer switchover, which waits for the target replica to be caught up before switching:

patronictl -c /etc/patroni/patroni.yml switchover pg-cluster \
  --master pg-node1 --candidate pg-node2

Patroni waits for zero lag, signals pg-node2 to promote, then pg-node1 reconnects as a replica. Effective duration: under 5 seconds under normal conditions.

Status REST API.

Without a PostgreSQL client, query the state from a load balancer or monitoring script:

curl -s http://NODE1_IP:8008/leader    # 200 = this is the leader
curl -s http://NODE2_IP:8008/replica   # 200 = this is a healthy replica
curl -s http://NODE1_IP:8008/health    # JSON: state, role, lag

HAProxy can point its health checks to /leader and /replica to route writes and reads to the right nodes. See the HAProxy on VPS guide for the full wiring.

Daily operations

Backups with pgBackRest 2.59.

Install pgBackRest on all three nodes and designate a shared repository (S3 object storage, NFS, or a dedicated local directory). The recommended setup pulls backups from a replica to avoid loading the leader:

pgbackrest --stanza=pg-cluster --type=full backup

Enable compression and daily incremental backups in pgbackrest.conf (repo1-retention-full=7). See the backups on VPS guide for complementary strategies.

Cluster monitoring.

patronictl list shows the lag in MB per replica. Alert when the lag exceeds a threshold (e.g. 50 MB): this signals either a slow replica or a network issue. The GET /patroni endpoint returns a full JSON payload including xlog_location and replication_state.

Vertical scaling.

To increase a node's resources: stop Patroni on that node (it becomes a disconnected replica), resize the VPS, restart. Patroni reconnects and catches up automatically via pg_rewind or pg_basebackup depending on how far behind it is.

synchronous_commit trade-off.

With synchronous_mode: true, each COMMIT waits for the sync standby's acknowledgement. On a local private network, each COMMIT waits for the sync standby's acknowledgement — the delay depends on the network latency between nodes (monitor with pg_stat_replication.replay_lag). On a wider network (nodes in different datacenters), this latency can impact write-intensive applications. In that case, switch to synchronous_mode: false with asynchronous replication: you lose the zero-data-loss guarantee on leader crash, but writes remain fast. This trade-off must be documented explicitly in your configuration.

Hardening: mutual TLS auth between nodes

By default, etcd communication and PostgreSQL replication connections travel in plaintext over the private network. On a shared network or multi-tenant environment, enable mutual TLS auth.

For etcd, generate a CA and per-node certificates, then add to etcd.conf.yml:

client-transport-security:
  cert-file: /etc/etcd/tls/server.crt
  key-file: /etc/etcd/tls/server.key
  trusted-ca-file: /etc/etcd/tls/ca.crt
  client-cert-auth: true
peer-transport-security:
  cert-file: /etc/etcd/tls/peer.crt
  key-file: /etc/etcd/tls/peer.key
  trusted-ca-file: /etc/etcd/tls/ca.crt
  peer-client-cert-auth: true

For PostgreSQL replication, use sslmode=verify-full in Patroni's primary_conninfo connection parameters. Each replica then verifies the leader's certificate.

Troubleshooting: the five most common errors

1. etcd quorum lost — cluster refuses to elect a leader.

Symptom: patronictl list shows all nodes as running but no Leader. Cause: one etcd node is unreachable and the quorum (2 of 3) is no longer met. Check with etcdctl endpoint status — the failing node appears with no response or a connection error. Fix the node or temporarily remove it from the cluster (etcdctl member remove).

2. NTP drift — election loop.

Symptom: the leader changes every 30 seconds, Patroni logs show failed to update leader key. Cause: one node's clock is drifting by more than one second. Check with chronyc tracking on each node and fix before restarting Patroni.

3. Potential split-brain — pg_rewind refuses to apply.

Symptom: a former leader restarts and Patroni refuses to rejoin it as a replica, with error could not connect to the target server: pg_rewind target server must be in standby mode. The server kept writing after losing the lease. Fix: pg_rewind --target-pgdata=/var/lib/postgresql/17/main --source-server='host=NEW_LEADER_IP ...', then restart Patroni.

4. Peer connection rejected — missing pg_hba.conf entry.

Symptom: replication initialises but fails with FATAL: no pg_hba.conf entry for replication connection. The rule host replication replicator 0.0.0.0/0 scram-sha-256 is not present in the pg_hba section of patroni.yml. Add it via patronictl edit-config — not directly in pg_hba.conf.

5. Persistent lag after failover — insufficient max_wal_senders.

Symptom: the replica shows a lag that does not decrease after promotion. Common cause: max_wal_senders is too low (default of 10 on some versions) and the replication slot is saturated. Increase to 20 via patronictl edit-config (parameter max_wal_senders) and reload.

A cluster that is operated, not improvised

Patroni 4.1 with etcd 3.6 covers most of what a managed service offers on availability: automatic election, synchronous replication, management API. The difference is control: root access, free extensions, fixed cost, and the ability to debug the failing node instead of waiting for third-party support.

The operational prerequisite is not Patroni's complexity — the procedure above shows it is manageable. It is discipline on three points: NTP synchronised, backups verified regularly, and a failover runbook tested before the outage happens.

To get started, a 3-node Patroni cluster requires three VPS instances with root access, a dedicated IPv4, and a private network. See the basic installation guide PostgreSQL on VPS and the comparison self-hosted vs Amazon RDS to choose the approach that fits your workload.