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.
ImportantThe 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.
NoteAfter 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
Create a publisher node on the publisher.
NoteYou must set the
host
parameter to 127.0.0.1 for the publisher node. You can execute theSHOW PORT;
statement to obtain the value of theport
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' );
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']);
NoteA 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.
Create a subscriber node on the subscriber.
NoteYou must set the
host
parameter to 127.0.0.1 for the subscriber node. You can execute theSHOW PORT;
statement to obtain the value of theport
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' );
Create a subscription on the subscriber.
NoteThe
host
parameter must be set to the internal endpoint of the publisher, such aspgm-****.pg.rds.aliyuncs.com
. Theport
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.