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
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 |