Upgrading to a new major version

Extensions and logical replication objects to review

Review the following items before the upgrade:

pg_repack

Drop pg_repack before the upgrade and recreate it after the upgrade. pg_repack uses a version-specific extension and client/server components that must match the PostgreSQL major version.

DROP EXTENSION pg_repack;

Recreate the extension after the upgrade only if your workload still requires it.

CREATE EXTENSION pg_repack;

old_snapshot

Drop old_snapshot before the upgrade. DO NOT recreate it after upgrading to PostgreSQL 18 because it is no longer supported.

DROP EXTENSION old_snapshot;

wal2json replication slots

If you use wal2json for logical decoding, you must drop all associated replication slots before the upgrade. The pg_upgrade utility strictly forbids major version upgrades while replication slots exist and will throw a critical error and halt the upgrade.

Before upgrading:

1. Ensure that all pending WAL data has been consumed.
2. Stop your application that uses the replication slot.
3. Drop the replication slots:

SELECT pg_drop_replication_slot('your_slot_name');

After the upgrade, you can recreate the replication slots as needed. Note that wal2json is not installed using CREATE EXTENSION but is configured through database parameters (wal_level, max_replication_slots, max_wal_senders) and table permissions, which do not prevent upgrades.

anon

Remove the anon extension before the upgrade and re-enable it after the upgrade if you still need it. Additional steps are required before you drop anon.

If the anon extension is installed, complete the steps below and execute the commands as the admin user before performing the upgrade.

1. Remove all masking rules (if enabled).

   SELECT anon.remove_masks_for_all_columns();
   

2. Disable masking roles (the upgrade might fail if any roles are marked as masked).

   SECURITY LABEL FOR anon ON ROLE <role_name> IS NULL;
   

3. Drop the anon extension with the cascade option.

   DROP EXTENSION anon CASCADE;
   

4. If the anon extension is installed in multiple databases within an instance, complete the outlined steps for each database.

5. After the upgrade is complete, re-enable the anon extension and reapply masking rules as required.

It is strongly recommended to validate the data both before and after dropping the extension to ensure masking consistency prior to performing the upgrade.

PostGIS

If you are using PostGIS, first upgrade PostGIS before you upgrade PostgreSQL.

SELECT postgis_extensions_upgrade();

Use the following query to validate the PostGIS extension upgrade.

SELECT postgis_full_version();

Logical replication slots

Drop all logical replication slots before the upgrade and recreate them after the upgrade. Logical slots are tied to the source server state and should be recreated cleanly on the upgraded instance.

SELECT pg_drop_replication_slot('<slot_name>');

Before you begin

Consider the following aspects before starting the upgrade procedure.

• Check if version upgrade is available for your deployment version by checking the deployment capability information using UI, API, CLI, or Terraform.

  Example: looking up version upgrade information with CLI:

  ibmcloud cdb capability-show versions postgresql
  

• Ensure that you review the precheck-related requirements in this topic before you trigger IPU. IPU runs directly on the source deployment and does not create a new instance. For customer safety, the service runs prechecks before the upgrade starts and blocks the operation if a risk is detected. In particular, verify the following items:

  • Your deployment is in a healthy state.
  • Your deployment has at least 10% free disk space available. The default maximum allowed disk usage for IPU prechecks is 90%.
  • Your deployment is not under heavy I/O pressure. The default maximum allowed I/O utilization for IPU precheck is 90%.
  • Your schema size and object counts are within the default precheck thresholds. By default, no individual schema can exceed 100 GB and the total number of indexes and sequences must remain below 50,000.
  • You completed any required extension and logical replication slot cleanup before the upgrade.

• Each major version contains some features that might not be backward-compatible with previous versions. Check the release notes from the database vendor to see any changes that might affect your applications.

• Downgrading a deployment to a previous version is not supported.

• In-place major version upgrade cannot be cancelled after it starts.

• If you do not have a fresh backup, consider taking one before upgrading.

Also, note that after the upgrade completes, your database runs a new PostgreSQL major version. Because PostgreSQL stores data in version-specific formats, backups and PITR points from before the upgrade belong to the earlier version timeline and cannot be restored into the upgraded version. To maintain full restore and PITR (Point-in-Time Recovery) capabilities for the new version, take a new backup immediately after the upgrade completes. That backup becomes the baseline for future recovery operations on the new version timeline.

If IPU fails, valid backups from before the upgrade can still be used with PITR to restore the earlier PostgreSQL version into a new instance.

Troubleshooting

If your applications show unexpected issues after a successful in-place major version upgrade and you need to return to the previous PostgreSQL version, contact our support team for guidance. Avoid initiating a PITR or restoring a backup on your own, as this can complicate recovery.

An in-place major upgrade will not proceed until all prechecks have passed. These safeguards exist to protect your deployment because the upgrade operates directly on the source instance. If the upgrade is blocked, review the following areas:

• Cluster state: ensure that the Patroni cluster is healthy and that a clear leader/replica state is present. Upgrades cannot proceed if Patroni reports instability or failover conditions.
• Disk space: verify that sufficient free space is available. The process uses pg_upgrade link mode, which requires adequate headroom. If disk usage exceeds the configured limit (default: 90%), free space before retrying.
• Disk I/O load: check current I/O utilization and IOPS. Upgrades are paused when the system is under heavy load to prevent performance degradation or upgrade failure.
• Schema size and object count: As mentioned, schema size directly affects the duration of an in-place major version upgrade. Ensure that no individual schema exceeds the maximum size (default: 100 GB) and that the total number of index and sequence objects remains below the limit (default: 50,000). Large schemas or unusually high object counts may require cleanup or optimization before proceeding with the upgrade. pg_upgrade performs rapid upgrades by creating new system tables and simply reusing the old user data files. The time required to create these system tables scales with the number of database objects. The resource consumption can be evaluated by using the monitoring integration. If not all database components are available to be upgraded, the upgrade task fails. This can happen due to maintenance. Tasks that failed due to failed healthchecks can be retried later. If the task continuously fails, open a support ticket with IBM Cloud support. If certain checks are not relevant to your environment and the upgrade is still blocked, create a support ticket for further assistance.

Dry running the promotion and upgrade

To evaluate the effects of major version upgrades, trigger a dry run. A dry run simulates the promotion and upgrade, with the results printed to the database logs. Access and view your database logs through the Log analysis integration. This ensures the version that you are currently running with its extensions can be successfully upgraded to your intended version.

The dry run must be run with skip_initial_backup set to false, and version defined.

The command looks like:

curl -X POST \
  https://api.{region}.databases.cloud.ibm.com/v5/ibm/deployments/{id}/remotes/promotion \
  -H 'Authorization: Bearer <>'  \
 -H 'Content-Type: application/json' \
 -d '{
    "promotion": {
        "version": "14",
        "skip_initial_backup": false,
        "dry_run": true
    }
}' \