cnpg-database

CNPG Database Management

Guide to provisioning and managing PostgreSQL databases using CloudNative-PG (CNPG) in the homelab Kubernetes platform. The platform supports both a shared multi-tenant cluster and dedicated per-application clusters.

Architecture Overview

┌──────────────────────────────────────────────────────────────────────┐
│                    Database Architecture                             │
├──────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  database namespace                                                  │
│  ┌────────────────────────────────────┐                             │
│  │  Shared Platform Cluster           │                             │
│  │  ├─ platform-0 (primary)           │ ◄── cnpg-platform-superuser │
│  │  ├─ platform-1 (replica)           │     (stays in database ns)  │
│  │  └─ platform-2 (replica)           │                             │
│  │                                    │                             │
│  │  Managed Roles (CNPG-controlled):  │                             │
│  │  ├─ authelia, lldap, sonarr, ...   │ ◄── <app>-role-password     │
│  │                                    │     (secret-generator)      │
│  │  Database CRDs (CNPG-controlled):  │                             │
│  │  ├─ authelia, lldap, sonarr-main,  │                             │
│  │  │  sonarr-log, radarr-main, ...   │                             │
│  │                                    │                             │
│  │  platform-pooler-rw (PgBouncer)    │ ◄── Apps connect here       │
│  └────────────────────────────────────┘                             │
│                                                                      │
│  ┌────────────────────────────────────┐                             │
│  │  Dedicated Cluster (e.g. Immich)   │                             │
│  │  ├─ immich-database-0 (primary)    │ ◄── immich-database-app     │
│  │  └─ immich-database-1 (replica)    │     (CNPG-generated)        │
│  └────────────────────────────────────┘                             │
│                                                                      │
│  ──── kubernetes-replicator ──────────────────────────────────────   │
│                                                                      │
│  app namespaces (authelia, zipline, immich, ...)                    │
│  ┌─────────────────────────────────────┐                            │
│  │  Replica secrets:                   │                            │
│  │  ├─ <app>-db-credentials            │ (from database/<app>-role- │
│  │  │                                  │  password via replicator)  │
│  │  └─ cnpg-immich-database-app        │ (from database/immich-     │
│  │                                     │  database-app)             │
│  └─────────────────────────────────────┘                            │
│                                                                      │
└──────────────────────────────────────────────────────────────────────┘

---

Decision Tree: Shared vs Dedicated Cluster

App needs a PostgreSQL database?
│
├─ Standard workload (generic SQL, no special extensions)?
│   └─ Use shared platform cluster
│      Connect via platform-pooler-rw service
│      (See: Shared Cluster Workflow below)
│
├─ Needs custom extensions (vector search, PostGIS, etc.)?
│   └─ Use dedicated cluster
│      Custom imageName with extensions pre-installed
│      (See: Dedicated Cluster Workflow below)
│
├─ Needs isolation for performance or data sensitivity?
│   └─ Use dedicated cluster
│      Independent resources, storage, and backup
│
└─ Unclear?
    └─ Start with shared cluster
       Migrate to dedicated if needed later

---

Shared Platform Cluster

The shared cluster is defined at kubernetes/platform/config/database/ and deployed to all clusters via the platform Kustomization. It uses CNPG managed roles and Database CRDs to declaratively provision databases and users -- no init containers or superuser access needed in app namespaces.

Key Files

File	Purpose
cluster.yaml	CNPG Cluster CR with managed roles (spec.managed.roles)
databases.yaml	Database CRDs for each application database
role-secrets.yaml	Per-role password secrets (secret-generator + replicator)
pooler.yaml	PgBouncer Pooler for connection pooling
superuser-secret.yaml	Auto-generated superuser password (stays in database ns only)
prometheus-rules.yaml	CNPG-specific PrometheusRules for alerting
kustomization.yaml	Kustomize aggregation of all database resources

Cluster Configuration

