Re: system views for walsender activity

On Fri, Jun 18, 2010 at 04:33, Takahiro Itagaki
<itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> wrote:
> Hi,
>
> We don't have any statistic views for walsenders in SR's master server
> in 9.0, but such views would be useful to monitor and manage standby
> servers from the master server. I have two ideas for the solution -
> adding a new system view or recycling pg_stat_activity:
>
> 1. Add another system view for walsenders, ex. "pg_stat_replication".
>   It would show pid, remote host, and sent location for each walsender.
>
> 2. Make pg_stat_activity to show walsenders. We could use current_query
>   to display walsender-specific information, like:
>    =# SELECT * FROM my_stat_activity ;
>    -[ RECORD 1 ]----+---------------------------------
>    datid            | 16384
>    <snip>
>    current_query    | SELECT * FROM my_stat_activity ;
>    -[ RECORD 2 ]----+---------------------------------
>    datid            | 0
>    datname          |
>    procpid          | 4300
>    usesysid         | 10
>    usename          | itagaki
>    application_name |
>    client_addr      | ::1
>    client_port      | 37710
>    backend_start    | 2010-06-16 16:47:35.646486+09
>    xact_start       |
>    query_start      |
>    waiting          | f
>    current_query    | walsender: sent=0/701AAA8
>
> The attached patch is a WIP codes for the case 2, but it might be
> better to design management policy via SQL in 9.1 before such detailed
> implementation.  Comments welcome.

I like version 1 much better. It'll be a lot easier for a management
tool to get the data in proper columns and not have to parse it out of
an arbitrary string field.

The downside is that version 1 will require an initdb, and not version
2, right? In that light, #2 is probably the only one we can do for 9.0
(unless we stumble upon other initdb-forcing changes), so maybe we
should do that one as a temporary measure (and if so, note it both in
the documentation and code that it's a temporary thing).

Wouldn't we also need something similar for the receiving end?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/