Manual PostgreSQL Upgrade (CNPG Operator-Managed)

This guide covers the manual steps required to upgrade PostgreSQL across a major version when using the CloudNativePG (CNPG) operator-based PostgreSQL installation — i.e., when PostgreSQL is managed through the experimental postgresql-operator and postgresql-cluster rather than through the Deploy CR's built-in spec.postgresql keys.

The CNPG Cluster CR and its PVCs are not managed by xl kube upgrade preserve-key migration. A PostgreSQL major-version upgrade requires a clean data migration: dump the old data, let the upgrade create a fresh cluster on the new version, then restore the dump.

Use these manual steps only if the automatic PostgreSQL upgrade fails.

caution

Before proceeding with any actions, ensure you have created a backup of your database.

These steps use the default namespace digitalai. If Deploy is installed in a different namespace, replace digitalai in the commands with your namespace.

Step 1 — Retrieve Credentials from the Existing CNPG Cluster

POSTGRES_PASSWORD=$(kubectl get secret -n digitalai dai-xld-postgres-superuser \
  -o jsonpath="{.data.password}" | base64 -d)
echo "Postgres password: $POSTGRES_PASSWORD"

Step 2 — Dump All Databases from the Source Cluster Pod

kubectl exec -n digitalai dai-xld-postgres-1 -- mkdir -p /var/lib/postgresql/data/backup

kubectl exec -n digitalai dai-xld-postgres-1 \
  -- bash -c "PGPASSWORD='$POSTGRES_PASSWORD' pg_dump -U postgres -F c -f /var/lib/postgresql/data/backup/xld-db.dump xld-db"

kubectl exec -n digitalai dai-xld-postgres-1 \
  -- bash -c "PGPASSWORD='$POSTGRES_PASSWORD' pg_dump -U postgres -F c -f /var/lib/postgresql/data/backup/xld-report-db.dump xld-report-db"

kubectl exec -n digitalai dai-xld-postgres-1 -- ls -lh /var/lib/postgresql/data/backup/

Step 3 — Copy Dumps to Local Machine

mkdir -p ./postgres-backup/deploy

kubectl cp -n digitalai \
  dai-xld-postgres-1:/var/lib/postgresql/data/backup/xld-db.dump \
  ./postgres-backup/deploy/xld-db.dump

kubectl cp -n digitalai \
  dai-xld-postgres-1:/var/lib/postgresql/data/backup/xld-report-db.dump \
  ./postgres-backup/deploy/xld-report-db.dump

ls -lh ./postgres-backup/deploy/

Step 4 — Scale Application to Zero Replicas

MASTER_REPLICA_COUNT=$(kubectl get digitalaideploys.xld.digital.ai dai-xld -n digitalai \
  -o jsonpath='{.spec.master.replicaCount}')
WORKER_REPLICA_COUNT=$(kubectl get digitalaideploys.xld.digital.ai dai-xld -n digitalai \
  -o jsonpath='{.spec.worker.replicaCount}')
CC_REPLICA_COUNT=$(kubectl get digitalaideploys.xld.digital.ai dai-xld -n digitalai \
  -o jsonpath='{.spec.centralConfiguration.replicaCount}')
echo "Master: $MASTER_REPLICA_COUNT, Worker: $WORKER_REPLICA_COUNT, CentralConfig: $CC_REPLICA_COUNT"

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

kubectl wait --for=delete pod -l app.kubernetes.io/name=digitalai-deploy \
  -n digitalai --timeout=300s

Step 5 — Run xl kube upgrade

When prompted about PVC preservation:

Should we preserve persisted volume claims? If not all volume data will be lost: Yes

Answer: Y (Yes)

warning

Do not answer No to the PVC preservation prompt. Answering No deletes all PVCs in the namespace — including Deploy's own repository and central configuration volumes — causing data loss beyond the intended PostgreSQL upgrade.

Preserving PVCs keeps Deploy's application volumes intact. After the upgrade completes, manually delete only the old CNPG PostgreSQL PVCs so the new cluster can start fresh:

kubectl delete pvc -n digitalai -l cnpg.io/cluster=dai-xld-postgres

