Troubleshooting Inactive Logical Replication Slots

Description

Inactive logical replication slots must be cleared in a timely manner for a production DB instance. If the value of the inactive logical replication slots metric is no less than 1 in three consecutive periods, there are inactive logical replication slots.

Inactive logical replication slots have the following impacts:

• Residual inactive logical replication slots retain resources required for logical replication. WAL logs cannot be cleared, occupying much storage space or even causing full storage.
• Applications may not run as expected, which may cause risks.

Troubleshooting

To troubleshoot inactive logical replication slots, perform the following steps:

1. Check whether there is any invalid logical replication slot.
2. Determine whether the logical replication slot is still useful.
3. Delete the useless logical replication slot.

Solution

1. Check whether there is any invalid logical replication slot.

   Run the SQL statement shown below on the publishing client to check whether there is any invalid logical replication slot:

   If any command output is displayed, there is an invalid logical replication slot in the instance. The slot_name field in the command output indicates the name of the invalid logical replication slot.

   select slot_name,database,active from pg_replication_slots where active ='f' and slot_type='logical';

2. Determine whether the logical replication slot is still useful.

   If no, go to 3.

   If yes, do as follows:

   1. Check whether the replication of the logical replication slot is not enabled during subscription creation on the subscription client.
      Run the following SQL statement on the subscription client to check the return value in the subenabled column:

      select  subname,subenabled from pg_subscription;

      • If the return value is f, logical replication is not enabled for subscriptions. Run the following SQL statement to enable logical replication:

        ALTER SUBSCRIPTION sub_name ENABLE;

      • If the return value is t, go to the next step.
   2. Check whether the logical replication slot is not cleared after any other tool is used to execute a task due to task interruption or exceptions in operations on the source or destination database, such as backup and index rebuilding.
      Run the SQL statement shown below on the publishing client and check whether the return value of slot_name starts with drs. If yes, the logical replication slot is generated during DRS task execution. In this case, you can determine whether to clear the slot based on the task status.

      select slot_name,database,active from pg_replication_slots where active ='f' and slot_type='logical';

3. If the logical replication slot is no longer used, delete the invalid logical replication slot.

   select pg_drop_replication_slot('slot_name');