This topic describes how to use the pglogical plug-in of PostgreSQL to replicate the data of an ApsaraDB RDS for PostgreSQL instance. This plug-in uses a publish/subscribe pattern to implement logical streaming replication.

Prerequisites

  • Your RDS instance runs one of the following PostgreSQL versions:
    • PostgreSQL 14
    • PostgreSQL 13
    • PostgreSQL 12
    • PostgreSQL 11
    • PostgreSQL 10
  • pglogical is added to the value of the shared_preload_libraries parameter of your RDS instance.

    For more information about how to add pglogical to the value of the shared_preload_libraries parameter, see Manage the parameters of an ApsaraDB RDS for PostgreSQL instance.

Background information

The pglogical plug-in 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 of major engine versions
  • Replication of all data from a database on your RDS instance
  • Replication of specified 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 as 127.0.0.1, and the value of the port parameter is fixed as 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 as 127.0.0.1, and the value of the port parameter is fixed as 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 number of the provider.
    SELECT pglogical.create_subscription(
        subscription_name := 'subscription',
        provider_dsn := 'host=<The private IP address of the provider> port=<The internal port number 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.