Practical Guide to Building PostgreSQL High Availability Cluster with Patroni, etcd, and PgBouncer on 3 Low-Cost VPS
A detailed guide on deploying a PostgreSQL High Availability cluster using Patroni, etcd, and PgBouncer on 3 low-cost VPS servers, ensuring high availability and stable performance...
Image credit: Pexels · Pixabay Content License
Introduction to PostgreSQL High Availability Cluster
In modern web applications, the database (DB) is a critical component, making high availability (HA) and recovery capabilities essential. A PostgreSQL High Availability (HA) cluster ensures the database remains stable and operational, even when one or more servers fail. This guide demonstrates how to build a PostgreSQL HA cluster using Patroni, etcd, and PgBouncer on three low-cost VPS servers.
This approach is ideal for small businesses, startups, or personal projects that need a reliable and cost-effective database HA solution. Key components include:
- PostgreSQL: An open-source relational database.
- Patroni: Automates failover and PostgreSQL cluster configuration.
- etcd: A distributed key-value store used to synchronize cluster state across nodes.
- PgBouncer: A PostgreSQL connection pooler that optimizes performance and reduces connection load.
Requirements and Environment Preparation
To deploy this PostgreSQL HA cluster, you need:
- 3 VPS servers (can be low-cost NVMe VPS from Cloud VPS NVMe).
- OS: Ubuntu 20.04 or 22.04 (64-bit).
- 3 public IP addresses (or at least 1 public IP and 2 private IPs if using a private network).
- Software: PostgreSQL, Patroni, etcd, PgBouncer, Python, and supporting tools.
- SSH root or sudo access.
We will name the servers as follows:
- node1: 192.168.1.10
- node2: 192.168.1.11
- node3: 192.168.1.12
Deploying the PostgreSQL HA Cluster
Step 1: Install PostgreSQL and Required Tools
On each VPS, update the system and install PostgreSQL:
sudo apt update
sudo apt install -y postgresql postgresql-contrib
Modify the PostgreSQL configuration file (`/etc/postgresql/12/main/postgresql.conf`) to allow remote connections:
listen_addresses = '*'
port = 5432
Update the `pg_hba.conf` file to allow connections from other nodes:
# Permit all connections from any host
host all all 0.0.0.0/0 md5
Restart PostgreSQL:
sudo systemctl restart postgresql
Step 2: Install and Configure etcd
Run on all three servers:
wget https://github.com/etcd-io/etcd/releases/download/v3.5.10/etcd-v3.5.10-linux-amd64.tar.gz
tar -xzf etcd-v3.5.10-linux-amd64.tar.gz
sudo mv etcd-v3.5.10-linux-amd64/etcd* /usr/local/bin/
Create the etcd configuration file (`/etc/etcd/etcd.conf.yml`):
name: 'node1'
data-dir: /var/lib/etcd
wal-dir: /var/lib/etcd/wal
listen-client-urls: http://192.168.1.10:2379
advertise-client-urls: http://192.168.1.10:2379
listen-peer-urls: http://192.168.1.10:2380
initial-advertise-peer-urls: http://192.168.1.10:2380
initial-cluster: node1=http://192.168.1.10:2380,node2=http://192.168.1.11:2380,node3=http://192.168.1.12:2380
initial-cluster-token: etcd-cluster-1
initial-cluster-state: new
Start etcd:
sudo systemctl start etcd
sudo systemctl enable etcd
Step 3: Install and Configure Patroni
Run on all three servers:
sudo apt install -y python3-pip
pip3 install patroni[etcd]
Create the Patroni configuration file (`/etc/patroni.yml`):
scope: postgresql
namespace: /db/
name: node1
restapi:
listen: 192.168.1.10:8008
connect_address: 192.168.1.10:8008
etcd:
hosts: 192.168.1.10:2379,192.168.1.11:2379,192.168.1.12:2379
postgresql:
listen: 192.168.1.10:5432
connect_address: 192.168.1.10:5432
data_dir: /var/lib/postgresql/12/main
bin_dir: /usr/lib/postgresql/12/bin
pgpass: /tmp/pgpass
authentication:
replication:
username: replicator
password: replicatorpass
superuser:
username: postgres
password: postgrespass
parameters:
max_connections: 100
shared_buffers: 128MB
effective_cache_size: 512MB
Start Patroni:
sudo systemctl start patroni
sudo systemctl enable patroni
Step 4: Install and Configure PgBouncer
Run on one or more servers (can be on a separate server or same node):
sudo apt install -y pgbouncer
Create the configuration file (`/etc/pgbouncer/pgbouncer.ini`):
[pgbouncer]
listen_addr = 192.168.1.10
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
server_reset_query = DISCARD ALL
[database]
host = 192.168.1.10
port = 5432
database = postgres
user = postgres
Start PgBouncer:
sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer
Testing and Verification
1. Check Cluster Status
Run on any node to verify the cluster status:
patronictl list
Expected result:
+ Cluster: postgresql (6977914102265726488) ---+----+
| Member | Host | Role | State | TL | Lag in MB |
+----------+-----------------+---------+---------+----+-----------+
| node1 | 192.168.1.10 | Leader | running | 1 | |
| node2 | 192.168.1.11 | Replica | running | 1 | 0 |
| node3 | 192.168.1.12 | Replica | running | 1 | 0 |
+----------+-----------------+---------+---------+----+-----------+
2. Test Failover
Simulate a failure by stopping PostgreSQL on node1:
sudo systemctl stop postgresql
Observe that Patroni automatically promotes node2 or node3 to Leader.
Troubleshooting and Optimization
1. etcd Connection Issues
If Patroni cannot connect to etcd, verify:
- IP and port in `etcd.conf.yml` are correct.
- Firewall is disabled or ports 2379, 2380 are open.
- Run `etcdctl endpoint status` to check etcd status.
2. Performance Optimization
- Set `max_connections` according to application load.
- Use PgBouncer to reduce PostgreSQL connection load.
- Ensure data replication across nodes.
References
Recommended NVMe Cloud VPS Plans to Deploy This Setup
Enterprise RAID 10 NVMe SSDs, CMC & FPT District 9 uplinks with Anycast Anti-DDoS and 99.99% SLA.
VPS-3E
10% VAT included
- CPU: 2 vCPU
- RAM: 4 GB
- SSD: 50 GB NVMe
