Chuyển đến nội dung chính
BlogCoursesHL7Cert PrepRoadmapGameDomainAboutMCPNEW
Topics
DatabaseDevOpsKiến trúc hệ thốngLinuxProgrammingSecurity

Lesson 1: Overview of PostgreSQL High Availability

Learn why High Availability is needed, compare popular HA solutions (Patroni, Repmgr, Pacemaker) and master the overall architecture of the PostgreSQL HA system.

Lesson Objectives

After this lesson, you will:

  • Understand why High Availability (HA) is important for database systems_
  • Master HA implementation methods for PostgreSQL_
  • Compare the advantages and disadvantages of Patroni, Repmgr and Pacemaker
  • Understand the overall architecture of the PostgreSQL system HA

1. Why do we need High Availability?

1.1. Problem with Single Point of Failure (SPOF)

In a traditional database system with single server:

Single Point of Failure (SPOF)

Consequences when the database server crashes error:

  • Downtime: Application cannot access data
  • Revenue loss: Every possible minute of downtime costs millions of dong
  • Loss of reputation: Users cannot use the service
  • Data loss: If there is no timely backup time

1.2. Common causes of downtime

Cause__HTMLTAG_57___ Rate Impact
Hardware error (disk, RAM, CPU) 30% High
Network error 20% Average
Software errors/bug__HTMLTAG_83___ 25% High
Maintenance has a plan__HTMLTAG_91___ 15% Controllable
Human error__HTMLTAG_99___ 10% High

1.3. What is High Availability?

High Availability (HA) is the ability of a system to maintain continuous operation even when one or more components fail.

Measurement indicators HA:

Availability Downtime/year Downtime/month Level
99% (2 nines) 3.65 days 7.2 hours Low
99.9% (3 nines) 8.76 hours 43.2 minutes Average
99.99% (4 nines) 52.56 minutes__HTMLTAG_157___ 4.32 minutes__HTMLTAG_159___ High
99.999% (5 nines) 5.26 minutes 25.9 seconds Very high

1.4. Benefits of HA

Business Benefits:

  • Minimize downtime and lost revenue_
  • Increase system reliability system_
  • Improve user experience_
  • Meet SLA (Service Level Agreement)_

Technical Benefits:_

  • Automatic failover when the primary server has a problem__HTMLTAG_198___
  • _Zero-downtime maintenance_
  • _Load for read queries
  • Disaster recovery
  • Data protection

2. HA methods for PostgreSQL

2.1. Log-Shipping (WAL Shipping)_

_Log-Shipping (WAL Shipping)

How it works:

  • Primary server writes WAL (Write-Ahead Log) files
  • WAL files are copied to the standby server
  • Standby server replays WAL to synchronize data

Advantages Points:

  • Simple, easy to set up
  • Less resource consuming original

Disadvantages:

  • Recovery Time Objective (RTO) high (minutes → hour)
  • No automatic failover
  • Data loss may occur__HTMLTAG_252___
  • Standby cannot be queried (warm standby)

2.2. Streaming Replication

How it works:

  • Primary stream WAL records realtime to standby_
  • Standby apply changes immediately
  • Standby can serve read queries (hot standby)
Log-Shipping (WAL Shipping)

Advantages:

  • Low Latency (< 1 second)_
  • Hot standby can serve read queries
  • Synchronous mode reduces data loss__HTMLTAG_287___

_Disadvantages point:_

  • Still need manual failover_
  • Need external tool for automation_

2.3. Logical Replication

How it works:

  • Replicate at logical level (tables, rows)
  • Enable selective replication data
  • Publisher → Subscriber model

Pros point:

  • Replication between different PostgreSQL versions
  • _Selective replication (some tables only)_
  • _Multi-master possible (with BDR)

Disadvantages:_

  • Higher overhead than physical replication
  • None Main HA solution (usually used for data distribution)

2.4. Shared Storage (SAN)

Shared Storage (SAN)

Advantages:

  • Failover is fast (just start PostgreSQL)_
  • No data loss_

Disadvantages:

  • Expensive (needs SAN infrastructure)
  • SAN becomes single point of failure
  • Complicated to maintain

3. Comparison: Patroni vs Repmgr vs Pacemaker

3.1. Patroni_

Features:_

  • _Python-based_
  • Using DCS (etcd, Consul, ZooKeeper) to save cluster state_
  • REST API for management
  • Automatic smart failover_
  • Template-based configuration

