Amir Mehmood

Posted on Oct 22 Originally published at hostingpodium.com

Designing PostgreSQL HA with Patroni, etcd, and HAProxy in Multi-Zone Linux Clusters

#postgres #linux #database #sre

Originally published on the HostingPodium Enterprise Engineering Blog.

Free SRE & Developer Diagnostics: Test your stack live with our Global DNS Propagation Checker & SSL Health Auditor, Free Website Speed & Core Web Vitals Checker, or explore all 9 Free Webmaster & Cloud Engineering Tools.

This guide explains how to construct a zero-data-loss PostgreSQL HA cluster with Patroni, etcd, and HAProxy across three Linux zones, covering configuration, kernel tuning, and operational runbooks.

Scope

This guide details a production-grade PostgreSQL high-availability (HA) stack built on Patroni, etcd, and HAProxy. The target environment is a three-zone deployment on RHEL 9 or AlmaLinux 9, each zone hosting a dedicated compute node. The design guarantees zero data loss under a single-zone outage, enforces split-brain protection at the consensus layer, and provides deterministic client routing via health-checked TCP load balancing.

Cluster topology

The logical layout consists of:

Component	Instances per zone	Purpose
etcd	1	Consensus store for Patroni metadata
Patroni + PostgreSQL	1	Primary or replica, runs synchronous replication
HAProxy	1 (optional active-passive pair)	Client entry point, performs TCP health checks

All nodes share a private VLAN (10.0.0.0/24) for inter-process traffic and a public VLAN for client access. The HAProxy virtual IP (VIP) is allocated from the public subnet and is moved by keepalived on failover.

etcd consensus layer

etcd runs as a three-member cluster, one member per zone. The --initial-cluster-state flag is set to new on first boot, and --initial-cluster-token is a UUID unique to the deployment. To prevent split-brain, the --auto-compaction-retention is set to 1h and the election timeout is tuned to 5 seconds.

# /etc/etcd/etcd.conf
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_NAME="etcd-$(hostname)"
ETCD_INITIAL_ADVERTISE_PEER_URLS="https://$(hostname -I | awk '{print $1}'):2380"
ETCD_ADVERTISE_CLIENT_URLS="https://$(hostname -I | awk '{print $1}'):2379"
ETCD_INITIAL_CLUSTER="etcd-node1=https://10.0.0.11:2380,etcd-node2=https://10.0.0.12:2380,etcd-node3=https://10.0.0.13:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="$(cat /etc/etcd/token)"
ETCD_CLIENT_CERT_AUTH="true"
ETCD_PEER_CLIENT_CERT_AUTH="true"
ETCD_CERT_FILE="/etc/etcd/ssl/etcd.pem"
ETCD_KEY_FILE="/etc/etcd/ssl/etcd-key.pem"
ETCD_TRUSTED_CA_FILE="/etc/etcd/ssl/ca.pem"
ETCD_QUOTA_BACKEND_BYTES=8589934592   # 8 GiB
ETCD_ELECTION_TIMEOUT=5000
ETCD_HEARTBEAT_INTERVAL=250
ETCD_AUTO_COMPACTION_RETENTION=1h

All etcd traffic is encrypted with TLS 1.2+. The quorum size of three ensures that any two zones can form a majority, satisfying the majority rule for Raft.

Patroni configuration

Patroni reads its state from etcd and controls PostgreSQL via pg_ctl. The configuration file is identical on each node, with zone-specific values injected through environment variables.

# /etc/patroni/patroni.yml
scope: pg-ha-cluster
namespace: /db/
name: $(hostname)
restapi:
  listen: 0.0.0.0:8008
  connect_address: $(hostname -I | awk '{print $1}'):8008
etcd:
  host: https://10.0.0.11:2379,https://10.0.0.12:2379,https://10.0.0.13:2379
  protocol: https
  cacert: /etc/patroni/ssl/ca.pem
  cert: /etc/patroni/ssl/patroni.pem
  key: /etc/patroni/ssl/patroni-key.pem
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576   # 1 MiB
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: replica
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 1024          # MiB
        synchronous_commit: on
        synchronous_standby_names: "*"
        hot_standby: on
        max_connections: 500
        shared_buffers: 4GB
        effective_cache_size: 12GB
        work_mem: 64MB
        maintenance_work_mem: 512MB
        checkpoint_timeout: 5min
        checkpoint_completion_target: 0.9
  initdb:
    - encoding: UTF8
    - locale: en_US.UTF-8
    - data-checksums
  pg_hba:
    - host all all 10.0.0.0/24 md5
    - host replication all 10.0.0.0/24 md5
    - host all all 0.0.0.0/0 reject
  users:
    admin:
      password: "$(cat /etc/patroni/secrets/admin.pass)"
      options: [CREATEDB]