# kubernetes/platform/config/database/cluster.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: platform
spec:
  description: "Shared platform PostgreSQL cluster"
  imageName: ghcr.io/cloudnative-pg/postgresql:17.2
  instances: ${default_replica_count}
  primaryUpdateStrategy: unsupervised

  enableSuperuserAccess: true
  superuserSecret:
    name: cnpg-platform-superuser

  # Declarative role management - CNPG creates/updates these PostgreSQL roles
  managed:
    roles:
      - name: authelia
        ensure: present
        login: true
        passwordSecret:
          name: authelia-role-password
      # ... one entry per application

  postgresql:
    parameters:
      shared_buffers: "256MB"
      max_connections: "200"
      effective_cache_size: "512MB"
      log_statement: "ddl"
      log_min_duration_statement: "1000"
    pg_hba:
      - host all all 10.0.0.0/8 scram-sha-256
      - host all all 172.16.0.0/12 scram-sha-256

  storage:
    storageClass: fast
    size: ${database_volume_size}

  monitoring:
    enablePodMonitor: true

  affinity:
    enablePodAntiAffinity: true
    topologyKey: kubernetes.io/hostname

Database CRDs

Each database is declared as a separate Database CR. CNPG creates it if missing or adopts it if it already exists (with databaseReclaimPolicy: retain):

# kubernetes/platform/config/database/databases.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
  name: authelia-database
  namespace: database
spec:
  name: authelia          # PostgreSQL database name
  owner: authelia         # Must match a managed role name
  cluster:
    name: platform
  databaseReclaimPolicy: retain

Note: Some apps (sonarr, radarr, prowlarr) use multiple databases (e.g. sonarr-main, sonarr-log) but share a single role. Each database gets its own Database CR with the same owner.

Role Password Secrets

Each managed role has a corresponding Secret in the database namespace. The secret uses secret-generator for auto-generation and kubernetes-replicator for cross-namespace replication to the app that needs it:

# kubernetes/platform/config/database/role-secrets.yaml
apiVersion: v1
kind: Secret
metadata:
  name: authelia-role-password
  namespace: database
  annotations:
    secret-generator.v1.mittwald.de/autogenerate: password
    secret-generator.v1.mittwald.de/encoding: hex
    secret-generator.v1.mittwald.de/length: "32"
    replicator.v1.mittwald.de/replication-allowed: "true"
    replicator.v1.mittwald.de/replication-allowed-namespaces: "authelia"
type: kubernetes.io/basic-auth
stringData:
  username: authelia

PgBouncer Pooler

Applications connect through PgBouncer rather than directly to PostgreSQL:

# kubernetes/platform/config/database/pooler.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: platform-pooler-rw
spec:
  cluster:
    name: platform
  instances: ${default_replica_count}
  type: rw
  pgbouncer:
    poolMode: transaction
    parameters:
      max_client_conn: "1000"
      default_pool_size: "25"
  monitoring:
    enablePodMonitor: true

Connection string for apps using the shared cluster:

postgresql://<username>:<password>@platform-pooler-rw.database.svc:5432/<dbname>

---

Workflow: Add a Database for a New App (Shared Cluster)

Step 1: Add Managed Role to Cluster

Add a new role entry to spec.managed.roles in kubernetes/platform/config/database/cluster.yaml:

managed:
  roles:
    # ... existing roles
    - name: <app>
      ensure: present
      login: true
      passwordSecret:
        name: <app>-role-password

Step 2: Create Role Password Secret

Add a new Secret to kubernetes/platform/config/database/role-secrets.yaml:

---
apiVersion: v1
kind: Secret
metadata:
  name: <app>-role-password
  namespace: database
  annotations:
    secret-generator.v1.mittwald.de/autogenerate: password
    secret-generator.v1.mittwald.de/encoding: hex
    secret-generator.v1.mittwald.de/length: "32"
    replicator.v1.mittwald.de/replication-allowed: "true"
    replicator.v1.mittwald.de/replication-allowed-namespaces: "<app-namespace>"
