Postgres migration

Postgres migration for docker

Overview

This chapter deals with the migration of the admin database, which is managed in a Postgres container. For further questions, please refer to the official postgres webpage.

Database backup

The database contains all objects and processes defined in XDM together with the historical information to these objects.

To prepare a backup, shutdown XDM and only start the XDM db:

docker-compose down

docker-compose up -d xdm-db
This is only an example how an backup can be done. This example uses the pgdump command of the postgres database. Please have a look at the postgres pgdump command documentation to get more information.

To back up an XDM instance, create a zipped backup file named xdm-db-$(date +%Y-%m-%d).sql.gz and put it into the directory admin-backups by running the following command:

docker compose exec -T xdm-db pg_dump -U xdm --clean --insert | gzip > ./admin-backups/xdm-db-$(date +%Y-%m-%d).sql.gz
Verify that the backup file xdm-db-$(date +%Y-%m-%d).sql.gz has been created.

  • the option --clean creates drop commands to delete existing objects in the database

  • the option --insert is used to create single INSERT statements instead of COPY. This makes the backup file larger and slows down the restore process. But it is useful to restore only specific rows of a table instead of the whole table.

  • there are more options that can be used. Please have a look at the postgres pgdump documentation to get a full list.

The previous command can be placed into a .sh file. This allows you to create a cronjob to automatically create backups with the following example expression:

0 0 * * * /opt/xdm/backup-xdm-db.sh

Upgrade the version

After you finished taking the backup, you must remove the bind mount on the local file system or Postgres Docker volume. To do this, postgres container has to be stopped.

docker-compose down

Bind Mount

In the provided docker-compose.yaml the bind mount pgdata is used, this may differ in your installation. Check the details of the bind under the volume section in the docker-compose.yaml for the service xdm-db.

This folder on the file system must now be removed.

Next step is changing the xdm-db version in your docker-compose.yaml.

postgres:
    image: docker.ubs-hainer.com/postgres:15

Volume

To find out the name of the Docker volume, you can use the following command, the second command deletes the volume with the specified name.

docker volume ls
docker volume rm <xdm-db-volume-name>

Next step is changing the xdm-db version in your docker-compose.yaml.

postgres:
    image: docker.ubs-hainer.com/postgres:15

Database restore

Please note that restoring a backup into XDM may take some time.

To restore an XDM instance the following steps need to be performed:

  1. Shutdown XDM and only start the XDM db:

docker-compose down
docker-compose up -d xdm-db

Use the following command to restore the backup to the database.

gunzip < ./admin-backups/xdm-db-$(date +%Y-%m-%d).sql.gz | docker-compose exec -T xdm-db psql -U xdm

Finally, the complete XDM environment can be restarted.

docker-compose up -d
After restarting XDM the neo4j graph database must be rebuilt. To do this, in the XDM web-ui go to Settings → System Configuration → Rebuild Graph .

Postgres migration for Kubernetes

Overview

Basically, the migration of the Postgres service between Docker and Kubernetes is very similar. The difference is basically connecting to the corresponding Kubernetes pod and creating and receiving the backup from there.

Database backup

Make sure no objects are created during the pg_dump command is running.
This is only an example how an backup can be done. This example uses the pgdump command of the postgres database. Please have a look at the postgres pgdump command documentation to get more information.

  • Run the following command to create a backup of your database:

kubectl --namespace <namespace> exec -it <postgres-container-name> -- bash -c "export PGPASSWORD='xdm'; pg_dump -U xdm --clean --insert" > xdm-db-backup.sql
Verify that the backup file xdm-db-backup.sql has been created.

  • the option --clean creates drop commands to delete existing objects in the database

  • the option --insert is used to create single INSERT statements instead of COPY. This makes the backup file larger and slows down the restore process. But it is useful to restore only specific rows of a table instead of the whole table.

  • there are more options that can be used. Please have a look at the postgres pgdump documentation to get a full list.

Upgrade postgres version

When the release is installed, the Helm Chart automatically switches to Postgres 15 when executing helm repro update.

If you do not want to carry out the automated upgrade, you have the option of permanently entering version 11 for Postgres in the values.yaml of your Helm Chart. To do this, open your values.yaml file, set the version property to the postgres section to version: 11 and save the changes to the YAML file.

Database restore

Please note that restoring a backup into XDM may take some time.

  • Delete the current xdm-postgres pod:

kubectl delete --namespace <namespace> pod xdm-postgres-0

  • Delete the persistent volume claim for the xdm-postgres pod:

kubectl delete --namespace <namespace> pvc xdm-postgres
This may take some time, depending on the size of the database.

  • Upgrade the xdm environment:

helm upgrade <release> <repository-name> --values values.yaml --namespace <namespace>

  • Import the previously created backup:

cat xdm-db-backup.sql | kubectl --namespace <namespace> exec -i xdm-postgres-0 -- bash -c "export PGPASSWORD='xdm'; psql -U xdm"

  • Restart the xdm-core pod:

Use the command kubectl get pods --namespace <namespace> to get the name of the xdm-core pod. 
kubectl delete --namespace <namespace> pod <xdm-core-pod-name>
After restarting XDM the neo4j graph database must be rebuilt. To do this, in the XDM web-ui go to Settings → System Configuration → Rebuild Graph .