RDS for PostgreSQL provides a logical subscription function that supports one-way real-time synchronization at the table level between multiple RDS for PostgreSQL instances. This function can be used for data sharing, data aggregation, data splitting, and other business scenarios.

If you deploy your business in multiple regions across the country, you can use logical subscriptions to share data in different regions. For example, you can share the data in the data center to other regions, or aggregate data from other regions to the center for real-time analysis and query.

This topic describes how to use the logical subscription function. The following example describes the whole procedure:

Create a publication in the src database of the source instance, publish the public.t1 table, create a subscription named sub1_from_pub1 in the dst database of the destination instance, and subscribe to the public.t1 table that is in the source database.

For more information, see Logical subscriptions.

Note

You can implement logical subscriptions between two tables of a single instance or between two instances in the same VPC. For the logical subscriptions between two instances in the same VPC, you must use the internal endpoint.

Prerequisites

  • Currently, only ApsaraDB RDS for PostgreSQL 10 Basic Edition supports logical subscriptions because of the network connectivity.
  • The wal_level parameter of the instance must be set to logical for logical subscriptions. You can modify it on the Parameter Settings page. After you modify the parameter, you must restart the instance to make the changes take effect. A restart will disconnect the instance. Use caution when performing the instance restart and make sure that the restart does not affect your services.
  • To implement logical subscriptions between two instances in the same VPC, you must add the IP CIDR block of the VPC to the whitelist of each instance. For example, the IP CIDR block of the VPC is 172.16.0.0/16, you must add 172.16.0.0/16 to the whitelist. For more information, see Configure a whitelist.
  • The account used to implement logical subscriptions must have the rds_superuser permission, such as a privileged account and accounts created by running the command: create role xxx with superuser.

Procedures

Create a new publication in the source database

  1. Connect to the ApsaraDB RDS for PostgreSQL instance.
  2. Run the following command to create a new publication in the source database:
    CREATE PUBLICATION <publication name> FOR TABLE <table name>;

    Examples

    create publication pub1 for table public.t1;
    Note
    • Only persistent base tables can be part of a publication. For more information, see CREATE PUBLICATION.
    • You can query the publications of the current database by running the command: select * from pg_publication.

Create a subscription in the destination database

  1. Connect to the ApsaraDB RDS for PostgreSQL instance.
  2. This step takes the source database and the destination database in the same instance as an example. If you want to implement logical subscriptions between two instances, skip to step 3.

    Run the following command:

    select * from pg_create_logical_replication_slot('<subscription name>','pgoutput');
  3. To create a new subscription in the destination database, run the following command:
    CREATE SUBSCRIPTION <subscription name>
        CONNECTION '<The connection string of the source instance>'
        PUBLICATION <The publication 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
    • The connection string of the source database is in the following format: host = <VPC name of the source instance> port = <internal port of the source instance> user = <account that has the permission to publish tables in the source database> password = <account password to access the source instance > dbname = <source database name> .
    • If the source database and destination database are in the same instance, you need to specify host = localhost, create_slot = false. The port number can be queried by running the show port command. By default, the port number is 3002.
    • You can run select * from pg_sub to query all the subscriptions of the database cluster.
    • You can add relevant subscription parameters after the publication name of the source database by using with statements. For more information, see Create subscription.