type: kubernetes.io/basic-auth
stringData:
  username: <app>

Step 3: Create Database CRD

Add a new Database CR to kubernetes/platform/config/database/databases.yaml:

---
apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
  name: <app>-database
  namespace: database
spec:
  name: <app>
  owner: <app>
  cluster:
    name: platform
  databaseReclaimPolicy: retain

Step 4: Create App-Namespace Credential Replica

Create a replica Secret in the app's config directory that pulls the password from the database namespace:

# kubernetes/clusters/<cluster>/config/<app>/<app>-db-credentials.yaml
---
apiVersion: v1
kind: Secret
metadata:
  name: <app>-db-credentials
  namespace: <app-namespace>
  annotations:
    replicator.v1.mittwald.de/replicate-from: database/<app>-role-password
type: kubernetes.io/basic-auth
data: { }

Step 5: Add Network Policy Access

The app's namespace must have the postgres access label. Add it in kubernetes/platform/namespaces.yaml:

- name: <app-namespace>
  labels:
    pod-security.kubernetes.io/enforce: baseline
    network-policy.homelab/profile: standard  # or appropriate profile
    access.network-policy.homelab/postgres: "true"  # Required for DB access

Step 6: Configure App to Use Database

The app should connect to the Pooler service, not the Cluster directly:

Setting	Value
Host	platform-pooler-rw.database.svc
Port	5432
Database	<app> (created by CNPG Database CRD)
Username	From <app>-db-credentials secret (username key)
Password	From <app>-db-credentials secret (password key)

Step 7: Register in Kustomization

Add the new credential replica file to the app's kustomization.yaml and ensure the parent config references the directory.

---

Workflow: Create a Dedicated CNPG Cluster

Use this when an app needs custom PostgreSQL extensions or isolation from the shared cluster.

Step 1: Define the Cluster

# kubernetes/clusters/<cluster>/config/<app>/<app>-cluster.yaml
---
# yaml-language-server: $schema=https://raw.githubusercontent.com/datreeio/CRDs-catalog/main/postgresql.cnpg.io/cluster_v1.json
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: <app>-database
  namespace: database
spec:
  inheritedMetadata:
    annotations:
      replicator.v1.mittwald.de/replication-allowed: "true"
      replicator.v1.mittwald.de/replication-allowed-namespaces: "<app-namespace>"
  description: "<App> PostgreSQL with <extension> support"
  imageName: <custom-image>  # e.g., ghcr.io/tensorchord/cloudnative-vectorchord:17.7-1.0.0
  instances: ${default_replica_count}
  primaryUpdateStrategy: unsupervised

  postgresql:
    shared_preload_libraries:
      - "<extension>.so"  # e.g., "vchord.so"
    parameters:
      shared_buffers: "128MB"
      max_connections: "100"

  bootstrap:
    initdb:
      database: <app>
      owner: <app>
      postInitApplicationSQL:
        - CREATE EXTENSION IF NOT EXISTS <extension> CASCADE;

  storage:
    storageClass: fast
    size: 10Gi

  resources:
    requests:
      cpu: 50m
      memory: 256Mi
    limits:
      memory: 1Gi

  monitoring:
    enablePodMonitor: true

  affinity:
    enablePodAntiAffinity: true
    topologyKey: kubernetes.io/hostname

Real example: kubernetes/clusters/live/config/immich/immich-cluster.yaml

Key Differences from Shared Cluster

Feature	Shared Cluster	Dedicated Cluster
Location	kubernetes/platform/config/database/	kubernetes/clusters/<cluster>/config/<app>/
Namespace	database (deployed by platform)	database (deployed by cluster config)
Image	Standard PostgreSQL	Custom image with extensions
Role management	spec.managed.roles + Database CRDs	bootstrap.initdb creates DB and owner
Credential source	<app>-role-password (secret-generator)	<app>-database-app (CNPG auto-generated)
inheritedMetadata	Not needed (role secrets have explicit replication)	Required for app secret replication
Superuser	Confined to database namespace	CNPG auto-generates (<cluster-name>-superuser)

