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, xld, and xld-report user passwords.

# 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

2. To back up your database installation, SSH to the 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

Ensure that there is enough free space on the data-dai-xld-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 digitalaideploys.xld.digital.ai dai-xld \
  --type=merge --patch '{"spec": {"postgresql": {"image": {"tag": "'"$tag"'"}}}}' 

4. Upgrade Deploy with xl kube upgrade.

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

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

Wait until all the master and worker pods are removed.

6. Stop the PostgreSQL server on the pod by starting the pod in debug mode.

kubectl patch -n digitalai digitalaideploys.xld.digital.ai dai-xld \
  --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 into the PostgreSQL pod and rename or delete the old installation directory, depending on the available 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 digitalaideploys.xld.digital.ai dai-xld \
  --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-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

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

10. Restore Deploy master and worker pods to the original replicaCount.

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

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