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