This also seems to be a problem for somewhat fringe case of subscriptions created with connect=false option.
They cannot be dropped in an obvious way, without knowing the ALTER SUBSCRIPTION trick.
For example:
contrib_regression=# create subscription test_sub connection 'dbname=contrib_regression' publication test_pub with ( connect=false );
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
CREATE SUBSCRIPTION
contrib_regression=# drop subscription test_sub; -- fails
ERROR: could not drop the replication slot "test_sub" on publisher
DETAIL: The error was: ERROR: replication slot "test_sub" does not exist
contrib_regression=# alter subscription test_sub set ( slot_name=none );
ALTER SUBSCRIPTION
contrib_regression=# drop subscription test_sub; -- succeeds
DROP SUBSCRIPTION
Note that the publication was never refreshed.
It seems that the first DROP should succeed in the above case.
Or at least some hint should be given how to fix this.
On 2019-09-24 16:31, Jeff Janes wrote:
I recently had to cut loose (pg_drop_replication_slot) a logical replica
that couldn't keep up and so was threatening to bring down the master.
In mopping up on the replica side, I couldn't just drop the
subscription, because it couldn't drop the nonexistent slot on the
master and so refused to work. So I had to do a silly little dance
where I first disable the subscription, then ALTER SUBSCRIPTION ... SET
(slot_name = NONE), then drop it.
Wanting to clean up after itself is admirable, but if there is nothing
to clean up, why should that be an error condition?
The alternatives seem quite error prone to me. Better be explicit.
--
Peter Eisentraut
http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services