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

• ✅ Setup pg_stat_statements cho query analysis
• ✅ Deploy Prometheus monitoring cho PostgreSQL
• ✅ Build Grafana dashboards cho PG metrics
• ✅ Vacuum tuning và autovacuum configuration
• ✅ Connection pooling optimization với PgBouncer
• ✅ Day-2 operations: scaling, minor upgrade, major upgrade

---

PHẦN 1: pg_stat_statements — QUERY ANALYSIS

1.1. Kích hoạt pg_stat_statements

# Đã enable trong Cluster CRD (Bài 17):
# postgresql:
#   shared_preload_libraries:
#     - "pg_stat_statements"
#   pg_stat_statements.max: "10000"
#   pg_stat_statements.track: "all"
Verify:
kubectl -n database exec production-pg-1 -- psql -U postgres -d appdb -c 

"CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
kubectl -n database exec production-pg-1 -- psql -U postgres -d appdb -c 

"SHOW shared_preload_libraries;"
shared_preload_libraries
pg_stat_statements

1.2. Top Slow Queries

-- Top 10 queries by total time:
SELECT 
  queryid,
  LEFT(query, 80) AS query_preview,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS mean_time_ms,
  round((100.0 * total_exec_time / 
    NULLIF(sum(total_exec_time) OVER(), 0))::numeric, 2) AS pct_total,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- Top I/O intensive queries:
SELECT
LEFT(query, 80),
calls,
shared_blks_read + shared_blks_hit AS total_blocks,
round(100.0 * shared_blks_hit /
NULLIF(shared_blks_read + shared_blks_hit, 0), 2) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;
-- Reset stats (periodic):
SELECT pg_stat_statements_reset();

---

PHẦN 2: PROMETHEUS MONITORING

2.1. CloudNativePG Built-in Metrics

# CloudNativePG tự expose metrics tại :9187/metrics
# Metrics chính:
cnpg_backends_total — Số connections hiện tại
cnpg_backends_waiting_total — Connections đang wait
cnpg_pg_database_size_bytes — Database size
cnpg_pg_stat_replication_lag — Replication lag bytes
cnpg_pg_postmaster_start_time — PG start time
cnpg_collector_up — Collector health
cnpg_pg_stat_user_tables_n_dead_tup — Dead tuples (vacuum)
cnpg_pg_stat_bgwriter_buffers_checkpoint — Checkpoint rate

2.2. PodMonitor cho Prometheus

apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: postgresql-monitor
  namespace: database
spec:
  selector:
    matchLabels:
      cnpg.io/cluster: production-pg
  podMetricsEndpoints:
    - port: metrics
      interval: 15s
      path: /metrics

2.3. Custom Metrics Queries

# Trong Cluster CRD thêm monitoring config:
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: production-pg
spec:
  monitoring:
    enablePodMonitor: true
    customQueriesConfigMap:
      - name: pg-custom-queries
        key: queries.yaml
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: pg-custom-queries
  namespace: database
data:
  queries.yaml: |
    pg_stat_statements:
      query: |
        SELECT
          queryid,
          calls,
          total_exec_time / 1000 as total_time_seconds,
          mean_exec_time / 1000 as mean_time_seconds,
          rows
        FROM pg_stat_statements
        WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user)
        ORDER BY total_exec_time DESC
        LIMIT 20
      metrics:
        - queryid:
            usage: "LABEL"
        - calls:
            usage: "COUNTER"
        - total_time_seconds:
            usage: "COUNTER"
        - mean_time_seconds:
            usage: "GAUGE"
        - rows:
            usage: "COUNTER"
pg_database_size:
  query: |
    SELECT datname, pg_database_size(datname) as size_bytes
    FROM pg_database WHERE datname NOT IN ('template0','template1')
  metrics:
    - datname:
        usage: "LABEL"
    - size_bytes:
        usage: "GAUGE"

pg_locks:
  query: |
    SELECT mode, count(*) as count
    FROM pg_locks GROUP BY mode
  metrics:
    - mode:
        usage: "LABEL"
    - count:
        usage: "GAUGE"

---

PHẦN 3: GRAFANA DASHBOARDS

3.1. Import Dashboard

# CloudNativePG official dashboard:
# Grafana ID: 20417
# URL: https://grafana.com/grafana/dashboards/20417
ConfigMap for auto-provisioning:
apiVersion: v1
kind: ConfigMap
metadata:
name: grafana-dashboard-cnpg
namespace: monitoring
labels:
grafana_dashboard: "true"
data:
cnpg-dashboard.json: |
{
"annotations": { "list": [] },
"title": "CloudNativePG",
"uid": "cnpg-overview",
"panels": [
{
"title": "Database Size",
"type": "stat",
"targets": [{
"expr": "cnpg_pg_database_size_bytes{datname="appdb"}"
}]
},
{
"title": "Active Connections",
"type": "timeseries",
"targets": [{
"expr": "cnpg_backends_total{datname="appdb"}"
}]
},
{
"title": "Replication Lag",
"type": "timeseries",
"targets": [{
"expr": "cnpg_pg_replication_lag{namespace="database"}"
}]
},
{
"title": "Transactions/sec",
"type": "timeseries",
"targets": [{
"expr": "rate(cnpg_pg_stat_database_xact_commit{datname="appdb"}[5m])"
}]
},
{
"title": "Dead Tuples (needs VACUUM)",
"type": "timeseries",
"targets": [{
"expr": "cnpg_pg_stat_user_tables_n_dead_tup{namespace="database"}"
}]
}
]
}

