CloudNativePG: production PostgreSQL in Kubernetes

Published: 2026-03-23

Running PostgreSQL in Kubernetes used to mean StatefulSets, manual failover, and custom backup scripts. CloudNativePG (CNPG) is a Kubernetes operator that handles streaming replication, automatic failover, WAL archival, scheduled backups, and point-in-time recovery declaratively. One CRD to define a cluster.

Install the operator

yamlapiVersion: helm.toolkit.fluxcd.io/v2
kind: HelmRelease
metadata:
  name: cloudnative-pg
  namespace: cnpg-system
spec:
  chart:
    spec:
      chart: cloudnative-pg
      version: "0.x"
      sourceRef:
        kind: HelmRepository
        name: cnpg
        namespace: flux-system
  values:
    monitoring:
      podMonitorEnabled: true
      grafanaDashboard:
        create: true
        namespace: monitoring

Define a cluster

yamlapiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-main
  namespace: app
spec:
  instances: 3
  imageName: ghcr.io/cloudnative-pg/postgresql:16

  primaryUpdateStrategy: unsupervised

  storage:
    size: 20Gi
    storageClass: local-path

  resources:
    requests:
      cpu: 500m
      memory: 1Gi
    limits:
      cpu: 2000m
      memory: 2Gi

  postgresql:
    parameters:
      max_connections: "200"
      shared_buffers: "256MB"
      effective_cache_size: "768MB"
      work_mem: "4MB"
      maintenance_work_mem: "64MB"
      wal_level: logical
    pg_hba:
      - host all all 10.0.0.0/8 scram-sha-256

  bootstrap:
    initdb:
      database: myapp
      owner: myapp
      secret:
        name: pg-main-app-secret

  backup:
    retentionPolicy: "30d"
    barmanObjectStore:
      destinationPath: s3://pg-backups/pg-main
      endpointURL: https://storage.yandexcloud.net
      s3Credentials:
        accessKeyId:
          name: pg-s3-secret
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: pg-s3-secret
          key: SECRET_ACCESS_KEY
      wal:
        compression: gzip
      data:
        compression: gzip

  monitoring:
    enablePodMonitor: true

instances: 3 = 1 primary + 2 replicas. CNPG manages streaming replication automatically. If the primary fails, CNPG promotes the most up-to-date replica within ~30 seconds.

Scheduled backups

yamlapiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: pg-main-daily
  namespace: app
spec:
  schedule: "0 2 * * *"
  cluster:
    name: pg-main
  backupOwnerReference: self

CNPG streams WAL to S3 continuously. The daily backup is the base backup. Together they enable point-in-time recovery to any second within the retention window.

Point-in-time recovery

yamlapiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-main-restored
  namespace: app
spec:
  instances: 1
  bootstrap:
    recovery:
      source: pg-main
      recoveryTarget:
        targetTime: "2026-10-17 14:30:00"
  externalClusters:
    - name: pg-main
      barmanObjectStore:
        destinationPath: s3://pg-backups/pg-main
        endpointURL: https://storage.yandexcloud.net
        s3Credentials:
          accessKeyId:
            name: pg-s3-secret
            key: ACCESS_KEY_ID
          secretAccessKey:
            name: pg-s3-secret
            key: SECRET_ACCESS_KEY

Use a different cluster name to avoid conflicting with production. CNPG replays WAL up to targetTime.

PgBouncer connection pooling

yamlapiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: pg-main-pooler
  namespace: app
spec:
  cluster:
    name: pg-main
  instances: 2
  type: rw
  pgbouncer:
    poolMode: transaction
    parameters:
      max_client_conn: "1000"
      default_pool_size: "25"

type: rw routes to the primary. Use type: ro for a read-only pooler routing to replicas. Transaction pooling mode allows many clients with few backend connections.

Connecting from an application

CNPG creates a Service for each cluster:

pg-main-rw — primary (read-write)
pg-main-ro — replicas (read-only)
pg-main-r — all instances

Application connection string:

postgresql://myapp:password@pg-main-rw.app.svc.cluster.local:5432/myapp

With PgBouncer:

postgresql://myapp:password@pg-main-pooler.app.svc.cluster.local:5432/myapp

Useful kubectl commands

bash# Cluster status
kubectl cnpg status pg-main -n app

# List backups
kubectl get backup -n app

# Manual base backup now
kubectl cnpg backup pg-main -n app

# Promote a standby (for testing failover)
kubectl cnpg promote pg-main pg-main-2 -n app

# Check WAL archiving
kubectl cnpg status pg-main -n app | grep -A5 "WAL Archiving"

# Hibernate cluster (stop all pods, keep PVCs)
kubectl cnpg hibernate pg-main -n app

Troubleshooting

WAL archiving failing: Check S3 credentials and bucket permissions. Run kubectl cnpg status pg-main -n app to see WAL archiving errors.

Replica lagging: Check pg_replication_slots on the primary. Stuck slots block WAL cleanup and can fill the disk. Drop them with pg_drop_replication_slot if stale.

Failover didn't happen: CNPG needs quorum. With 3 instances, the primary is considered failed if at least 2 replicas report it gone. Check Pod health and node connectivity.

weblog