PostgreSQL Backup/Restore Procedure for EF-CX on Kubernetes

In this guide, we will evaluate procedures to back up from PostgreSQL databases. This procedure requires that the end-user is comfortable with common interfacing with Kubernetes and understands the basics of Kubernetes operations.

There is an actively developed guide for backups using Velero for the EF-CX solution on Kubernetes. This procedure is only valid for manual backup, and doesn't establish a continuous approach for backup. Velero-based backups are considered more appropriate for continuous and effective backup solutions. Please refer to Kubernetes Backup/Restore using Velero

Backups

In order to save backups on your local system, create a folder under $HOME/backups

CODE

mkdir -p  $HOME/backups/postgresql

PostgreSQL Backup

For PostgreSQL backups, please follow these steps.

Step 1: Export the admin username/password pair for the PostgreSQL-client pod

CODE

export POSTGRES_ADMIN_PASSWORD=$(kubectl get secret --namespace ef-external ef-postgresql -o jsonpath="{.data.postgres-password}" | base64 -d)

and for sa user ( default user for EF-CX component using PostgreSQL database )

CODE

export POSTGRES_PASSWORD=$(kubectl get secret --namespace ef-external ef-postgresql -o jsonpath="{.data.password}" | base64 -d)

Step 2: Launch the PostgreSQL client

CODE

 kubectl run ef-postgresql-client  --tty -i  --namespace ef-external --image docker.io/bitnami/postgresql:14.5.0-debian-11-r21 --env="PGPASSWORD=$POSTGRES_PASSWORD"  --command -- sleep infinity

Step 3: Verify the pod is running

CODE

# kubectl -n ef-external get pods "ef-postgresql-client"
NAME                   READY   STATUS    RESTARTS       AGE
ef-postgresql-client   1/1     Running   15 (15d ago)   120d

Step 4: exec into the PostgreSQL-client pod

CODE

kubectl -n ef-external exec -it ef-postgresql-client -- bash

Step 5: Evolve the pod Env script

Execute the environment setup for postgresql-client ( Only needed when the postgresql is running in non-HA mode, like no pgpool and multiple replicas of PostgreSQL are running )

CODE

/opt/bitnami/scripts/postgresql/entrypoint.sh /bin/bash

The above step will output something like below:

CODE

I have no name!@ef-postgresql-client:/$ /opt/bitnami/scripts/postgresql/entrypoint.sh /bin/bash
postgresql 14:55:10.29
postgresql 14:55:10.29 Welcome to the Bitnami postgresql container
postgresql 14:55:10.30 Subscribe to project updates by watching https://github.com/bitnami/containers
postgresql 14:55:10.30 Submit issues and feature requests at https://github.com/bitnami/containers/issues
postgresql 14:55:10.30

postgres@ef-postgresql-client:/$

Step 6: List databases to take backups.

CODE

psql --host ef-postgresql -U sa -d licenseManager -p 5432 -q -A -t -c  "SELECT datname FROM pg_database"

Identify the databases in the first column of the output in the previous step, and continue with their backup

Step 7: Archive the backup

Take a backup of the keycloak_db and licenseManager Databases.

CODE

# keycloak_db database

pg_dump --host ef-postgresql -U sa  -F c -b -v -f  "/tmp/licenseManager.backup" "licenseManager"

#licenseManager database
pg_dump --host ef-postgresql -U sa  -F c -b -v -f  "/tmp/keycloak_db.backup" "keycloak_db"

exit the postgresql-client pod

CODE

exit; exit

Step 8: Restore the backup onto the host

On the host System, once all the databases from the PostgreSQL are backed up successfully, exit out of the PostgreSQL client pod and recover the files onto your host system to be further saved at a secure location.

CODE

kubectl -n ef-external cp ef-postgresql-client:/tmp/keycloak_db.backup $HOME/backups/postgresql/keycloak_db.backup

