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 any logical replication slot is not in use.
2. Determine whether the logical replication slot you found is still needed.
3. If it is no longer used, delete it.

Solution

1. Check whether any logical replication slot is not in use.

   Run the SQL statement shown below on the publisher to check whether any logical replication slot is not in use:

   If command output is displayed, there is an inactive logical replication slot in the instance. slot_name in the command output indicates the name of the inactive 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 you found is still needed.

   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 subscriber.
      Run the following SQL statement on the subscriber 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 the subscription. 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 source or destination database operations, such as backup and index rebuilding.
      Run the SQL statement shown below on the publisher 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 clear the slot as needed.

      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 it.

   select pg_drop_replication_slot('slot_name');