Skip to main content
Version: Release 23.3

Manual upgrade PostgreSQL

Postgresql during operator2operator upgrade or helm2operator upgrade will not upgrade automatically to the latest server version (it will stay on the server version from 23.1: 11.13.0-debian-10-r73). The version installed with operator 23.3 is Postgresql server 15.4.0-debian-11-r10.

To upgrade PostgreSQL server from 11.13.0 to 15.4.0 we need to that manually. See more details on possible options here:

NOTE: Please, create a backup of your database before running any of these actions.

NOTE: This manual is only for the versions above 23.3

We will describe here Upgrading Data via pg_dumpall:

  1. Get the postgresql password:
# get postgres user password
kubectl get secret --namespace digitalai dai-xlr-postgresql -o jsonpath="{.data.postgres-password}" | base64 --decode; echo
# get xlr user password
kubectl get secret --namespace digitalai dai-xlr-digitalai-release -o jsonpath="{.data.mainDatabasePassword}" | base64 --decode; echo
# get xlr-report user password
kubectl get secret --namespace digitalai dai-xlr-digitalai-release -o jsonpath="{.data.reportDatabasePassword}" | base64 --decode; echo
  1. Back up your database installation, ssh to PostgreSQL pod:
mkdir /bitnami/postgresql/backup
pg_dump -U xlr xlr-db | gzip > /bitnami/postgresql/backup/pg_dump-xlr-db.sql.gzip
pg_dump -U xlr-report xlr-report-db | gzip > /bitnami/postgresql/backup/pg_dump-xlr-report-db.sql.gzip

Make sure that you have enough free space on the data-dai-xlr-postgresql-0 PVC for the backup data.

  1. Stop all Release pods, by using replica count 0:
kubectl patch -n digitalai digitalaireleases.xlr.digital.ai dai-xlr \
  --type=merge --patch '{"spec": {"replicaCount": 0}}'

It will remove all Release pods. Wait until it is finished!

  1. Stop the postgresql server on the pod by starting pod in debug mode:
kubectl patch -n digitalai digitalaireleases.xlr.digital.ai dai-xlr \
  --type=merge --patch '{"spec": {"postgresql": {"diagnosticMode": {"enabled": true}}}}'

It will restart postgresql in the diagnostic mode, the database will be not running. Wait until it is finished!

  1. Rename or delete the old installation directory, depending on the free space on your PV, ssh to PostgreSQL pod:
mv /bitnami/postgresql/data /bitnami/postgresql/data.old
  1. Upgrade image to new tag and start it in the normal mode:
kubectl patch -n digitalai digitalaireleases.xlr.digital.ai dai-xlr \
  --type=merge --patch '{"spec": {"postgresql": {"image": null, "diagnosticMode": {"enabled": false}}}}'

It will restart postgresql in the normal mode, the database will be running with a new version. Wait until it is finished!

  1. Restore your data from backup with:

Get init script and execute output in the shell

kubectl get secret -n digitalai dai-xlr-postgresql-release -o jsonpath='{.data.init\.sql}' | base64 -d > init.sql
kubectl cp -c postgresql init.sql digitalai/dai-xlr-postgresql-0:/bitnami/postgresql/backup/init.sql

Execute the code from the scripts, ssh to PostgreSQL pod:

cat /bitnami/postgresql/backup/init.sql | psql -U postgres
gunzip -c /bitnami/postgresql/backup/pg_dump-xlr-db.sql.gzip | psql -U xlr xlr-db
gunzip -c /bitnami/postgresql/backup/pg_dump-xlr-report-db.sql.gzip | psql -U xlr-report xlr-report-db
  1. Restore Release pods to the original replicaCount (here is 3 as example):
kubectl patch -n digitalai digitalaireleases.xlr.digital.ai dai-xlr \
  --type=merge --patch '{"spec": {"replicaCount": 3}}'