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:

• Upgrading a PostgreSQL Cluster

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:

0. Get the postgresql password:

# get postgres user password
kubectl get secret --namespace digitalai dai-xld-postgresql -o jsonpath="{.data.postgres-password}" | base64 --decode; echo
# get xld user password
kubectl get secret --namespace digitalai dai-xld-digitalai-deploy -o jsonpath="{.data.mainDatabasePassword}" | base64 --decode; echo
# get xld-report user password
kubectl get secret --namespace digitalai dai-xld-digitalai-deploy -o jsonpath="{.data.reportDatabasePassword}" | base64 --decode; echo

1. Back up your database installation, ssh to PostgreSQL pod:

mkdir /bitnami/postgresql/backup
pg_dump -U xld xld-db | gzip > /bitnami/postgresql/backup/pg_dump-xld-db.sql.gzip
# execute this if you have separate xld-report-db 
pg_dump -U xld-report xld-report-db | gzip > /bitnami/postgresql/backup/pg_dump-xld-report-db.sql.gzip

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

2. Stop all master and worker pods, by using replica count 0:

kubectl patch -n digitalai digitalaideploys.xld.digital.ai dai-xld \
  --type=merge --patch '{"spec": {"worker": {"replicaCount": 0}}}'
kubectl patch -n digitalai digitalaideploys.xld.digital.ai dai-xld \
  --type=merge --patch '{"spec": {"master": {"replicaCount": 0}}}'

It will remove all master and worker pods. Wait until it is finished!

3. Stop the postgresql server on the pod by starting pod in debug mode:

kubectl patch -n digitalai digitalaideploys.xld.digital.ai dai-xld \
  --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!

4. 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

5. Upgrade image to new tag and start it in the normal mode:

kubectl patch -n digitalai digitalaideploys.xld.digital.ai dai-xld \
  --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!

6. Restore your data from backup with:

Get init script and execute output in the shell

kubectl get secret -n digitalai dai-xld-postgresql-deploy -o jsonpath='{.data.init\.sql}' | base64 -d > init.sql
kubectl cp -c postgresql init.sql digitalai/dai-xld-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-xld-db.sql.gzip | psql -U xld xld-db
# execute this if you have separate xld-report-db 
gunzip -c /bitnami/postgresql/backup/pg_dump-xld-report-db.sql.gzip | psql -U xld-report xld-report-db

7. Restore Deploy master and worker pods to the original replicaCount (here is 3 as example:

kubectl patch -n digitalai digitalaideploys.xld.digital.ai dai-xld \
  --type=merge --patch '{"spec": {"master": {"replicaCount": 3}}}'
kubectl patch -n digitalai digitalaideploys.xld.digital.ai dai-xld \
  --type=merge --patch '{"spec": {"worker": {"replicaCount": 3}}}'