PostgreSQL version 9.4.x is coming to an end on February 13, 2020.
That means current IBM Cloud Databases users will no longer be able to provision PostgreSQL 9.4 instances.
By February 20, 2020, all Databases for PostgreSQL instances on version 9.4 that are still active will be disabled. Users are expected to be either on PostgreSQL version 9.6 or get the latest preferred version of Databases for PostgreSQL with all the great features that have been added in versions 10 and 11.
We reserve the right to end support at our discretion if a security vulnerability is discovered during the window between February 13th, 2020 and February 20th, 2020. This means that we may need to shut down those PostgreSQL deployments and remove them from our system since they would not meet compliance regulations.
Impact to Databases for PostgreSQL users
By the end of 2019, we will introduce a native, major version upgrade feature that does not require pg_dump. You may want to wait until that functionality is released before migrating to a new PostgreSQL version. We’ll offer guidance once this feature is available.
In the interim, it’s recommended that users begin testing Databases for PostgreSQL 9.5, 9.6, 10, or 11.
Impact to Compose for PostgreSQL users
Over the next few months, we will release a migration path from Compose for PostgreSQL 9.4 to Databases for PostgreSQL 9.4 and up. Compose for PostgreSQL users should consider upgrading to Compose for PostgreSQL 9.4 to 9.6 before migrating to Databases for PostgreSQL if you want a limited downtime migration solution or have a large database, which would be prohibitive to migrate with the dump and restore method.
Migrating your data
In the meantime, if you want to get started on the migration process, we have some guidance on how to dump and restore your PostgreSQL database into a new Databases for PostgreSQL version.
To start, you’ll need to provision a new PostgreSQL deployment. From the IBM Cloud dashboard, click the blue Create Resource button.
Then, search for “Databases for PostgreSQL” and click on the box.
Provision a new Databases for PostgreSQL database. The preferred version will automatically be selected by default. You have the option of scaling the database’s memory and disk size beforehand. IBM Cloud Databases doesn’t autoscale, so we recommend allocating the appropriate disk and RAM that’s at least the same as your PostgreSQL 9.4 deployment.
After that, click Create and you’ll have a new IBM Cloud Databases for PostgreSQL deployment.
Using pg_dump or a tool like pgAdmin, you’ll need to migrate your data from your current PostgreSQL 9.4 deployment to the new one. For guidance on pg_dump for version 9.4 see the PostgreSQL documentation.
A basic example of how to use pg_dump for your terminal is the following:
This command will place all the PostgreSQL tables, schemas, functions, etc. from the my_database database into a SQL file called allMyData.sql. Once that file has been created, make sure that you have set up all the users and roles from your old PostgreSQL deployment into your new one. Then, simply restore it into your new deployment as follows:
After restoring your data, run some tests to make sure that it has been restored properly into your new database.
Migrate from a Backup
You also have the ability to migrate your PostgreSQL data through a backup. From the IBM Cloud UI, just select your database and click on the Backups tab. From there, you have three options available to you: point-in-time, automatic, or on-demand.
For point-in-time backups, you can restore at the latest available point or choose a specific time within the last seven days from which to create a new deployment.
With automatic, you can select the latest backup we’ve created for you to create a new deployment.
Or, you can create an on-demand backup and restore that to a new deployment.
n this example, let’s create an on-demand backup of the database so that we have a backup of the latest data. Select the On-demand tab, then click on the Back up now button to start a backup.
Once the backup has been initiated, you will see just now appear in the panel. If you click on that, it will give you the backup ID number only, until the backup has finished being created.
If you click on the Overview tab, you can see the backup creation progress.
When the backup is finished, it will appear with a green check mark and the time when it was taken. Select the arrow on the backup you just created. This will open a drop-down panel with information on how to restore the backup into a new PostgreSQL deployment.
You have two options to restore into a new deployment—from the UI or by using the IBM Cloud CLI. The IBM Cloud CLI command to restore your backup into a new database deployment is given to you at the bottom of that panel.
All you need to do is supply a new name for your deployment where it says SERVICE_INSTANCE_NAME, us-south for any region you want your new deployment to reside in, then run that command from the terminal. Note that the command will restore your backup to the preferred version of PostgreSQL on IBM Cloud.
If you wanted to restore right from the UI, you can do that as well. Click on the blue Restore button at the top of the panel shown in the image above. Once that’s clicked, you’ll be forwarded to another screen which will give you the option of naming your deployment, as well as selecting a version:
This screen will provide you with an arbitrary name (which you can change), and will allow you to select the version of PostgreSQL you want and the region you want the database to reside in. Once you’ve filled out the form, click on Restore and you’ll have a new deployment with the restored data from the backup.
Moving forward
If you have any questions about migrating your data to a new IBM Cloud Databases for PostgreSQL deployment, please reach out to our support team who can guide you through the process.