Advantages:

  • ✅ Easy to install and configure image
  • ✅ Powerful REST API
  • ✅ Good integration with Kubernetes_
  • ✅ Active development, large community__HTMLTAG_399___
  • ✅ Automatic leader election
  • ✅ Rolling restart, zero-downtime updates

Disadvantages points:

  • ❌ Depends on DCS (add component)
  • ❌ Need to learn DCS (etcd/Consul)

Use suitable cases:

  • Cloud-native applications
  • Kubernetes deployments
  • Microservices architecture
  • Need high automation

3.2. Repmgr

Features:

  • Open-source tool from 2ndQuadrant (EnterpriseDB)
  • Standalone tool, no need for DCS
  • Witness node for quorum voting_
  • Command-line based management

Advantages:

  • ✅ No need for external DCS addition
  • ✅ Simpler than Patroni
  • ✅ Good Documentation
  • ✅ Mature and stable

Disadvantages:

  • ❌ Fewer automation features Patroni
  • ❌ No REST API
  • ❌ Smaller community
  • ❌ Complex failover more

Use suitable cases:

  • Traditional infrastructure
  • Single Simple, few nodes
  • Don't want to add DCS

3.3. Pacemaker + Corosync_

Features:_

  • High Availability cluster framework (Linux-HA)
  • Management many types of resources, not just PostgreSQL
  • Voting quorum mechanism
  • Fencing/STONITH to avoid split-brain_

Pros score:

  • ✅ Mature, production-proven (20+ years)
  • ✅ Manage many services (PostgreSQL, web server, etc.)
  • ✅ Powerful Fencing mechanism
  • ✅ Supports shared storage

Disadvantages Points:_

  • ❌ Very complicated to setup and maintain_
  • ❌ High learning curve_
  • ❌ Difficult XML configuration read
  • ❌ Debugging is difficult

Use cases are suitable Case:

  • Enterprise environment_
  • Need to manage many services
  • Has shared storage (SAN)
  • Team has experience with Pacemaker

3.4. Overview comparison table

Criteria Patroni Repmgr Pacemaker
Complexity Average Low High
Learning curve Average Low Very high
Setup time Nhanh Nhanh Slow
Automatic failover ✅ Excellent ✅ Good ✅ Excellent
REST API ✅ Yes ❌ No ❌ No
Kubernetes support ✅ Excellent ⚠️ Limited ❌ No
Community ⭐⭐⭐⭐⭐ ⭐⭐⭐ ⭐⭐⭐⭐
Documentation ⭐⭐⭐⭐⭐ ⭐⭐⭐⭐ ⭐⭐⭐
Dependencies_ DCS (etcd/Consul) None None
Best for Modern/Cloud Simple setups Enterprise/Complex

3.5. Recommended

Choose Patroni if:

  • Deploy on cloud or Kubernetes_
  • Need automation and REST API
  • Team has experience with modern DevOps tools
  • ✅ This is the most popular option currently nay

_Select Repmgr if:

  • Simple setup, few nodes (2-3)
  • Don't want to depend on DCS
  • Team is familiar with PostgreSQL traditional tools_

Choose Pacemaker if:

  • Complicated enterprise environment
  • Pacemaker infrastructure already available
  • Need to manage many services at the same time_
  • Shared storage available (SAN)

4. System overview architecture Patroni + etcd

4.1. 3-node cluster architecture

System overview architecture Patroni + etcd

4.2. Main components_

PostgreSQL

  • Database main engine_
  • One node is the Leader (read/write)
  • Other nodes are Replica (read-only)
  • Use Streaming Replication to sync set

Patroni

  • PostgreSQL lifecycle management_
  • _Monitor health of nodes_
  • Perform automatic failover_
  • Expose REST API (:8008) to query cluster state_
  • Read/write configuration to DCS

etcd (DCS - Distributed Configuration Store)

  • Storing cluster state and configuration
  • Leader election (decide which node is the Leader)
  • Distributed lock mechanism
  • 3 nodes etcd form the quorum (majority voting)_

HAProxy (optional but recommended)_

  • _Load balancer
  • Route write traffic → Leader
  • Route read traffic → Replicas (round-robin)
  • Health check and automatically route when failover

4.3. Activity flow

1. Normal Operations

