viewing connectioninfo used by subscriber on the publication server when inactive

From: Wim Bertels <wim(dot)bertels(at)ucll(dot)be>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: viewing connectioninfo used by subscriber on the publication server when inactive
Date: 2020-05-14 10:50:42
Message-ID: 25037dc66dadcdd61148f6d59208217441abfd80.camel@ucll.be
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello,

(context: logical built-in replication)

On the publication server:

How can you see what is the subscription connection is when this
connection is inactive?
Is there query that can be used?, or is this only possible with extra
monitoring tools (or going through the pg.log files if logged)?
(If so, what would you suggest on debian, preferably available in the
repository of apt.postresql.org or debian.org?)

An example:

# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary |
active | active_pid | xmin | catalog_xmin | restart_lsn |
confirmed_flush_lsn
-----------+----------+-----------+--------+----------+-----------+--
------+------------+------+--------------+-------------+---------------
------
db2_sub1 | pgoutput | logical | 20751 | db2 | f |
f | | | 171086 | C/C4C4EA08 | C/C4C4EA08
db2_sub2 | pgoutput | logical | 20751 | db2 | f |
t | 9957 | | 171113 | C/C4C90EE0 | C/C4CC0FE0

* so for db2_sub2 there is pid: pg_stat_activity gives me the userinfo
and others,
* but how can you obtain this for the inactive subscription (db2_sub1)?

https://www.postgresql.org/docs/current/logical-replication-monitoring.html
https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION-MONITORING

--
mvg,
Wim
--
In India, "cold weather" is merely a conventional phrase and has come into
use through the necessity of having some way to distinguish between weather
which will melt a brass door-knob and weather which will only make it mushy.
-- Mark Twain

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Keith Fiske 2020-05-14 12:05:23 Re: viewing connectioninfo used by subscriber on the publication server when inactive
Previous Message Laurenz Albe 2020-05-13 11:35:06 Re: what is the best way to access cold data on another server?