Postgres Pro
Downloads
Home   >   mailing lists

Thread: Removing a subscription that does not exist

Removing a subscription that does not exist

From
Jeff Ross
Date:
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

From
Kyotaro Horiguchi
Date:
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

From
Jeff Ross
Date:
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