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.

caution

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

Replace <NAMESPACE> with your actual namespace (default: digitalai) throughout these steps.

Step 1 — Retrieve Credentials from the Existing CNPG Cluster

POSTGRES_PASSWORD=$(kubectl get secret -n <NAMESPACE> 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 <NAMESPACE> dai-xld-postgres-1 -- mkdir -p /var/lib/postgresql/data/backup

kubectl exec -n <NAMESPACE> 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 <NAMESPACE> 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"

# Verify the dump files exist and have non-zero size
kubectl exec -n <NAMESPACE> 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 <NAMESPACE> \
  dai-xld-postgres-1:/var/lib/postgresql/data/backup/xld-db.dump \
  ./postgres-backup/deploy/xld-db.dump

kubectl cp -n <NAMESPACE> \
  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

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

# Scale all three application components down to 0
kubectl patch -n <NAMESPACE> digitalaideploys.xld.digital.ai dai-xld \
  --type=merge --patch '{"spec": {"master": {"replicaCount": 0}, "worker": {"replicaCount": 0}, "centralConfiguration": {"replicaCount": 0}}}'

# Wait until all Deploy application pods are terminated
kubectl wait --for=delete pod -l app.kubernetes.io/name=digitalai-deploy \
  -n <NAMESPACE> --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:

# Delete the old CNPG cluster PVCs
kubectl delete pvc -n <NAMESPACE> -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

# Watch until cluster phase is "Cluster in healthy state"
kubectl get cluster -n <NAMESPACE> dai-xld-postgres -w

# Or wait until the primary pod is running and ready
kubectl wait pod/dai-xld-postgres-1 \
  -n <NAMESPACE> \
  --for=condition=Ready \
  --timeout=300s

# Confirm the upgraded PostgreSQL image is in use
kubectl get pod dai-xld-postgres-1 -n <NAMESPACE> \
  -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:

# Restore xld-db as its database owner (xld)
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"

# Restore xld-report-db as its database owner (xld-report)
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