How can I determine which database a replication subscription belongs to?

All we need is an easy explanation of the problem, so here it is.

Following along from

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply