Upgrading to a new major version

As of December 2025, Databases for PostgreSQL provides three different upgrade paths:

In-place upgrade to a new major version.
Restoring from backup.
Upgrading from a read-only replica.

When a major version of a database is nearing its End Of Life (EOL), it is advisable to upgrade to a current major version.

Find the available versions of Databases for PostgreSQL in the IBM Cloud catalog page, from the Cloud Databases CLI plug-in command ibmcloud cdb deployables-show, or from the Cloud Databases API /deployables endpoint.

When you upgrade to a new instance, you also need to change the connection information in your application.

Requirements for upgrading to newer PostgreSQL major version from PostgreSQL v14

If you have pg_repack installed, you need to remove it before performing the upgrade. This can be done with a command like:

DROP EXTENSION pg_repack;

After upgrading, reinstall pg_repack. This can be done with the following command:

CREATE EXTENSION pg_repack;

If you are using PostGIS, you must first upgrade to PostGIS before upgrading PostgreSQL. This can be done by running the following command against a database with PostGIS installed.

SELECT postgis_extensions_upgrade();

Use the following query to validate the PostGIS extension upgrade.

SELECT postgis_full_version();

In-place major version upgrades

In-place major version upgrade allows you to upgrade your deployment to the next new major version, eliminating the need to restore a backup into a new deployment. This approach maintains the same connection strings, without the need to reconfigure the deployment. However, if the new major version requires application adjustments, these must be addressed.

During the in-place major version upgrade window, your deployment will experience a brief period of downtime. This is expected, as the process follows the vendor-recommended upgrade approach. The exact duration may vary depending on the size and complexity of your deployment’s schema. If your service needs to read data from the upgraded instance during this time, you may want to create a standby instance and update your application’s connection details to point to the standby. This ensures you have an up-to-date copy of your database prior to starting the upgrade. The standby instance can also be promoted and used as a primary instance if the in-place upgrade does not complete successfully. Additional information is available on the read-only replica page and can be seen here.

Databases for PostgreSQL gives customers flexibility in managing their own backups. The in-place major version upgrade process does not automatically create a backup before or after the task. If the upgrade is not successful, you may need to restore your deployment from your most recent backup into a new instance.

While in-place upgrades are supported, performing them without a recent backup is not advised. Having a current backup before and after initiating the upgrade provides an added layer of protection and helps ensure a smooth recovery path if needed. Hence, consider having a fresh backup before and after the in-place major version upgrade task.

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 via UI, API, CLI, or Terraform.

Example: Looking up version upgrade information with CLI.
```
ibmcloud cdb capability-show versions postgresql
```
Your deployment must be in a healthy state before upgrading.
Your deployment must have at least 10% of your disk space available.
Your deployment must not be under heavy I/O pressure (max: 90% allowed).
You can only upgrade from PostgreSQL 14 to the PostgreSQL 15 at this stage.
Each major version contains some features that may not be backward-compatible with previous versions. Check the release notes from the database vendor to see any changes that may affect your applications.
Downgrading a deployment to a previous version is not supported.
In-place major version upgrade cannot be cancelled once it started.
If you do not have a fresh backup, consider to take one before upgrading.

Also note that after the upgrade completes, your database will be running a new PostgreSQL major version. Because PostgreSQL stores data in version-specific formats, the new PostgreSQL data directory cannot be used with earlier versions. This means that backups taken before the upgrade cannot be used to restore into the new PostgreSQL version. To maintain full restore and PITR (Point-in-Time Recovery) capabilities going forward, take a new backup after the upgrade. This new backup becomes the baseline for all future recovery operations in the PostgreSQL timeline.

Upgrading in the UI

Create a new Databases for PostgreSQL to test the upgrade process.
Create the deployment by restoring a backup from your existing deployment with the same version.
Point your staging application to the test deployment.
Update your staging application to point to the test deployment. Confirm that your test application can connect successfully to the staging deployment and that the application operates as expected. Perform any required performance and operational testing of the staging environment.
Upgrade the major version of your test deployment by clicking on the Upgrade major version button on the Overview page.
Note how long the upgrade takes to complete, so that you can use the upgrade expiry setting to contain upgrades within your maintenance window.
Confirm that your staging application works with the new database version.
If your application works, this step confirms that it should be safe to upgrade your production database.
Upgrade your production database deployment to the new version.
Once you confirmed that your application works correctly by using the new version of the database, you can return to the management console and start the process of upgrading your production deployment. In the Deployment details section of the Overview page, click the Upgrade major version button and follow the steps.

