CNPG (CloudNativePG)

Enterprise-grade PostgreSQL on Kubernetes with CNPG Operator, primary/standby replication, Barman Cloud Plugin for WAL archiving, and automated user provisioning via Grant Jobs.

Architecture

                CNPG Operator - Manages PostgreSQL cluster lifecycle

                PostgreSQL Cluster - Primary + standby instances (:5432)

                Barman Cloud Plugin - WAL archiving + base backups (:9090)

                Grant Job - Automated user provisioning + permission GRANTs

                PITR - Point-in-Time Recovery cluster from backup snapshot

Attributes

Attribute	Example	Where It Impacts
`namespace` REQ	`database`	All manifests, service DNS (cluster-{ns}-rw.{ns}.svc.cluster.local)
`psql_version` REQ	`16.2`	cluster.yaml container image tag
`admin_user` REQ	`admin`	cluster.yaml superuser configuration
`admin_password` REQ	`******`	secret/database-secret.yaml admin credential
`storage_size`	`10Gi`	cluster.yaml PVC storage size per instance
`storage_class`	`standard`	cluster.yaml storageClassName (optional, uses default if omitted)
`mem_request / mem_limit`	`1Gi / 4Gi`	cluster.yaml resources.requests/limits.memory
`cpu_request / cpu_limit`	`500m / 2000m`	cluster.yaml resources.requests/limits.cpu
`nodepool`	`db-pool`	cluster.yaml nodeAffinity rule
`init_db`	`CREATE EXTENSION pg_stat...`	cluster.yaml post-init SQL commands
`rotation`	`quarterly`	secret/database-secret.yaml rotation label
`custom_labels`	`team: platform`	kustomization.yaml commonLabels

Sub-Components

db - Database

Each db generates: database.yaml (Database CR), roles.yaml (role definitions), secret/database-secret.yaml (credentials), and jobs/grant-job.yaml (permission provisioning).

Attribute	Example	Impact
`database` REQ	`myapp`	Database name in database.yaml
`username` REQ	`myapp_user`	Role name in roles.yaml, grant-job.yaml user creation
`password` REQ	`******`	secret/database-secret.yaml user credential
`cnpg_permissions` REQ	`connect, read, write`	grant-job.yaml GRANT statements (see flags below)
`rotation_db`	`monthly`	secret/database-secret.yaml rotation label per db

Permission Flags (cnpg_permissions):

                        connect - CONNECT to database |
                        read - SELECT on all tables |
                        write - INSERT/UPDATE/DELETE |
                        manage_roles - CREATE/ALTER roles

                        create_schema - CREATE SCHEMA |
                        create_tables - CREATE TABLE |
                        execute_functions - EXECUTE on functions

                        default_privileges - ALTER DEFAULT PRIVILEGES |
                        metadata_access - pg_catalog/information_schema access
                    

Grant Job Sequence (jobs/grant-job.yaml):

                        1. Wait for cluster ready state

                        2. CREATE USER with password

                        3. Apply GRANT statements per cnpg_permissions flags

                        4. Test connection with new credentials

                        TTL: 3600s | Active Deadline: 600s

pitr_cluster - Point-in-Time Recovery

Generates day2/backup/point-in-time-recovery.yaml - creates a recovery cluster restored from a specific backup snapshot.

Attribute	Example	Impact
`psql_version`	`16.2`	Recovery cluster PostgreSQL version
`backup_id`	`backup-20240101`	Backup object name to restore from

migration - Cross-Database Migration

Handles cross-database migration via db-migration (source) and migration-db (destination) link types. Enables ETL and schema migration between databases.

Links

Link	Direction	Generated Output
Prometheus → CNPG	Inbound	Enables PodMonitor in `cluster.yaml` for PostgreSQL metrics scraping
Istio → CNPG	Inbound	Enables istio-injection label on `namespace.yaml`
Backend apps → CNPG db	Inbound (group)	`backend-db` group - apps (fastapi, image_base, etc.) connect to db sub-components

Sub-component level (bucket links):
cnpg-bucket (db → MinIO): Generates plugin/barman.yaml + ObjectStore config in cluster.yaml + secret/minio.env (SOPS)
cnpg-swbucket (db → SeaweedFS): Generates plugin/barman.yaml + ObjectStore config in cluster.yaml + secret/swbucket.env (SOPS)

Generated Files

File	Condition	Content
k8s/deploy/base/namespace.yaml	Always	Namespace definition with optional istio-injection label
k8s/deploy/base/cluster.yaml	Always	CNPG Cluster CR - image, storage, resources, affinity, backup config, PodMonitor
k8s/deploy/base/cnpg.yaml	Always	CNPG operator configuration
k8s/deploy/base/kustomization.yaml	Always	Kustomize resources, secretGenerator, custom_labels
k8s/deploy/base/database.yaml	db sub-components exist	Database CR definitions (one per db)
k8s/deploy/base/roles.yaml	db sub-components exist	Role definitions per database user
k8s/deploy/base/jobs/grant-job.yaml	db sub-components exist	User provisioning + GRANT jobs (TTL 3600s, deadline 600s)
k8s/deploy/base/plugin/barman.yaml	MinIO or SeaweedFS bucket linked	Barman Cloud Plugin for WAL archiving + base backups
k8s/deploy/base/secret/database-secret.yaml	Always	Admin + per-db user credentials, rotation labels
k8s/deploy/base/secret/cnpg.env	Always	Database connection credentials (SOPS encrypted)
k8s/deploy/base/secret/minio.env	MinIO bucket linked	MinIO backup credentials (SOPS encrypted)
k8s/deploy/base/secret/swbucket.env	SeaweedFS bucket linked	SeaweedFS backup credentials (SOPS encrypted)
k8s/deploy/base/secret/registry.json	Always	Container registry credentials (SOPS encrypted)
day2/backup/manual-backup.yaml	Always	On-demand backup manifest
day2/backup/schedule-backup.yaml	Always	ScheduledBackup CR for automated backups
day2/backup/cluster-restore.yaml	Always	Full cluster restore from backup
day2/backup/point-in-time-recovery.yaml	pitr_cluster sub-components exist	PITR recovery cluster from specific backup_id
day2/backup/backup-to-bucket-job.yaml	MinIO or SeaweedFS bucket linked	Backup export job to object storage

Ports

Port	Service	Protocol
5432	PostgreSQL (primary + replicas)	TCP
9090	Barman Cloud Plugin	HTTP

Internal Endpoints

                Read-Write (primary): cluster-{ns}-rw.{ns}.svc.cluster.local:5432

                Read-Only (replicas): cluster-{ns}-ro.{ns}.svc.cluster.local:5432

                Read (any instance): cluster-{ns}-r.{ns}.svc.cluster.local:5432