CloudNativePG PostgreSQL Stack

A production-ready PostgreSQL cluster for Kubernetes built with CloudNativePG. This project provides a complete database platform with high availability, automated secrets management, S3-compatible backups, and comprehensive monitoring.

Support the project:

---

Applications

Core Database

Application	Description
CloudNativePG	PostgreSQL operator for Kubernetes with HA, backups, and rolling updates
PostgreSQL 16.3	3-node cluster with replication and failover
PgBouncer	Connection pooler with session mode (1000 max connections)

Secrets Management

Application	Description
OpenBao	Vault-compatible secrets backend with Raft consensus
External Secrets Operator	Kubernetes-native secret synchronization
Secrets Store CSI Driver	Mount secrets as volumes in pods

Backup & Storage

Application	Description
RustFS	S3-compatible object storage for backups
Barman Cloud	PostgreSQL backup and recovery via CNPG plugin

Monitoring & Observability

Application	Description
Pigsty pg_exporter	Comprehensive PostgreSQL metrics exporter
VictoriaMetrics	Time-series database for metrics storage

---

Complete Guide

For detailed setup instructions, configuration examples, and deployment strategies:

Supercharge Postgres on K8s: A CNPG + PostgreSQL + Pigsty + Observability Guide

---

Architecture

                              ┌─────────────────────────────────────────────────────────┐
                              │                    Kubernetes Cluster                    │
                              │                                                          │
┌──────────────┐              │  ┌─────────────────┐      ┌─────────────────────────┐   │
│   Client     │──────────────┼──│    PgBouncer    │──────│   PostgreSQL Cluster    │   │
│ Application  │              │  │   (2 replicas)  │      │  (3 nodes: 1 primary,   │   │
└──────────────┘              │  └─────────────────┘      │     2 replicas)         │   │
                              │                           └───────────┬─────────────┘   │
                              │                                       │                 │
                              │  ┌─────────────────┐                  │                 │
                              │  │   OpenBao       │     Credentials  │   Backups       │
                              │  │  (3 replicas)   │◄─────────────────┤                 │
                              │  │   Raft HA       │                  │                 │
                              │  └────────┬────────┘                  ▼                 │
                              │           │                  ┌─────────────────┐        │
                              │           │                  │     RustFS      │        │
                              │           ▼                  │  (S3 Storage)   │        │
                              │  ┌─────────────────┐         │    40Gi         │        │
                              │  │ External Secrets│         └─────────────────┘        │
                              │  │    Operator     │                                    │
                              │  └─────────────────┘                  ┌─────────────────┐
                              │                                       │  Pigsty Exporter│
                              │           Metrics ────────────────────│  (Monitoring)   │
                              │                                       └────────┬────────┘
                              │                                                │        │
                              └────────────────────────────────────────────────┼────────┘
                                                                               │
                                                                               ▼
                                                                      ┌─────────────────┐
                                                                      │ VictoriaMetrics │
                                                                      │   (Metrics DB)  │
                                                                      └─────────────────┘

---

Quick Start

Requirements

• Kubernetes cluster (1.25+)
• CloudNativePG Operator
• External Secrets Operator
• Secrets Store CSI Driver
• VictoriaMetrics Operator (for monitoring)
• Storage class: openebs-lvm or equivalent

1. Deploy OpenBao (Secrets Backend)

# Add Helm repository
helm repo add openbao https://openbao.github.io/openbao-helm
helm repo update

# Install OpenBao
helm install openbao openbao/openbao \
  --namespace openbao \
  --create-namespace \
  -f openbao/values.yml

# Initialize and unseal (follow OpenBao documentation)
kubectl exec -n openbao openbao-0 -- bao operator init
kubectl exec -n openbao openbao-0 -- bao operator unseal <key>

2. Configure Kubernetes Authentication in OpenBao

# Enable Kubernetes auth
kubectl exec -n openbao openbao-0 -- bao auth enable kubernetes

# Configure the auth method (see external-secret-operator/k8s-role.md for details)
kubectl exec -n openbao openbao-0 -- bao write auth/kubernetes/config \
  kubernetes_host="https://kubernetes.default.svc:443"

# Create policy for External Secrets Operator
kubectl exec -n openbao openbao-0 -- bao policy write eso-policy - <<EOF
path "secrets/*" {
  capabilities = ["read", "list"]
}
EOF

# Create role
kubectl exec -n openbao openbao-0 -- bao write auth/kubernetes/role/openbao-readonly \
  bound_service_account_names=openbao-sa \
  bound_service_account_namespaces=postgres \
  policies=eso-policy \
  ttl=1h

3. Deploy RustFS (S3 Storage)

# Create namespace and deploy
kubectl apply -f rustfs/rustfs-stack.yml

# Verify deployment
kubectl get pods -n rustfs

4. Deploy External Secrets Operator Resources

# Create namespace
kubectl create namespace postgres

# Apply secret store and generators
kubectl apply -f external-secret-operator/secret-store.yml
kubectl apply -f external-secret-operator/password-generator.yml
kubectl apply -f external-secret-operator/es-superuser.yml
kubectl apply -f external-secret-operator/es-application.yml
kubectl apply -f external-secret-operator/push-secret.yml

5. Deploy PostgreSQL Cluster

# Apply CNPG resources
kubectl apply -f cnpg/object-store.yml
kubectl apply -f cnpg/es-object-store.yml
kubectl apply -f cnpg/cluster.yml
kubectl apply -f cnpg/connection-pooler.yml

# Verify cluster status
kubectl get cluster -n postgres
kubectl get pods -n postgres

6. Deploy Monitoring

