A new process is available via PostgreSQL logical replication that allows continued writes to the database during and after an upgrade.

IBM Cloud Databases for PostgreSQL offers two direct ways to perform a major version upgrade:

• Provision a read replica and choose to perform an upgrade when promoting it.
• Back up the database and restore it into a new instance (optionally performing a point-in-time restore).

Unfortunately, both processes involve a period of time during which writes to the database must be suspended to prevent data from being lost following the upgrade.

When upgrading from IBM Cloud Databases for PostgreSQL versions 10+, a new process is available via PostgreSQL logical replication that allows continued writes to the database during and after the upgrade, requiring only a momentary interruption while application(s) are reconfigured to write to the upgraded database instance. This post walks through how to execute the process and discusses some of the caveats and limitations associated with it.

Note: We recommend testing the procedure described below in a non-production environment first to get familiar with it and identify any issues that may occur before attempting the upgrade against any production database instances.

The upgrade process

First, you’ll need to prepare the original database instance to be upgraded:

• Enable logical replication as described in the IBM Cloud Databases for PostgreSQL wal2json documentation up to Step 2 (complete the wal_* configuration and set a password for the repl user):
  • Note that Step 3 isn’t supported on IBM Cloud Databases for PostgreSQL version 10, but it isn’t needed to complete the migration.
• Grant the replication (repl) user permission to read all tables you want to migrate using GRANT SELECT {…} TO repl;:
  • You can grant access to all tables in a schema you wish to migrate with GRANT SELECT ON ALL TABLES IN SCHEMA {schema} TO repl;, filling in the {schema} name as appropriate.
  • The GRANT command is described in more detail in the PostgreSQL documentation.
• Collect the hostname and port of the source instance from the Endpoints > PostgreSQL panel on the Overview tab of the database console or by using the ibmcloud cdb deployment-connections CLI.

Next, to perform the upgrade:

• Create a new IBM Cloud Databases for PostgreSQL instance at the target version and load all tables via DDL:
  • You can use pg_dump --schema-only/pg_restore to migrate the DDL.
  • pg_dump is described in more detail in the PostgreSQL documentation.
• Create publication(s) on the original database instance for the table(s) you wish to migrate using CREATE PUBLICATION {schema}_migration FOR TABLE {table}, {table}, {...};, filling in the {table} names and {schema} as needed.
• Create a subscription on the target database instance using SELECT create_subscription('{schema}_subscription', '{hostname}', '{port}', '{password}', 'repl', 'ibmclouddb', '{schema}_migration');, filling in the fields as needed.
• From the original database, watch the target database replicate data using SELECT slot_name, confirmed_flush_lsn, pg_current_wal_lsn(), (pg_current_wal_lsn() - confirmed_flush_lsn) AS lsn_distance FROM pg_replication_slots;:
  • If data isn’t replicating, check the logs of both the original and target databases via the IBM Cloud Log Analysis integration for possible issues.
  • If the replication slot no longer appears, it may have been interrupted by maintenance. See “Caveats and limitations” below.
• Once the target has caught up (and the lsn_distance has reduced to zero), reconfigure your application(s) to begin writes to the target instance rather than the source.

After verifying that the upgrade completed successfully, clean up as follows:

1. Remove the subscription(s) created above using SELECT delete_subscription(‘{schema}_subscription’, ‘ibmclouddb’);.
2. Delete the source database instance.

Caveats and limitations

Regularly scheduled maintenance performed by IBM Cloud Databases may impact the migration process and require it to be restarted. In that case, there should be no impact to running application(s) as the original database instance will continue to operate normally until the very end of the procedure.

The migration process has the same limitations as PostgreSQL logical replication, including the following:

• Schema and DDL commands are not replicated. Accordingly, the schema must be created manually on the target instance above, and schema changes performed to the original database instance after the target instance begins replication may cause the replication to fail.
• Sequences are not replicated.
• TRUNCATE actions involving tables that are not included in the subscription may fail.
• Large objects are not replicated.
• Only tables can be replicated; views, materialized views and foreign tables must be migrated separately.

Learn more

See the PostgreSQL logical replication documentation for more details.

Get started with IBM Cloud Databases for PostgreSQL.