Skip to main content
Version: 1.30

PostgreSQL upgrades

PostgreSQL upgrades fall into two categories:

Minor Version Upgrades​

PostgreSQL version numbers follow a major.minor format. For instance, in version 18.1:

  • 18 is the major version
  • 1 is the minor version

Minor releases are fully compatible with earlier and later minor releases of the same major version. They include bug fixes and security updates but do not introduce changes to the internal storage format.

Upgrading a Minor Version in CloudNativePG​

To upgrade to a newer minor version, simply update the PostgreSQL container image reference in your cluster definition, either directly or via image catalogs. CloudNativePG will trigger a rolling update of the cluster, replacing each instance one by one, starting with the replicas. Once all replicas have been updated, it will perform either a switchover or a restart of the primary to complete the process.

Major Version Upgrades​

Major PostgreSQL releases introduce changes to the internal data storage format, requiring a more structured upgrade process.

CloudNativePG supports three methods for performing major upgrades:

  1. Logical dump/restore – Blue/green deployment, offline.
  2. Native logical replication – Blue/green deployment, online.
  3. Physical with pg_upgrade – In-place upgrade, offline (covered in the "Offline In-Place Major Upgrades" section below).

Each method has trade-offs in terms of downtime, complexity, and data volume handling. The best approach depends on your upgrade strategy and operational constraints.

Important

We strongly recommend testing all methods in a controlled environment before proceeding with a production upgrade.

Offline In-Place Major Upgrades​

CloudNativePG performs an offline in-place major upgrade when a new operand container image with a higher PostgreSQL major version is declaratively requested for a cluster.

Important

Major upgrades are only supported between images based on the same operating system distribution. For example, if your previous version uses a bullseye image, you cannot upgrade to a bookworm image.

warning

There is a bug in PostgreSQL 17.0 through 17.5 that prevents successful upgrades if the max_slot_wal_keep_size parameter is set to any value other than -1. The upgrade process will fail with an error related to replication slot configuration. This issue has been fixed in PostgreSQL 17.6 and 18 or later versions. If you are using PostgreSQL 17.0 through 17.5, ensure that you upgrade to at least PostgreSQL 17.6 before attempting a major upgrade, or make sure to temporarily set the max_slot_wal_keep_size parameter to -1 in your cluster configuration.

You can trigger the upgrade in one of two ways:

  • By updating the major version in the image tag via the .spec.imageName option.
  • Using an image catalog to manage version changes.

For details on supported image tags, see "Image Tag Requirements".

warning

CloudNativePG is not responsible for PostgreSQL extensions. You must ensure that extensions in the source PostgreSQL image are compatible with those in the target image and that upgrade paths are supported. Thoroughly test the upgrade process in advance to avoid unexpected issues. The extensions management feature can help manage extension upgrades declaratively.

Upgrade Process​

  1. Shuts down all cluster pods to ensure data consistency.
  2. Records the previous PostgreSQL version and image in the cluster’s status under .status.pgDataImageInfo.
  3. Initiates a new upgrade job, which:
    • Verifies that the binaries in the image and the data files align with a major upgrade request.
    • Creates new directories for PGDATA, and where applicable, WAL files and tablespaces.
    • Performs the upgrade using pg_upgrade with the --link option.
    • Upon successful completion, replaces the original directories with their upgraded counterparts.
warning

During the upgrade process, the entire PostgreSQL cluster, including replicas, is unavailable to applications. Ensure that your system can tolerate this downtime before proceeding.

warning

Performing an in-place upgrade is an exceptional operation that carries inherent risks. It is strongly recommended to take a full backup of the cluster before initiating the upgrade process.

info

For detailed guidance on pg_upgrade, refer to the official PostgreSQL documentation.

Post-Upgrade Actions​

If the upgrade is successful, CloudNativePG:

  • Destroys the PVCs of replicas (if available).
  • Scales up replicas as required.
warning

Re-cloning replicas can be time-consuming, especially for very large databases. Plan accordingly to accommodate potential delays. After completing the upgrade, take a new base backup as soon as possible. Pre-upgrade backups and WAL files cannot be used for point-in-time recovery (PITR) across major version boundaries. See Backup and WAL Archive Considerations for more details.

warning

pg_upgrade doesn't transfer optimizer statistics. After the upgrade, you may want to run ANALYZE on your databases to update them.

If the upgrade fails, revert the image in the cluster's configuration to the previous major version. The operator detects the rollback and automatically deletes the failed upgrade job, allowing the cluster to restart on the original version.

Important

This process protects your existing database from data loss, as no data is modified during the upgrade. If the upgrade fails, a rollback is usually possible, without having to perform a full recovery from a backup. Ensure you monitor the process closely and take corrective action if needed.

Extensions during a major upgrade​

When a cluster declares image-volume extensions, the upgrade Job mounts both the source-version and the target-version extension images side by side. Source-version extensions are needed so the old server can start with its existing shared libraries already in place. Target-version extensions (built for the new PostgreSQL major) must also be present so that pg_upgrade can properly perform the upgrade to the new major:

  • Source-version extensions are mounted at /extensions/<name> (the steady-state path), so dynamic_library_path and extension_control_path GUCs in the old PGDATA keep the existing values. This ensures that, in case of major upgrade failure, a revert to the old major version will work seamlessly.
  • Target-version extensions are temporarily mounted at /new-extensions/<name> just during the upgrade job, and dynamic_library_path and extension_control_path are configured accordingly for the PGDATA of the new major.

