All Products
Search

This Product

    ApsaraDB RDS:Use the logical subscription feature

    Document Center

    ApsaraDB RDS:Use the logical subscription feature

    Last Updated:Jun 06, 2024

    This topic describes the logical subscription feature of ApsaraDB RDS for PostgreSQL. This feature supports quasi-real-time table-level one-way synchronization between multiple ApsaraDB RDS for PostgreSQL instances. This feature is suitable for business scenarios such as data sharing, data aggregation, and data splitting.

    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:

    Create a publication in the src database of the source RDS instance, publish the public.t1 table, and create a subscription named sub1_from_pub1 on the dst database of the required RDS instance 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 of 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 with 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 make the new value 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 to which one RDS instance belongs is added to an IP address whitelist of the other RDS instance. For example, you can add 172.16.0.0/16 to an IP address whitelist. For more information, see Configure a whitelist for an RDS PostgreSQL instance.

    • 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

    To create a publication in the source database of the source RDS instance, perform the following steps:

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

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

      CREATE PUBLICATION <Name of the publication> FOR TABLE <Name of the source table>;

      Example:

      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 that stores the specified source table.

    Perform the following steps to create a subscription in the destination database of the destination RDS instance:

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

    1. Create a subscription in the destination database.

      • If the source and destination databases reside on the same RDS instance, perform the following steps:

        1. Create a logical replication slot in the source database. 

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

        2. Create a logical subscription in the destination database.

          3. Note

          • You must create a table in the destination database. The table must have the same schema as the table in the source database.

          • If the source and destination databases reside on the same RDS instance, you must use the WITH clause to add the create_slot=false setting for the subscription. 

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

      • If the source and destination databases reside on different RDS instances, perform the following steps:

        Create a logical subscription in the destination database. 

        CREATE SUBSCRIPTION <Subscription name>
    CONNECTION '<The information that is required to log on to the RDS instance on which the source database resides>'
    PUBLICATION <Publication name of the source database>;

        The information that is required to log on to the RDS instance on which the source database resides is in the following format: host=<The internal endpoint of the RDS instance> port=<The internal port of the RDS instance> user=<The username of the account that has permissions on publications on the RDS instance> password=<The password of the account that has permissions on publications on the RDS instance> dbname=<The name of the source database>.

        Examples:

        CREATE SUBSCRIPTION sub1_from_pub1 
CONNECTION 'host=pgm-xxxxx.pgsql.singapore.rds.aliyuncs.com port=3433 user=test password=xxxxx dbname=src' 
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 append a subscription parameter to the name of the publication by using a WITH clause. For more information, see CREATE SUBSCRIPTION.