Upgrading to a new major version
When a major version of a database is at its End Of Life (EOL), it is a good idea 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 v12 or earlier
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 upgrade to PostGIS 3.3 before upgrading. This can be done by running the following against a database with PostGIS installed.
SELECT * FROM update_to_postgis_33();
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.
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
}'
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
}
}' \