---

PHẦN 4: VACUUM TUNING

4.1. Autovacuum Configuration

# Trong Cluster CRD postgresql parameters:
postgresql:
  parameters:
    # Autovacuum settings:
    autovacuum: "on"
    autovacuum_max_workers: "4"           # Default: 3
    autovacuum_naptime: "30s"             # Check frequency
    autovacuum_vacuum_threshold: "50"     # Min rows changed
    autovacuum_vacuum_scale_factor: "0.05" # 5% of table (default 20%)
    autovacuum_analyze_threshold: "50"
    autovacuum_analyze_scale_factor: "0.025"
    autovacuum_vacuum_cost_delay: "2ms"   # I/O throttle
    autovacuum_vacuum_cost_limit: "400"   # More aggressive
# Prevent transaction ID wraparound:
autovacuum_freeze_max_age: "200000000"

4.2. Manual VACUUM cho bảng lớn

# Check tables cần vacuum:
kubectl -n database exec production-pg-1 -- psql -U postgres -d appdb -c \
  "SELECT 
    schemaname || '.' || relname AS table,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum,
    last_autoanalyze
  FROM pg_stat_user_tables
  ORDER BY n_dead_tup DESC
  LIMIT 10;"
Manual vacuum (non-blocking):
kubectl -n database exec production-pg-1 -- psql -U postgres -d appdb -c 

"VACUUM (VERBOSE, ANALYZE) large_table;"
For heavily bloated tables → VACUUM FULL (requires lock):
⚠️ Only during maintenance window!
kubectl -n database exec production-pg-1 -- psql -U postgres -d appdb -c 

"VACUUM FULL large_table;"

---

PHẦN 5: PGBOUNCER TUNING

# Pooler CRD optimization:
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: production-pg-pooler-rw
  namespace: database
spec:
  cluster:
    name: production-pg
  instances: 2                    # 2 PgBouncer pods (HA)
  type: rw
  pgbouncer:
    poolMode: transaction         # Best cho microservices
    parameters:
      default_pool_size: "25"     # Per user/db pair
      max_client_conn: "200"      # Total client connections
      reserve_pool_size: "5"      # Overflow pool
      reserve_pool_timeout: "3"   # Seconds before using reserve
      server_idle_timeout: "300"  # Close idle server conns
      query_wait_timeout: "60"    # Max wait for server conn
      log_connections: "1"
      log_disconnections: "1"
      stats_period: "30"

---

PHẦN 6: DAY-2 OPERATIONS

6.1. Scale Replicas

# Add replica (3 → 5):
kubectl -n database patch cluster production-pg --type merge \
  -p '{"spec":{"instances": 5}}'
Verify:
kubectl cnpg status production-pg -n database
Instances: 5
Ready: 5/5
Scale down (5 → 3):
kubectl -n database patch cluster production-pg --type merge 

-p '{"spec":{"instances": 3}}'

6.2. Minor Version Upgrade (e.g., 16.3 → 16.4)

# Update image tag:
kubectl -n database patch cluster production-pg --type merge \
  -p '{"spec":{"imageName":"ghcr.io/cloudnative-pg/postgresql:16.4"}}'
CloudNativePG performs rolling update:
1. Upgrade standby pods first (one by one)
2. Switchover primary → upgraded standby
3. Upgrade old primary
→ Zero downtime! ✅
Watch progress:
kubectl -n database get pods -w

6.3. Major Version Upgrade (e.g., 16 → 17)

# Major upgrade requires pg_upgrade or logical replication
# Strategy: Create new cluster + switchover
Step 1: Create PG 17 cluster:
(copy Cluster CRD, change image to PG 17)
Step 2: Setup logical replication:
kubectl -n database exec production-pg-1 -- psql -U postgres -d appdb -c 

"CREATE PUBLICATION app_pub FOR ALL TABLES;"
Step 3: On new cluster, create subscription:
kubectl -n database exec production-pg17-1 -- psql -U postgres -d appdb -c 

"CREATE SUBSCRIPTION app_sub
CONNECTION 'host=production-pg-rw.database port=5432 dbname=appdb user=postgres'
PUBLICATION app_pub;"
Step 4: Wait for sync, then switch app DNS
Step 5: Drop old cluster

---

💡 KEY TAKEAWAYS

1. pg_stat_statements: Essential cho query performance analysis
2. Prometheus + Grafana: Real-time monitoring, alerting on lag/connections
3. Vacuum tuning: Giảm scale_factor cho bảng lớn (5% thay vì 20%)
4. PgBouncer: transaction pooling, tune default_pool_size theo workload
5. Minor upgrade: Rolling update zero-downtime
6. Major upgrade: Logical replication strategy

---

🎯 BÀI TẬP

Bài tập 1: Performance Baseline

• Enable pg_stat_statements
• Run pgbench workload
• Identify top 5 slow queries
• Setup Grafana dashboard

Bài tập 2: Vacuum Lab

• Create table, INSERT 1M rows, DELETE 500K
• Observe dead tuples, trigger VACUUM
• Compare autovacuum vs manual VACUUM timing

---

📚 BÀI TIẾP THEO

Trong Bài 21: RabbitMQ HA Cluster trên Kubernetes, chúng ta sẽ deploy message queue HA cho microservices communication.