Search code examples
postgresqlpglogical

How can I get past this problem recreating a pg_logical subscription?

I'm having a problem dropping and creating subscriptions - I think these messages are saying it exists on the replica and not the primary:

db=#   CREATE SUBSCRIPTION sub 
db-#   CONNECTION 'dbname=db host=some.domain.com user=logical_replicator password=xxxxxxx' 
db-#   PUBLICATION pub;
ERROR:  subscription "sub" already exists
db=# DROP subscription sub;
ERROR:  could not drop the replication slot "sub" on publisher
DETAIL:  The error was: ERROR:  replication slot "sub" does not exist

How can I get this subscription recreated?

Solution

  • I found the answer in the documentation:

    db =# ALTER SUBSCRIPTION sub DISABLE;
ALTER SUBSCRIPTION
db =# ALTER SUBSCRIPTION sub SET (slot_name=NONE);
ALTER SUBSCRIPTION
db =# drop subscription sub;