Step 2: Replicate App Credentials to Consumer Namespace

CNPG auto-generates an <cluster-name>-app secret in the database namespace. The inheritedMetadata annotations allow replication to the app namespace:

# kubernetes/clusters/<cluster>/config/<app>/database-secret-replication.yaml
---
apiVersion: v1
kind: Secret
metadata:
  name: cnpg-<app>-database-app
  namespace: <app-namespace>
  annotations:
    replicator.v1.mittwald.de/replicate-from: database/<app>-database-app
data: { }

Real example: kubernetes/clusters/live/config/immich/database-secret-replication.yaml

Step 3: Network Policy and Kustomization

Same as shared cluster workflow -- add access.network-policy.homelab/postgres: "true" label and register files in kustomization.yaml.

---

Credential Chain Summary

Shared Cluster Credential Flow

database namespace                          app namespace
┌──────────────────────┐                    ┌──────────────────────┐
│ <app>-role-password   │  kubernetes-       │ <app>-db-credentials │
│ (secret-generator)    │──replicator──────► │ (replica)            │
│  username: <app>      │                    │  username: <app>     │
│  password: <random>   │                    │  password: <random>  │
└──────────────────────┘                    └──────────────────────┘
         │
         ▼
  CNPG Cluster (managed role)
  uses passwordSecret to set
  PostgreSQL role password
         │
         ▼
  Database CRD
  creates DB owned by role

Key security property: The superuser secret (cnpg-platform-superuser) never leaves the database namespace. App namespaces only receive their own role password.

Dedicated Cluster Credential Flow

CNPG auto-generates              kubernetes-replicator
     │                                   │
     ▼                                   ▼
<app>-database-app  ──────────► cnpg-<app>-database-app
  (database ns)                   (app ns)

The dedicated cluster's inheritedMetadata annotations make the auto-generated secret replicable. The replication-allowed-namespaces controls which namespaces can pull it.

---

Monitoring

PodMonitor

Both the Cluster and Pooler have enablePodMonitor: true, which creates PodMonitor resources that Prometheus discovers automatically. No additional ServiceMonitor configuration is needed.

PrometheusRules

CNPG-specific alerts are defined in kubernetes/platform/config/database/prometheus-rules.yaml:

Alert	Expression	Severity	Description
CNPGClusterNotHealthy	cnpg_pg_replication_streaming == 0	critical	Replication broken
CNPGClusterHighConnections	Connection usage > 80% of max_connections	warning	Near connection limit
CNPGInstanceNotReady	Replica WAL receiver down	critical	Replica cannot follow primary

Key Metrics

Metric	Description
cnpg_pg_replication_streaming	Whether streaming replication is active
cnpg_pg_stat_activity_count	Current active connections
cnpg_pg_settings_setting{name="max_connections"}	Configured max connections
cnpg_pg_replication_in_recovery	Whether instance is a replica
cnpg_pg_replication_is_wal_receiver_up	Whether WAL receiver is connected

---

Configuration Reference

Cluster Spec Fields

Field	Purpose	Example
instances	Number of PostgreSQL instances (primary + replicas)	${default_replica_count}
imageName	PostgreSQL container image	ghcr.io/cloudnative-pg/postgresql:17.2
primaryUpdateStrategy	How to handle primary upgrades	unsupervised (automatic failover)
enableSuperuserAccess	Whether to allow superuser connections	true for shared, not set for dedicated
superuserSecret	Reference to superuser credentials	cnpg-platform-superuser
managed.roles	Declarative PostgreSQL role definitions	See Managed Roles section
storage.storageClass	Kubernetes StorageClass for PVCs	fast
storage.size	Volume size per instance	${database_volume_size} or 10Gi
monitoring.enablePodMonitor	Auto-create PodMonitor for Prometheus	true
affinity.enablePodAntiAffinity	Spread instances across nodes	true
postgresql.pg_hba	Client authentication rules	Allow pod CIDR with SCRAM-SHA-256
inheritedMetadata	Annotations/labels applied to generated secrets	Replication annotations

