Removing a subscription that does not exist

From: Jeff Ross <jross(at)openvistas(dot)net>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Removing a subscription that does not exist
Date: 2021-07-09 16:49:46
Message-ID: eae195c7-0747-02f8-84d7-13a2857b4453@openvistas.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)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(at)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(at)metro_logical> alter subscription metro disable;
ERROR:  subscription "metro" does not exist
Time: 24.263 ms
mirror_admin(at)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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kenneth Marshall 2021-07-09 18:27:43 Re: optimization issue
Previous Message gzh 2021-07-09 07:09:51 after psqlodbc upgrade - Which change solves the error: -2147217887