PostgreSQL Major Version Upgrade

Overview

VSHNPostgreSQL instances running on CloudNativePG (CNPG) support offline in-place major version upgrades. The upgrade is triggered by setting a higher majorVersion in your instance spec. CNPG then shuts down the entire cluster and runs pg_upgrade --link to migrate the data in place.

Major version upgrades are only available for CloudNativePG-based instances. StackGres-based instances do not support this feature. See how to deploy an instance using CloudNativePG.

A major version upgrade causes downtime for the entire PostgreSQL cluster, including all replicas. Plan a maintenance window accordingly.

Preparing for the Upgrade

Before proceeding, ensure the following:

Verify Backup: Ensure a recent backup exists before upgrading. Point-in-time recovery (PITR) across a major version boundary is not supported, so the pre-upgrade backup is your only recovery option if something goes wrong. Check the backup documentation for details.
Check Disk Space: The upgrade uses hard links (pg_upgrade --link), which avoids copying data, but we still recommend having sufficient free space available. Check Available Disk Space.
Verify Extension Compatibility: You are responsible for ensuring that all extensions used by your instance are available and compatible with the target PostgreSQL major version. Check the supported extensions list before upgrading.
Review Configuration: Ensure your current PostgreSQL configuration is compatible with the target version.
Test First: Perform the upgrade in a test environment before applying it to production.

Limitations

PITR Not Available Across Major Versions: After an upgrade, the cluster receives a new system identifier and the WAL timeline resets to 1. You cannot use pre-upgrade backups to recover to a point in time after the upgrade.

Upgrade Procedure

Set majorVersion to the target version in your instance spec:

apiVersion: vshn.appcat.vshn.io/v1
kind: VSHNPostgreSQL
metadata:
  name: pgsql-app1-prod
  namespace: prod-app
spec:
  parameters:
    service:
      majorVersion: "17" (1)
  writeConnectionSecretToRef:
    name: postgres-creds

1	Set this to the desired target major version.

After applying the change, CNPG will:

Shut down all cluster pods
Run an upgrade job (<pod-name>-major-upgrade) that executes pg_upgrade --link
Restart the primary and re-clone all replicas from scratch

The upgrade is complete once status.currentVersion matches the requested majorVersion.

Post-Upgrade Steps

After the upgrade completes:

Update table statistics: The upgrade does not transfer statistics. Run ANALYZE on all databases to rebuild them:
```
ANALYZE;
```

Available Disk Space

To check the available disk space for a VSHNPostgreSQL instance:

Check the total disk space allocated
```
kubectl -n <namespace> get VSHNPostgreSQL <name-of-instance> -o=jsonpath='{.spec.parameters.size}'
```
If no size is set, refer to the plan documentation for the default allocation.
Check database sizes from within the PostgreSQL instance

Connect to your instance using admin credentials and list all database sizes:
```
\l+
```