Managed Role Fields

Field	Purpose	Example
name	PostgreSQL role name	authelia
ensure	Whether role should exist	present
login	Whether role can log in	true
passwordSecret.name	Secret containing the role password	authelia-role-password

Database CRD Fields

Field	Purpose	Example
spec.name	PostgreSQL database name	authelia
spec.owner	Database owner (must be a managed role)	authelia
spec.cluster.name	Target CNPG Cluster	platform
spec.databaseReclaimPolicy	What happens when CRD is deleted	retain (keeps database)

Pooler Spec Fields

Field	Purpose	Example
cluster.name	Target CNPG Cluster	platform
instances	Number of PgBouncer pods	${default_replica_count}
type	Access mode (rw or ro)	rw
pgbouncer.poolMode	Connection pooling mode	transaction
pgbouncer.parameters.max_client_conn	Max client connections	"1000"
pgbouncer.parameters.default_pool_size	Default server connections per pool	"25"

Bootstrap initdb Fields (Dedicated Clusters Only)

Field	Purpose	Example
database	Database name to create	immich
owner	Database owner role	immich
postInitApplicationSQL	SQL to run after database creation	CREATE EXTENSION IF NOT EXISTS ...

---

Debugging

Cluster Health

# Check cluster status
KUBECONFIG=~/.kube/<cluster>.yaml kubectl get clusters.postgresql.cnpg.io -n database

# Detailed cluster info
KUBECONFIG=~/.kube/<cluster>.yaml kubectl describe cluster platform -n database

# Check pod status
KUBECONFIG=~/.kube/<cluster>.yaml kubectl get pods -n database -l cnpg.io/cluster=platform

# Check pooler status
KUBECONFIG=~/.kube/<cluster>.yaml kubectl get poolers.postgresql.cnpg.io -n database

# Check managed databases
KUBECONFIG=~/.kube/<cluster>.yaml kubectl get databases.postgresql.cnpg.io -n database

Common Issues

Symptom	Cause	Fix
Pods Pending	No PVs available	Check StorageClass fast exists, Longhorn is healthy
CrashLoopBackOff	OOM or bad config	Check kubectl logs, increase memory limits
Replication lag	Slow disk or network	Check cnpg_pg_replication_streaming metric
App can't connect	Network policy missing	Add access.network-policy.homelab/postgres: "true" label
App can't connect	Secret not replicated	Check replication annotations on source secret
Secret empty after replication	Source namespace wrong	Verify replicate-from points to correct <ns>/<name>
Extension not found	Wrong image	Verify imageName includes the extension
Database not created	Database CRD missing	Add Database CR to databases.yaml
Role not created	Missing from managed.roles	Add role entry to cluster.yaml
Role password mismatch	Secret not regenerated	Delete the role-password secret, secret-generator will recreate

Checking Connectivity

# Verify network policy allows traffic
hubble observe --from-namespace <app-ns> --to-namespace database --since 5m

# Test connection from a debug pod
KUBECONFIG=~/.kube/<cluster>.yaml kubectl run -n <app-ns> pg-test --rm -it \
  --image=postgres:17 -- psql "postgresql://user:pass@platform-pooler-rw.database.svc:5432/dbname"

CNPG Plugin (Optional)

The kubectl cnpg plugin provides additional cluster management commands:

kubectl cnpg status platform -n database
kubectl cnpg promote platform-2 -n database  # Manual failover

---

Cross-References

Document	Relevance
kubernetes/platform/config/CLAUDE.md	Config subsystem and CRD dependency patterns
secrets skill	secret-generator, ExternalSecret, and replication patterns
deploy-app skill	End-to-end deployment including database setup
sre skill	Debugging methodology for database incidents