NEWS Earn Money with Onidel Cloud! Affiliate Program Details - Check it out
Back to Blog

How to Deploy a Highly Available PostgreSQL 16 Cluster on 3 Ubuntu 24.04 VPS with Patroni, etcd, HAProxy, and TLS (2025 Tutorial)

Building a robust, highly available PostgreSQL cluster is crucial for production applications that demand zero downtime and data consistency. This comprehensive tutorial will guide you through deploying a three-node PostgreSQL 16 cluster using Patroni for automated failover, etcd for distributed consensus, HAProxy for load balancing, and TLS encryption for secure communications.

By the end of this guide, you’ll have a production-ready PostgreSQL cluster that automatically handles primary failover, read-write splitting, and maintains data integrity across multiple nodes.

Prerequisites

Before starting this deployment, ensure you have:

  • Three Ubuntu 24.04 LTS VPS instances with at least 2GB RAM and 20GB storage each
  • Root or sudo access on all three servers
  • Network connectivity between all nodes (private networking preferred)
  • Basic understanding of PostgreSQL, Linux administration, and database clustering concepts
  • Domain names or static IPs for each node

System Requirements:

  • CPU: 2+ cores per node
  • RAM: 2GB minimum, 4GB recommended
  • Storage: 20GB+ with good I/O performance
  • Network: Low-latency connections between nodes

Step-by-Step Tutorial

Step 1: Initial System Setup

First, update all three Ubuntu 24.04 systems and install essential packages:

# Run on all three nodes
sudo apt update && sudo apt upgrade -y
sudo apt install -y curl wget gnupg2 software-properties-common python3-pip python3-venv

# Set hostnames for easier identification
# Node 1:
sudo hostnamectl set-hostname pg-node1
# Node 2:
sudo hostnamectl set-hostname pg-node2
# Node 3:
sudo hostnamectl set-hostname pg-node3

Step 2: Install PostgreSQL 16

Install PostgreSQL 16 from the official repository on all nodes:

# Add PostgreSQL official APT repository
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql.gpg
echo "deb [signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list

# Install PostgreSQL 16
sudo apt update
sudo apt install -y postgresql-16 postgresql-16-dev postgresql-client-16

# Stop and disable default PostgreSQL service
sudo systemctl stop postgresql
sudo systemctl disable postgresql

Step 3: Deploy etcd Cluster

Install and configure etcd for distributed consensus across all nodes:

# Download and install etcd v3.5.10
ETCD_VERSION="3.5.10"
wget https://github.com/etcd-io/etcd/releases/download/v${ETCD_VERSION}/etcd-v${ETCD_VERSION}-linux-amd64.tar.gz
tar -xzf etcd-v${ETCD_VERSION}-linux-amd64.tar.gz
sudo mv etcd-v${ETCD_VERSION}-linux-amd64/etcd* /usr/local/bin/
sudo mkdir -p /var/lib/etcd
sudo useradd -r -s /bin/false etcd
sudo chown etcd:etcd /var/lib/etcd

Create etcd configuration file. Replace IP addresses with your actual node IPs:

# Node 1 (/etc/etcd/etcd.conf)
name: 'node1'
data-dir: '/var/lib/etcd'
initial-advertise-peer-urls: 'http://10.0.1.10:2380'
listen-peer-urls: 'http://10.0.1.10:2380'
advertise-client-urls: 'http://10.0.1.10:2379'
listen-client-urls: 'http://10.0.1.10:2379,http://127.0.0.1:2379'
initial-cluster: 'node1=http://10.0.1.10:2380,node2=http://10.0.1.11:2380,node3=http://10.0.1.12:2380'
initial-cluster-state: 'new'
initial-cluster-token: 'postgresql-cluster'

Step 4: Configure Patroni

Install Patroni for PostgreSQL high availability management:

# Create patroni user and install via pip
sudo useradd -r -s /bin/false patroni
sudo python3 -m pip install patroni[etcd] psycopg2-binary

# Create Patroni configuration directory
sudo mkdir -p /etc/patroni
sudo mkdir -p /var/lib/postgresql/16/main
sudo chown -R postgres:postgres /var/lib/postgresql

Create Patroni configuration file for each node:

# /etc/patroni/patroni.yml (Node 1 example)
scope: postgres-cluster
namespace: /db/
name: node1

restapi:
  listen: 10.0.1.10:8008
  connect_address: 10.0.1.10:8008

etcd:
  hosts: 10.0.1.10:2379,10.0.1.11:2379,10.0.1.12:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 30
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_connections: 100
        max_wal_senders: 10
        wal_keep_size: 128MB
        hot_standby_feedback: "on"
        ssl: "on"
        ssl_cert_file: '/etc/ssl/certs/postgres.crt'
        ssl_key_file: '/etc/ssl/private/postgres.key'