postgresql:
  listen: 0.0.0.0:5432
  data_dir: /var/lib/pgsql/13/data
  bin_dir: /usr/pgsql-13/bin
  authentication:
    replication:
      username: replicator
      password: "$(cat /etc/patroni/secrets/replicator.pass)"
    superuser:
      username: postgres
      password: "$(cat /etc/patroni/secrets/postgres.pass)"

Key Patroni Bootstrap & Synchronous Replication Parameters

synchronous_standby_names="*" forces every replica to acknowledge writes, guaranteeing zero data loss as long as a quorum of replicas remains reachable.
maximum_lag_on_failover=1MiB prevents a lagging replica from being promoted.
wal_keep_size=1024 reserves enough WAL for a full-zone outage.

HAProxy routing

HAProxy runs in TCP mode on a dedicated VIP (10.0.1.100). It performs a health check against the Patroni REST API; only nodes reporting state: "running" and role: "master" are advertised as primary. Replicas are exposed on a separate port for read-only workloads.

# /etc/haproxy/haproxy.cfg
global
    log /dev/log    local0
    maxconn 5000
    daemon
    tune.ssl.default-dh-param 2048
    ssl-default-bind-ciphers PROFILE=SYSTEM
    ssl-default-server-ciphers PROFILE=SYSTEM
    ssl-default-bind-options no-sslv3 no-tlsv10 no-tlsv11
    nbproc 2
    cpu-map 1 0-3
    cpu-map 2 4-7

defaults
    mode tcp
    log global
    timeout connect 5s
    timeout client 30s
    timeout server 30s
    option tcplog
    retries 3

frontend pgsql_front
    bind 10.0.1.100:5432
    default_backend pgsql_primary
    acl is_read path_beg /read
    use_backend pgsql_replica if is_read

backend pgsql_primary
    option httpchk GET /master
    http-check expect status 200
    server pg1 10.0.0.21:5432 check port 8008 inter 2s rise 2 fall 3
    server pg2 10.0.0.22:5432 check port 8008 inter 2s rise 2 fall 3 backup
    server pg3 10.0.0.23:5432 check port 8008 inter 2s rise 2 fall 3 backup

backend pgsql_replica
    option httpchk GET /replica
    http-check expect status 200
    server pg1 10.0.0.21:5432 check port 8008 inter 2s rise 2 fall 3 disabled
    server pg2 10.0.0.22:5432 check port 8008 inter 2s rise 2 fall 3
    server pg3 10.0.0.23:5432 check port 8008 inter 2s rise 2 fall 3

Failover is driven by keepalived moving the VIP to the node whose Patroni instance reports role: "master". The health-check interval of 2 seconds yields sub-second detection of primary loss.

Kernel and system parameters

Linux kernel tuning focuses on network buffers, shared memory limits, and file descriptor counts. The following sysctl settings are applied on every PostgreSQL node:

Parameter	Value	Rationale
net.core.somaxconn	4096	Allows HAProxy to accept many concurrent connections.
net.ipv4.tcp_tw_reuse	1	Reduces TIME_WAIT buildup under heavy client churn.
vm.max_map_count	262144	Required for large shared memory segments used by PostgreSQL.
kernel.shmmax	68719476736	68 GiB – matches shared_buffers plus overhead.
kernel.shmall	16777216	Number of pages (8 KiB each) to support shmmax.
fs.file-max	1000000	Ensures PostgreSQL can open the required number of sockets and files.

Persist the values in /etc/sysctl.d/99-pg-ha.conf and reload with sysctl --system.

Synchronous replication strategy

Patroni is configured for synchronous_commit=on and synchronous_standby_names="*". In a three-zone deployment this means the primary must receive acknowledgment from both replicas before confirming a transaction. The write latency typically rises by 2-4 ms, measured with pgbench -c 50 -j 4 -T 60. The trade-off is acceptable for workloads that cannot tolerate data loss.

In the event that a replica becomes unreachable, Patroni automatically demotes the primary to asynchronous mode after a configurable failover_timeout (default 30 seconds). This prevents a permanent write stall while preserving data integrity once the missing replica rejoins.

Failover and recovery runbook

Detect primary loss: HAProxy health check fails, keepalived triggers VIP move.
Patroni election: Remaining nodes consult etcd; the node with the lowest name wins the Raft election and assumes role: "master".
Promote replica: Patroni runs pg_ctl promote. PostgreSQL replays pending WAL up to the last synchronous commit.
Validate quorum: Verify SELECT pg_is_in_recovery(); returns false on the new primary and SELECT * FROM pg_stat_replication; shows both replicas in sync.
Update HAProxy: Health check endpoint /master now returns 200, HAProxy routes traffic to the new primary within two seconds.
Re-integrate failed zone: Once the node is back online, start etcd, let it join the Raft cluster, then start Patroni. It will register as a replica and begin streaming WAL.
Post-failover audit: Capture etcdctl endpoint status --write-out=table and patronictl list output for compliance records.

