Обсуждение: Removing a subscription that does not exist
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
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
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
On 7/11/21 7:38 PM, Kyotaro Horiguchi wrote:
Thank you!
Jeff
That was it exactly. Once I connected to that database the subscription could be disabled, its slot name set to None and finally dropped.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.
Thank you!
Jeff