Databases for PostgreSQL as a logical replication destination

IBM Cloud® Databases for PostgreSQL supports logical replication, where you can create a subcriber or a publisher. You can also set up your external PostgreSQL as a publisher and your Databases for PostgreSQL deployment as a subscriber, and replicate your data across from an external database into your deployment.

Logical replication is only available on deployments running PostgreSQL 10 or above. Links to the PostgreSQL documentation direct you to the current version of PostgreSQL. If you need documentation for a specific version, you can find links to different PostgreSQL versions on the PostgreSQL documentation page.

Configuring the publisher

The external PostgreSQL instance is the publisher, and needs to be configured in order for your Databases for PostgreSQL deployment to connect and be able to pull the data in correctly.

Publisher functions

create_publisher

In each database you can create multiple publications to publish the changes into a different subcriber.

Arguments:

    publisher_name               The Unique name of publisher.
    for table                    To publish single/list of tables
    for all table                To publish all tables, along with future tables.
    for all tables in schema     To publish all tables in schema, along wth future tables.

Usage:
    exampledb=> create publication my_publication for all tables ;
    CREATE PUBLICATION

list_publisher

List the number of publications running in each database.

Usage:
   exampledb=# select * from pg_publication;
   oid  |    pubname     | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
   -------+----------------+----------+--------------+-----------+-----------+-----------+-------------
   16401 | my_publication |       10 | t            | t         | t         | t         | t

alter_publisher

Modify the definition of the publication.

Syntax:
ALTER PUBLICATION name ADD publication_object [, ...]
ALTER PUBLICATION name SET publication_object [, ...]
ALTER PUBLICATION name DROP publication_object [, ...]
ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] )
ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION name RENAME TO new_name


 Usage:
    exampledb=# ALTER PUBLICATION my_publication RENAME TO my_publication_new;
    ALTER PUBLICATION

drop_publisher

Drop/Remove the publication not in use.

Usage:
    exampledb=# drop publication my_publication_new;
    DROP PUBLICATION

Consider the following as prerequisites:

Configure the extenal (publisher) PostgreSQL wal_level=logical.
Every table that is selected for replication needs to contain a Primary key, or have REPLICA IDENTITY set.
Your external (publisher) PostgreSQL needs to have a replication user that has the PostgreSQL privilege REPLICATION, and that user needs to have SELECT and USAGE privileges on the databases along with REPLICATION privileges.
The PostgreSQL you are replicating from needs to have TLS/SSL enabled.
Provide BYPASSRLS privileges to external (publisher) PostgreSQL replication user, if row-level-security is enabled.

There are inherent limitations and some restrictions to logical replication outlined in the PostgreSQL documentation. Review these before deciding that logical replication is appropriate for your use case.

Configuring the subscriber

To configure your Databases for PostgreSQL deployment and to make sure that your data is replicated across correctly, make sure of the following.

You need to create a database in your deployment with same name as the database you intend to replicate.
Logical Replication works at the table level, so every table you select to publish, you need to create in the subscriber before starting the logical replication process. (You can use pg_dump to help.) The table on the subscriber does not need to be identical to its publisher counterpart. However, the table on the subscriber must contain at least every column present in the table on the publisher. Additional columns present in the subscriber must not have NOT NULL or other constraints. If they do, replication fails.

Native PostgreSQL subscription commands require superuser privileges, which are not available on Databases for PostgreSQL deployments. Instead, your deployment includes a set of functions that can be used to set and manage logical replication for the subscription.

Only the admin user that is provided by Databases for PostgreSQL has permissions to run the following replication commands that allow you to subscribe and replicate content from an external PostgreSQL publisher.

Subscriber functions

create_subscription

Arguments:
    subscription_name   Unique name to create the subscription channel with
    host_ip             Publisher hostname or public IP address
    port                Port number publisher is running on
    username            `admin` user created on the publisher
    password            Password of the `admin` user on the publisher
    db_name             The name of the database to be replicated
    publisher_name      The name of publisher channel on the publisher

Usage:
    exampledb=> SELECT create_subscription('subs1','130.215.223.184','5432','password','admin','exampledb','my_publication');

delete_subscription

Arguments:
    subscription_name   Name the subscription channel to delete
    db_name             The name of the replicated database

Usage:
exampledb=> SELECT delete_subscription('subs1', 'exampledb');

list_subscriptions

Arguments:
    None

Usage:
    exampledb=> SELECT * FROM list_subscriptions();

disable_subscription

Arguments:
    subscription_name   Name the subscription channel to disable
    db_name             The name of the replicated database

Usage:
    exampledb=> SELECT disable_subscription('subs1','exampledb');

enable_subscription

Arguments:
    subscription_name   Name the subscription channel to enable
    db_name             The name of the replicated database

Usage:
    exampledb=> SELECT enable_subscription('subs1','exampledb');

subscription_slot_none

This function is used to set the slot name of a subscription to NONE. This is needed in order to delete a subscription so that a remote replication slot cannot be dropped or does not exist or never existed.

Arguments:
    subscription_name   Name the subscription channel to alter
    db_name             The name of the replicated database

Usage:
    exampledb=> SELECT subscription_slot_none('subs1','exampledb');

refresh_subscription

This function is used to refresh a subscription on the subscriber after changes are made on the publisher, like adding or removing a table.

Arguments:
    subscription_name   Name the subscription channel to refresh
    db_name             The name of the replicated database

Usage:
    exampledb=> SELECT refresh_subscription('subs1','exampledb');

Setting up logical replication on the publisher

To configure your external PostgreSQL as a publisher, perform the following steps.

Edit your local pg_hba.conf and add the following.

hostssl    replication            replicator         0.0.0.0/0      md5
hostssl    all                    replicator         0.0.0.0/0      md5

The "replicator" field is the user that you set up with the PostgreSQL privilege REPLICATION.

Edit your local postgresql.conf with the required logical replication configuration. Set wal_level to 'logical', and set listen_addresses='*' to accept connections from any host.
```
listen_addresses='*'
wal_level = logical
```
Restart your PostgreSQL server.

Now you can define a publisher on the database and add the tables that you want to replicate to the subscriber.

Create the publication channel.

exampledb=> CREATE PUBLICATION my_publication;

Add tables to publisher.
```
exampledb=> ALTER PUBLICATION my_publication ADD TABLE my_table;
```
The number of workers that back the synchronization that is defined by the max_logical_replication_workers configuration parameter is limited and cannot be changed. Therefore, use the least possible number of publications and add as many tables as possible to one publication.

Setting up logical replication on the subscriber

To configure your Databases for PostgreSQL deployment as a subscriber, perform the following steps.

Run the following query to call the create_subscription function and create the subscriber channel.

exampledb=> SELECT create_subscription('subs1','130.215.223.184','5432','admin','password','exampledb','my_publication');

Monitoring replication

You can monitor the status of logical replication from both the publisher and the subscriber by running the following query on either.

exampledb=> SELECT * FROM pg_stat_replication;