cozystack/packages/apps/postgres

Managed PostgreSQL Service

PostgreSQL is currently the leading choice among relational databases, known for its robust features and performance. The Managed PostgreSQL Service takes advantage of platform-side implementation to provide a self-healing replicated cluster. This cluster is efficiently managed using the highly acclaimed CloudNativePG operator, which has gained popularity within the community.

Deployment Details

This managed service is controlled by the CloudNativePG operator, ensuring efficient management and seamless operation.

• Docs: https://cloudnative-pg.io/docs/
• Github: https://github.com/cloudnative-pg/cloudnative-pg

Operations

PostgreSQL backups have two layers, and the recommended setup uses both together rather than picking one:

Layer	What it does	Configured via
Archive plumbing (chart)	Renders spec.backup.barmanObjectStore on the cnpg.io Cluster from helm-install onwards. CNPG starts postgres with archive_command=barman-cloud-wal-archive, every WAL switch ships to object storage, and any backup driver gets a complete WAL chain to start replay from.	backup.enabled=true plus backup.destinationPath, backup.endpointURL, backup.s3CredentialsSecret, backup.endpointCA
Backup orchestration (recommended)	Drives ad-hoc and scheduled backups, retention, and restores from backups.cozystack.io resources that can span multiple Postgres apps in a tenant. The driver SSA-merges its own barmanObjectStore shape over the chart's with ForceOwnership (so compression / serverName / target settings flow from the strategy template); chart-managed values cover the same destination so there is no tug-of-war on the live Cluster.	strategy.backups.cozystack.io/CNPG + BackupClass + Plan (recurring) or BackupJob (ad-hoc), restores via RestoreJob
Legacy chart-emitted scheduled backup	The chart can also emit a cnpg.io/ScheduledBackup directly. Superseded by the BackupClass + Plan path, kept around for clusters that did not migrate. Off by default - rendered only when both backup.enabled and backup.schedule are non-empty. With backup.schedule="" the archive plumbing still runs; only the chart-emitted scheduled backup is silent.	backup.enabled=true plus backup.schedule (CNPG 6-field cron, e.g. "0 2 * * * *")

The canonical setup for tenant-managed backups is therefore:

spec:
  backup:
    enabled: true                # archive WAL to object storage
    destinationPath: s3://my-bucket/pg-src/
    endpointURL: https://seaweedfs-s3.tenant-foo:8333
    s3CredentialsSecret:
      name: pg-src-cnpg-backup-creds
    endpointCA:
      name: pg-src-cnpg-backup-ca
    # backup.schedule intentionally left empty - the BackupClass / Plan
    # below drives recurring backups, no chart-emitted ScheduledBackup
    # should compete with that schedule.

paired with a strategy.backups.cozystack.io/CNPG + BackupClass + Plan in the same tenant. The end-to-end e2e fixture under examples/backups/postgres/ is the canonical reference (05-postgres-src.yaml shows the chart side, 10-cnpg-strategy.yaml and 15-backupclass.yaml show the orchestration side, 25-backupjob-adhoc.yaml and 40-restorejob-to-copy.yaml show ad-hoc backup and restore).

Why both layers and not one? WAL archive is a postmaster setting - CNPG can swap archive_command at runtime via SIGHUP, but any WAL that closed before the swap was "archived" by archive_command=/bin/true and is gone for good. A backup taken from such a cluster is missing the WAL its begin_wal points at, and recovery later fails with WAL not found. Letting the chart bootstrap archive_command from helm-install removes the race.

How to enable backups (preferred: BackupClass + Plan)

End-to-end manifests live under examples/backups/postgres/. Briefly, the moving parts are:

1. A strategy.backups.cozystack.io/CNPG describing the destination bucket and templating the barmanObjectStore (including a Secret reference to S3 credentials - the credentials never appear on the Postgres CR .spec; see Security note below).
2. A backups.cozystack.io/BackupClass that names the strategy and is selected by an applicationRef matching the Postgres app's Kind/Name.
3. A backups.cozystack.io/Plan (recurring) or BackupJob (ad-hoc) that references the BackupClass. The controller materialises a Backup artifact when the cnpg.io Backup completes; restores then reference that Backup via RestoreJob.

