postgresql logical replication - unable to drop subscription

While testing different scenarios, I run as advised:

alter subscription mysub set (slot_name = NONE);

drop subscription mysub;

I get an error now:

postgres=# select pg_drop_replication_slot('mysub');
ERROR:  replication slot "mysub" does not exist

postgres=# create subscription mysub connection 'host=192.168.1.140 port=5432 user=postgres dbname=postgres' publication mypub, insert_only;
ERROR:  could not create replication slot "mysub": ERROR:  replication slot "mysub" already exists

postgres=# drop subscription mysub;
ERROR:  subscription "mysub" does not exist

how can I resolve it?

Solution 1:

You must DISABLE your SUBSCRIPTION by name first.

postgres=#\d reportcenter;
reportcenter=# ALTER SUBSCRIPTION mysub DISABLE;
reportcenter=# ALTER SUBSCRIPTION mysub SET (slot_name=NONE);
reportcenter=# DROP SUBSCRIPTION mysub;

https://dba.stackexchange.com/questions/207653/cannot-drop-database-with-logical-replication?answertab=votes#tab-top

Solution 2:

The replication slot is created on the primary node, so your attempt to drop it on the standby has no effect and causes an error.

Connect to the primary to drop the replication slot.

Since you failed to drop the replication slot, it comes as no surprise that creating a new subscription with the same name (and hence replication slot) fails.

To find a replication slot, run the following on the primary:

SELECT * FROM pg_replication_slots;

This allows you to see all the replication slots. Then drop the relevant slot:

SELECT * FROM pg_drop_replication_slot('ENTER SLOT NAME HERE');