Manual upgrade PostgreSQL

PostgreSQL cannot be automatically upgraded during an operator-to-operator or Helm-to-operator upgrade due to incompatible data formats between different PostgreSQL server versions. In such cases, a manual upgrade is necessary. The process involves first backing up the PostgreSQL data, then upgrading to the new PostgreSQL version, and finally restoring the data in the upgraded version.

During the upgrade from version 24.1 to 24.3 using the xl kube upgrade command, the PostgreSQL image will not be automatically upgraded. The PostgreSQL server will remain at version 24.1: 15.6.0-debian-12-r7 as used in 24.1. The newer version included with operator 24.3 is PostgreSQL server 16.3.0-debian-12-r19. This upgrade must be performed manually after completing the xl kube upgrade. For more information, see Upgrading a PostgreSQL Cluster.

Note: Before proceeding with any actions, ensure you have created a backup of your database. This manual upgrade applies only to versions above 23.3.

Step-by-step upgrade process:

1. Run the following commands to obtain the PostgreSQL, xlr, and xlr-report user passwords.

# 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

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

Ensure that there is enough free space on the data-dai-xlr-postgresql-0 PVC to store the backup data.

3. Patch the CR yaml file with existing PostgreSQL image tag. This ensures that during the upgrading process, the PostgreSQL image tag field's value is retained.

Fetch the current PostgreSQL image version from the cluster.

image=$(kubectl get pod -l app.kubernetes.io/name=postgresql -o jsonpath='{.items[0].spec.containers[0].image}' -n digitalai)
tag=$(echo $image | cut -d':' -f2)
echo $tag

Patch the CR yaml file with the postgresql image tag from shell variable or the actual value.

kubectl patch -n digitalai digitalaireleases.xlr.digital.ai dai-xlr \
  --type=merge --patch '{"spec": {"postgresql": {"image": {"tag": "'"$tag"'"}}}}' 

4. Upgrade Release with xl kube upgrade.

When prompted with this question: Edit list of custom resource keys that will migrate to the new Release CR:, ensure that this field spec.postgresql.image.tag is included. This will keep the PostgreSQL version unchanged during the upgrade.

5. 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}}'

Wait until all the Release pods are removed.

6. 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}}}}'

Wait until PostgreSQL is fully restarted in diagnostic mode, where the database will not be running.

7. SSH to PostgreSQL pod and rename or delete the old installation directory, depending on the free space on your PV.

mv /bitnami/postgresql/data /bitnami/postgresql/data.old

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

kubectl patch -n digitalai digitalaireleases.xlr.digital.ai dai-xlr \
  --type=merge --patch '{"spec": {"postgresql": {"image": {"tag": "<new-postgresql-version>"}, "diagnosticMode": {"enabled": false}}}}'

Wait for PostgreSQL to restart in normal mode with the new version.

9. Restore your data from backup with the 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

You can SSH into PostgreSQL pod and execute the following commands:

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

10. Restore Release pods to the original replicaCount.

For example, the original replicaCount is set to 3 in the following command:

kubectl patch -n digitalai digitalaireleases.xlr.digital.ai dai-xlr \
  --type=merge --patch '{"spec": {"replicaCount": 3}}'