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:

• ✅ Install CloudNativePG Operator
• ✅ Create PostgreSQL cluster 3 instances
• ✅ Configure PgBouncer connection pooling__HTMLTAG_77___
• ✅ Custom postgresql.conf parameters
• ✅ Verify replication and connectivity__HTMLTAG_81___

---

PART 1: INSTALL CLOUDNATIVEPG OPERATOR

1.1. Helm Install

# Add CloudNativePG Helm repo:
helm repo add cnpg https://cloudnative-pg.github.io/charts
helm repo update
Install operator:
helm install cnpg cnpg/cloudnative-pg 

--namespace cnpg-system 

--create-namespace 

--version 0.22.1 

--set monitoring.podMonitorEnabled=true
Verify:
kubectl -n cnpg-system get pods
NAME                                     READY   STATUS    RESTARTS   AGE
cnpg-cloudnative-pg-xxxxx-xxxxx          1/1     Running   0          30s
Verify CRDs:
kubectl get crd | grep cnpg
backups.postgresql.cnpg.io
clusters.postgresql.cnpg.io
poolers.postgresql.cnpg.io
scheduledbackups.postgresql.cnpg.io

---

PART 2: CREATE POSTGRESQL CLUSTER

2.1. Cluster CRD

# pg-cluster.yaml:
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: production-pg
  namespace: database
spec:
  instances: 3                           # 1 Primary + 2 Standbys
imageName: ghcr.io/cloudnative-pg/postgresql:16.4
PostgreSQL configuration:
postgresql:
parameters:
# Memory:
shared_buffers: "1GB"
effective_cache_size: "3GB"
work_mem: "64MB"
maintenance_work_mem: "256MB"
  # WAL:
  wal_buffers: "64MB"
  min_wal_size: "1GB"
  max_wal_size: "4GB"
  checkpoint_completion_target: "0.9"
  
  # Replication:
  max_connections: "200"
  max_replication_slots: "10"
  max_wal_senders: "10"
  
  # Performance (SSD):
  random_page_cost: "1.1"
  effective_io_concurrency: "200"
  
  # Logging:
  log_min_duration_statement: "1000"    # Log queries > 1s
  log_checkpoints: "on"
  log_connections: "on"
  log_disconnections: "on"
  log_lock_waits: "on"
  log_temp_files: "0"
  
  # Security:
  password_encryption: "scram-sha-256"

pg_hba:
  - host all all 10.244.0.0/16 scram-sha-256    # Pod network
  - host all all 10.96.0.0/12 scram-sha-256      # Service network

Storage:
storage:
storageClass: ceph-block
size: 50Gi
walStorage:
storageClass: ceph-block
size: 10Gi
Resources:
resources:
requests:
cpu: "1"
memory: "4Gi"
limits:
cpu: "4"
memory: "8Gi"
Affinity — spread across nodes:
affinity:
enablePodAntiAffinity: true
topologyKey: kubernetes.io/hostname
Monitoring:
monitoring:
enablePodMonitor: true
Superuser secret:
superuserSecret:
name: pg-superuser-secret
Bootstrap (tạo DB và user):
bootstrap:
initdb:
database: appdb
owner: appuser
secret:
name: pg-app-secret
postInitSQL:
- CREATE EXTENSION IF NOT EXISTS pg_stat_statements
- CREATE EXTENSION IF NOT EXISTS pgcrypto

2.2. Create Secrets

# Tạo namespace:
kubectl create namespace database
Superuser secret:
kubectl -n database create secret generic pg-superuser-secret 

--from-literal=username=postgres 

--from-literal=password="$(openssl rand -base64 24)"
App user secret:
kubectl -n database create secret generic pg-app-secret 

--from-literal=username=appuser 

--from-literal=password="$(openssl rand -base64 24)"

2.3. Deploy Cluster

kubectl apply -f pg-cluster.yaml
Monitor deployment:
kubectl -n database get pods -w
NAME               READY   STATUS    RESTARTS   AGE
production-pg-1    1/1     Running   0          2m    ← Primary
production-pg-2    1/1     Running   0          90s   ← Standby
production-pg-3    1/1     Running   0          60s   ← Standby
Check cluster status:
kubectl -n database get cluster production-pg
NAME            AGE   INSTANCES   READY   STATUS                  PRIMARY
production-pg   5m    3           3       Cluster in healthy state production-pg-1

---

PART 3: VERIFY REPLICATION__HTMLTAG_99___

3.1. Check Replication Status

# Dùng cnpg plugin:
kubectl cnpg status production-pg -n database
# Cluster Summary:
# Name:              production-pg
# Namespace:         database
# PostgreSQL:        16.4
# Primary instance:  production-pg-1
# Status:            Cluster in healthy state
# Instances:         3
# Ready instances:   3
#
# Instances status:
# Name               Database Size  Current LSN  Rep role   Status  Node
# ----               -------------  -----------  --------   ------  ----
# production-pg-1    25 MB          0/5000060    Primary    OK      worker1
# production-pg-2    25 MB          0/5000060    Standby    OK      worker2
# production-pg-3    25 MB          0/5000060    Standby    OK      worker3
Verify replication on Primary:
kubectl -n database exec production-pg-1 -- psql -U postgres -c 

