1

Following along from https://stackoverflow.com/questions/68720250/cannot-drop-subscription-from-postgresql

It seems in order to drop the subscription, you first need to connect to the associated database. However I'm not sure how to determine which database a subscription belongs to?

An example of a subscription I'd like to delete:

finder_v2=# SELECT * FROM pg_subscription;
 subdbid | subname | subowner | subenabled |                                            subconninfo                                            | subslotname | subsynccommit | subpublications 
---------+---------+----------+------------+---------------------------------------------------------------------------------------------------+-------------+---------------+-----------------
   16384 | chris   |       10 | t          | host=localhost user=postgres password=mysecretpassword port=5555 dbname=finder connect_timeout=10 | chris       | off           | {chris}
(1 row)

I did a web search for this, as well as reading through some of the postgres documentation but still not able to figure this out.

3
  • If the associated database is connectable, dropping the subscription will automatically connect to it, you don't need to do anything.
    – jjanes
    Jul 12, 2022 at 15:12
  • Doesn't the subconninfo column contain the info you want?
    – jjanes
    Jul 12, 2022 at 15:13
  • I don't think it does? It gave me an ERROR: subscription "chris" does not exist error when I tried that. And no, subconfninfo seems to only relate to the publication connection details. Jul 12, 2022 at 15:58

1 Answer 1

3

subdbid identifies the database in which the subscription is defined:

select *
from pg_database
where oid = 16384;

Or join it to pg_subscription:

SELECT d.datname, s.subname, s.subowner::regrole::text
FROM pg_subscription s
  join pg_database d on d.oid = s.subdbid;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.