Configure Patroni with etcd for automatic failover, deploy HAProxy for connection routing, set up CNPG (CloudNativePG) on Kubernetes for cloud-native HA, and run failover tests that prove your setup works under failure. This guide covers both traditional VPS and Kubernetes approaches so you can choose what fits your team.
Database downtime is the most expensive kind of outage. A 2024 Gartner survey found that the average cost of IT downtime is $5,600 per minute, and database failures are the single most common cause. Yet many small and medium businesses run PostgreSQL on a single server with no automatic failover, because HA has historically been complex and expensive. Modern tools like Patroni and CNPG have changed that. This guide shows you how to set up production-grade HA without a dedicated DBA team.
---
1. Understanding PostgreSQL HA Architecture
PostgreSQL high availability requires three components: replication (data copied to standbys), failure detection (knowing when the primary is down), and automatic failover (promoting a standby to primary). Without all three, you have replication but not HA.
HA architecture comparison
| Approach | Failover Time | Complexity | Best For | Cost |
| Manual failover | 10-60 minutes | Low | Dev/staging | Free |
| Patroni + HAProxy | 5-30 seconds | Medium | VPS/bare metal | Free (OSS) |
| CNPG on Kubernetes | 5-15 seconds | Medium-High | K8s environments | Free (OSS) |
| Managed service (RDS/Cloud SQL) | 30-120 seconds | Low | Teams without DBA | $200-2000/mo |
For SMBs, Patroni is the best choice for VPS deployments, and CNPG is the best choice for Kubernetes. Both are open source, battle-tested, and used in production by thousands of organizations.
---
2. Patroni Setup for VPS Deployments
Patroni is a template for PostgreSQL HA built by Zalando. It manages replication, monitors the primary, and handles automatic failover using a distributed consensus store (etcd, Consul, or ZooKeeper). Patroni is used in production by Zalando, GitLab, and many other organizations.
Infrastructure requirements
- 3 nodes minimum (1 primary + 2 standbys, or 2 Postgres + 1 etcd-only)
- Each node: 2+ CPU, 4GB+ RAM, SSD storage
- Low-latency network between nodes (same datacenter, <1ms RTT)
- Ubuntu 22.04 or RHEL 8+ recommended
Install and configure etcd (on all 3 nodes)
<h1>Install etcd</h1>
apt-get install -y etcd
<h1>/etc/etcd/etcd.conf (adjust IPs for each node)</h1>
ETCD_NAME=node1
ETCD_DATA_DIR=/var/lib/etcd/default
ETCD_LISTEN_PEER_URLS=http://10.0.0.1:2380
ETCD_LISTEN_CLIENT_URLS=http://10.0.0.1:2379,http://127.0.0.1:2379
ETCD_INITIAL_ADVERTISE_PEER_URLS=http://10.0.0.1:2380
ETCD_ADVERTISE_CLIENT_URLS=http://10.0.0.1:2379
ETCD_INITIAL_CLUSTER=node1=http://10.0.0.1:2380,node2=http://10.0.0.2:2380,node3=http://10.0.0.3:2380
ETCD_INITIAL_CLUSTER_STATE=new
ETCD_INITIAL_CLUSTER_TOKEN=pg-cluster
<h1>Start etcd</h1>
systemctl enable etcd
systemctl start etcd
<h1>Verify cluster health</h1>
etcdctl cluster-health
Install Patroni and PostgreSQL (on all 3 nodes)
<h1>Install PostgreSQL 16</h1>
apt-get install -y postgresql-16
systemctl stop postgresql
systemctl disable postgresql
<h1>Install Patroni</h1>
pip3 install patroni[etcd] psycopg2-binary
Patroni configuration (/etc/patroni/config.yml)
scope: pg-cluster
name: node1
restapi:
listen: 0.0.0.0:8008
connect_address: 10.0.0.1:8008
etcd:
hosts: 10.0.0.1:2379,10.0.0.2:2379,10.0.0.3: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:
max_connections: 200
shared_buffers: 1GB
effective_cache_size: 3GB
work_mem: 16MB
maintenance_work_mem: 256MB
wal_level: replica
max_wal_senders: 5
max_replication_slots: 5
hot_standby: "on"
wal_log_hints: "on"
initdb:
- encoding: UTF8
- data-checksums
pg_hba:
- host replication replicator 10.0.0.0/24 scram-sha-256
- host all all 10.0.0.0/24 scram-sha-256
- host all all 0.0.0.0/0 scram-sha-256
postgresql:
listen: 0.0.0.0:5432
connect_address: 10.0.0.1:5432
data_dir: /var/lib/postgresql/16/main
bin_dir: /usr/lib/postgresql/16/bin
authentication:
superuser:
username: postgres
password: "secure-superuser-password"
replication:
username: replicator
password: "secure-replication-password"
tags:
nofailover: false
noloadbalance: false
clonefrom: false
Start Patroni
<h1>Create systemd service</h1>
cat > /etc/systemd/system/patroni.service << EOF
[Unit]
Description=Patroni PostgreSQL HA
After=network.target etcd.service
[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni/config.yml
Restart=on-failure
RestartSec=10
[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
systemctl enable patroni
systemctl start patroni
<h1>Check cluster status</h1>
patronictl -c /etc/patroni/config.yml list
---
3. HAProxy for Connection Routing
HAProxy sits in front of the Patroni cluster and routes connections to the current primary. It uses Patroni's REST API to detect which node is the leader, ensuring zero-downtime failover from the application's perspective.
HAProxy configuration
<h1>/etc/haproxy/haproxy.cfg</h1>
global
maxconn 1000
log stdout format raw local0
defaults
mode tcp
timeout connect 5s
timeout client 30m
timeout server 30m
retries 3
listen stats
bind *:7000
mode http
stats enable
stats uri /stats
stats refresh 10s
listen postgresql-primary
bind *:5000
option httpchk GET /primary
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions maxconn 300
server node1 10.0.0.1:5432 check port 8008
server node2 10.0.0.2:5432 check port 8008
server node3 10.0.0.3:5432 check port 8008
listen postgresql-replicas
bind *:5001
balance roundrobin
option httpchk GET /replica
http-check expect status 200
default-server inter 3s fall 3 rise 2 maxconn 300
server node1 10.0.0.1:5432 check port 8008
server node2 10.0.0.2:5432 check port 8008
server node3 10.0.0.3:5432 check port 8008
Applications connect to HAProxy port 5000 for read-write queries and port 5001 for read-only queries. When a failover happens, HAProxy detects the new primary within 3-9 seconds (inter x fall) and routes traffic accordingly.
---
4. CNPG on Kubernetes
CloudNativePG (CNPG) is a Kubernetes operator that manages the entire PostgreSQL lifecycle: provisioning, replication, failover, backup, and scaling. It is the approach used by Arxentra and recommended by the Cloud Native Computing Foundation.
Install CNPG operator
helm repo add cnpg https://cloudnative-pg.github.io/charts
helm repo update
helm install cnpg cnpg/cloudnative-pg \
--namespace cnpg-system \
--create-namespace
CNPG Cluster manifest
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: app-db
namespace: production
spec:
instances: 3
imageName: ghcr.io/cloudnative-pg/postgresql:16.2
postgresql:
parameters:
max_connections: "200"
shared_buffers: 1GB
effective_cache_size: 3GB
work_mem: 16MB
maintenance_work_mem: 256MB
wal_level: replica
storage:
size: 50Gi
storageClass: gp3
resources:
requests:
cpu: 500m
memory: 2Gi
limits:
memory: 4Gi
backup:
barmanObjectStore:
destinationPath: s3://my-backups/cnpg/
s3Credentials:
accessKeyId:
name: s3-creds
key: ACCESS_KEY_ID
secretAccessKey:
name: s3-creds
key: SECRET_ACCESS_KEY
wal:
compression: gzip
data:
compression: gzip
retentionPolicy: "30d"
monitoring:
enablePodMonitor: true
nodeMaintenanceWindow:
inProgress: false
reusePVC: true
Connect to the CNPG cluster
<h1>Get connection credentials</h1>
kubectl get secret app-db-app -n production -o jsonpath='{.data.uri}' | base64 -d
<h1>Read-write service (primary)</h1>
<h1>app-db-rw.production.svc:5432</h1>
<h1>Read-only service (replicas)</h1>
<h1>app-db-ro.production.svc:5432</h1>
<h1>Read service (any instance)</h1>
<h1>app-db-r.production.svc:5432</h1>
<h1>Check cluster status</h1>
kubectl cnpg status app-db -n production
---
5. Failover Testing
HA is meaningless until you test it. Run failover tests monthly to verify your setup handles real failures. According to Netflix's chaos engineering practices, teams that test failover regularly experience 50% fewer unplanned outages because they catch configuration drift and edge cases before they become incidents.
Patroni failover test
#!/bin/bash
<h1>patroni-failover-test.sh</h1>
set -euo pipefail
echo "=== Patroni Failover Test ==="
echo ""
<h1>Step 1: Identify current primary</h1>
echo "Current cluster state:"
patronictl -c /etc/patroni/config.yml list
PRIMARY=$(patronictl -c /etc/patroni/config.yml list -f json
jq -r '.[] select(.Role == "Leader")
.Member')
echo "Primary: $PRIMARY"
echo ""
<h1>Step 2: Start a continuous write test in background</h1>
echo "Starting write test..."
psql -h localhost -p 5000 -U postgres -c "CREATE TABLE IF NOT EXISTS failover_test (id serial, ts timestamptz DEFAULT now());"
while true; do
psql -h localhost -p 5000 -U postgres -c "INSERT INTO failover_test DEFAULT VALUES;" 2>/dev/null
sleep 0.5
done &
WRITE_PID=$!
sleep 5
<h1>Step 3: Trigger failover</h1>
echo "Triggering failover..."
patronictl -c /etc/patroni/config.yml failover --force
echo ""
<h1>Step 4: Wait and verify</h1>
sleep 15
echo "Post-failover cluster state:"
patronictl -c /etc/patroni/config.yml list
<h1>Step 5: Check for data loss</h1>
kill $WRITE_PID 2>/dev/null || true
ROW_COUNT=$(psql -h localhost -p 5000 -U postgres -t -c "SELECT count(*) FROM failover_test;")
echo "Rows written during test: $ROW_COUNT"
echo ""
<h1>Cleanup</h1>
psql -h localhost -p 5000 -U postgres -c "DROP TABLE failover_test;"
echo "Test complete."
CNPG failover test
<h1>Trigger failover by deleting the primary pod</h1>
PRIMARY_POD=$(kubectl get pods -n production -l cnpg.io/cluster=app-db,role=primary -o name)
echo "Deleting primary: $PRIMARY_POD"
kubectl delete "$PRIMARY_POD" -n production
<h1>Watch failover progress</h1>
kubectl get pods -n production -l cnpg.io/cluster=app-db -w
<h1>Verify new primary is elected</h1>
kubectl cnpg status app-db -n production
---
6. Monitoring HA Health
| Metric | Source | Alert Threshold |
| Replication lag (bytes) | `pg_stat_replication` | Above 100MB |
| Replication lag (seconds) | Patroni API / CNPG status | Above 30s |
| Failover count | Patroni history / CNPG events | Any failover |
| Standby count | `pg_stat_replication` | Below 2 |
| WAL generation rate | `pg_stat_wal` | Sudden spikes |
| Connection count | `pg_stat_activity` | Above 80% of max_connections |
For monitoring configuration details, read our Kubernetes observability stack guide and backup guide.
---
Frequently Asked Questions
How much does PostgreSQL HA cost for an SMB?
For VPS deployments (Patroni + HAProxy): 3 servers at roughly $40-80/month each, totaling $120-240/month. For Kubernetes (CNPG): the operator is free; costs are the underlying compute for 3 pods plus storage. Compare this to managed services like RDS Multi-AZ at $200-2000/month. The open-source route is 50-80% cheaper for most SMBs.
What is the maximum acceptable replication lag?
For synchronous replication: 0 bytes (guaranteed). For asynchronous replication (the default and recommended): under 1MB and under 1 second for most workloads. Patroni's `maximum_lag_on_failover` setting (default 1MB) prevents promoting a standby that is too far behind. Set it based on your acceptable data loss tolerance.
Should I use synchronous or asynchronous replication?
Asynchronous for most SMB workloads. Synchronous replication guarantees zero data loss but adds latency to every write (the primary waits for at least one standby to confirm). This typically adds 1-5ms per transaction for same-datacenter setups. Use synchronous only if your application absolutely cannot tolerate any data loss (financial transactions, compliance requirements).
How do I handle split-brain scenarios?
Patroni uses etcd consensus to prevent split-brain. Only the node that holds the etcd leader key can accept writes. If a node loses connectivity to etcd, Patroni demotes it to standby. CNPG uses a similar fencing mechanism. Both tools have been battle-tested against split-brain scenarios. The Arxentra blog has additional articles on distributed systems failure modes.
Can I add read replicas later?
Yes. With Patroni, add a new node with the same configuration and it will automatically clone from the current primary and join as a standby. With CNPG, increase `spec.instances` in the Cluster manifest and CNPG will provision and configure the new instance automatically.
What happens to connections during failover?
Active connections to the old primary are dropped. HAProxy detects the failover within 3-9 seconds and routes new connections to the new primary. Applications should implement connection retry logic with exponential backoff. Most connection poolers (PgBouncer, pgcat) handle this automatically. See our connection pooling guide for PgBouncer configuration.