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 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.

An example of the logical subscription process is as follows:

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 destination RDS instance to subscribe to data from the public.t1 table.

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

Precautions

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 only by using internal endpoints.

Prerequisites

  • Your RDS instance runs one of the following PostgreSQL versions:
    • PostgreSQL 12 (with standard or enhanced SSDs)
    • PostgreSQL 11 (with standard or enhanced SSDs)
    • PostgreSQL 10 (with standard or enhanced SSDs)
  • The wal_level parameter is set to logical for your RDS instance. You can reconfigure this parameter on the Parameters page in the ApsaraDB for RDS console. After you reconfigure this parameter, you must restart your RDS instance to make the new value take effect. Restarting your RDS instance will terminate all its connections. Make appropriate service arrangements before you restart your RDS instance.
  • If you want to create logical subscriptions between two RDS instances that reside in the same VPC, the Classless Inter-Domain Routing (CIDR) block that contains the IP address 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 superuesr command.

Procedure

Follow these steps to create a publication in the source database of the source RDS instance:

  1. Connect to the source RDS instance. For more information, see Connect to an RDS PostgreSQL instance.
  2. Execute the following statement to create a publication in the source database:
    CREATE PUBLICATION <The name of the publication> FOR TABLE <The 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 run the select * frompg_publication; command to view the existing publications of the database that stores the specified source table.

Follow these 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 RDS PostgreSQL instance.
  2. Execute the following statement. In this example, the source and destination databases are on the same RDS instance. If you want to create a subscription between two RDS instances, go to step 3.
    select * from pg_create_logical_replication_slot('<The name of the subscription>','pgoutput');
  3. Execute the following statement to create a subscription in the destination database:
    CREATE SUBSCRIPTION <The name of the subscription>
        CONNECTION '<The information that is required to log on to the source database>'
        PUBLICATION <The name of the publication in the source database>;

    Example:

    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
    • The information that is required to log on to the source database is in the following format: host=<The internal endpoint of the source RDS instance> port=<The internal port of the source RDS instance> user=<The username of the account that has permissions on publications on the source RDS instance> password=<The password of the account that has permissions on publications on the source RDS instance> dbname=<The name of the source database>.
    • If the source and destination databases are on the same RDS instance, you must set the host parameter to localhost and the create_slot parameter to false. You can query the port number by running the show port command. In most cases, the port number is 3002.
    • You can view the subscriptions of your entire database system by running the select * from pg_subscription; command.
    • You can append a subscription parameter to the name of the publication by using a WITH clause. For more information, see CREATE SUBSCRIPTION.