LESSON 16: POSTGRESQL HA ARCHITECTURE WITH PATRONI AND CLOUDNATIVEPG

Deploy Microservices On-Premises with Kubernetes HA

Part 4: PostgreSQL HA with Patroni & CloudNativePG

xdev.asia

🎯 LESSON OBJECTIVE__HTMLTAG_68___

After completing this lesson, you will:

• ✅ Understanding PostgreSQL streaming replication
• ✅ Compare Patroni vs CloudNativePG vs PGO (CrunchyData)
• ✅ Understanding synchronous vs asynchronous replication__HTMLTAG_77___
• ✅ HA architecture: primary-standby, failover, fencing
• ✅ Connection pooling with PgBouncer

---

PART 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: receive writes, stream WAL to standbys ✅ Standby: replay WAL, serve read queries ✅ Failover: promote standby to 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

---

PART 2: COMPARE POSTGRESQL OPERATORS

Criteria	CloudNativePG	Patroni (Zalando)	PGO (CrunchyData)
Architecture	K8s-native operator__HTMLTAG_168___	Sidecar + DCS	K8s operator
Failover	K8s controller	Raft-like via DCS	K8s controller
DCS dependency	❌ No need (using K8s)	✅ Need etcd/Consul/K8s	❌ No need
Backup	Barman (S3/local)	WAL-G, pgBackRest	pgBackRest
Connection pooling__HTMLTAG_206___	PgBouncer built-in	Need separate setup	PgBouncer built-in
CNCF	Sandbox	Community	Community
License	Apache 2.0	MIT	Apache 2.0
Complexity	Low	Average	Average

👉 Choose CloudNativePG: K8s-native, no need for external DCS, CNCF project, integrated backup, simpler than Patroni on K8s.

---

PART 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

---

PART 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)

---

PART 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

1. CloudNativePG: K8s-native, no need for etcd/Consul, automatic failover
2. Streaming replication: Primary → Standby via WAL streaming
3. Synchronous for zero data loss, asynchronous for throughput
4. PgBouncer: Connection pooling reduced by 20× database processes
5. Ceph RBD (ReadWriteOnce) suitable for database PV
6. 3 services: rw (primary), ro (standbys), r (any instance)

---

🎯 EXERCISES

Exercise 1: Research

• Read CloudNativePG documentation
• Compare 3 operators: CNPG vs Patroni vs PGO
• Decide sync vs async for your use case__HTMLTAG_304___

---

📚 NEXT POST

In Lesson 17: Deploy CloudNativePG Operator and PostgreSQL Cluster, we will install CloudNativePG and create PostgreSQL cluster 3 instances.