{"id":5143,"date":"2026-11-07T03:05:33","date_gmt":"2026-11-06T21:35:33","guid":{"rendered":"https:\/\/cpanelfree.com\/blog\/setting-up-postgresql-high-availability-with-patroni-etcd-and-pgbouncer-on-linux-vps\/"},"modified":"2026-11-07T03:05:33","modified_gmt":"2026-11-06T21:35:33","slug":"setting-up-postgresql-high-availability-with-patroni-etcd-and-pgbouncer-on-linux-vps","status":"publish","type":"post","link":"https:\/\/cpanelfree.com\/blog\/setting-up-postgresql-high-availability-with-patroni-etcd-and-pgbouncer-on-linux-vps\/","title":{"rendered":"Setting Up PostgreSQL High Availability with Patroni, etcd, and PgBouncer on Linux VPS"},"content":{"rendered":"

Operating a mission-critical relational database on Linux VPS infrastructure demands an architecture that survives sudden hypervisor panics, kernel out-of-memory kills, and network partitions without data corruption or manual failover delays. Traditional PostgreSQL streaming replication provides data redundancy, but when the primary node drops offline at 03:00 AM, human intervention to promote a standby and redirect application traffic inevitably inflates Recovery Time Objective (RTO) into dozens of minutes. By coupling Patroni as an intelligent supervisor, etcd as a fault-tolerant Distributed Configuration Store (DCS), and PgBouncer for transparent connection pooling and endpoint routing, systems engineers can deploy a production-ready, self-healing database cluster on CpanelFree<\/a> staging nodes and enterprise cloud environments.<\/p>\n

<\/p>\n

What is PostgreSQL High Availability with Patroni, etcd, and PgBouncer?<\/h2>\n
\n

Direct Answer:<\/strong> PostgreSQL high availability with Patroni, etcd, and PgBouncer delivers automated failover, distributed consensus, and connection management across Linux VPS nodes. etcd provides a resilient distributed configuration store (DCS), Patroni continuously monitors node health and orchestrates Raft-driven leader election, while PgBouncer pools connections and routes client traffic seamlessly to prevent application connection storms during automated failover transitions.<\/p>\n<\/div>\n

In modern Linux infrastructure environments, achieving authentic High Availability (HA) requires solving three independent distributed systems challenges simultaneously: state consensus, node orchestration, and client connection virtualization. If any of these layers is misconfigured, the cluster risks either catastrophic split-brain conditions (where two nodes accept simultaneous writes) or cascading connection exhaustion when thousands of client sockets overwhelm the newly promoted primary.<\/p>\n

The standard reference architecture consists of three virtual private servers (nodes pg-node01<\/code>, pg-node02<\/code>, and pg-node03<\/code>), each hosting an instance of etcd<\/code>, a patroni<\/code> daemon managing a local PostgreSQL 16+ engine, and a co-located or fronted pgbouncer<\/code> service.<\/p>\n

High-Availability Architectural Matrix: Manual vs. Patroni Cluster<\/h2>\n

Before evaluating the step-by-step implementation, review the fundamental operational divergence between legacy primary-standby configurations and a modern Patroni-orchestrated consensus cluster:<\/p>\n

\n\n\n\n\n\n\n\n\n\n\n
Architecture Metric \/ Capability<\/th>\nStandby Streaming (Manual)<\/th>\nPatroni + etcd + PgBouncer (HA Cluster)<\/th>\n<\/tr>\n<\/thead>\n
Failover RTO (Recovery Time Objective)<\/td>\n10\u201345 minutes (Manual sysadmin intervention)<\/td>\n< 10 seconds (Automated consensus election)<\/td>\n<\/tr>\n
Split-Brain Protection<\/td>\nVulnerable without manual fencing\/STONITH<\/td>\nGuaranteed via etcd Raft quorum lease TTL<\/td>\n<\/tr>\n
Connection Handling Under Load<\/td>\nDirect backend forks (~10MB RAM per client)<\/td>\nPgBouncer transaction pooling (>10,000 clients)<\/td>\n<\/tr>\n
Client Routing \/ Service Discovery<\/td>\nManual DNS update or IP takeover lag<\/td>\nHealth endpoint dynamic routing via Patroni REST API<\/td>\n<\/tr>\n
Replica Resynchronization<\/td>\nComplex manual pg_rewind \/ pg_basebackup<\/td>\nAutomated pg_rewind self-healing recovery<\/td>\n<\/tr>\n
Cluster Configuration Drift<\/td>\nHigh (individual postgresql.conf per server)<\/td>\nSynchronized cluster-wide DCS configuration<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n

Step 1: Linux Kernel and OS Tuning for PostgreSQL VPS Instances<\/h2>\n

High-concurrency PostgreSQL clusters running on Linux virtual machines require dedicated kernel memory management parameters. PostgreSQL relies heavily on the operating system page cache alongside its internal shared_buffers<\/code>. Furthermore, aggressive virtual memory overcommitting or excessive dirty page accumulation can trigger kernel latency spikes or catastrophic Out-Of-Memory (OOM) kills during high write throughput.<\/p>\n

Deploy the following configuration file across all cluster nodes at \/etc\/sysctl.d\/99-postgresql-ha.conf<\/code>:<\/p>\n

# \/etc\/sysctl.d\/99-postgresql-ha.conf\n# Linux Kernel Optimization for PostgreSQL HA, etcd, and PgBouncer\n\n# Disable memory overcommit to prevent unpredictable OOM kills of the primary\nvm.overcommit_memory = 2\nvm.overcommit_ratio = 80\n\n# Keep page cache flushing predictable and continuous rather than bursty\nvm.dirty_background_ratio = 5\nvm.dirty_ratio = 10\n\n# Minimize swapping aggressiveness while retaining basic swap emergency cushion\nvm.swappiness = 1\n\n# Prevent memory fragmentation on high memory VPS instances\nvm.zone_reclaim_mode = 0\n\n# Network stack tuning for high-frequency consensus heartbeats and connection pools\nnet.core.somaxconn = 4096\nnet.ipv4.tcp_max_syn_backlog = 8192\nnet.ipv4.tcp_tw_reuse = 1\nnet.ipv4.tcp_fin_timeout = 15\n\n# Increase socket buffer sizes for heavy replication streams\nnet.core.rmem_default = 262144\nnet.core.wmem_default = 262144\nnet.core.rmem_max = 16777216\nnet.core.wmem_max = 16777216\n\n# File descriptor ceiling for busy database and pooling instances\nfs.file-max = 2097152<\/code><\/pre>\n
Apply the tuned parameters immediately across all nodes without rebooting:<\/p>\n
sudo sysctl --system<\/code><\/pre>\n
\n
Architecture Note on OOM Defense:<\/strong> Setting vm.overcommit_memory = 2<\/code> ensures the Linux kernel will strictly refuse memory allocations that exceed (RAM * vm.overcommit_ratio \/ 100) + Swap<\/code>. This completely eliminates the risk of the Linux OOM-killer arbitrarily sending SIGKILL<\/code> to PostgreSQL backend processes, which would trigger immediate crash recovery on the primary.<\/p>\n<\/blockquote>\n
Step 2: Provisioning the 3-Node etcd Consensus Cluster<\/h2>\n
Patroni requires a Distributed Configuration Store (DCS) to maintain leader leases and cluster topology state. etcd utilizes the Raft consensus algorithm, meaning a minimum of three nodes is required to tolerate the failure of one node while preserving quorum ((N\/2) + 1 = 2<\/code> votes). Running etcd on only two nodes provides zero fault tolerance, as the failure of either node drops the quorum below the necessary majority.<\/p>\n
Install etcd on all three nodes (10.0.0.11<\/code>, 10.0.0.12<\/code>, 10.0.0.13<\/code>). Here is the complete production configuration for node 1 at \/etc\/etcd\/etcd.yml<\/code>:<\/p>\n
# \/etc\/etcd\/etcd.yml (Node 1: pg-node01)\nname: 'pg-node01'\ndata-dir: '\/var\/lib\/etcd\/pg-cluster.etcd'\nlisten-peer-urls: 'http:\/\/10.0.0.11:2380'\nlisten-client-urls: 'http:\/\/10.0.0.11:2379,http:\/\/127.0.0.1:2379'\ninitial-advertise-peer-urls: 'http:\/\/10.0.0.11:2380'\nadvertise-client-urls: 'http:\/\/10.0.0.11:2379'\n\n# Raft consensus peer cluster discovery\ninitial-cluster: 'pg-node01=http:\/\/10.0.0.11:2380,pg-node02=http:\/\/10.0.0.12:2380,pg-node03=http:\/\/10.0.0.13:2380'\ninitial-cluster-token: 'etcd-pg-ha-token'\ninitial-cluster-state: 'new'\n\n# Heartbeat and election timeouts (tuned for stable Linux VPS networking)\nheartbeat-interval: 250\nelection-timeout: 1250\nauto-compaction-retention: '1'\nsnapshot-count: 10000\nmax-request-bytes: 1572864\nquota-backend-bytes: 8589934592<\/code><\/pre>\n
Enable and start the etcd service across all nodes:<\/p>\n
sudo systemctl daemon-reload\nsudo systemctl enable --now etcd\netcdctl --endpoints=http:\/\/127.0.0.1:2379 endpoint health\netcdctl --endpoints=http:\/\/127.0.0.1:2379 member list -w table<\/code><\/pre>\n
\n
Consensus Quorum Warning:<\/strong> Never deploy an even number of etcd consensus nodes. A 4-node cluster requires 3 nodes for quorum; if two nodes partition, the entire cluster halts writes. A 3-node cluster also requires 2 nodes for quorum, meaning 3 nodes and 4 nodes provide the exact same fault tolerance (1 node failure), but 4 nodes introduce an additional failure surface.<\/p>\n<\/blockquote>\n
Step 3: Deploying and Configuring Patroni<\/h2>\n
Patroni acts as a customized supervisor that wraps PostgreSQL. It registers with etcd, acquires a leader lease lock with a Time-To-Live (TTL), and bootstraps the PostgreSQL database engine automatically. Standby nodes query etcd, identify the current primary, and configure native streaming replication via pg_basebackup<\/code> without manual intervention.<\/p>\n
Install Patroni with python etcd3 support:<\/p>\n
sudo apt-get update && sudo apt-get install -y postgresql-16 postgresql-contrib patroni python3-etcd3<\/code><\/pre>\n
Place the following production configuration at \/etc\/patroni\/patroni.yml<\/code> on pg-node01<\/code> (adjusting node IP addresses accordingly for pg-node02<\/code> and pg-node03<\/code>):<\/p>\n
# \/etc\/patroni\/patroni.yml\nscope: postgres-ha-cluster\nnamespace: \/service\nname: pg-node01\n\n# Patroni REST API for health checks and orchestrator commands\nrestapi:\n  listen: 10.0.0.11:8008\n  connect_address: 10.0.0.11:8008\n\n# Distributed Configuration Store connection\netcd3:\n  hosts:\n    - 10.0.0.11:2379\n    - 10.0.0.12:2379\n    - 10.0.0.13:2379\n\nbootstrap:\n  dcs:\n    ttl: 30\n    loop_wait: 10\n    retry_timeout: 10\n    maximum_lag_on_failover: 1048576\n    synchronous_mode: false\n    postgresql:\n      use_pg_rewind: true\n      use_slots: true\n      parameters:\n        shared_buffers: 4GB\n        work_mem: 32MB\n        maintenance_work_mem: 512MB\n        effective_cache_size: 12GB\n        wal_level: replica\n        max_wal_size: 16GB\n        min_wal_size: 2GB\n        checkpoint_completion_target: 0.9\n        checkpoint_timeout: 15min\n        archive_mode: \"on\"\n        archive_command: \"bin\/true\"\n        wal_keep_size: 2048MB\n        hot_standby: \"on\"\n        track_io_timing: \"on\"\n        max_connections: 300\n\n  initdb:\n    - encoding: UTF8\n    - data-checksums\n\n  pg_hba:\n    - host replication replicator 10.0.0.0\/24 md5\n    - host all all 10.0.0.0\/24 md5\n    - host all all 127.0.0.1\/32 md5\n\npostgresql:\n  listen: 10.0.0.11:5432,127.0.0.1:5432\n  connect_address: 10.0.0.11:5432\n  data_dir: \/var\/lib\/postgresql\/16\/patroni\n  bin_dir: \/usr\/lib\/postgresql\/16\/bin\n  pgpass: \/var\/lib\/postgresql\/.pgpass\n  authentication:\n    replication:\n      username: replicator\n      password: SuperSecretReplicationPassword2026\n    superuser:\n      username: postgres\n      password: SuperSecretPostgresAdminPassword2026\n\ntags:\n  nofailover: false\n  noloadbalance: false\n  clonefrom: false\n  nosync: false<\/code><\/pre>\n
Create the systemd service unit to supervise Patroni at \/etc\/systemd\/system\/patroni.service<\/code>:<\/p>\n
# \/etc\/systemd\/system\/patroni.service\n[Unit]\nDescription=Patroni PostgreSQL High Availability Orchestrator\nAfter=network.target etcd.service\nRequires=etcd.service\n\n[Service]\nType=simple\nUser=postgres\nGroup=postgres\nExecStart=\/usr\/bin\/patroni \/etc\/patroni\/patroni.yml\nExecReload=\/bin\/kill -s HUP $MAINPID\nKillMode=process\nTimeoutSec=30\nRestart=on-failure\nRestartSec=5\nLimitNOFILE=65536\n\n[Install]\nWantedBy=multi-user.target<\/code><\/pre>\n
Before launching Patroni, stop and disable the standard PostgreSQL systemd service. Patroni must maintain exclusive control over spawning and terminating the PostgreSQL daemon:<\/p>\n
sudo systemctl stop postgresql\nsudo systemctl disable postgresql\nsudo systemctl daemon-reload\nsudo systemctl enable --now patroni<\/code><\/pre>\n
Verify cluster status from any node using the patronictl<\/code> CLI:<\/p>\n
patronictl -c \/etc\/patroni\/patroni.yml list<\/code><\/pre>\n
\n
Automated Self-Healing via pg_rewind:<\/strong> Note the parameter use_pg_rewind: true<\/code>. When an old primary rejoins after a failover, its write-ahead log (WAL) might have diverged from the newly elected leader. Rather than forcing a costly and bandwidth-heavy full pg_basebackup<\/code>, Patroni executes pg_rewind<\/code> to identify the divergent point, rewind the uncommitted blocks, and switch into a streaming standby in seconds.<\/p>\n<\/blockquote>\n
Step 4: PgBouncer Integration and Connection Virtualization<\/h2>\n
PostgreSQL processes connections using a traditional process-fork model. Each client socket consumes roughly 5MB to 10MB of RAM and incurs kernel context-switching overhead. Attempting to support thousands of simultaneous web application connections directly against PostgreSQL degrades throughput exponentially. Deploying PgBouncer in transaction pooling mode allows tens of thousands of client threads to share a lean, highly optimized pool of 30 to 50 active PostgreSQL server connections.<\/p>\n
Deploy PgBouncer on each node (or on a dedicated load balancing tier) with the following production configuration at \/etc\/pgbouncer\/pgbouncer.ini<\/code>:<\/p>\n
# \/etc\/pgbouncer\/pgbouncer.ini\n[databases]\n# Dynamic routing target updated via local Patroni REST API or HAProxy\napp_production = host=127.0.0.1 port=5432 dbname=app_production auth_user=postgres\n\n[pgbouncer]\nlogfile = \/var\/log\/postgresql\/pgbouncer.log\npidfile = \/var\/run\/postgresql\/pgbouncer.pid\nlisten_addr = 0.0.0.0\nlisten_port = 6432\nauth_type = scram-sha-256\nauth_file = \/etc\/pgbouncer\/userlist.txt\nadmin_users = postgres,sysadmin\nstats_users = monitoring\n\n# Connection Pool Architecture\npool_mode = transaction\nmax_client_conn = 10000\ndefault_pool_size = 40\nmin_pool_size = 10\nreserve_pool_size = 10\nreserve_pool_timeout = 5\nmax_db_connections = 100\n\n# Client and Server Connection Health\nserver_reset_query = DISCARD ALL\nserver_check_delay = 10\nserver_check_query = SELECT 1\nserver_lifetime = 3600\nserver_idle_timeout = 600\nclient_idle_timeout = 0\nquery_timeout = 120\n\n# Socket buffer optimizations\nso_reuseport = 1\ntcp_keepalive = 1\ntcp_keepidle = 30\ntcp_keepintvl = 10\ntcp_keepcnt = 3<\/code><\/pre>\n
Generate the authentication file \/etc\/pgbouncer\/userlist.txt<\/code> using SCRAM-SHA-256 password hashes extracted directly from PostgreSQL:<\/p>\n
# \/etc\/pgbouncer\/userlist.txt\n\"app_user\" \"SCRAM-SHA-256$4096:5a9G...==:7bF2...==\"\n\"postgres\" \"SCRAM-SHA-256$4096:2xM9...==:8cV1...==\"<\/code><\/pre>\n
Enable and start PgBouncer:<\/p>\n
sudo systemctl enable --now pgbouncer\npsql -p 6432 -U postgres -d pgbouncer -c \"SHOW POOLS;\"<\/code><\/pre>\n
Step 5: Client Traffic Steering via Patroni REST Endpoints<\/h2>\n
To steer client write queries strictly to the active leader and read queries to standbys, modern load balancers (such as HAProxy, Keepalived, or Envoy) leverage Patroni’s embedded HTTP REST service (port 8008<\/code>). Patroni responds with specific HTTP status codes based on the node’s cluster role:<\/p>\n