Both in-place restores (overwrite the source app's data) and to-copy restores (restore into a separate target Postgres app in the same namespace) are supported via the RestoreJob.spec.targetApplicationRef field.

Security: With the BackupClass path, S3 credentials live in a tenant-readable Secret referenced from the strategy template. The CNPG driver forwards that Secret reference into the Postgres app's spec.backup.s3CredentialsSecret on restore, so access keys never land in the Postgres CR .spec, etcd object store, or kubectl get -o yaml output. Prefer this over the chart-managed path whenever possible.

How to enable chart-managed scheduled backups (legacy)

The chart can also emit a cnpg.io/ScheduledBackup directly, without a BackupClass. Superseded by the BackupClass + Plan path above and kept around for clusters that did not migrate. It does not run by default - backup.schedule defaults to an empty string, which gates the chart's ScheduledBackup template off. To turn it on, fill in a CNPG 6-field cron expression:

## @param backup.enabled Enable archive_command + render the chart-managed ScheduledBackup
## @param backup.schedule Cron schedule (CNPG 6-field). Empty means no chart-managed ScheduledBackup
## @param backup.retentionPolicy Retention policy
## @param backup.destinationPath Path to store the backup (i.e. s3://bucket/path/to/folder)
## @param backup.endpointURL S3 Endpoint used to upload data to the cloud
## @param backup.s3AccessKey Access key for S3, used for authentication
## @param backup.s3SecretKey Secret key for S3, used for authentication
backup:
  enabled: true
  retentionPolicy: 30d
  destinationPath: s3://bucket/path/to/folder/
  endpointURL: http://minio-gateway-service:9000
  schedule: "0 2 * * * *"  # opt in - empty (the default) means no chart-managed schedule
  s3AccessKey: oobaiRus9pah8PhohL1ThaeTa4UVa7gu
  s3SecretKey: ju3eum4dekeich9ahM1te8waeGai0oog

How to recover a backup (preferred: RestoreJob)

For BackupClass-managed backups, create a backups.cozystack.io/RestoreJob that references the desired Backup. See examples/backups/postgres/35-restorejob-in-place.yaml and examples/backups/postgres/40-restorejob-to-copy.yaml. On a to-copy restore the controller replaces the target app's spec.databases and spec.users with the source-spec snapshot persisted in Backup.status.underlyingResources, so the chart's post-install init-job does not drop the recovered roles or databases.

e2e coverage: the CI e2e (hack/e2e-apps/backup-postgres.bats) exercises the same-namespace to-copy restore (Steps 0-7), which leaves the source running and is therefore the deterministic end-to-end signal. The cross-tenant variant (target Postgres in a different tenant from the source's seaweedfs) stays a manual / dev-cluster exercise — reachability is blocked by the per-tenant Cilium egress policy. The in-place restore code path is shipped and is covered at the unit level by TestClusterHasRecoveryBootstrap_TerminatingCluster, TestCNPGBackupWALArchived, TestCNPGPurgeNeeded, and the rest of the internal/backupcontroller/cnpgstrategy_controller_test.go suite.

How to recover a backup (chart-managed bootstrap)

CloudNativePG supports point-in-time-recovery. Recovering a backup is done by creating a new database instance and restoring the data in it.

Create a new PostgreSQL application with a different name, but identical configuration. Set bootstrap.enabled to true and fill in the name of the database instance to recover from and the recovery time:

## @param bootstrap.enabled Restore database cluster from a backup
## @param bootstrap.recoveryTime Timestamp (PITR) up to which recovery will proceed, expressed in RFC 3339 format. If left empty, will restore latest
## @param bootstrap.oldName Name of database cluster before deleting
##
bootstrap:
  enabled: false
  recoveryTime: ""  # leave empty for latest or exact timestamp; example: 2020-11-26 15:22:00.00000+00
  oldName: "<previous-postgres-instance>"

How to switch primary/secondary replica

See:

• https://cloudnative-pg.io/documentation/1.15/rolling_update/#manual-updates-supervised

Parameters

Common parameters

Name	Description	Type	Value
replicas	Number of Postgres replicas.	int	2
resources	Explicit CPU and memory configuration for each PostgreSQL replica. When omitted, the preset defined in resourcesPreset is applied.	object	{}
resources.cpu	CPU available to each replica.	quantity	""
resources.memory	Memory (RAM) available to each replica.	quantity	""
resourcesPreset	Default sizing preset used when resources is omitted.	string	micro
size	Persistent Volume Claim size available for application data.	quantity	10Gi
storageClass	StorageClass used to store the data.	string	""
external	Enable external access from outside the cluster.	bool	false
version	PostgreSQL major version to deploy	string	v18

Application-specific parameters

Name	Description	Type	Value
postgresql	PostgreSQL server configuration.	object	{}
postgresql.parameters	PostgreSQL server parameters. All values must be strings (quote numbers: "100"). BLOCKED (enable arbitrary code execution): archive_command, restore_command, ssl_passphrase_command, dynamic_library_path, local_preload_libraries, session_preload_libraries, shared_preload_libraries. Do NOT override CloudNativePG-managed parameters: archive_mode, primary_conninfo, wal_level, max_replication_slots.	map[string]string	{}

Quorum-based synchronous replication

Name	Description	Type	Value
quorum	Quorum configuration for synchronous replication.	object	{}
quorum.minSyncReplicas	Minimum number of synchronous replicas required for commit.	int	0
quorum.maxSyncReplicas	Maximum number of synchronous replicas allowed (must be less than total replicas).	int	0

Users configuration

Name	Description	Type	Value
users	Users configuration map.	map[string]object	{}
users[name].password	Password for the user.	string	""
users[name].replication	Whether the user has replication privileges.	bool	false

Databases configuration

Name	Description	Type	Value
databases	Databases configuration map.	map[string]object	{}
databases[name].roles	Roles assigned to users.	object	{}
databases[name].roles.admin	List of users with admin privileges.	[]string	[]
databases[name].roles.readonly	List of users with read-only privileges.	[]string	[]
databases[name].extensions	List of enabled PostgreSQL extensions.	[]string	[]

Backup parameters

Name	Description	Type	Value
backup	Backup configuration.	object	{}
backup.enabled	Enable regular backups.	bool	false
backup.schedule	Legacy. Cron schedule (CNPG 6-field format) for the chart-emitted ScheduledBackup. Empty means no chart-managed schedule, which is the recommended setup when a BackupClass from backups.cozystack.io already drives backup orchestration. The chart still emits spec.backup.barmanObjectStore whenever backup.enabled=true, so archive_command runs and the BackupClass driver can take ad-hoc / Plan-driven backups against a live WAL archive.	string	""
backup.retentionPolicy	Retention policy (e.g. "30d").	string	30d
backup.destinationPath	Destination path for backups (e.g. s3://bucket/path/).	string	s3://bucket/path/to/folder/
backup.endpointURL	S3 endpoint URL for uploads.	string	http://minio-gateway-service:9000
backup.s3AccessKey	Access key for S3 authentication. Ignored when s3CredentialsSecret.name is set.	string	<your-access-key>
backup.s3SecretKey	Secret key for S3 authentication. Ignored when s3CredentialsSecret.name is set.	string	<your-secret-key>
backup.s3CredentialsSecret	Pre-existing Secret with S3 credentials. When set, the chart references this Secret directly instead of materialising one from s3AccessKey/s3SecretKey. The CNPG backup driver writes this field on restore so credentials never land in the CR .spec.	object	{}
backup.s3CredentialsSecret.name	Name of the Secret in the application namespace. Empty means the chart materialises <release>-s3-creds from s3AccessKey/s3SecretKey.	string	""
backup.s3CredentialsSecret.accessKeyIDKey	Key in the Secret holding the access key ID. Defaults to AWS_ACCESS_KEY_ID.	string	""
backup.s3CredentialsSecret.secretAccessKeyKey	Key in the Secret holding the secret access key. Defaults to AWS_SECRET_ACCESS_KEY.	string	""
backup.endpointCA	Pre-existing Secret with the CA bundle Barman should trust when reaching a self-signed S3 endpoint. Used for both backup and bootstrap recovery. The CNPG backup driver writes this field on restore.	object	{}
backup.endpointCA.name	Name of the Secret in the application namespace. Empty means no endpointCA is emitted (Barman uses the system trust store).	string	""
backup.endpointCA.key	Key within the Secret containing the CA bundle. Defaults to ca.crt.	string	""

Bootstrap (recovery) parameters

Name	Description	Type	Value
bootstrap	Bootstrap configuration.	object	{}
bootstrap.enabled	Whether to restore from a backup.	bool	false
bootstrap.recoveryTime	Timestamp (RFC3339) for point-in-time recovery; empty means latest.	string	""
bootstrap.oldName	Previous cluster name before deletion.	string	""
bootstrap.serverName	Barman server name (S3 path prefix) used by the original cluster when writing backups. Set this only when the original cluster had an explicit barmanObjectStore.serverName that differed from its Kubernetes resource name.	string	""

Parameter examples and reference

resources and resourcesPreset

resources sets explicit CPU and memory configurations for each replica. When left empty, the preset defined in resourcesPreset is applied.

resources:
  cpu: 4000m
  memory: 4Gi

resourcesPreset sets named CPU and memory configurations for each replica. This setting is ignored if the corresponding resources value is set.

Preset name	CPU	memory
nano	250m	128Mi
micro	500m	256Mi
small	1	512Mi
medium	1	1Gi
large	2	2Gi
xlarge	4	4Gi
2xlarge	8	8Gi

users

users:
  user1:
    password: strongpassword
  user2:
    password: hackme
  airflow:
    password: qwerty123
  debezium:
    replication: true

databases

databases:          
  myapp:            
    roles:          
      admin:        
      - user1       
      - debezium    
      readonly:     
      - user2       
  airflow:          
    roles:          
      admin:        
      - airflow     
    extensions:     
    - hstore