Once the in-place upgrade process starts, it cannot be stopped or rolled back. So, in the unlikely event of an error, your database deployment could become unrecoverable. Therefore, create a backup that you can then use to restore to a new deployment.

The expiration for starting upgrade allows you to configure a 'timeout' period that the upgrade job must start within before it is automatically cancelled. In addition, test the upgrade in staging upfront to ensure that the upgrade completes within your desired time window. If, for example, you want to complete the upgrade within 1 hour, and you tested the upgrade and know that it takes 30 minutes, then your upgrade job must start within 30 minutes of you confirming that you want to upgrade. Therefore, set the expiration to 30 minutes, so that if it doesn't start within that time, it won't overrun your window.

Upgrading through the API

Use the following command to upgrade in-place:

curl -X PATCH https://api.{region}.databases.cloud.ibm.com/v5/ibm/deployments/{id}/version -H 'Authorization: Bearer <>' -H 'Content-Type: application/json' -d '{"version": "15"}'

The expiration for starting upgrade allows you to configure a 'timeout' period that the upgrade job must start within before it is automatically cancelled. In addition, test the upgrade in staging upfront to ensure that the upgrade completes within your desired time window. If, for example, you want to complete the upgrade within 1 hour, and you tested the upgrade and know that it takes 30 minutes, then your upgrade job must start within 30 minutes of you confirming that you want to upgrade. Therefore, set the expiration to a timestamp of 30 minutes from now, so that if it doesn't start within that time, it won't overrun your window. The expiration must be between 5 minutes (default) and 24 hours from now. For more information, see the Cloud Databases API.

Upgrading through the CLI

Available in CDB plugin version >= 0.20.0.

To view the list of allowed upgrade and restore transitions for the deployment:

ibmcloud cdb deployment-capability-show <NAME|CRN> versions

To upgrade the command with required parameters:

ibmcloud cdb deployment-version-upgrade <NAME|CRN> <TARGET_VERSION>

To view full details of command parameters:

ibmcloud cdb deployment-version-upgrade --help

Upgrading through Terraform

Available in Terraform provider version >= 1.79.2 .

To upgrade, just add or change the version value in your configuration.

Skipping a backup before a version upgrade is dangerous and may result in data loss if the upgrade fails at any stage — there will be no immediate backup to restore from. Hence, consider to have a fresh backup before initiating an in-place major version upgrade task.

Upgrading may require more time than the default timeout. A longer timeout value can be set with using the timeouts attribute.

Terraform has timeouts instead of expiration timestamps. Therefore, increase your timeout, as your timeout update value is used as the expiration. For example, if you set a timeout of 20 minutes, the expiration will be set to 20 minutes and if the upgrade does not start in that time frame, it expires and the upgrade will not start. Note that the maximum expiration is 24 hours — so even if you set a timeout of 36 hours, the upgrade will expire if it hasn't started within the first 24 hours.

If an upgrade is in progress, note that some tasks may be queued and will not proceed until the version upgrade completes.

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 may complicate recovery.

An in-place major upgrade will not proceed until all prechecks have passed. These safeguards exist to protect your deployment, since 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.

Handling the `anon` extension before upgrade

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

Remove all masking rules (if enabled).

SELECT anon.remove_masks_for_all_columns();

Disable masking roles (the upgrade might fail if any roles are marked as masked).
```
SECURITY LABEL FOR anon ON ROLE <role_name> IS NULL;
```
Drop the anon extension with the cascade option.
```
DROP EXTENSION anon CASCADE;
```
If the anon extension is installed in multiple databases within an instance, follow the outlined steps for each database.
Post-upgrade steps:

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.

Upgrading from a read-only replica

Upgrade by configuring a read-only replica. Provision a read-only replica with the same database version as your deployment and wait while it replicates all of your data. When your deployment and its replica are synced, promote and upgrade the read-only replica to a full, stand-alone deployment running the new version of the database. To perform the upgrade and promotion step, use a POST request to the /deployments/{id}/remotes/promotion endpoint with the version that you want to upgrade to in the body of the request.

This request 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
    }
}' \

skip_initial_backup is optional. If set to true, the new deployment does not take an initial backup when the promotion completes. Your new deployment is available in a shorter amount of time, at the expense of not being backed up until the next automatic backup is run, or you take an on-demand backup.

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
    }
}' \

Back up and restore upgrade

You can upgrade your database version by restoring a backup of your data into a new deployment that is running the new database version.

Upgrading in the UI