# Apply Pigsty exporter and monitoring rules
kubectl apply -f pigsty-exporter/es-exporter-cred.yml
kubectl apply -f pigsty-exporter/exporter.yml
kubectl apply -f pigsty-exporter/pod-monitor.yml
kubectl apply -f pigsty-exporter/vmservicescrape.yml
kubectl apply -f pigsty-exporter/vmrule.yml

7. Access PostgreSQL

# Get connection credentials
kubectl get secret cluster-app-user -n postgres -o jsonpath='{.data.password}' | base64 -d

# Connect via pgbouncer (LoadBalancer)
kubectl get svc -n postgres psql-cluster-pooler

# Port forward for local access
kubectl port-forward svc/psql-cluster-pooler -n postgres 5432:5432
psql -h localhost -U app -d app

---

Configuration

PostgreSQL Cluster

The cluster is configured in cnpg/cluster.yml:

# Key settings
instances: 3
postgresql:
  parameters:
    max_connections: "200"
    shared_buffers: "256MB"
    effective_cache_size: "768MB"
    wal_buffers: "8MB"
    min_wal_size: "1GB"
    max_wal_size: "2GB"

Resources per instance:

• Memory: 384Mi - 1Gi
• CPU: 200m - 2 cores
• Storage: 5Gi (OpenEBS LVM)

Connection Pooler

PgBouncer is configured in cnpg/connection-pooler.yml:

instances: 2
type: rw                    # Read-write connections
poolMode: session           # Session pooling mode
parameters:
  max_client_conn: "1000"
  default_pool_size: "10"

Backup Configuration

Backups are configured in cnpg/object-store.yml:

retentionPolicy: "30d"
barmanObjectStore:
  destinationPath: s3://ar-cnpg-backup/hl-cnpg-cluster/
  endpointURL: http://rustfs-service.rustfs.svc:7000
  wal:
    compression: gzip
  data:
    compression: gzip

Manual backup:

kubectl apply -f cnpg/backup.yml
kubectl get backup -n postgres

Secrets Management

External Secrets Operator handles credential rotation:

• Superuser credentials: Auto-generated, 24h refresh
• Application credentials: Auto-generated, 24h refresh
• Backup credentials: Synced from OpenBao vault

Configure the secret store in external-secret-operator/secret-store.yml:

provider:
  vault:
    server: "http://openbao.openbao.svc:8200"
    path: "secrets"
    version: "v2"

---

Project Structure

cnpg-postgres-stack/
├── cnpg/                          # PostgreSQL cluster configurations
│   ├── cluster.yml                # 3-node PostgreSQL cluster definition
│   ├── connection-pooler.yml      # PgBouncer pooler configuration
│   ├── backup.yml                 # On-demand backup resource
│   ├── object-store.yml           # S3 backup destination config
│   └── es-object-store.yml        # External secret for backup creds
├── external-secret-operator/      # Secret management
│   ├── secret-store.yml           # OpenBao connection config
│   ├── password-generator.yml     # Automatic password generation
│   ├── es-superuser.yml           # PostgreSQL superuser secret
│   ├── es-application.yml         # Application user secret
│   ├── push-secret.yml            # Sync secrets back to vault
│   └── k8s-role.md                # Kubernetes auth setup guide
├── openbao/                       # Secrets backend
│   ├── Chart.yml                  # Helm chart definition
│   └── values.yml                 # OpenBao Helm values (3-node HA)
├── pigsty-exporter/               # Monitoring stack
│   ├── exporter.yml               # pg_exporter deployment
│   ├── pg_exporter.yml            # Metrics collector config
│   ├── vmrule.yml                 # Recording & alerting rules
│   ├── pod-monitor.yml            # Prometheus PodMonitor
│   ├── vmservicescrape.yml        # VictoriaMetrics scrape config
│   └── es-exporter-cred.yml       # Exporter credentials secret
├── rustfs/                        # S3-compatible storage
│   └── rustfs-stack.yml           # Complete RustFS deployment
├── LICENSE                        # MIT License
└── README.md                      # This file

---

Storage Requirements

Component	Storage	Class
PostgreSQL (per instance)	5Gi × 3	openebs-lvm
OpenBao data (per instance)	2Gi × 3	openebs-lvm
OpenBao audit (per instance)	1Gi × 3	openebs-lvm
RustFS	40Gi	openebs-lvm
Total	~64Gi

---

Monitoring

The Pigsty exporter provides comprehensive PostgreSQL metrics:

• Connection statistics and pooler metrics
• Replication lag and streaming status
• Query performance (pg_stat_statements)
• Table and index bloat detection
• WAL archiving status
• Lock contention monitoring
• Background worker statistics

Access metrics:

# Port forward to exporter
kubectl port-forward svc/pg-exporter -n postgres 9630:9630

# View metrics
curl http://localhost:9630/metrics

VictoriaMetrics integration:

• Recording rules aggregate metrics for dashboards
• Alert rules monitor database health
• ServiceScrape configured for 30s intervals

---

Contributing

Contributions are welcome. If you find a bug or have an improvement:

1. Fork the repository
2. Create a feature branch
3. Submit a pull request

Please open an issue first to discuss significant changes.

---

License

This project is licensed under the MIT License - see the LICENSE file for details.

---

Disclaimer

This project is intended for educational purposes and development environments.

The default configuration uses trust-based authentication which is not suitable for production. Before deploying to production:

1. Change pg_hba authentication from trust to scram-sha-256
2. Enable TLS for all connections
3. Review and harden OpenBao seal configuration
4. Implement proper network policies
5. Configure appropriate resource limits for your workload

Users are solely responsible for ensuring their deployment meets security requirements for their environment.