CloudNative PostgreSQL

Example Cluster

Create the S3 Bucket

Create a Bucket in an Exoscale zone that is NOT at-vie-1 by following the instructions:

---
apiVersion: appcat.vshn.io/v1
kind: ObjectBucket
metadata:
   name: <project>-s3-bucket-pgbackup
   namespace: aris-<project>
spec:
   parameters:
     bucketName: <project>-s3-bucket-pgbackup
     region: at-vie-2
   writeConnectionSecretToRef:
     name: <project>-s3-bucket-pgbackup

This creates a bucket in Exoscale region at-vie-2 and writes the connection information to a secret, that can then be used by PostgreSQL.

Now that you have a S3 bucket, it is time to configure your database and database backup. How this is done depends on when you create your database. The way CloudNative PostgreSQL handles backups and restores has changed in recent releases and the old way is now considered 'legacy' but has not been removed yet. Although a date for the deprecation of the old backup has not been announced as of writing, we strongly recommend you use the new method for all newly created clusters.

The following text describes the new method of database creation and backup. The old method can be found here for reference: postgresql-legacy.md.

Create the ObjectStore configuration for Backup

Prepare the ObjectStore configuration object that holds the information necessary for PostgreSQL to perform regular scheduled backups as well as backups of the write ahead logs (WALs):

---
apiVersion: barmancloud.cnpg.io/v1
kind: ObjectStore
metadata:
  name: backup-store
  namespace: aris-<project>
spec:
  # configuration for s3 storage
  # credentials are stored in an external secret
  configuration:
    destinationPath: s3://<project>-s3-bucket-pgbackup/
    endpointURL: https://sos-at-vie-2.exo.io
    s3Credentials:
      accessKeyId:
        name: <project>-s3-bucket-pgbackup
        key: AWS_ACCESS_KEY_ID
      secretAccessKey:
        name: <project>-s3-bucket-pgbackup
        key: AWS_SECRET_ACCESS_KEY
    wal:
      compression: gzip
  # do not reserve too much, but give the backup some flexibility
  instanceSidecarConfiguration:
    resources:
      requests:
        cpu: "10m"
        memory: "128Mi"
      limits:
        cpu: "1"
        memory: "256Mi"
  retentionPolicy: "30d"

Create the Database

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  namespace: aris-<project>
  name: <project>-db

spec:
  description: "Postgres cluster for <project>"
  imageName: ghcr.io/cloudnative-pg/postgresql:17.7-standard-trixie
  instances: 3
  primaryUpdateStrategy: unsupervised
  startDelay: 300
  stopDelay: 300
  monitoring:
    enablePodMonitor: true

  # activate backup plugin and use it for WAL archiving
  plugins:
    - name: barman-cloud.cloudnative-pg.io
      isWALArchiver: true
      parameters:
        barmanObjectName: backup-store

  # Persistent storage configuration: start small, you can always grow
  # Use at least the amount of storage you reserved for max_slot_wal_keep_size
  # plus your database size
  storage:
    storageClass: rbd-storagepool-cluster
    size: 2Gi

  postgresql:
    parameters:
      max_slot_wal_keep_size: "1GB"

  # this creates an empty database with random credentials for the owner
  # if you want to or have to set the credentials have a look at the docs
  bootstrap:
    initdb:
       database: xxx
       owner: xxx

  # these are the resources for a very small database that is allowed
  # to use more resources during startup or for peaks
  resources:
    requests:
      memory: "500Mi"
      cpu: "50m"  # should be 2
    limits:
      memory: "2Gi"

  # always place the replicas on different nodes!
  affinity:
    enablePodAntiAffinity: true
    topologyKey: kubernetes.io/hostname
    podAntiAffinityType: required

This YAML when applied will create 3 PostgreSQL Pods with their respective storage volumes. One of the Pods will be the primary database node, the others are followers.

Services for read-write and readonly connections are automatically created by the operator.

But first create a base backup while your database is still small.

Backup the database

The YAML from above will create a database and backups of the write ahead logfiles (WAL). To restore the database from this backup you will need every WAL ever written since the last base backup.

We therefore recommend you create a backup schedule, e.g. backup your database by creating a ScheduledBackup custom resource:

apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: <project>-db-scheduledbackup
  namespace: aris-<project>
spec:
  backupOwnerReference: self
  cluster:
    name: <project>-db
  schedule: "0 30 10 * * *"
  method: plugin
  pluginConfiguration:
    name: barman-cloud.cloudnative-pg.io
  target: primary

This will create a full backup of your database every day at 10:30 a.m. UTC, i.e. at 11:30 CET/12:30 CEST. The ScheduledBackup will use your S3 bucket to save the backups. It will keep 30 full backups. the Objectstores retentionPolicy and delete the oldest when that limit is reached.

Use the database

The credentials are placed in a secret called xxx-app where xxx is the name of your database. The secret contains everything you need to connect to your database in several formats, ready to use. If one of the formats fits your use case, just mount the secret into your deployment and you are ready to go.