Upgrade to a new version when restoring a backup from the Backups menu of your Deployment dashboard. Click Restore on a backup to the provisioning page on a new tab, where you can change some options for the new deployment. One of the options is the database version, which is auto-populated with the versions available for you to upgrade to. Select a version and click Create to start the provision and restore process.

Upgrading through the CLI

To upgrade and restore from a backup through the IBM Cloud CLI, use the provisioning command from the resource controller.

ibmcloud resource service-instance-create <DEPLOYMENT_NAME_OR_CRN> <SERVICE_ID> <SERVICE_PLAN_ID> <REGION>

The parameters service-name, service-id, service-plan-id, and region are all required. You also supply the -p with the version and backup ID parameters in a JSON object. The new deployment is automatically sized with the same disk and memory as the source deployment at the time of the backup.

This command looks like:

ibmcloud resource service-instance-create example-upgrade databases-for-postgresql standard us-south \
-p \ '{
  "backup_id": "crn:v1:bluemix:public:databases-for-postgresql:us-south:a/54e8ffe85dcedf470db5b5ee6ac4a8d8:1b8f53db-fc2d-4e24-8470-f82b15c71717:backup:06392e97-df90-46d8-98e8-cb67e9e0a8e6",
  "version":14
}'

Upgrading through the API

Complete the necessary steps to use the Resource controller API before you use it to upgrade from a backup. Then, send the API a POST request. The parameters name, target, resource_group, and resource_plan_id are all required. You also supply the version and backup ID. The new deployment has the same memory and disk allocation as the source deployment at the time of the backup.

This command looks like:

curl -X POST \
  https://resource-controller.cloud.ibm.com/v2/resource_instances \
  -H 'Authorization: Bearer <>' \
  -H 'Content-Type: application/json' \
    -d '{
    "name": "my-instance",
    "target": "bluemix-us-south",
    "resource_group": "5g9f447903254bb58972a2f3f5a4c711",
    "resource_plan_id": "databases-for-postgresql-standard",
    "backup_id": "crn:v1:bluemix:public:databases-for-postgresql:us-south:a/54e8ffe85dcedf470db5b5ee6ac4a8d8:1b8f53db-fc2d-4e24-8470-f82b15c71717:backup:06392e97-df90-46d8-98e8-cb67e9e0a8e6",
    "version":14
  }'

Forced upgrade

After the end-of-life date, all active Databases for PostgreSQL deployments on the deprecated version will be forcibly upgraded to the next supported version. For example, PostgreSQL Version 13 (deprecated) upgrades to Version 14.

Upgrade before the end-of-life date to avoid the following risks:

No SLAs are provided for this type of forced upgrade.
You may experience some data loss.
Your application may experience prolonged downtime.
Your application may stop working if it is incompatible with the new version.
You cannot control the timing of when this upgrade will happen for your deployment.
There is no rollback process for this forced upgrade.

For the end-of-life dates, refer to the version policy page.

Admin user issues during version upgrades

Starting with PostgreSQL 16, role privilege enforcement has become more stringent. In contrast, earlier versions allowed roles with the CREATEROLE attribute to manage other roles; version 16 and beyond require that a role has the ADMIN OPTION on another role to grant or revoke it.

If you are upgrading your database from PostgreSQL v15 or earlier to PostgreSQL v16 or higher, you may encounter privilege-related errors, such as:

ERROR: only roles with the ADMIN OPTION on role "some_role" may grant this role
DETAIL: role "admin" is not permitted to grant role "some_role"

To address this issue, we provide a built-in helper function grant_admin_option_to_roles that is designed to restore the expected role management behavior.

This function:

Applies only to databases upgraded from PostgreSQL v15 and earlier versions to PostgreSQL 16 and later (if you're encountering the error described above).
Accepts an arbitrary list of roles to apply the fix to.
Can only be executed by the admin user.
Is safe to run multiple times (idempotent).

Sample usage:

SELECT grant_admin_option_to_roles('role1', 'role2', 'role3');

This ensures that the admin user retains the appropriate ADMIN OPTION privileges to manage designated roles in upgraded instances.

Upgrading to a new major version

Requirements for upgrading to newer PostgreSQL major version from PostgreSQL v14

In-place major version upgrades

Before you begin

Upgrading in the UI

Upgrading through the API

Upgrading through the CLI

Upgrading through Terraform

Troubleshooting

Handling the anon extension before upgrade

Upgrading from a read-only replica

Dry running the promotion and upgrade

Back up and restore upgrade

Upgrading in the UI

Upgrading through the CLI

Upgrading through the API

Forced upgrade

Admin user issues during version upgrades

Changelog for major PostgreSQL versions

Handling the `anon` extension before upgrade