Thread
-
Removing a subscription that does not exist
Jeff Ross <jross@openvistas.net> — 2021-07-09T16:49:46Z
Hello, I'm working with an RDS instance running 12 that has an old subscription that I can't seem to drop. The logs show this, repeating every 5 seconds or so. 2021-07-09 16:08:07 UTC::@:[1637]:LOG: logical replication apply worker for subscription "metro" has started 2021-07-09 16:08:07 UTC::@:[1637]:ERROR: could not connect to the publisher: could not connect to server: Connection refused Is the server running on host "dbp3" (108.200.30.101) and accepting TCP/IP connections on port 5433? dbp3 is long gone--the server no long exists. It shows up here: mirror_admin@metro_logical> select * from pg_subscription; oid │ subdbid │ subname │ subowner │ subenabled │ subconninfo │ subslotname │ subsynccommit │ subpublications ───────┼─────────┼─────────────────┼──────────┼────────────┼───────────────────────────────────────────────────────────────┼─────────────────┼───────────────┼──────────────────────── 83645 │ 66754 │ cargowel_common │ 16394 │ t │ host=108.200.30.103 port=5433 user=postgres dbname=metro_prod │ cargowel_common │ off │ {cargowel_common_prod} 83646 │ 66754 │ metro_prod │ 16394 │ t │ host=108.200.30.103 port=5433 user=postgres dbname=metro_prod │ metro_prod │ off │ {metro_prod} 51490 │ 14313 │ metro │ 16394 │ t │ dbname=metro host=dbp3 port=5433 user=repmgr │ metro │ off │ {metro} (3 rows) Time: 28.627 ms But not in here: mirror_admin@metro_logical> \dRs+ List of subscriptions Name │ Owner │ Enabled │ Publication │ Synchronous commit │ Conninfo ─────────────────┼──────────────┼─────────┼────────────────────────┼────────────────────┼─────────────────────────────────────────────────────────────── cargowel_common │ mirror_admin │ t │ {cargowel_common_prod} │ off │ host=108.200.30.103 port=5433 user=postgres dbname=metro_prod metro_prod │ mirror_admin │ t │ {metro_prod} │ off │ host=108.200.30.103 port=5433 user=postgres dbname=metro_prod (2 rows) And it can't be disabled or dropped: mirror_admin@metro_logical> alter subscription metro disable; ERROR: subscription "metro" does not exist Time: 24.263 ms mirror_admin@metro_logical> drop subscription metro; ERROR: subscription "metro" does not exist Time: 23.648 ms I did try deleting it directly from the pg_subscription table but that failed with a permission denied error. My suspicion is that's because of the RDS environment. What else can I try to remove this old non-functional subscription? Thanks, Jeff Ross -
Re: Removing a subscription that does not exist
Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2021-07-12T01:38:01Z
At Fri, 9 Jul 2021 10:49:46 -0600, Jeff Ross <jross@openvistas.net> wrote in > Hello, > > I'm working with an RDS instance running 12 that has an old > subscription that I can't seem to drop. ... > It shows up here: > > mirror_admin@metro_logical> select * from pg_subscription; > oid │ subdbid │ subname │ subowner │ ... > ────┼─────┼─────────┼─────┼ ... > 83645 │ 66754 │ cargowel_common │ 16394 │ ... > 83646 │ 66754 │ metro_prod │ 16394 │ ... > 51490 │ 14313 │ metro │ 16394 │ ... > (3 rows) ... > But not in here: > > mirror_admin@metro_logical> \dRs+ > List of subscriptions > Name │ Owner │ Enabled │ Publication > ─────────┼───────┼─────┼─────────── > cargowel_common │ mirror_admin │ t │ {cargowel_common_prod} > metro_prod │ mirror_admin │ t │ {metro_prod} > (2 rows) > > And it can't be disabled or dropped: Look at the subdbid field in the first query result. You were logging into the databsae with OID=66754 and the subscription "metro" belongs to the database 14313. The second command doesn't show metro which is not of the current database. > mirror_admin@metro_logical> alter subscription metro disable; > ERROR: subscription "metro" does not exist > Time: 24.263 ms > mirror_admin@metro_logical> drop subscription metro; > ERROR: subscription "metro" does not exist > Time: 23.648 ms > > I did try deleting it directly from the pg_subscription table but that > failed with a permission denied error. My suspicion is that's because > of the RDS environment. > > What else can I try to remove this old non-functional subscription? Thus you need to log in to the databse OID=14313 to manipulate on the subsciption metro. regards. -- Kyotaro Horiguchi NTT Open Source Software Center -
Re: Removing a subscription that does not exist
Jeff Ross <jross@openvistas.net> — 2021-07-12T14:24:50Z
On 7/11/21 7:38 PM, Kyotaro Horiguchi wrote: > At Fri, 9 Jul 2021 10:49:46 -0600, Jeff Ross <jross@openvistas.net> wrote in >> Hello, >> >> I'm working with an RDS instance running 12 that has an old >> subscription that I can't seem to drop. > ... > Look at the subdbid field in the first query result. You were logging > into the databsae with OID=66754 and the subscription "metro" belongs > to the database 14313. The second command doesn't show metro which is > not of the current database. > > > > | What else can I try to remove this old non-functional subscription? >> ... > Thus you need to log in to the databse OID=14313 to manipulate on the > subsciption metro. > > regards. > That was it exactly. Once I connected to that database the subscription could be disabled, its slot name set to None and finally dropped. Thank you! Jeff