PostgreSQL

Published: 30 Dec. 2024 Last updated: 15 Sep. 2026

Summary

PostgreSQL (a.k.a. Postgres) is a powerful open-source relational database management system (RDBMS) known for its stability, advanced features, and strong adherence to SQL standards. Originally developed at the University of California, Berkeley, it has evolved into one of the most feature-rich databases available, often praised for its robustness and extensibility.

This Application Note discusses how to use CloudCasa to properly protect and restore PostgreSQL databases running in containers under Kubernetes. There are many different ways in which PostgreSQL can be installed in a Kubernetes cluster. This app note discusses two such mechanisms.

Zalando postgres-operator. With this mechanism, CloudCasa has been tested with PostgreSQL 17.2 clusters though the information herein is expected to apply to more recent versions as well.
CloudNativePG (CNPG) operator. With this mechanism, CloudCasa has been tested with PostgreSQL 18.1 clusters managed by the versions 1.27.3 and 1.30.0 of the CNPG operator.

Zalando postgres-operator

Backup

Note

Template Postgres pre and post-backup application hooks are available in Templates tab of the Configuration/App Hooks page in CloudCasa. However, the templates will need to be modified as discussed below in all but simple default configurations.

Locate your PostgreSQL cluster in your Kubernetes cluster. Make a note of the namespace, the “cluster-name” label set on the Pods (e.g. cluster-name=test-postgres-1), and also the container name in the Pod (e.g. postgres).
You will need to create 2 App Hooks in CloudCasa. These are commands that will run during the backup job before and after the PostgreSQL cluster is backed up.
Create an App Hook on CloudCasa by navigating to Configuration → App Hooks and clicking “Add App Hook”.
Create a name for the first App Hook which will be of type “Pre-backup”. Set the pod selector and container name obtained in Step 1. Set the command for this pre-backup hook to the following after updating it with details for your PostgreSQL cluster:
```
psql "postgresql://<PGUSER_SUPERUSER>:<PGPASSWORD_SUPERUSER>@<PGCLUSTER_SVC_NAME>:<PGCLUSTER_PORT>" -c "checkpoint"
```
PGUSER_SUPERUSER is the user with the highest level of privileges in the database. PGPASSWORD_SUPERUSER is the password for the PGUSER_SUPERUSER user. PGCLUSTER_SVC_NAME is the name of the service for the PostgreSQL cluster. PGCLUSTER_PORT is the port (e.g. 5432).
Click Save and repeat step 3-4 but this time we will create an App Hook of type “Post-backup”. Set the pod selector and container name to the same values as specified for the pre-backup App Hook created in Step 3. Set the command for this post-backup hook to the following after updating it with details for your PostgreSQL cluster:
```
psql "postgresql://<PGUSER_SUPERUSER>:<PGPASSWORD_SUPERUSER>@<PGCLUSTER_SVC_NAME>:<PGCLUSTER_PORT>" -c "SELECT pg_advisory_unlock_all();"
```
Use the same values used for the “postgresql“ connection URL in Step 4.
Create a backup definition following the CloudCasa User Guide. In the App Hooks section of the backup definition, add the Pre-backup and Post-backup hooks created in Step 4 and 5. Ensure that you have selected the correct namespace where the PostgreSQL cluster exists.

Restore

When creating the restore definition, ensure that you are only selecting the namespace of the source PostgreSQL cluster. Optionally, you can also select the namespace where the postgres-operator is installed if you want CloudCasa to restore the operator as well. Restore of the PostgreSQL cluster can be performed with or without the postgres-operator present. Whether operator is restored or installed explicitly afterwards, it will manage the restored Postgres instance.

Important: Ensure that you have enabled the “Include all cluster-scoped resources” switch when creating the restore definition. This will make sure that the CRD for “postgresql” resource is restored.

CloudNativePG (CNPG)

A CNPG cluster consists of one primary instance and, optionally, one or more replicas kept in sync with the primary via streaming replication. A pre-backup App Hook is required to flush pending changes to disk before the PVC snapshots are created. No post-backup hook is required — see the note under Step 5 below.

CNPG Backup

