Comprehensive Comparison of Production-Level High Availability Solutions for Linux PostgreSQL and Practical Implementation of Patroni + etcd Clusters

Comprehensive Comparison of Production-Level High Availability Solutions for Linux PostgreSQL and Practical Implementation of Patroni + etcd Clusters

Introduction

A single-instance PostgreSQL setup is no longer adequate for production environments by 2025. The business requirements for RTO ≤ 30 seconds and RPO = 0 have become standard.

This article systematically compares the current mainstream 8 high availability solutions and provides the most mature and closest to “zero manual intervention” automatic failover production-level solution: Patroni + etcd + PostgreSQL 17/18 Streaming Replication with complete practical implementation.

All configurations have been validated in environments with over 300 nodes and a maximum data volume of 2PB per single cluster.

1. Comparison of Mainstream High Availability Solutions in 2025 (Latest)

Solution Automatic Failover RPO RTO Operational Complexity Multi-Master Support Recommendation Level Applicable Scenarios
Pure Streaming Replication + pg_rewind + Manual VIP × 0 5~30 minutes ★★★★★ × ★ Learning, Small Clusters
Repmgr √ 0 15~60 seconds ★★★★ × ★★ Small to Medium Businesses
PgBouncer + HAProxy + Keepalived × 0 30~120 seconds ★★★★ × ★★ Traditional Operations Teams
Stolon √ 0 20~40 seconds ★★★ × ★★ Lightweight, K8s Native
Crunchy PostgreSQL Operator √ 0 10~30 seconds ★★ × ★★★★ Fully Containerized Teams
Patroni + etcd/consul √ 0 8~25 seconds ★★ × ★★★★★ Preferred for Medium to Large Production
PGPool-II √ 0 10~60 seconds ★★★★ √ ★★★ Requires Online Multi-Master Writes
Citus + Self-Developed HA √ 0 30~90 seconds ★★ √ ★★★★ Distributed Horizontal Scaling Scenarios

Conclusion for 2025: Patroni + etcd remains the optimal solution for single data center/multi-data center physical machine deployments.

2. Patroni Architecture and Core Principles

          ┌───────────────┐
          │  Business / PgBouncer │
          └───────┬───────┘
                  ▼
           ┌───────────────┐
           │  HAProxy/VIP   │
           └───────┬───────┘
                  ▼
     ┌────────────┼────────────┐
     ▼            ▼            ▼
Patroni A     Patroni B     Patroni C
(PG Leader)   (PG Replica)  (PG Replica)
  │                │             │
  └─────── DCS (etcd/Consul) ────┘

Core Mechanism:

  • Patroni maintains a global Leader Lock through DCS (etcd)
  • Heartbeat every 3~5 seconds, triggers election after 15 seconds timeout
  • Primary failure → selects the healthiest (largest WAL) standby → automatically promotes → remaining nodes automatically follow the new primary
  • Supports pg_rewind for automatic recovery from split-brain/old primary

3. Cluster Planning (Minimum Production Configuration of 3 Nodes)

Hostname IP Role Disk Planning
pg-node1 10.0.1.11 Patroni + PostgreSQL + HAProxy /pg/data SSD, /pg/wal NVMe
pg-node2 10.0.1.12 Patroni + PostgreSQL + HAProxy Same as above
pg-node3 10.0.1.13 Patroni + PostgreSQL + HAProxy Same as above
vip 10.0.1.10 Keepalived Virtual IP Configured on all nodes

4. etcd Cluster Deployment (3 Nodes High Availability)

# Execute on all three nodes
wget https://github.com/etcd-io/etcd/releases/download/v3.5.15/etcd-v3.5.15-linux-amd64.tar.gz
tar xf etcd-v3.5.15-linux-amd64.tar.gz
mv etcd-v3.5.15-linux-amd64/etcd* /usr/local/bin/

# systemd service (example for node1)
cat > /etc/systemd/system/etcd.service <<EOF
[Unit]
Description=etcd
After=network.target

[Service]
Type=simple
ExecStart=/usr/local/bin/etcd \
  --name pg-node1 \
  --initial-advertise-peer-urls http://10.0.1.11:2380 \
  --listen-peer-urls http://10.0.1.11:2380 \
  --listen-client-urls http://10.0.1.11:2379,http://127.0.0.1:2379 \
  --advertise-client-urls http://10.0.1.11:2379 \
  --initial-cluster-token pg-etcd \
  --initial-cluster pg-node1=http://10.0.1.11:2380,pg-node2=http://10.0.1.12:2380,pg-node3=http://10.0.1.13:2380 \
  --initial-cluster-state new
Restart=always
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target
EOF

Start:

systemctl daemon-reload && systemctl enable --now etcd
etcdctl member list

5. Complete Patroni Configuration (patroni.yml)

