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

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:

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

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:

Key Metrics

Configuration Reference

Cluster Spec Fields

Managed Role Fields

Database CRD Fields

Pooler Spec Fields

Bootstrap initdb Fields (Dedicated Clusters Only)

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

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