xDev Asia

So sánh Patroni vs CloudNativePG cho PostgreSQL HA trên K8s, kiến trúc streaming replication, synchronous vs asynchronous, failover mechanisms và connection pooling.

Deploy Microservices On-Premises với Kubernetes HA

Phần 4: PostgreSQL HA với Patroni & CloudNativePG

xdev.asia

🎯 MỤC TIÊU BÀI HỌC

Sau khi hoàn thành bài học này, bạn sẽ:

✅ Hiểu PostgreSQL streaming replication
✅ So sánh Patroni vs CloudNativePG vs PGO (CrunchyData)
✅ Hiểu synchronous vs asynchronous replication
✅ Kiến trúc HA: primary-standby, failover, fencing
✅ Connection pooling với PgBouncer

PHẦN 1: POSTGRESQL REPLICATION

1.1. Streaming Replication

graph TD
    APP["🖥️ APPLICATION"] --> PGB["🔀 PgBouncer<br/>port 6432<br/>Connection Pool"]
    
    PGB -->|"write (rw)"| PRI["🟢 PRIMARY<br/>read/write<br/>pg1"]
    PGB -->|"read (ro)"| STB1["🔵 STANDBY<br/>read-only<br/>pg2"]
    PGB -->|"read (ro)"| STB2["🔵 STANDBY<br/>read-only<br/>pg3"]
    
    PRI -->|"WAL streaming"| STB1
    PRI -->|"WAL streaming"| STB2

    style APP fill:#0f172a,stroke:#3b82f6,color:#e2e8f0
    style PGB fill:#7c3aed,stroke:#a78bfa,color:#e2e8f0
    style PRI fill:#15803d,stroke:#22c55e,color:#e2e8f0
    style STB1 fill:#1e3a5f,stroke:#3b82f6,color:#e2e8f0
    style STB2 fill:#1e3a5f,stroke:#3b82f6,color:#e2e8f0

✅ Primary: nhận writes, stream WAL tới standbys ✅ Standby: replay WAL, serve read queries ✅ Failover: promote standby thành primary

1.2. Synchronous vs Asynchronous