postgresql:
  listen: 10.0.1.10:5432
  connect_address: 10.0.1.10:5432
  data_dir: /var/lib/postgresql/16/main
  bin_dir: /usr/lib/postgresql/16/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: "secure_repl_password"
    superuser:
      username: postgres
      password: "secure_postgres_password"

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

Step 5: Generate TLS Certificates

Create self-signed certificates for secure PostgreSQL connections:

# Generate CA key and certificate
sudo openssl genrsa -out /etc/ssl/private/ca-key.pem 4096
sudo openssl req -new -x509 -key /etc/ssl/private/ca-key.pem -out /etc/ssl/certs/ca-cert.pem -days 365 -subj "/CN=PostgreSQL-CA"

# Generate server certificate for each node
sudo openssl genrsa -out /etc/ssl/private/postgres.key 2048
sudo openssl req -new -key /etc/ssl/private/postgres.key -out /tmp/postgres.csr -subj "/CN=postgres"
sudo openssl x509 -req -in /tmp/postgres.csr -CA /etc/ssl/certs/ca-cert.pem -CAkey /etc/ssl/private/ca-key.pem -out /etc/ssl/certs/postgres.crt -days 365 -CAcreateserial

# Set proper permissions
sudo chown postgres:postgres /etc/ssl/private/postgres.key
sudo chown postgres:postgres /etc/ssl/certs/postgres.crt
sudo chmod 600 /etc/ssl/private/postgres.key
sudo chmod 644 /etc/ssl/certs/postgres.crt

Step 6: Deploy HAProxy Load Balancer

Install and configure HAProxy for database connection routing:

# Install HAProxy
sudo apt install -y haproxy

# Configure HAProxy (/etc/haproxy/haproxy.cfg)
global
    daemon
    user haproxy
    group haproxy
    log stdout local0

defaults
    mode tcp
    timeout connect 5000ms
    timeout client 50000ms
    timeout server 50000ms
    log global

frontend postgres_frontend
    bind *:5000
    default_backend postgres_servers

frontend postgres_readonly_frontend
    bind *:5001
    default_backend postgres_readonly_servers

backend postgres_servers
    balance leastconn
    option tcp-check
    tcp-check connect port 8008
    tcp-check send GET\ /primary\ HTTP/1.0\r\n\r\n
    server node1 10.0.1.10:5432 check port 8008
    server node2 10.0.1.11:5432 check port 8008 backup
    server node3 10.0.1.12:5432 check port 8008 backup

backend postgres_readonly_servers
    balance roundrobin
    option tcp-check
    tcp-check connect port 8008
    tcp-check send GET\ /replica\?lag=1MB\ HTTP/1.0\r\n\r\n
    server node1 10.0.1.10:5432 check port 8008
    server node2 10.0.1.11:5432 check port 8008
    server node3 10.0.1.12:5432 check port 8008

Step 7: Start Services and Initialize Cluster

Start all services in the proper order:

# Start etcd on all nodes
sudo systemctl start etcd
sudo systemctl enable etcd

# Start Patroni on all nodes (start node1 first)
sudo systemctl start patroni
sudo systemctl enable patroni

# Start HAProxy
sudo systemctl start haproxy
sudo systemctl enable haproxy

# Verify cluster status
patronictl -c /etc/patroni/patroni.yml list

Best Practices

Security Considerations:

  • Use proper TLS certificates: Replace self-signed certificates with CA-signed ones for production
  • Secure etcd communication: Enable TLS for etcd client-server and peer communications
  • Network isolation: Use private networks and firewalls to restrict database access
  • Strong passwords: Generate complex passwords for PostgreSQL users and store securely

Performance Optimization:

  • Tune PostgreSQL parameters: Adjust shared_buffers, work_mem, and max_connections based on workload
  • Monitor replication lag: Set up alerts for high replication lag between primary and replicas
  • Use connection pooling: Implement PgBouncer for efficient connection management
  • Regular maintenance: Schedule automated backups and consider automated backup solutions

Monitoring and Alerting:

  • Monitor Patroni API endpoints for cluster health
  • Set up alerts for failover events and replication issues
  • Track database performance metrics and query execution times

Conclusion

You’ve successfully deployed a highly available PostgreSQL 16 cluster with automatic failover capabilities, load balancing, and TLS encryption. This production-ready setup ensures database continuity and optimal performance for your applications.

The combination of Patroni, etcd, and HAProxy provides robust failover mechanisms, distributed consensus, and intelligent traffic routing. Your cluster can now handle node failures gracefully while maintaining data consistency and application availability.

For enhanced performance and reliability, consider deploying this cluster on high-performance infrastructure. Onidel’s VPS solutions offer the compute power, network performance, and storage capabilities needed for demanding PostgreSQL workloads across multiple regions.

Share your love

Related Posts

  • Copyright © 2025 Onidel Pty Ltd. ABN 67 662 357 397.