8

Is there a method to disable a specific replication_slot? When I try to drop it I got an error because it is Active. Thanks

Salvatore Tumminaro
  • 93
  • 1
  • 5
  • 2
    What do you use the slot for? If it's used for logical replication, you need to disable the subscription. –  Jul 13 '20 at 13:19
  • 1
    Yes, it was created for a logical replication but something went wrong when I made the Subscription and now I only have the replication slot but not the Subscription. So I need to drop only the replication slot. Thank you – Salvatore Tumminaro Jul 14 '20 at 15:34

1 Answers1

16

No, you need to turn it off on the replica side before dropping. If you can't do that, then you have to terminate the wal sender and then drop the slot before it has a chance to start up again. Since it is a race, you should arrange to submit the drop command on the same keystroke as the terminate command.

select pg_drop_replication_slot('rep_slot');
    ERROR:  replication slot "rep_slot" is active for PID 162564
select pg_terminate_backend(162564); select pg_drop_replication_slot('rep_slot');
jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Your comment would be more useful if you could provide the details on how to turn it off on the replica side. – mvd Aug 26 '22 at 01:40
  • I've no idea how you want to stop it on the replica side, it is not my replica. You can shut it down, destroy it, evict it from the network, promote it to be a competing master, remove the primary_conninfo and restart as a log-shipping-only replica, drop the subscription (although if you do that the simple way it would drop the slot for you), or disable the subscription. Maybe the replica isn't even another PostgreSQL system, in which case you would have a different set of options. – jjanes Aug 26 '22 at 13:02