Use logical subscription for one-way table-level data synchronization on PostgreSQL - ApsaraDB RDS

Logical subscription enables quasi-real-time, table-level, one-way synchronization between ApsaraDB RDS for PostgreSQL instances. Use it to share data across regions, aggregate data from multiple sources into a central database, or split data across separate databases.

Prerequisites

Before you begin, make sure you have:

An ApsaraDB RDS for PostgreSQL instance running PostgreSQL 10 or later with cloud disks
The wal_level parameter set to logical on the publisher instance — reconfigure it on the Parameters page in the ApsaraDB RDS console, then restart the instance for the change to take effect
(For cross-instance subscriptions) The virtual private cloud (VPC) CIDR block added to the IP address whitelist of both instances — for example, add 172.16.0.0/16. For details, see Configure an IP address whitelist
An account with the rds_superuser role — either the privileged account or a standard account created with CREATE ROLE xxx WITH SUPERUSER

How it works

On the publisher, create a publication for one or more tables in a database.
On the subscriber, create a subscription that connects to the publisher's database and references the publication.
PostgreSQL streams row-level changes (INSERT, UPDATE, DELETE) from the published tables to the subscriber in quasi-real time.

Logical subscriptions work between two databases on the same RDS instance, or between two RDS instances that reside in the same VPC and are connected via internal endpoints.

For background on the underlying mechanism, see Principles and best practices of logical subscription.

Create a publication on the publisher

Connect to the publisher instance and connect to the database you want to publish from.

Create a publication for a table:

Only persistent base tables can be published. For supported objects, see CREATE PUBLICATION.

-- On the publisher database
CREATE PUBLICATION <publication_name> FOR TABLE <schema.table_name>;

Example:

-- On the publisher database (testdb01)
CREATE PUBLICATION pub1 FOR TABLE public.t1;

Verify that the publication was created:
```
-- On the publisher database
SELECT * FROM pg_publication;
```
The output lists pub1 with its configuration details.

Create a subscription on the subscriber

The steps differ depending on whether the publisher and subscriber are on the same RDS instance or on different instances.

Same RDS instance, different databases

When the publisher and subscriber are on the same instance, PostgreSQL cannot create a replication slot within the same CREATE SUBSCRIPTION command — doing so causes the command to hang. This is a PostgreSQL engine restriction, not an RDS limitation. Create the slot separately first, then reference it in the subscription.

Connect to the publisher database and create a logical replication slot:

-- On the publisher database
SELECT pg_create_logical_replication_slot('<slot_name>', 'pgoutput');

Connect to the subscriber database and create a table with the same schema and name as the table on the publisher.

Connect to the subscriber database and create the subscription, referencing the slot you created in step 1. If the instance runs PostgreSQL 16 or later:

Important

Always include create_slot=false in the WITH clause when the publisher and subscriber are on the same instance. Omitting it causes the command to hang.

-- On the subscriber database
CREATE SUBSCRIPTION <subscription_name>
CONNECTION 'host=127.0.0.1 port=<port> user=<username> password=<password> dbname=<publisher_db_name>'
PUBLICATION <publication_name> WITH (create_slot=false, slot_name=<slot_name>);

If the instance runs PostgreSQL 10 to 15:

-- On the subscriber database
CREATE SUBSCRIPTION <subscription_name>
CONNECTION 'dbname=<publisher_db_name>'
PUBLICATION <publication_name> WITH (create_slot=false, slot_name=<slot_name>);

Verify the subscription:

-- On the subscriber database
SELECT * FROM pg_subscription;

Different RDS instances

Connect to the subscriber instance and connect to the subscriber database.
Create a table with the same schema and name as the table on the publisher.

Create the subscription:

-- On the subscriber database
CREATE SUBSCRIPTION <subscription_name>
CONNECTION 'host=<Internet endpoint of the publisher RDS instance> port=<port> user=<username> password=<password> dbname=<publisher_db_name>'
PUBLICATION <publication_name>;

Example:

-- On the subscriber database (testdb02)
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');

For all available subscription parameters, see CREATE SUBSCRIPTION.

Verify the subscription:

-- On the subscriber database
SELECT * FROM pg_subscription;

Add tables to an existing publication

After adding tables to a publication, refresh the subscription on the subscriber — new tables are not picked up automatically.

-- On the subscriber database
ALTER SUBSCRIPTION <subscription_name> REFRESH PUBLICATION;

Monitor replication slots

Warning

Unused replication slots hold Write-Ahead Logging (WAL) files and prevent them from being recycled. If a subscriber stops consuming changes, WAL files accumulate and can fill the publisher's storage, making the instance unavailable.

After setting up logical subscriptions:

Monitor Storage Used on the publisher instance and set an alert for abnormal growth.
Monitor Maximum Used Transaction IDs to detect transaction ID wraparound risk.

Drop any replication slot that is no longer in use:

-- On the publisher database
SELECT pg_drop_replication_slot('<slot_name>');

To list all active replication slots:

-- On the publisher database
SELECT * FROM pg_replication_slots;

ApsaraDB RDS:Use the logical subscription feature