Mongo, PostgreSQL Backup/Restore Procedure for CX on Kubernetes ( manual procedure )

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

There is an actively developed guide for backups using Velero for EF-CX solution on kubernetes. This procedure is only valid for manual backup, and doesn't ascertain the continued 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/{mongo,postgresql}

Mongo Backup

In order to take backup from the current mongo databases, follow these steps

Run the mongoDB client pod

CODE

kubectl run --namespace ef-external  --tty -i   mongo-mongodb-client  --env="MONGODB_ROOT_PASSWORD=$MONGODB_ROOT_PASSWORD" --image docker.io/bitnami/mongodb:6.0.2-debian-11-r1 --command -- sleep infinity

wait for the mongo-client pod to run. Sample run given below

CODE

#kubectl get pods -n ef-external mongo-mongodb-client
NAME                   READY   STATUS    RESTARTS   AGE
mongo-mongodb-client   1/1     Running   0          16s

For CX-4.5.1 onwards only

CODE

mkdir /tmp/mongodb_certs
CERTFILES=($(kubectl get secret mongo-mongodb-ca -n ef-external -o go-template='{{range $k,$v := .data}}{{$k}}{{"\n"}}{{end}}'))
for f in ${CERTFILES[*]}; do   kubectl get secret mongo-mongodb-ca  -n ef-external -o go-template='{{range $k,$v := .data}}{{ if eq $k "'$f'"}}{{$v  | base64decode}}{{end}}{{end}}' > /tmp/mongod

kubectl -n ef-external cp /tmp/mongodb_certs mongo-mongodb-client:/tmp/

Exec into the mongo client pod

CODE

kubectl -n ef-external exec -i -t mongo-mongodb-client -- bash

For releases before 4.5.1

take the backup of all databases from the current host

CODE

mongodump --host "mongo-mongodb" --gzip --out /tmp/mongo-backup-$(date +%Y-%m-%d)

For 4.5.1 and newer

CODE

mongodump  \
--host "mongo-mongodb" \
--authenticationDatabase admin -u root -p"Expertflow123"  \
--ssl   \
--tlsInsecure   \
--sslPEMKeyFile  /tmp/mongodb_certs/client-pem   \
--sslCAFile  /tmp/mongodb_certs/client-pem \
--gzip \
--out /tmp/mongo-backup-$(date +%Y-%m-%d)

Please note that the path of the backup has to under /tmp folder. the above command will create a folder under /tmp with the date in YYYY-MM-DD appended e.g /tmp/mongo-backup-2023-09-12

exit out of the mongo-client pod

CODE

exit

step-4 retrieve the backup file from the POD to actual storage ( change the directory name with correct date parameters before continuing )

CODE

kubectl -n ef-external cp  mongo-mongodb-client:/tmp/mongo-backup-<DATE> $HOME/backups/mongo/mongo-backup-<DATE>

PostgreSQL Backup

For PostgreSQL backups, please follow along these steps.

export the admin username/password pair for 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)

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

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

exec into the postgresql-client pod

CODE

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

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:/$

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 previous step and continue with their backup

take 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

On the host System. once all the databases from the postgresql are backed up succcessfully, exit out of the postgresql client pod and recove 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 step7 and step-8 for all required databases if other databases' backup are also required.

Restore

PostgreSQL

export admin and sa user password

export the admin username/password pair for 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)

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

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

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

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

CODE

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

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:/$

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 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 "lienceManager" Database by running

CODE

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

similarly restore the keycloak_db database

CODE

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

exit the postgresql-client pod.

CODE

exit

Mongo

start the client pod

Run the mongoDB client POD

CODE

kubectl run --namespace ef-external  --tty -i   mongo-mongodb-client  --env="MONGODB_ROOT_PASSWORD=$MONGODB_ROOT_PASSWORD" --image docker.io/bitnami/mongodb:6.0.2-debian-11-r1 --command -- sleep infinity

wait for the mongo-client pod to run. Sample run given below

CODE

#kubectl get pods -n ef-external mongo-mongodb-client
NAME                   READY   STATUS    RESTARTS   AGE
mongo-mongodb-client   1/1     Running   0          16s

copy the backup into the mongo-client pod

CODE

kubectl -n ef-external cp /root/backups/mongo mongo-mongodb-client:/tmp/

Exec into the mongo client pod

CODE

kubectl -n ef-external exec -i -t mongo-mongodb-client -- bash

For pre 4.5.1 releases.

restore the backup of all databases from the current host

CODE

mongorestore --host "mongo-mongodb" --gzip --verbose  /tmp/mongo

login into the mongodb and verify the databases

CODE

mongosh --host "mongo-mongodb" --eval 'show dbs'

for 4.5.1 and newer releases

CODE

mongorestore  \
--host "mongo-mongodb" \
--authenticationDatabase admin -u root -p $MONGODB_ROOT_PASSWORD  \
--ssl   \--tlsInsecure   \
--sslPEMKeyFile  /tmp/mongodb_certs/client-pem   \
--sslCAFile  /tmp/mongodb_certs/client-pem \
--gzip \
--verbose  /tmp/mongo/mongo-backup-<DATE>/

verify the restore

CODE

mongosh admin --host "mongo-mongodb" \
--authenticationDatabase admin \
-u root \
-p $MONGODB_ROOT_PASSWORD \
--tls  \
--tlsAllowInvalidHostnames  \
--tlsAllowInvalidCertificates \
--tlsCertificateKeyFile /tmp/mongodb_certs/client-pem  \
--tlsCAFile /tmp/mongodb_certs/client-pem --eval 'show dbs'

exit out of the mongo-client pod

CODE

exit

delete the mongodb client pod

CODE

kubectl -n ef-external delete pod  mongo-mongodb-client

Make sure to FLUSH Redis after restoring MongoDB and then restart routing engine and agent manager pods.