PostgreSQL

PostgreSQL#

Overview#

PostgreSQL is provided based on the Operator CloudNativePG.

Pointers#

Usage pattern#

We are using one cluster per application so that the handling of both backup and restore is possible per application.

This also allows to update the PostgreSQL version one application at a time.

See also

0004 Multiple databases in one PostgreSQL Cluster

Kustomize components#

Backup and restore are provided as reusable Kustomize components from the business-operations repository:

  • ``components/cnpg-cluster/backup`` — adds spec.backup, a ScheduledBackup, and a cnpg-backup ExternalSecret.

  • ``components/cnpg-cluster/restore`` — adds spec.bootstrap.recovery, spec.externalClusters, and a cnpg-restore ExternalSecret.

Both components use Kustomize replacements to derive the S3 destination path and server name from the Cluster resource’s metadata.name and annotations.

Annotations#

The Cluster resource controls revision values through two annotations:

metadata:
  annotations:
    cnpg.local/backup-revision: "v1"
    cnpg.local/restore-revision: "v1"

  • ``cnpg.local/backup-revision`` — appended to the backup serverName (e.g. db-rev${cluster_revision}-v1). Increment this when starting a new backup series.

  • ``cnpg.local/restore-revision`` — appended to the restore serverName (e.g. db-rev${cluster_bootstrap_revision}-v1). Set this to the revision of the backup you want to restore from.

A bare database cluster with these annotations looks like this:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: myapp-db
  annotations:
    cnpg.local/backup-revision: "v1"
    cnpg.local/restore-revision: "v1"
spec:
  instances: 1
  storage:
    size: 5Gi

Composing backup and restore#

Include the components via components: in kustomization.yaml. The combination determines the mode:

Mode

Components

Normal operation (backup only)

backup

Restore with backup

backup + restore

Restore only (no backup)

restore

When using the restore component, patch the bootstrap to specify the database and owner:

apiVersion: kustomize.config.k8s.io/v1beta1
kind: Kustomization

resources:
  - ../../base

components:
  - ../../../../../../../external/business-operations/kubernetes/components/cnpg-cluster/backup
  - ../../../../../../../external/business-operations/kubernetes/components/cnpg-cluster/restore

patches:
  - target:
      kind: Cluster
    patch: |
      - op: add
        path: /spec/bootstrap/recovery/database
        value: mydb
      - op: add
        path: /spec/bootstrap/recovery/owner
        value: myuser

Backup and restore#

Revisions#

  • The kubernetes cluster revision is applied so that a new cluster will never backup into the backups of an old cluster.

  • The postgresql cluster revision has to be increased when another restore is performed because the operator cannot backup into an existing folder in S3.

Backup details#

The backup component adds two parts to the database:

  • The content of spec.backup in the Cluster resource.

  • A resource of type ScheduledBackup to define the schedule.

The section Backup and Recovery of the CloudNativePG operator describes all the details about this.

The following patch shows the backup configuration added to a Cluster:

backup:
  retentionPolicy: 30d
  barmanObjectStore:
    data:
      compression: bzip2
    wal:
      compression: bzip2
      maxParallel: 8
    destinationPath: s3://${cnpg_backup_base_path}
    endpointURL: ${cnpg_backup_endpoint}
    serverName: db-rev${cluster_revision}
    s3Credentials:
      accessKeyId:
        name: cnpg-backup
        key: s3_access_key_id
      secretAccessKey:
        name: cnpg-backup
        key: s3_access_key_secret

This is complemented by a ScheduledBackup provided by the same component:

apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: CLUSTER_NAME
  namespace: default
spec:
  schedule: "@daily"
  immediate: true
  backupOwnerReference: self
  cluster:
    name: CLUSTER_NAME

Creating a cluster from a backup#

This is done based on the bootstrap procedure.

The backup concept is based on the cluster as a whole, as such it will restore the database with the used names from the backup.

By configuring both database and owner it is possible to have a new password be generated for the user and set within the postgresql cluster. The password will then also be available as a kubernetes secret.

Example:

bootstrap:
  recovery:
    source: source-cluster-name
    database: mydb
    owner: myuser

Point-In-Time Recovery (PITR)#

To restore a database to a specific point in time, use the recoveryTarget.targetTime field in the bootstrap configuration.

Important: The timestamp format must be PostgreSQL-compatible. Use the format YYYY-MM-DD HH:MM:SS+00 instead of ISO 8601 with Z suffix.

bootstrap:
  recovery:
    source: source-cluster-name
    database: mydb
    owner: myuser
    recoveryTarget:
      # Correct format - PostgreSQL accepts this
      targetTime: "2026-01-10 13:00:00+00"
      # Wrong format - PostgreSQL rejects the Z suffix
      # targetTime: "2026-01-10T13:00:00Z"

The CNPG operator converts timestamps to PostgreSQL’s recovery_target_time parameter. PostgreSQL does not accept the Z timezone suffix and will fail with:

invalid value for parameter "recovery_target_time":
"2026-01-10 13:00:00.000000Z"

Interacting manually with the database#

The databases are typically not exposed outside the cluster. This leaves the following options to manually interact with the database:

  • exec into the Pod via kubectl exec -it ....

  • Use the port-forwarding of kubectl, e.g. kubectl port-forward to make the database accessible locally.

The preferred method is to run commands directly in the Pod of the cluster so that tooling like psql is already correctly configured.

Using pg_dump#

Creating a dump manually:

kubectl exec myapp-db-1 -- pg_dump -Fc mydb > mydb.dump

Inspecting the contents:

pg_restore -l mydb.dump

Loading data via psql#

Loading a SQL dump into the cluster:

gzcat backup-database.sql.gz | \
  kubectl exec -n default -i myapp-db-1 -- \
  /bin/sh -c 'PGDATABASE=mydb psql'

The idea is to pipe the data through kubectl exec.