Cannot Drop Subscription from PostgresQL

Question

I'm using pgAdmin and can't delete the subscriptions in the database or drop the database.

When I attempt to drop the database I receive the error message

ERROR: database "xxxx" is being used by logical replication subscription DETAIL: There are 4 subscriptions.

When I attempt to drop the subscriptions I receive

ERROR: could not drop the replication slot "xxxx" on publisher DETAIL: The error was: ERROR: replication slot "xxxx" does not exist

I receive 0 results when querying for replication slots

select * from pg_replication_slots

When I query for subscriptions they all show up

SELECT * FROM pg_subscription

Any help much appreciated!

Answer 1

Unfortunately, you have to go through this elaborate dance:

alter subscription <sub1> disable;
alter subscription <sub1> set (slot_name = none);
drop subscription <sub1>;

for each subscription, before you can drop the database.

I think the justification for this monstrosity is they want to make it really hard to drop a subscription without also dropping the slot it services on the publisher. You can do it, you just have to go two steps out of your way, for each subscription.