Skip to main content
Version: Release Next

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

Step-by-step upgrade process:​

note

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

Step 1 — Retrieve Credentials from the Existing Cluster​

# Superuser (postgres) password — used for pg_dump
POSTGRES_PASSWORD=$(kubectl get secret -n <NAMESPACE> dai-xlr-postgres-superuser \
  -o jsonpath="{.data.password}" | base64 -d)
echo "Postgres password: $POSTGRES_PASSWORD"

Step 2 — Dump All Databases from the Source Cluster Pod​

Create a backup directory inside the running PostgreSQL pod and dump each database using the superuser account. The dumps use the custom format (-F c) which supports selective restore and is compressed.

# Create the backup directory inside the pod
kubectl exec -n <NAMESPACE> dai-xlr-postgres-1 -- mkdir -p /var/lib/postgresql/data/backup

# Dump xlr-db
kubectl exec -n <NAMESPACE> dai-xlr-postgres-1 \
  -- bash -c "PGPASSWORD='$POSTGRES_PASSWORD' pg_dump -U postgres -F c -f /var/lib/postgresql/data/backup/xlr-db.dump xlr-db"

# Dump xlr-report-db
kubectl exec -n <NAMESPACE> dai-xlr-postgres-1 \
  -- bash -c "PGPASSWORD='$POSTGRES_PASSWORD' pg_dump -U postgres -F c -f /var/lib/postgresql/data/backup/xlr-report-db.dump xlr-report-db"

# Verify the dump files exist and have non-zero size
kubectl exec -n <NAMESPACE> dai-xlr-postgres-1 -- ls -lh /var/lib/postgresql/data/backup/

Step 3 — Copy Dumps to Local Machine​

Copy the dumps out before the upgrade destroys the pod and PVC.

mkdir -p ./postgres-backup/release

kubectl cp -n <NAMESPACE> \
  dai-xlr-postgres-1:/var/lib/postgresql/data/backup/xlr-db.dump \
  ./postgres-backup/release/xlr-db.dump

kubectl cp -n <NAMESPACE> \
  dai-xlr-postgres-1:/var/lib/postgresql/data/backup/xlr-report-db.dump \
  ./postgres-backup/release/xlr-report-db.dump

ls -lh ./postgres-backup/release/

Step 4 — Scale Application to Zero Replicas​

Scale the Release application pods to zero before running xl kube upgrade. This ensures no application writes occur during the migration and avoids connection errors while the PostgreSQL cluster is being replaced.

# Record your current replicaCount before scaling down
REPLICA_COUNT=$(kubectl get digitalaireleases.xlr.digital.ai dai-xlr -n <NAMESPACE> \
  -o jsonpath='{.spec.replicaCount}')
echo "Current replicaCount: $REPLICA_COUNT"

# Scale down to 0
kubectl patch -n <NAMESPACE> digitalaireleases.xlr.digital.ai dai-xlr \
  --type=merge --patch '{"spec": {"replicaCount": 0}}'

# Wait until all Release pods are terminated
kubectl wait --for=delete pod -l app.kubernetes.io/instance=dai-xlr \
  -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 / True)

warning

This prompt is global answering No deletes all PVCs, not just PostgreSQL. Ensure you have backed up any other persistent data before answering No.

The wizard will delete the existing CNPG Operator & Cluster CR (dai-xlr-postgres) and its associated PVCs, then apply the new postgresql-cluster which provisions a fresh cluster on the new PostgreSQL version.

Step 6 — Wait for the New Cluster to be Ready​

After the upgrade wizard completes, verify the new cluster comes up fully before restoring data.

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

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

# Confirm the upgraded PostgreSQL image is in use
kubectl get pod dai-xlr-postgres-1 -n <NAMESPACE> \
  -o jsonpath='{.spec.containers[0].image}'; echo

The image should reflect the new PostgreSQL version for this upgrade.

Step 7 — Copy Dumps into the New Cluster Pod​

note

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-xlr-postgres-1 -- mkdir -p /run/tmp/restore

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

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

kubectl exec -n <NAMESPACE> dai-xlr-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 application user passwords from the Release secret:

# Get xlr user password
XLR_PASSWORD=$(kubectl get secret -n <NAMESPACE> dai-xlr-digitalai-release \
  -o jsonpath="{.data.mainDatabasePassword}" | base64 -d)

# Get xlr-report user password
XLR_REPORT_PASSWORD=$(kubectl get secret -n <NAMESPACE> dai-xlr-digitalai-release \
  -o jsonpath="{.data.reportDatabasePassword}" | base64 -d)

Then restore each database as its owner:

# Restore xlr-db as its database owner (xlr)
kubectl exec -n <NAMESPACE> dai-xlr-postgres-1 \
  -- bash -c "PGPASSWORD='$XLR_PASSWORD' pg_restore \
      -U xlr \
      -h localhost \
      -d xlr-db \
      --no-owner \
      --exit-on-error \
      /run/tmp/restore/xlr-db.dump"

# Restore xlr-report-db as its database owner (xlr-report)
kubectl exec -n <NAMESPACE> dai-xlr-postgres-1 \
  -- bash -c "PGPASSWORD='$XLR_REPORT_PASSWORD' pg_restore \
      -U 'xlr-report' \
      -h localhost \
      -d xlr-report-db \
      --no-owner \
      --exit-on-error \
      /run/tmp/restore/xlr-report-db.dump"

Verify Row Counts (Optional Sanity Check)​

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

Step 9 — Scale Application Back to Original Replica Count​

kubectl patch -n <NAMESPACE> digitalaireleases.xlr.digital.ai dai-xlr \
  --type=merge --patch "{\"spec\": {\"replicaCount\": $REPLICA_COUNT}}"

# Monitor pods coming up
kubectl get pods -n <NAMESPACE> -l app.kubernetes.io/instance=dai-xlr -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-xlr-postgres-1 -- rm -rf /run/tmp/restore