system views for walsender activity

ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>

From: Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp>
To: pgsql-hackers@postgresql.org
Date: 2010-06-18T02:33:27Z
Lists: pgsql-hackers

Attachments

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.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center