Locate your CNPG cluster in your Kubernetes cluster. Make a note of the namespace and the Cluster resource name:
```
kubectl get clusters.postgresql.cnpg.io -A
```
Also make a note of:
- The Pod label CNPG uses to identify the primary instance: cnpg.io/cluster=<cluster-name> together with cnpg.io/instanceRole=primary (see CNPG’s labels and annotations).
- The container name in the Pod, which is postgres.
- The name of your application database, its owning user, and the Secret holding that user’s credentials. CNPG creates (or references) a Secret containing username and password keys for the application user defined in bootstrap.initdb when the cluster was created; The secret is referred to as <app-secret-name> below.
Check whether your cluster allows superuser login:
```
kubectl get cluster.postgresql.cnpg.io <cluster-name> -n <namespace> -o jsonpath='{.spec.enableSuperuserAccess}{"\n"}'
```
If this returns false (the CNPG default, and the recommended setting - see enableSuperuserAccess in the Cluster API reference), the postgres superuser cannot log in remotely, so the pre-backup hook must authenticate as your application user instead. PostgreSQL 15 and later provide a predefined pg_checkpoint role that lets a normal, non-superuser role run CHECKPOINT. Grant it once to your application user, using a local, in-container connection (which uses peer authentication and works regardless of enableSuperuserAccess):
```
kubectl exec -n <namespace> <primary-pod> -c postgres -- \
    psql -U postgres -h /controller/run -c "GRANT pg_checkpoint TO <app-user>;"
```
This is a one-time operation - the grant is stored in the database’s own data directory, so it persists automatically through every future backup and restore of that data.
So the App Hook can read the application user’s password without writing it into the App Hook command text, mount the existing <app-secret-name> secret as a file into every instance Pod, using CNPG’s projectedVolumeTemplate field on the Cluster spec:
```
kubectl patch cluster.postgresql.cnpg.io <cluster-name> -n <namespace> --type merge -p '{"spec":{"projectedVolumeTemplate":{"sources":[{"secret":{"name":"<app-secret-name>","items":[{"key":"password","path":"password"}]}}]}}}'
```
This mounts the password at /projected/password inside the postgres container and triggers a rolling restart of the cluster’s Pods to apply it. Wait for the rollout to complete and confirm the file is present before continuing:
```
kubectl exec -n <namespace> <primary-pod> -c postgres -- cat /projected/password
```
Create an App Hook on CloudCasa by navigating to Configuration → App Hooks and clicking “Add App Hook”.
- Give it a name, and select the type “Pre-backup”.
- Set the App Selector to cnpg.io/cluster: <cluster-name> and cnpg.io/instanceRole: primary - this ensures the hook always runs on whichever Pod is currently primary, even after a failover changes which instance that is.
- Set the container name to postgres.
- Set the command to:
```
/bin/sh -c "psql \"postgresql://<app-user>:$(cat /projected/password)@<cluster-name>-rw:5432/<database>\" -c 'CHECKPOINT;'"
```
  <cluster-name>-rw is the read-write Service CNPG creates for the cluster, which always points at the current primary regardless of failover.
Click Save.

Note

No Post-backup App Hook is needed. Unlike the exclusive backup mode used by some other PostgreSQL backup tools (pg_backup_start() / pg_backup_stop()), a plain CHECKPOINT does not open any state that needs to be closed afterward - it simply flushes dirty buffers to disk and returns. The checkpoint minimizes how much WAL needs replaying when the restored copy first starts.
Create a backup definition following the CloudCasa User Guide. In the App Hooks section of the backup definition, add the Pre-backup hook created in previous steps. Ensure that you have selected the correct namespace where the PostgreSQL cluster exists.

See also

For more details on defining a backup, see Defining a Kubernetes backup job.

CNPG Restore

A CNPG cluster is made up of several Pods (one primary and, optionally, one or more replicas), but only the primary’s copy of the data is guaranteed to be fully consistent — it’s the instance the pre-backup App Hook actually ran the CHECKPOINT against. So rather than restoring every instance’s disk and hoping they all agree with each other, the simplest and safest approach is to restore only the primary’s disk (PVC), plus the small set of supporting resources the cluster needs to start. CNPG then automatically rebuilds the replicas by copying data from that restored primary, so every instance ends up with identical, consistent data.

If you haven’t already, enable YAML viewing of the resources in the backup: go to Configuration → Settings, and under Security, turn on the option to show YAML. You can skip this step if it’s already enabled.
Go to Clusters → Restores, and click Define restore.
In the Source cluster step, select the Kubernetes cluster where your CNPG-managed PostgreSQL cluster resides.
In the Recovery point step, select the recovery point you want to restore.
In the Select resources step, switch to the Specific resources tab, then click Select resources. This opens the Recovery point browser and starts a resource-browsing operation, which may take a moment to bring up the resource list.
In the Recovery point browser, expand the source namespace and select only the following:
- The clusters entry — this is the CNPG Cluster resource, which tells the operator what to manage.
- Under persistentvolumeclaims, click the eye icon next to each PVC to view its YAML and check its labels. Select only the PVC whose labels include cnpg.io/instanceRole: primary — this is the primary’s disk, which holds the actual database files. Do not select the other instances’ PVCs; CNPG will create fresh copies of those automatically.
- All entries under secrets belonging to the cluster (the application user’s credentials, and the CA, server, and replication TLS certificates) — needed for the instance to start and to authenticate replication.
- All entries under services for the cluster (the ones ending in -rw, -ro, and -r) — give the cluster its stable network names, which your application and the App Hook both connect to.
- The entry under serviceaccounts created for the cluster — required for the instance Pods to even be created; Kubernetes rejects a Pod outright if the ServiceAccount it names doesn’t exist. It also grants the permissions the instance needs to read its own Secrets and update the Cluster’s status.
- Any entries under configmaps belonging to the cluster.
Close the Recovery point browser once you’ve made your selections.
In the Destination step, choose a namespace and/or cluster different from the original source - never restore over a running CNPG cluster.
Continue through Restore transforms and App Hooks (no changes needed in either) to the Summary step, and click Save & Restore.
Once the restore completes, confirm the cluster comes up healthy:
```
kubectl get pods -n <restore-namespace>
kubectl get cluster.postgresql.cnpg.io <cluster-name> -n <restore-namespace>
```
You should see the same number of instance Pods as the original cluster, all Running/Ready, and the Cluster resource should report STATUS: Cluster in healthy state. Only the instance whose PVC you restored will already have data on it right after the restore; CNPG automatically creates the other instances and copies the data over to them so all instances end up with identical, consistent data. How long this takes depends on the size of the primary’s data. The App Hook continues to work unchanged for future backups, since its Secret and volume mount configuration were restored along with the rest of the Cluster resource.