ApsaraDB RDS:Use the logical subscription feature

Feature description

If you deploy your business in more than one region, you can use logical subscriptions to share data among these regions. For example, you can share data from the data center that serves your business in a region to other regions. You can also aggregate data from other regions to the data center. This allows you to analyze and query all of your business data in real time.

The following example describes the logical subscription process:

1. Create a publication in the testdb01 database on the RDS instance of the publisher and publish the public.t1 table.

2. Create a subscription named sub1_from_pub1 in the testdb02 database on the RDS instance of the subscriber to subscribe to data from the public.t1 table.

For more information, see Principles and best practices of logical subscription.

Usage notes

You can create logical subscriptions between two tables on a single RDS instance or between two RDS instances that reside in the same virtual private cloud (VPC) and can be connected by using internal endpoints.

Prerequisites

• The RDS instance runs PostgreSQL 10 or later and uses cloud disks.

• The wal_level parameter is set to logical for your RDS instance. You can reconfigure this parameter on the Parameters page in the ApsaraDB RDS console. After you reconfigure this parameter, you must restart your RDS instance to for the new value to take effect. If you restart your RDS instance, your application is disconnected from the RDS instance. To ensure business continuity, we recommend that you take appropriate measures before your restart the RDS instance.

• If you want to create logical subscriptions between two RDS instances that reside in the same VPC, the CIDR block of the VPC is added to an IP address whitelists of the RDS instances. For example, you can add 172.16.0.0/16 to an IP address whitelist. For more information, see Configure an IP address whitelist.

• The account that you use has the permissions of the rds_superuser role. The account can be the privileged account of your RDS instance. The account can also be a standard account that you create by using the privileged account and the CREATE ROLE xxx WITH SUPERUSER statement.

Procedure

Create a publication on the publisher

1. Connect to the RDS instance of the publisher. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance.

2. Execute the following statement to create a publication in the database of the publisher:

   CREATE PUBLICATION <Publication name> FOR TABLE <Table name>;

   Examples

   CREATE PUBLICATION pub1 FOR TABLE public.t1;

   Note

   • You can publish only persistent base tables. For more information, see CREATE PUBLICATION.

   • You can execute the SELECT * FROM pg_publication; statement to view the existing publications of the database.

Create a subscription on the subscriber

1. Connect to the RDS instance of the subscriber. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance.

2. Create a subscription in the database of the subscriber:

   The publisher and the subscriber are different databases on the same RDS instance

   1. Create a logical replication slot in the database of the publisher.

      SELECT pg_create_logical_replication_slot('<slot_name>', 'pgoutput');

   2. You must create a table in the database of the subscriber. The table must have the same schema and name as the table in the database of the publisher to which you want to subscribe.

   3. Create a logical subscription in the database of the subscriber.

      • If your RDS instance runs PostgreSQL 16 or later, execute the following statements:

        CREATE SUBSCRIPTION <Subscription name>
        CONNECTION 'host=127.0.0.1 port=<Internet port> user=<Username of the account that has the publication permissions> password=<Password of the account> dbname=<Name of the database of the publisher>'
        PUBLICATION publication_name WITH (create_slot=false,slot_name=<slot_name>);

      • If your RDS instance runs a version from PostgreSQL 10 to PostgreSQL 15, execute the following statements:

        CREATE SUBSCRIPTION <Subscription name>
        CONNECTION 'dbname=<Name of the database of the publisher>'
        PUBLICATION publication_name WITH (create_slot=false,slot_name=<slot_name>);

   Important

   If the database of the publisher and the database of the subscriber reside on the same RDS instance, you must use the WITH clause to append the create_slot=false subscription setting.

   The publisher and the subscriber are different databases on different RDS instances

   1. You must create a table in the database of the subscriber. The table must have the same schema and name as the table in the database of the publisher to which you want to subscribe.

   2. Create a logical subscription in the database of the subscriber.

      CREATE SUBSCRIPTION <Subscription name>
      CONNECTION 'host=<Internet endpoint of the RDS instance of the publisher> port=<Internet port of the RDS instance of the publisher> user=<Username of the account on the RDS instance of the publisher that has the publication permissions> password=<Password of the account> dbname=<Name of the database on the RDS instance of the publisher>'
      PUBLICATION <Name of the publication in the database of the publisher>;

   Examples:

   CREATE SUBSCRIPTION sub1_from_pub1 
   CONNECTION 'host=pgm-2ze7n9zb0n1f****.pg.rds.aliyuncs.comm port=5432 user=testdbuser password=****x dbname=testdb01' 
   PUBLICATION pub1 WITH (enabled, create_slot, slot_name='sub1_from_pub1');  

   Note

   • You can execute the SELECT * FROM pg_subscription; statement to view the subscriptions of your entire database system.

   • You can use the WITH clause to append a subscription parameter to the name of the publication on the database of the publisher. For more information, see CREATE SUBSCRIPTION.