Re: viewing connectioninfo used by subscriber on the publication server when inactive

On Thu, May 14, 2020 at 6:50 AM Wim Bertels <wim(dot)bertels(at)ucll(dot)be> wrote:

> 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
>
>
The replication slot connection could use any role that has been configured
to allow replication. So there is no way to know which role is actually
using a slot without it actually being connected. You can look and see
which roles have replication privileges by checking the output of \du in
psql, but there is nothing tying any roles to any specific slots.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com