March 15, 2023 By James Thorne
Daniel Pittner
3 min read

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.

Was this article helpful?

More from Cloud

A clear path to value: Overcome challenges on your FinOps journey 

3 min read - In recent years, cloud adoption services have accelerated, with companies increasingly moving from traditional on-premises hosting to public cloud solutions. However, the rise of hybrid and multi-cloud patterns has led to challenges in optimizing value and controlling cloud expenditure, resulting in a shift from capital to operational expenses.   According to a Gartner report, cloud operational expenses are expected to surpass traditional IT spending, reflecting the ongoing transformation in expenditure patterns by 2025. FinOps is an evolving cloud financial management discipline…

IBM Power8 end of service: What are my options?

3 min read - IBM Power8® generation of IBM Power Systems was introduced ten years ago and it is now time to retire that generation. The end-of-service (EoS) support for the entire IBM Power8 server line is scheduled for this year, commencing in March 2024 and concluding in October 2024. EoS dates vary by model: 31 March 2024: maintenance expires for Power Systems S812LC, S822, S822L, 822LC, 824 and 824L. 31 May 2024: maintenance expires for Power Systems S812L, S814 and 822LC. 31 October…

24 IBM offerings winning TrustRadius 2024 Top Rated Awards

2 min read - TrustRadius is a buyer intelligence platform for business technology. Comprehensive product information, in-depth customer insights and peer conversations enable buyers to make confident decisions. “Earning a Top Rated Award means the vendor has excellent customer satisfaction and proven credibility. It’s based entirely on reviews and customer sentiment,” said Becky Susko, TrustRadius, Marketing Program Manager of Awards. Top Rated Awards have to be earned: Gain 10+ new reviews in the past 12 months Earn a trScore of 7.5 or higher from…

IBM Newsletters

Get our newsletters and topic updates that deliver the latest thought leadership and insights on emerging trends.
Subscribe now More newsletters