If the failed zone contained the original primary and its data directory is corrupted, initiate a fresh base backup using pg_basebackup -D /var/lib/pgsql/13/data -Fp -Xs -P -R from the current primary before re-joining.

Observability stack

Metrics are collected via Prometheus exporters:

etcd exporter on port 2379/metrics
Patroni exporter (built-in) on port 8008/metrics
PostgreSQL exporter on port 9187
HAProxy exporter on port 9101

Alertmanager Rules & Quorum Loss Thresholds

# alerts.yml
- alert: PostgreSQLPrimaryDown
  expr: patroni_role{role="master"} == 0
  for: 30s
  labels:
    severity: critical
  annotations:
    summary: "Primary PostgreSQL instance is down"
    runbook: "https://internal.runbooks/pg-primary-failure"

- alert: EtcdQuorumLost
  expr: etcd_server_has_leader == 0
  for: 15s
  labels:
    severity: warning
  annotations:
    summary: "Etcd cluster has lost quorum"
    runbook: "https://internal.runbooks/etcd-quorum"

Logs are shipped to Elasticsearch via Filebeat; each component tags logs with service=postgresql|etcd|haproxy for easy correlation.

Trade-offs

Latency vs. durability: Synchronous replication adds 2-4 ms per transaction; asynchronous mode reduces latency but permits data loss if the primary crashes.
Complexity of etcd: Managing a Raft cluster adds operational overhead compared to a simple file-based DCS, but it eliminates split-brain scenarios that can arise with ZooKeeper or Consul under network partitions.
HAProxy vs. PgBouncer: HAProxy offers TCP-level health checks and VIP failover, whereas PgBouncer provides connection pooling but cannot move a VIP without additional tooling.
Resource footprint: Running three full PostgreSQL instances with 4 GiB shared buffers each consumes ~12 GiB RAM per node; sizing must account for OS, etcd, and HAProxy overhead.

Operational checklist

Validate TLS certificates on etcd, Patroni, and HAProxy before deployment.
Run etcdctl endpoint health on each member; expect 100 % success.
Confirm patronictl list shows one master and two replicas.
Execute pgbench -c 10 -j 2 -T 30 and record 95th-percentile latency; must stay below 10 ms under baseline load.
Test failover by stopping patroni on the master; verify VIP moves and new master promotion within 30 seconds.
Inspect Prometheus alerts after each test; no pending critical alerts should remain.
Back up etcd snapshots daily with etcdctl snapshot save /backups/etcd-$(date +%F).db.

Following this checklist on a weekly cadence keeps the HA stack within SLA limits and provides early detection of configuration drift.

Summary

The combination of Patroni, etcd, and HAProxy delivers a deterministic failover path, strict split-brain avoidance, and zero-data-loss guarantees for PostgreSQL workloads spread across three availability zones. By adhering to the kernel parameters, replication settings, and runbook steps outlined above, operators can meet sub-second recovery objectives and maintain consistent performance under load.

Free Webmaster, SRE & Cloud Diagnostic Tools (No Signup Required)

Verify and benchmark your production infrastructure directly from your browser using the HostingPodium Free Webmaster & Developer Suite:

Free Engineering Utility	Primary Diagnostic Use Case
Global DNS Propagation & SSL Checker	Verify live A, AAAA, CNAME, MX, TXT, NS & CAA records across 21+ global resolvers + TLS certificate audits
Website Speed Test & Core Web Vitals Auditor	Measure server TTFB, LCP, INP, CLS, and HTTP/2 compression latency in real time
Rich Snippets & Schema.org JSON-LD Generator	Generate & validate structured JSON-LD markup for TechArticles, FAQs, Products & Organizations
HTTP Redirect Chain & Status Code Tracer	Trace 301/302 redirect hops, inspect response headers, and verify load balancer health checks
DMARC, SPF & DKIM Email Security Validator	Audit domain email authentication policies and generate compliant DMARC records
Google SERP Simulator & Meta Tag Previewer	Preview desktop/mobile Google snippets, OpenGraph cards, and pixel-width truncation
Free WebP Image Converter & Optimizer	Client-side PNG/JPG to WebP compression for faster Core Web Vitals LCP

Related Enterprise Architecture & Benchmarks

Canonical Engineering Guide: Designing PostgreSQL HA with Patroni, etcd, and HAProxy in Multi-Zone Linux Clusters
Managed Engineering Practice: 24/7 Managed Infrastructure, Linux SRE & Observability
Infrastructure Benchmark Comparison: Hetzner Alternative: 24/7 Managed Bare Metal vs Unmanaged Hardware

DEV Community