"SELECT * FROM pg_stat_replication;"
pid  | usename  | application_name | client_addr    | state     | sync_state
-----+----------+------------------+----------------+-----------+-----------
1234 | postgres | production-pg-2  | 10.244.2.5     | streaming | async
1235 | postgres | production-pg-3  | 10.244.3.5     | streaming | async

3.2. Test Data Replication

# Write on Primary:
kubectl -n database exec production-pg-1 -- psql -U appuser -d appdb -c \
  "CREATE TABLE test (id serial PRIMARY KEY, data text, created_at timestamp DEFAULT now());
   INSERT INTO test (data) VALUES ('hello from primary');"
Read on Standby:
kubectl -n database exec production-pg-2 -- psql -U appuser -d appdb -c 

"SELECT * FROM test;"
id |        data        |         created_at
---+--------------------+----------------------------
1 | hello from primary | 2025-04-02 07:00:00.123456
✅ Data replicated!

---

PART 4: SERVICES

# CloudNativePG tự tạo Services:
kubectl -n database get svc
# NAME                  TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)
# production-pg-rw      ClusterIP   10.96.xxx.xx    <none>        5432/TCP   ← Primary (read-write)
# production-pg-ro      ClusterIP   10.96.xxx.xx    <none>        5432/TCP   ← Standbys (read-only)
# production-pg-r       ClusterIP   10.96.xxx.xx    <none>        5432/TCP   ← Any (read)

# Connection strings:
# Write: postgresql://appuser:[email protected]:5432/appdb
# Read:  postgresql://appuser:[email protected]:5432/appdb

---

PART 5: PGBOUNCER CONNECTION POOLING

# pgbouncer.yaml:
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
  type: rw                              # read-write pooler
  pgbouncer:
    poolMode: transaction               # Transaction pooling
    parameters:
      max_client_conn: "1000"
      default_pool_size: "50"
      min_pool_size: "10"
      reserve_pool_size: "10"
      reserve_pool_timeout: "5"
      max_db_connections: "100"
      max_user_connections: "100"
      server_reset_query: "DISCARD ALL"
      log_connections: "1"
      log_disconnections: "1"
      stats_period: "60"

---
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: production-pg-pooler-ro
  namespace: database
spec:
  cluster:
    name: production-pg
  instances: 2
  type: ro                              # read-only pooler
  pgbouncer:
    poolMode: transaction
    parameters:
      max_client_conn: "2000"
      default_pool_size: "100"

kubectl apply -f pgbouncer.yaml

# Verify PgBouncer pods:
kubectl -n database get pods -l cnpg.io/poolerName
# NAME                                           READY   STATUS
# production-pg-pooler-rw-xxxxx-xxxxx            1/1     Running
# production-pg-pooler-rw-xxxxx-xxxxx            1/1     Running
# production-pg-pooler-ro-xxxxx-xxxxx            1/1     Running
# production-pg-pooler-ro-xxxxx-xxxxx            1/1     Running

# Connection strings via PgBouncer:
# Write: production-pg-pooler-rw.database:5432
# Read:  production-pg-pooler-ro.database:5432

---

PART 6: TEST CONNECTIVITY

# Deploy psql client pod:
kubectl -n database run pg-client --rm -it --image=postgres:16-alpine -- bash

# Connect via rw service:
psql "host=production-pg-rw.database dbname=appdb user=appuser"
# appdb=> \conninfo
# You are connected to database "appdb" as user "appuser"

# Connect via PgBouncer:
psql "host=production-pg-pooler-rw.database dbname=appdb user=appuser"
# appdb=>

# Test write → read:
# On rw: INSERT INTO test (data) VALUES ('via pgbouncer');
# On ro: SELECT * FROM test;

---

💡 KEY TAKEAWAYS

1. CloudNativePG operator: single CRD creates complete PG HA cluster
2. 3 instances: 1 primary + 2 standby, anti-affinity spread across nodes
3. Ceph RBD for storage + separate WAL storage for performance
4. 3 services: rw (primary), ro (standbys), r (any)
5. PgBouncer Pooler: transaction pooling, 50 real connections serve 1000 clients
6. postInitSQL: auto-create extensions, users, schemas at bootstrap

---

🎯 EXERCISES__HTMLTAG_144___

Exercise 1: Deploy PostgreSQL HA

• Install CloudNativePG Operator
• Create 3-instance cluster with Ceph storage
• Verify replication with pg_stat_replication__HTMLTAG_153___

Exercise 2: PgBouncer

• Deploy PgBouncer Pooler (rw + ro)
• Connect via PgBouncer, verify query routing

---

📚 NEXT POST

In Lesson 18: PostgreSQL Backup, PITR and Disaster Recovery, we will set up automated backup and point-in-time recovery.