Upgrading IBM Cloud Databases for PostgreSQL with Minimal Downtime

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 (link resides outside of ibm.com).
• 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.