Failover Slot for Logical Subscriptions

Scenarios

RDS for PostgreSQL synchronizes information about logical replication slots specified as failover slots from the primary DB instance to the standby DB instance to ensure logical subscriptions remain connected after a primary/standby switchover.

Information about logical replication slots will not be transferred to the new primary DB instance during a primary/standby switchover. If there is a primary/standby switchover, the logical subscriptions are disconnected, and you will need to manually create new slots. Failover slots make it possible to synchronize all logical slots from the primary DB instance to the standby DB instance, preventing logical subscriptions from being disconnected after a primary/standby switchover.

Precautions

• This function is supported in RDS for PostgreSQL 12.6 and later minor versions, and all minor versions of RDS for PostgreSQL 13 and later major versions.
• This function supports only logical subscriptions. It does not support physical subscriptions.
• This function introduces new log types. Therefore, if you enable this function for your instance, old slot information will be retained when you restore data to a new instance from backups. You need to manually delete the old slot information after the restoration is complete.

How to Use

Run the following SQL statement on the publishing client to create a failover slot:

To create a failover slot, the value of the third parameter must be false and that of the last parameter must be true. If you do not specify the last parameter, the created slot is not a failover slot.

How to Check

Run the following SQL statement on the publishing client to query the replication slot information of the database:

select * from pg_get_replication_slots();

• Check the failover field. If the value is true, the replication slot is a failover slot.
• If there is no the failover field or its value is false, the replication slot is not a failover slot.

Example of a Complete Logical Subscription

• Create a table on the publishing client.

  create table tableName(id int primary key, num int);

• Create a publication on the publishing client.

  create publication pubName for table tableName;

• Create a failover slot on the publishing client. (For RDS for PostgreSQL 12 or 13, delete the fourth parameter.)

  SELECT * FROM pg_create_logical_replication_slot('slotname', 'pgoutput', false, false, true);

• Insert data into the table on the publishing client.

  insert into tableName values(1,1);

  insert into tableName values(2,2);

• Create a table on the subscription client.

  create table tableName (id int primary key, num int);

• Create a subscription on the subscription client and specify the name of the failover slot.

  create subscription subName connection 'host=192.168.0.10 dbname=postgres user=root port=5432 password=xxxxxxx' publication pubName with(copy_data=true,create_slot=false,slot_name= slotname);

• Query data on the subscription client and check whether the data is subscribed to.

  select * from tableName;

• Perform a primary/standby switchover.

  Insert data into the table on the publishing client and view the data on the subscription client. The logical subscription is not disconnected.