ApsaraDB RDS:Logical replication slots on read-only instances

Create a local logical replication slot on the read-only instance.

-- [RO] Create a local logical replication slot.
SELECT pg_create_logical_replication_slot('my_slot', 'test_decoding');

Verify that the slot was created.

-- [RO] Verify that the slot was created.
SELECT slot_name, plugin FROM pg_replication_slots WHERE slot_name = 'my_slot';

Sample output:

 slot_name |    plugin     
-----------+---------------
 my_slot   | test_decoding 
(1 row)

Write data to the primary instance.

-- [RW] Write data to the primary instance.
INSERT INTO my_table VALUES (1), (2), (3);

Wait for the WAL to be replayed on the read-only instance.

On the primary instance, query the current WAL LSN:

-- [RW] Get the current WAL LSN on the primary instance.
SELECT pg_current_wal_lsn();

On the read-only instance, query the replay progress until the replay LSN is greater than or equal to the WAL LSN of the primary instance:

-- [RO] Check the replay progress on the read-only instance.
SELECT pg_last_wal_replay_lsn();

Consume the changes through the slot on the read-only instance.

-- [RO] Consume changes through the slot.
SELECT * FROM pg_logical_slot_get_changes('my_slot', NULL, NULL);

Sample output:

    lsn    | xid |                  data
-----------+-----+----------------------------------------
 0/XXXXXXX | 123 | table public.my_table: INSERT: ...
(3 rows)

When the slot is no longer needed, drop it.

-- [RO] Drop the slot.
SELECT pg_drop_replication_slot('my_slot');

Create a table and a publication on the primary instance.

-- [RW] Create a table and a publication on the primary instance.
CREATE TABLE tab_rep (id int PRIMARY KEY, data text);
CREATE PUBLICATION my_pub FOR TABLE tab_rep;

Wait for the WAL to be replayed on the read-only instance to ensure that the publication is visible. For instructions, see Step 4 in the Basic usage section.

Create the same table structure on the downstream subscriber.

-- [Subscriber] Create the table structure on the subscriber instance.
CREATE TABLE tab_rep (id int PRIMARY KEY, data text);

On the downstream subscriber, create a subscription that connects to the read-only instance.

-- [Subscriber] Create a subscription that connects to the read-only instance.
-- The subscription automatically creates a slot named 'my_sub' on the read-only instance.
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=<RO_HOST> port=<RO_PORT> dbname=postgres'
    PUBLICATION my_pub
    WITH (copy_data = off);

Write data to the primary instance and verify the synchronization.

-- [RW] Write data to the primary instance.
INSERT INTO tab_rep VALUES (1, 'hello'), (2, 'world');

Data flow: primary instance → (streaming replication/WAL) → read-only instance → (logical replication) → downstream subscriber.

-- [Subscriber] Verify that the data is synchronized.
SELECT * FROM tab_rep;

Sample output:

 id | data
----+-------
  1 | hello
  2 | world
(2 rows)

When the subscription is no longer needed, drop it on the downstream subscriber. This action automatically removes the slot from the read-only instance.

-- [Subscriber] Drop the subscription. This automatically removes the slot on the read-only instance.
DROP SUBSCRIPTION my_sub;

Manually create a slot on the read-only instance.

-- [RO] Manually create the slot on the read-only instance.
SELECT pg_create_logical_replication_slot('my_sub', 'pgoutput');

On the downstream subscriber, create a subscription that uses the existing slot.

-- [Subscriber] Create a subscription and use the existing slot.
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=<RO_HOST> port=<RO_PORT> dbname=postgres'
    PUBLICATION my_pub
    WITH (copy_data = off, create_slot = false);