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. Try this path first. If the automatic upgrade fails, or your cluster doesn't meet the prerequisites below, use the manual upgrade process instead.

CNPG supports offline in-place major version upgrades using PostgreSQL's pg_upgrade --link. This path triggers automatically when xl kube upgrade with PVC preservation set to Yes applies a new Cluster template that bumps imageName to a higher major version against an existing running cluster.

Prerequisites

pg_upgrade succeeds only when all of the following are true.

Image flavor must be consistent across the upgrade

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

OS distribution must be the same across the upgrade

Both images must use the same Debian base, such as bookworm or trixie. CNPG doesn't support in-place upgrades from a bullseye image to a bookworm image.

PostgreSQL 17.0–17.5 requires max_slot_wal_keep_size set to -1

A PostgreSQL bug in versions 17.0 through 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.

Step-by-step upgrade process

These steps use the default namespace digitalai. If you installed Release in a custom namespace, replace digitalai with your namespace throughout these steps.

caution

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

Step 1 — Back up the Database

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

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

Step 2 — Scale Application to Zero Replicas

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

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

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

Step 3 — 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)

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-xlr-postgres -n digitalai -w

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

caution

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 process, using the dump taken in 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-xlr-postgres -w

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

# Confirm the upgraded PostgreSQL image is in use
kubectl get pod dai-xlr-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 digitalai digitalaireleases.xlr.digital.ai dai-xlr \
  --type=merge --patch "{\"spec\": {\"replicaCount\": $REPLICA_COUNT}}"

# Monitor pods coming up
kubectl get pods -n digitalai -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.