Back to articles
DevOps & Systems

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

DPTCloud Engineering5 min read
Practical Guide to Building PostgreSQL High Availability Cluster with Patroni, etcd, and PgBouncer on 3 Low-Cost VPS

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

DPTCloud Tier III Infrastructure

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.

View 22 VPS Plans
ECO · vn-02

VPS-2E

94.600 ₫ / mo

10% VAT included

  • CPU: 2 vCPU
  • RAM: 2 GB
  • SSD: 30 GB NVMe
ECO · vn-02Recommended

VPS-3E

170.500 ₫ / mo

10% VAT included

  • CPU: 2 vCPU
  • RAM: 4 GB
  • SSD: 50 GB NVMe
BASIC · vn-01Recommended

VPS-05

457.600 ₫ / mo

10% VAT included

  • CPU: 4 vCPU
  • RAM: 8 GB
  • SSD: 50 GB NVMe

Related Articles

View all
ZaloZalo SupportDiscord0384 753 478