Mode	Synchronous	Asynchronous
Data safety	Zero data loss (RPO=0)	Potential data loss
Write latency	Higher (wait for standby ACK)	Lower (don't wait)
Throughput	Lower	Higher
Network dependency	Strong (latency affects writes)	Weak
Best for	Financial, critical data	Most applications

PHẦN 2: SO SÁNH POSTGRESQL OPERATORS

Tiêu chí	CloudNativePG	Patroni (Zalando)	PGO (CrunchyData)
Architecture	K8s-native operator	Sidecar + DCS	K8s operator
Failover	K8s controller	Raft-like via DCS	K8s controller
DCS dependency	❌ Không cần (dùng K8s)	✅ Cần etcd/Consul/K8s	❌ Không cần
Backup	Barman (S3/local)	WAL-G, pgBackRest	pgBackRest
Connection pooling	PgBouncer built-in	Cần setup riêng	PgBouncer built-in
CNCF	Sandbox	Community	Community
License	Apache 2.0	MIT	Apache 2.0
Complexity	Thấp	Trung bình	Trung bình

👉 Chọn CloudNativePG: K8s-native, không cần external DCS, CNCF project, backup tích hợp, đơn giản hơn Patroni trên K8s.

PHẦN 3: CLOUDNATIVEPG ARCHITECTURE

graph TB
    subgraph K8S["☸ Kubernetes Cluster"]
        CNPG["🔧 CloudNativePG Operator<br/>Watches Cluster CRD<br/>Handles failover, backup, recovery"]

        subgraph CLUSTER["📦 Cluster CRD: production-pg"]
            PG1["🟢 Pod pg-1<br/>PRIMARY<br/>rw svc<br/>PVC 50Gi ceph-blk"]
            PG2["🔵 Pod pg-2<br/>STANDBY<br/>ro svc<br/>PVC 50Gi ceph-blk"]
            PG3["🔵 Pod pg-3<br/>STANDBY<br/>ro svc<br/>PVC 50Gi ceph-blk"]
        end

        subgraph SVC["🌐 Services"]
            RW["production-pg-rw → Primary"]
            RO["production-pg-ro → Standbys"]
            R["production-pg-r → Any instance"]
        end

        subgraph BACKUP["💾 Backup"]
            BK["ScheduledBackup CRD<br/>→ Barman → S3/Ceph"]
        end
    end

    CNPG -->|manages| CLUSTER
    RW --> PG1
    RO --> PG2
    RO --> PG3
    PG1 -.->|WAL| PG2
    PG1 -.->|WAL| PG3

    style K8S fill:#0f172a,stroke:#3b82f6,color:#e2e8f0
    style CNPG fill:#7c3aed,stroke:#a78bfa,color:#e2e8f0
    style CLUSTER fill:#1e293b,stroke:#3b82f6,color:#e2e8f0
    style SVC fill:#1e3a5f,stroke:#60a5fa,color:#e2e8f0
    style BACKUP fill:#15803d,stroke:#22c55e,color:#e2e8f0
    style PG1 fill:#15803d,stroke:#22c55e,color:#e2e8f0
    style PG2 fill:#1e3a5f,stroke:#3b82f6,color:#e2e8f0
    style PG3 fill:#1e3a5f,stroke:#3b82f6,color:#e2e8f0

3.1. Failover Flow

sequenceDiagram
    participant PG1 as pg-1 (PRIMARY)
    participant OP as CloudNativePG Operator
    participant PG2 as pg-2 (STANDBY)
    participant PG3 as pg-3 (STANDBY)
    participant SVC as Service rw

    PG1->>PG1: ❌ CRASH!
    OP->>OP: Health check fails
    OP->>OP: Select standby with<br/>highest LSN
    OP->>PG2: 🔼 PROMOTE to PRIMARY
    PG2->>PG2: pg_promote()
    OP->>PG3: Repoint replication → pg-2
    PG3->>PG2: WAL streaming resumed
    OP->>SVC: Update endpoint → pg-2
    Note over PG1,SVC: ⚡ Failover: 5-30 giây
    PG1->>PG1: Restart
    PG1->>PG2: Join as STANDBY

PHẦN 4: CONNECTION POOLING — PGBOUNCER


Tại sao cần PgBouncer?

Không có PgBouncer:
App (1000 connections) → PostgreSQL (1000 processes!)
→ Memory: 1000 × 10MB = 10GB
→ Context switching overhead
→ Performance drop

Có PgBouncer:
App (1000 connections) → PgBouncer (pool 50 connections) → PostgreSQL (50 processes)
→ Memory: 50 × 10MB = 500MB
→ 20× ít processes
→ Better performance

PgBouncer modes:
- session:      1:1 mapping (least pooling)
- transaction:  Release after each transaction (recommended)
- statement:    Release after each statement (most aggressive)

PHẦN 5: STORAGE CONSIDERATIONS

# PostgreSQL trên Ceph RBD:
# ✅ PVC (ceph-block) cho data directory
# ✅ Separate PVC cho WAL (optional, higher IOPS)
# ⚠️ ext4 filesystem (CloudNativePG default)
# ⚠️ fsync = on (DO NOT disable!)

# PostgreSQL storage parameters:
# - shared_buffers: 25% RAM
# - effective_cache_size: 75% RAM
# - wal_buffers: 64MB
# - checkpoint_completion_target: 0.9
# - random_page_cost: 1.1 (SSD)
# - effective_io_concurrency: 200 (SSD)

💡 KEY TAKEAWAYS

CloudNativePG: K8s-native, không cần etcd/Consul, failover tự động
Streaming replication: Primary → Standby qua WAL streaming
Synchronous cho zero data loss, asynchronous cho throughput
PgBouncer: Connection pooling giảm 20× database processes
Ceph RBD (ReadWriteOnce) phù hợp cho database PV
3 services: rw (primary), ro (standbys), r (any instance)

🎯 BÀI TẬP

Bài tập 1: Research

Đọc CloudNativePG documentation
So sánh 3 operators: CNPG vs Patroni vs PGO
Quyết định sync vs async cho use case của bạn

📚 BÀI TIẾP THEO

Trong Bài 17: Deploy CloudNativePG Operator và PostgreSQL Cluster, chúng ta sẽ cài CloudNativePG và tạo PostgreSQL cluster 3 instances.

BÀI 16: KIẾN TRÚC POSTGRESQL HA VỚI PATRONI VÀ CLOUDNATIVEPG