kubectl -n ef-external cp ef-postgresql-client:/tmp/licenseManager.backup $HOME/backups/postgresql/licenseManager.backup

Please repeat steps 7 and 8 for all required databases if other databases' backups are also required.

Restore

PostgreSQL Restore

Step 1: Admin and sa Password

Export the admin username/password pair for the PostgreSQL-client pod

CODE

export POSTGRES_ADMIN_PASSWORD=$(kubectl get secret --namespace ef-external ef-postgresql -o jsonpath="{.data.postgres-password}" | base64 -d)

and for sa user ( default user for EF-CX component using PostgreSQL database )

CODE

export POSTGRES_PASSWORD=$(kubectl get secret --namespace ef-external ef-postgresql -o jsonpath="{.data.password}" | base64 -d)

Step 2: Launch the PostgreSQL client

CODE

 kubectl run ef-postgresql-client  --tty -i  --namespace ef-external --image docker.io/bitnami/postgresql:14.5.0-debian-11-r21 --env="PGPASSWORD=$POSTGRES_PASSWORD"  --command -- sleep infinity

Step 3: Verify the pod is running

CODE

# kubectl -n ef-external get pods "ef-postgresql-client"
NAME                   READY   STATUS    RESTARTS       AGE
ef-postgresql-client   1/1     Running   15 (15d ago)   120d

Step 4: Copy the dump files from host

Copy all the backup files from the host. Here we are restoring only 2 backups for keycloak_db and licensemanage databases

CODE

# keycloak_db database
kubectl -n ef-external cp $HOME/backups/postgresql/keycloak_db.backup  ef-postgresql-client:/tmp/keycloak_db.backup

#licenseManager database
kubectl -n ef-external cp $HOME/backups/postgresql/licenseManager.backup  ef-postgresql-client:/tmp/licenseManager.backup

Step 5: exec into the postgresql-client pod

Once the pod is running, exec into the postgresql-client pod

CODE

kubectl -n ef-external exec -it ef-postgresql-client -- bash

Step 6: Evolve the env script.

Execute the environment setup for PostgreSQL-client ( Only needed when the postgresql is running in non-HA mode, like no pgpool and multiple replicas of PostgreSQL are running )

CODE

/opt/bitnami/scripts/postgresql/entrypoint.sh /bin/bash

The above step will output something like below:

CODE

I have no name!@ef-postgresql-client:/$ /opt/bitnami/scripts/postgresql/entrypoint.sh /bin/bash
postgresql 14:55:10.29
postgresql 14:55:10.29 Welcome to the Bitnami postgresql container
postgresql 14:55:10.30 Subscribe to project updates by watching https://github.com/bitnami/containers
postgresql 14:55:10.30 Submit issues and feature requests at https://github.com/bitnami/containers/issues
postgresql 14:55:10.30

postgres@ef-postgresql-client:/$

Step 7: Restore

Restore all databases one by one by running

For keyclock_db;

CODE

pg_restore --host ef-postgresql -U sa  -d "keycloak_db"  -v "/tmp/keycloak_db.backup"

For licenseManager databases, DROP the existing database

CODE

dropdb --host ef-postgresql -U sa  "licenseManager"

Create the licenseManager Databases with 'sa' user

CODE

createdb  --host ef-postgresql -U sa  "licenseManager"

Verify that the database is created successfully by running

CODE

psql --host ef-postgresql -U sa keycloak_db   -p 5432 -t  -c  "SELECT datname FROM pg_database"

It will display something like below:

CODE

postgres@ef-postgresql-client:/tmp$ psql --host ef-postgresql -U sa keycloak_db   -p 5432 -t  -c  "SELECT datname FROM pg_database"
 postgres
 template1
 template0
 keycloak_db
 superset

Restore the "licenseManager" Database by running

CODE

pg_restore --host ef-postgresql -U sa  -d "licenseManager"  -v "/tmp/licenseManager.backup"

Exit the postgresql-client pod.

CODE

exit