Skip to main content
Version: Deploy Next

Automatic PostgreSQL Upgrade (CNPG Operator-Managed)

This guide covers the automatic in-place upgrade path for a PostgreSQL major version upgrade when using the CloudNativePG (CNPG) operator. CNPG natively supports offline in-place major version upgrades using PostgreSQL's pg_upgrade --link. This is triggered automatically when xl kube upgrade with PreservePvc=Yes applies a new Cluster template that bumps the imageName to a higher major version against an existing running cluster.

Use this automatic path first. Only if the automatic upgrade fails do you need the manual upgrade steps.

caution

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

Prerequisites and criteria for pg_upgrade to succeed​

The following criteria must be met for the automatic in-place upgrade to succeed.

1. Image flavor must be consistent across the upgrade​

Both the old (source) and new (target) PostgreSQL images must use the same flavor. pg_upgrade runs inside the new container and must execute the old PostgreSQL binaries that are mounted from the old image. If the new container's OS is missing shared libraries that the old binaries depend on, pg_upgrade fails immediately.

2. OS distribution must be the same across the upgrade​

Both images must use the same Debian base (bookworm, trixie, and so on). Upgrading from a bullseye image to a bookworm image is not supported by CNPG for in-place upgrades.

3. PostgreSQL 17.0–17.5: max_slot_wal_keep_size must be -1​

A PostgreSQL bug in versions 17.0–17.5 causes pg_upgrade to fail if max_slot_wal_keep_size is set to any value other than -1. Upgrade to PostgreSQL 17.6 or later first, or set this parameter to -1 in your cluster configuration before upgrading.

Steps with xl kube upgrade​

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 — Backup the Database​

Take a full pg_dump of all databases before running the upgrade. Also note the PostgreSQL image version before the upgrade in case you need to roll back later.


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

Step 2 — Scale Application to Zero Replicas​

Scale the application pods to zero before triggering the upgrade to ensure no writes occur while the cluster is being upgraded.


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"

# Scale all three application components down to 0
kubectl patch -n digitalai 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 digitalai --timeout=300s

Step 3 — Run xl kube upgrade​

Run the upgrade wizard. When prompted about PVC preservation:

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

Answer: Y (Yes)

The wizard applies the new postgresql-operator and postgresql-cluster templates over the existing cluster. CNPG detects the imageName major version bump and automatically triggers the pg_upgrade --link job against the existing PGDATA on the PVC.

Step 4 — Monitor the Upgrade Job​

# Watch the upgrade job status
kubectl get jobs -n digitalai | grep major-upgrade

# Watch the cluster phase transition
kubectl get cluster dai-xld-postgres -n digitalai -w

The cluster phase progresses through major upgrade → primary instance stopping → primary instance restarting → Cluster in healthy state.

If the job fails, revert imageName in the cluster spec to the previous PostgreSQL version. CNPG detects the rollback, deletes the failed job, and restarts the cluster on the original version. Then fall back to the manual upgrade steps using the dump from Step 1.

Step 5 — Wait for the Cluster to Be Ready​

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

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

# Confirm the upgraded postgresql image is in use
kubectl get pod dai-xld-postgres-1 -n digitalai \
  -o jsonpath='{.spec.containers[0].image}'; echo

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

Step 6 — 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.