The wizard will still update the CNPG Operator & Cluster CR (dai-xld-postgres). Once the old PVCs are removed, the new cluster provisions fresh storage on the new PostgreSQL version.

Step 6 — Wait for the New Cluster to be Ready

kubectl get cluster -n digitalai dai-xld-postgres -w

kubectl wait pod/dai-xld-postgres-1 \
  -n digitalai \
  --for=condition=Ready \
  --timeout=300s

kubectl get pod dai-xld-postgres-1 -n digitalai \
  -o jsonpath='{.spec.containers[0].image}'; echo

Step 7 — Copy Dumps into the New Cluster Pod

The CNPG pod mounts /tmp as a read-only filesystem. Use /run/tmp/restore instead — /run is a writable tmpfs in the CNPG pod.

kubectl exec -n <NAMESPACE> dai-xld-postgres-1 -- mkdir -p /run/tmp/restore

kubectl cp -n <NAMESPACE> \
  ./postgres-backup/deploy/xld-db.dump \
  dai-xld-postgres-1:/run/tmp/restore/xld-db.dump

kubectl cp -n <NAMESPACE> \
  ./postgres-backup/deploy/xld-report-db.dump \
  dai-xld-postgres-1:/run/tmp/restore/xld-report-db.dump

kubectl exec -n <NAMESPACE> dai-xld-postgres-1 -- ls -lh /run/tmp/restore/

Step 8 — Restore the Database Dumps

xl kube upgrade provisions a fresh CNPG cluster which runs postInitApplicationSQLRefs on startup. That executes init.sql automatically, creating the application users and setting them as database owners. Restore as the database owner — no grants required.

First, retrieve the database owner passwords from the Deploy secret:

MAIN_DB_PASSWORD=$(kubectl get secret -n <NAMESPACE> dai-xld-digitalai-deploy \
  -o jsonpath="{.data.mainDatabasePassword}" | base64 -d)
REPORT_DB_PASSWORD=$(kubectl get secret -n <NAMESPACE> dai-xld-digitalai-deploy \
  -o jsonpath="{.data.reportDatabasePassword}" | base64 -d)

Then restore each database:

kubectl exec -n <NAMESPACE> dai-xld-postgres-1 \
  -- bash -c "PGPASSWORD='$MAIN_DB_PASSWORD' pg_restore \
      -U xld \
      -h localhost \
      -d xld-db \
      --no-owner \
      --exit-on-error \
      /run/tmp/restore/xld-db.dump"

kubectl exec -n <NAMESPACE> dai-xld-postgres-1 \
  -- bash -c "PGPASSWORD='$REPORT_DB_PASSWORD' pg_restore \
      -U 'xld-report' \
      -h localhost \
      -d xld-report-db \
      --no-owner \
      --exit-on-error \
      /run/tmp/restore/xld-report-db.dump"

Step 9 — Verify Row Counts (Optional)

kubectl exec -n <NAMESPACE> dai-xld-postgres-1 \
  -- bash -c "PGPASSWORD='$MAIN_DB_PASSWORD' psql -U xld -h localhost -d xld-db \
      -c 'SELECT count(*) FROM \"XLD_CIS\";'"

Step 10 — Scale Application Back to Original Replica Count

kubectl patch -n <NAMESPACE> digitalaideploys.xld.digital.ai dai-xld \
  --type=merge --patch "{\"spec\": {\"master\": {\"replicaCount\": $MASTER_REPLICA_COUNT}, \"worker\": {\"replicaCount\": $WORKER_REPLICA_COUNT}, \"centralConfiguration\": {\"replicaCount\": $CC_REPLICA_COUNT}}}"

kubectl get pods -n <NAMESPACE> -l app.kubernetes.io/name=digitalai-deploy -w

Wait for all application pods to reach Running and pass their readiness probes before treating the upgrade as complete.

Cleanup

Once the application is confirmed healthy, remove the dump files:

# Local cleanup
rm -rf ./postgres-backup/

# Pod cleanup
kubectl exec -n <NAMESPACE> dai-xld-postgres-1 -- rm -rf /run/tmp/restore