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.

About security

This method creates an S3 bucket using your own credentials, which is ok for development and staging, but not for production. An attacker who has your credentials can also delete the bucket, destroying all your backups. We can create a bucket for you which you do not have permissions to delete. Additionally, we will enable object versioning on the bucket for 180 days, so your data cannot be deleted even if an attacker gains access to the bucket credentials. Regardless of which method you use, you can follow the next section with your credentials.

Disaster recovery

Your backup is still hosted on ARISnet's underlying platform, Exoscale, which is why your backup should at the very least be stored in a different region. Depending on how sensitive your data is, you should consider creating an off-site backup that is independent of ARISnet.

Create the ObjectStore configuration for Backup

Note

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

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: "150Mi"
      cpu: "50m"  # should be 2
    limits:
      memory: "1Gi"

  # 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.

Restore or clone the database

From a technical standpoint a database restore and a database clone are basically the same. The name of a cloned database has to be different than the original name (you can't call your database <project>-db if that database already exists), but that's about it.

When creating a database clone, a new database is created by crafting a variant of the YAMLs that were used for the creation of the original database.

For backups of the clone you'll need a new S3 bucket:

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

This Bucket is then referenced in an ObjectStore like for the original database:

---
apiVersion: barmancloud.cnpg.io/v1
kind: ObjectStore
metadata:
  name: backup-clone-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>-clone-s3-bucket-pgbackup
        key: AWS_ACCESS_KEY_ID
      secretAccessKey:
        name: <project>-clone-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"

Then create a database that uses the original ObjectStore as the data source:

Clone the Database

The database creation stanza is the same as the one for the original database, but instead of spec.bootstrap.initdb, you have to specify spec.bootstrap.recovery and spec.externalClusters. The barmanObjectName referenced in spec.externalClusters has to be the ObjectStore that is used by the original database, not the one just created!

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

spec:
[...]
  # this creates a cloned database
  # the source is an "external cluster" that references the old database 
  bootstrap:
    recovery:
      source: clone-source
  externalClusters:
  - name: clone-source
    plugin:
      name: barman-cloud.cloudnative-pg.io
      parameters:
        barmanObjectName: backup-store
        serverName: <project>-db
[...]

After the Cluster is created the operator starts recovering the database. It is ready when all 3 database nodes are ready. Should you want to keep your database make sure to create a backup schedule.

The procedure outlined so far creates a clone of the database that contains whatever the state of the original database is at the time of the clone.

Point in time restore

The procedure for creating a point in time restore is the same as for creating a database clone. First create a Bucket, then an ObjectStore and finally the new Cluster:

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

spec:
[...]
  # this creates a cloned database
  # the source is an "external cluster" that references the old database 
  bootstrap:
    recovery:
      source: clone-source
      recoveryTarget:
        # Time base target for the recovery
        targetTime: "2026-10-10 10:00:00"
  externalClusters:
  - name: clone-source
    plugin:
      name: barman-cloud.cloudnative-pg.io
      parameters:
        barmanObjectName: backup-store
        serverName: <project>-db
[...]

This will create a new database that contains the database as of 2026-10-10 10:00:00 UTC. You can also specify the timezone for the recovery point. If a timezone is missing the targetTime is interpreted as UTC.