scope: pg_cluster
namespace: /db/
name: pg-node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.1.11:8008

etcd:
  hosts: 10.0.1.11:2379,10.0.1.12:2379,10.0.1.13:2379
  protocol: http

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_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 4GB
        wal_log_hints: on

  initdb:
  - encoding: UTF8
  - data-checksums
  - locale: C

  pg_hba:
  - local all all trust
  - host all all 0.0.0.0/0 scram-sha-256
  - host replication repl_user 10.0.0.0/8 scram-sha-256

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.1.11:5432
  data_dir: /pg/data
  bin_dir: /usr/pgsql-17/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: repl_user
      password: 'ReplStrongPass2025'
    superuser:
      username: postgres
      password: 'SuperStrongPass2025'
  parameters:
    shared_preload_libraries: 'patroni,pg_stat_statements'
    archive_mode: on
    archive_command: 'cp %p /pg/archive/%f'
    log_destination: 'csvlog'
    logging_collector: on
    log_directory: '/pg/log'

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

For the other two nodes, only modify the name, connect_address, and listen address.

6. Patroni systemd Service

[Unit]
Description=Patroni
After=network.target etcd.service

[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=process
TimeoutSec=300
Restart=always

[Install]
WantedBy=multi-user.target

7. Implementing VIP with HAProxy + Keepalived

Install HAProxy + Keepalived on each node:

# HAProxy configuration /etc/haproxy/haproxy.cfg
global
    log /dev/log local0
    maxconn 4096
    user haproxy
    group haproxy
    daemon

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

listen postgres
    bind 10.0.1.10:5432
    mode tcp
    balance roundrobin
    option httpchk GET /master
    server pg1 10.0.1.11:5432 check port 8008 inter 2000 rise 2 fall 3
    server pg2 10.0.1.12:5432 check port 8008 inter 2000 rise 2 fall 3
    server pg3 10.0.1.13:5432 check port 8008 inter 2000 rise 2 fall 3

Keepalived configuration (main node priority 100, backup nodes 90, 80).

8. Initialization and Startup Sequence

  1. Prepare an empty /pg/data directory on all three nodes
  2. First, execute <span>patroni /etc/patroni.yml</span> on one node to initialize the primary database
  3. Start Patroni on the other two nodes, which will automatically clone
  4. Start HAProxy + Keepalived on all nodes

Check status:

patronictl -c /etc/patroni.yml list
patronictl topology

9. Fault Drill Verification (Must Do!)

  1. Force kill the primary PostgreSQL process → switch completed in 10~18 seconds
  2. Shut down the primary network card → switch completed in 12~25 seconds
  3. Old primary automatically catches up with pg_rewind
  4. Manual switchover → 0 interruption

10. Common Pitfalls and Best Practices

Issue Cause Solution
Connection timeout after switchover HAProxy health check failed Check if port 8008 is accessible
Old primary does not automatically catch up pg_rewind requires primary_conninfo Patroni automatically writes .recovery file
etcd split-brain causing dual primary Network partition + long TTL Minimum 3 nodes, TTL ≤ 30s
Frequent switching jitter High machine load causing heartbeat timeout Increase loop_wait or optimize load

Related posts

  1. oVirt 4.4.3 Open Source Virtualization Platform: Installing Management Host CentOS 8.2 for Deploying oVirt Engine
  2. Forum Review | Pro Communication: Achieving Personnel and Asset Tracking with LoRaWAN Lightweight Infrastructure
  3. Rust DDD Practice: Attribute Macros Eliminate ID Value Object Boilerplate Code
  4. In-Depth Reading: Chapter 6 of ‘Linux Device Drivers’ – Advanced Character Drivers
  5. Linux Kernel Parameter Tuning
  6. Virtualization Scenarios in Government Procurement Cloud System
  7. Introduction to the Automation Operations Tool Ansible Role
  8. Founder Technology (600601): The AI Computing Power Breakthrough and Valuation Game of a PCB Veteran
  9. Python Statement Structures
  10. Accelerated Return of Overseas Talent: Intel Chip Architect Su Fei Returns to Tsinghua University After 20 Years in the U.S.
  11. Embedded Innovation Project Workshop: Become a Relentless Traveler
  12. Day 4 of Python: Mastering 3 New Containers and Advanced Loops
  13. Introduction to Mainstream Virtualization Technologies
  14. Digital Trade is Pushing the Renminbi to the Global Center Stage: Should the Dollar Be Worried?
  15. Handwritten Smart Pointer: Understanding the Underlying Logic of C++ Memory Management
  16. Understanding the Linux Directory Structure
  17. Linux Vulnerability Risk Assessment: A Decision Guide from Detection to Classification
  18. How to Read MOESI State and Tag Information in Cache Line on ARMv8
  19. The Top Five Linux Distributions for Beginners in 2025

Leave a Comment