All Products
Search
Document Center

ApsaraDB RDS:Use the pglogical extension

Last Updated:Jan 09, 2024

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

Prerequisites

  • The RDS instance runs PostgreSQL 10 or later.

  • The RDS instance runs a minor engine version of 20230830 or later.

    Important

    The extension is supported in minor engine versions that are earlier than 20230830. To standardize extension management and enhance extension security for ApsaraDB RDS for PostgreSQL, ApsaraDB RDS plans to optimize vulnerable extensions in minor engine version iterations. As a result, some extensions can no longer be created for RDS instances that run earlier minor engine versions. For more information, see [Product changes/Feature changes] Limits on extension creation for ApsaraDB RDS for PostgreSQL instances.

    • If you have created the extension for your RDS instance that runs a minor engine version earlier than 20230830, the extension is not affected.

    • If this is the first time you create the extension for your RDS instance or re-create the extension, you must update the minor engine version of the RDS instance to the latest version. For more information, see Update the minor engine version.

  • 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 Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.

    Note

    After you modify the value of the shared_preload_libraries parameter and apply the modification, your RDS instance immediately restarts. The restart causes a disconnection of your application from your RDS instance. Proceed with caution. Before the restart is triggered, plan your business and make sure that your application is configured to automatically reconnect to the database.

Background information

The pglogical extension of PostgreQL serves as a highly efficient method of replicating data as an alternative to physical replication by using a publish/subscribe pattern. The pglogical extension delivers higher replication speed than Slony, Bucardo, and Londiste and supports cross-version upgrades. The pglogical extension 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 extension

  • Enable the pglogical extension.

    CREATE EXTENSION pglogical;
  • Disable the pglogical extension.

    DROP EXTENSION pglogical;

Configure logical streaming replication

  1. Create a publisher node on the publisher.

    Note

    You must set the host parameter to 127.0.0.1 for the publisher node. You can execute the SHOW PORT; statement to obtain the value of the port parameter for the publisher node.

    SELECT pglogical.create_node(
        node_name := 'provider',
        dsn := 'host=127.0.0.1 port=<Port number> 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 publisher to a subscriber.

    • The default replication set is used to replicate all tables and all changes to these tables from the publisher to a subscriber. For more information, see the pglogical documentation.

  3. Create a subscriber node on the subscriber.

    Note

    You must set the host parameter to 127.0.0.1 for the subscriber node. You can execute the SHOW PORT; statement to obtain the value of the port parameter for the subscriber node.

    SELECT pglogical.create_node(
        node_name := 'subscriber',
        dsn := 'host=127.0.0.1 port=<Port number> dbname=test user=subscriber_user password=subscriber_pass'
    );
  4. Create a subscription on the subscriber.

    Note
    SELECT pglogical.create_subscription(
        subscription_name := 'subscription',
        provider_dsn := 'host=<Internal endpoint of the provider> port=<Internal port number of the provider> dbname=test user=provider_user password=provider_pass'
    );

    After the subscription is created, the synchronization and replication processes start in the background.

References

For more information, see pglogical documentation.