1. Application gửi query → HAProxy
2. HAProxy kiểm tra health check
3. Route write → Leader, read → Replicas
4. Patroni trên mỗi node:
   - Gửi heartbeat vào etcd mỗi 10s
   - Update health status
   - Maintain leader lease

2. Leader Failure Detection

1. Node 1 (Leader) gặp sự cố → stop heartbeat
2. etcd phát hiện: leader lease expired (30s)
3. Patroni trên Node 2 và Node 3 nhận ra
4. Leader election được trigger

3. Automatic Failover Process

Timeline: 0s  ──────────► 30s ──────► 45s ──────► 60s
          │              │           │            │
      Leader dies    etcd detects  New leader  Applications
                     lease expire   elected     reconnect
                                   (Node 2)

Node 1:   LEADER ──────► DOWN ──────────────────► STANDBY (sau khi recover)
Node 2:   REPLICA ─────────────────► LEADER ────► LEADER
Node 3:   REPLICA ──────────────────────────────► REPLICA

4. After Failover

- Node 2 trở thành Leader mới


    

  • Node 3 vẫn là Replica, đổi replication source sang Node 2
    • 

  • HAProxy tự động detect và route traffic sang Node 2
    • 

  • Node 1 (khi recover) sẽ join lại như Replica

4.4. Important scenarios

Scenario 1: Planned Switchover

# Admin muốn maintenance Node 1 (Leader)
$ patronictl switchover postgres-cluster


Patroni sẽ:


    

  1. Tạm dừng ghi vào Leader hiện tại
    2. 

  2. Đợi Replica sync hoàn toàn (zero lag)
    3. 

  3. Promote Replica → Leader
    4. 

  4. Demote Leader cũ → Replica
    5. 

  5. Zero data loss, downtime < 5s

Scenario 2: Split-brain Prevention_

Tình huống: Network partition giữa nodes


etcd quorum (3 nodes):


    

  • Partition A: Node 1, Node 2 (2 nodes = majority)
    • 

  • Partition B: Node 3 (1 node = minority)
    • 



Kết quả:
✅ Partition A: Tiếp tục hoạt động, có thể elect leader
❌ Partition B: Không thể elect leader (không đủ quorum)


→ Tránh được 2 leaders cùng tồn tại!

Scenario 3: Node Recovery

Node 1 recover sau khi die:


    

  1. Patroni start và đọc cluster state từ etcd
    2. 

  2. Nhận ra Node 2 đang là Leader
    3. 

  3. Tự động rejoin như Replica
    4. 

  4. Sử dụng pg_rewind để sync data nếu có divergence
    5. 

  5. Bắt đầu streaming replication từ Node 2

4.5. Timeline configuration (important parameters)

# patroni.yml
bootstrap:
dcs:
ttl: 30                    # Leader lease time (30s)
loop_wait: 10              # Check interval (10s)
retry_timeout: 10          # Retry time
maximum_lag_on_failover: 1048576  # Max lag for failover candidate (1MB)

Explanation:

  • ttl: 30: Leader must renewlease every 30 seconds, otherwise it will be considered dead
  • loop_wait: 10: Patroni checks health every 10s
  • Failover trigger: when (ttl - loop_wait) ends → ~20-30s

5. Summary_

Key Takeaways_

  1. High Availability is mandatory__HTMLTAG_857___ for production systems to reduce downtime and data loss whether
  2. Streaming Replication + Automatic Failover is the most popular HA method for PostgreSQL_
  3. Patroni is the best choice__HTMLTAG_865___ for most use cases out there modern:
    • Easy to setup and maintain
    • Automatic smart failover__HTMLTAG_870__
    • REST powerful API__HTMLTAG_872__
    • Good integration with cloud/K8s
  4. 3-node architecture with Patroni + etcd provides:
    • Automatic failover (RTO < 30s)
    • Zero data loss with sync replication
    • Split-brain prevention
    • Scalability for read workloads

Homework

  1. Calculate downtime for your system with different availability levels (99%, 99.9%, 99.99%)
  2. Draw the HA architecture for your specific use case (number of nodes, data centers, RTO/RPO requirements)
  3. Compare the costs between using HA and accepting downtime for your business you

Preparing for the next lesson

Lesson 2 will delve into Streaming Replication - the foundation of PostgreSQL HA:

  • Detailed operating mechanism of WAL
  • Synchronous vs Asynchronous replication
  • Replication slots_
  • Lab: Manual replication setup_

References_