ApsaraDB RDS:Use the pglogical extension

Prerequisites

• Your RDS instance runs PostgreSQL 10 or later.

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

  Important

  This extension is supported in some minor engine versions earlier than 20230830. However, ApsaraDB RDS plans to optimize vulnerable extensions in minor engine version updates for standardized extension management and enhanced security. Therefore, you cannot create this extension for RDS instances that run a minor engine version earlier than 20230830. For more information, see [Product changes/Feature changes] Limits on extension creation for ApsaraDB RDS for PostgreSQL instances.

  • If you have already created this extension for your RDS instance that runs a minor engine version earlier than 20230830, you can continue using this extension.

  • If you are creating this extension for the first time or need to recreate the extension for your RDS instance, 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

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

   • The host parameter must be set to the internal endpoint of the publisher, such as pgm-****.pg.rds.aliyuncs.com. The port parameter must be set to the internal port number of the publisher.

   • You can view the internal endpoint and internal port number of an RDS instance in the ApsaraDB RDS console. For more information, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.

   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.