This topic describes how to use the pglogical plug-in as a PostgreSQL extension to replicate data. This plug-in provides the logical streaming replication feature for ApsaraDB RDS for PostgreSQL instances by using a publish/subscribe pattern.

Prerequisites

Your RDS instance runs one of the following PostgreSQL versions:
  • PostgreSQL 13
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10

Background information

The pglogical plug-in is a PostgreSQL extension that uses the publish/subscribe pattern to replicate data. The publish/subscribe pattern makes selective replication more efficient. The pglogical plug-in delivers higher replication speeds than Slony, Bucardo, and Londiste and supports cross-version upgrades. The pglogical plug-in is suitable for the following scenarios:
  • Upgrade between major engine versions
  • Replication of all data from a database on your RDS instance
  • Replication of specific tables, rows, and columns by using replication sets
  • Aggregation and merging of data from multiple upstream servers

Use the pglogical plug-in

  • Enable the pglogical plug-in.
    CREATE EXTENSION pglogical;
  • Disable the pglogical plug-in.
    DROP EXTENSION pglogical;

Configure logical streaming replication

  1. Create a provider on the provider node.
    Note The value of the host parameter is fixed to 127.0.0.1, and the value of the port parameter is fixed to 3002.
    SELECT pglogical.create_node(
        node_name := 'provider',
        dsn := 'host=127.0.0.1 port=3002 dbname=test user=provider_user password=provider_pass'
    );
  2. Configure a replication set.

    Add all tables in the public schema to the default replication set.

    SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
    Note
    • A replication set is used to replicate only the specified tables and only the specified changes to these tables from the provider to a subscriber.
    • The default replication set is used to replicate all tables and all changes to these tables from the provider to a subscriber. For more information, see the pglogical documentation.
  3. Create a subscriber on the subscriber node.
    Note The value of the host parameter is fixed to 127.0.0.1, and the value of the port parameter is fixed to 3002.
    SELECT pglogical.create_node(
        node_name := 'subscriber',
        dsn := 'host=127.0.0.1 port=3002 dbname=test user=subscriber_user password=subscriber_pass'
    );
  4. Create a subscription on the subscriber node.
    Note The host parameter must be set to the private IP address of the provider, and the port parameter must be set to the internal port of the provider.
    SELECT pglogical.create_subscription(
        subscription_name := 'subscription',
        provider_dsn := 'host=<The private IP address of the provider> port=<The internal port of the provider> dbname=test user=provider_user password=provider_pass'
    );

    After the subscription is created, the synchronization and replication process starts in the background.

References

For more information, see the pglogical documentation.