PostgreSQL

Published: 30 Dec. 2024 Last updated: 11 Dec. 2025

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.

CloudCasa has been tested for this application note with PostgreSQL 17.2 clusters created using the Zalando postgres-operator. The information herein is expected to apply to more recent versions as well.

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.