LD_LIBRARY_PATH and PATH are extended with both sets, so binaries and shared objects from either version are reachable. Environment variables declared via extensions[].env obey the rules described in Precedence and Conflict Resolution.

note

Target-version entries are applied after source-version entries. When the same variable is defined for both, the target-version value takes precedence.

When pg_upgrade emits update_extensions.sql​

pg_upgrade emits an update_extensions.sql script inside the PGDATA when a target cluster contains extensions whose default_version is newer than the version pg_upgrade left installed. Until that script is applied, the SQL-level extension metadata in pg_catalog lags the shared libraries actually loaded by the running server.

The script lives inside the primary pod's PGDATA. You can execute it using the database superuser (postgres) to update those extensions in all databases:

PRIMARY=$(kubectl get cluster <name> -o jsonpath='{.status.currentPrimary}')
SCRIPT=/var/lib/postgresql/data/pgdata/update_extensions.sql

kubectl exec -i "$PRIMARY" -- psql -f "$SCRIPT"
note

The script path above assumes the default PGDATA location used by the CloudNativePG operand images. The operator also logs the resolved path when pg_upgrade emits the script, so check the operator logs if your operand image uses a different PGDATA.

Backup and WAL Archive Considerations​

When performing a major upgrade, pg_upgrade creates a new database system with a new System ID and resets the PostgreSQL timeline to 1. This has implications for backup and WAL archiving:

  • Timeline file conflicts: New timeline 1 files may overwrite timeline 1 files from the original cluster.
  • Mixed version archives: Without intervention, the archive will contain WAL files and backups from both PostgreSQL versions.
warning

Point-in-time recovery (PITR) is not supported across major PostgreSQL version boundaries. You cannot use pre-upgrade backups to recover to a point in time after the upgrade. Take a new base backup as soon as possible after upgrading to establish a recovery baseline for the new major version.

How backup systems handle major upgrades depends on the plugin implementation. Some plugins may automatically manage archive separation during upgrades, while others require manual configuration to use different archive paths for each major version. Consult your backup plugin documentation for its specific behavior during major upgrades.

Example: Manual archive path separation with the Barman Cloud plugin​

The Barman Cloud plugin does not automatically separate archives during major upgrades. To preserve pre-upgrade backups and keep archives clean, change the serverName parameter when you trigger the upgrade.

Before upgrade (PostgreSQL 16):

spec:
  imageName: ghcr.io/cloudnative-pg/postgresql:16-minimal-trixie
  plugins:
    - name: plugin-barman-cloud
      enabled: true
      parameters:
        destinationPath: s3://my-bucket/
        serverName: cluster-example-pg16

To trigger the upgrade, change both imageName and serverName together:

spec:
  imageName: ghcr.io/cloudnative-pg/postgresql:18-minimal-trixie
  plugins:
    - name: plugin-barman-cloud
      enabled: true
      parameters:
        destinationPath: s3://my-bucket/
        serverName: cluster-example-pg18

With this configuration, the old archive at cluster-example-pg16 remains intact for pre-upgrade recovery, while the upgraded cluster writes to cluster-example-pg18.

info

The deprecated in-tree barmanObjectStore implementation also requires manual serverName changes to separate archives during major upgrades.

Example: Performing a Major Upgrade​

Consider the following PostgreSQL cluster running version 16:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example
spec:
  imageName: ghcr.io/cloudnative-pg/postgresql:16-minimal-trixie
  instances: 3
  storage:
    size: 1Gi

You can check the current PostgreSQL version using the following command:

kubectl cnpg psql cluster-example -- -qAt -c 'SELECT version()'

This will return output similar to:

PostgreSQL 16.x ...

To upgrade the cluster to version 18, update the imageName field by changing the major version tag from 16 to 18:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example
spec:
  imageName: ghcr.io/cloudnative-pg/postgresql:18-minimal-trixie
  instances: 3
  storage:
    size: 1Gi

Upgrade Process​

  1. Cluster shutdown – All cluster pods are terminated to ensure a consistent upgrade.
  2. Upgrade job execution – A new job is created with the name of the primary pod, appended with the suffix -major-upgrade. This job runs pg_upgrade on the primary’s persistent volume group.
  3. Post-upgrade steps:
    • The PVC groups of the replicas (cluster-example-2 and cluster-example-3) are removed.
    • The primary pod is restarted.
    • Two new replicas (cluster-example-2 and cluster-example-3) are re-cloned from the upgraded primary.

Once the upgrade is complete, you can verify the new major version by running the same command:

kubectl cnpg psql cluster-example -- -qAt -c 'SELECT version()'

This should now return output similar to:

PostgreSQL 18.x ...

You can now update the statistics by running ANALYZE on the app database:

kubectl cnpg psql cluster-example -- app -c 'ANALYZE'