Waiting for 9.1 – pg_stat_replication

On 7th of January (I know, it was quite some time ago, I apologize for delay) Itagaki Takahiro committed patch:

New system view pg_stat_replication displays activity of wal sender processes.
 
Itagaki Takahiro and Simon Riggs.

This view is very simple, but it shows all necessary information.

For example, in my simple test case with single streaming replication slave:

# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
procpid          | 4997
usesysid         | 16658
usename          | replica
application_name | walreceiver
client_addr      | 127.0.0.1
client_port      | 60627
backend_start    | 2011-01-24 14:40:07.617364+01
state            | STREAMING
sent_location    | 0/C000458

For comparison purposes, my current xlog location is:

# SELECT pg_current_xlog_location();
 pg_current_xlog_location 
--------------------------
 0/C000458
(1 ROW)

(which means: it would be great to get a simple way to do arithmetics on xlog locations).

All in all it seems to be very easy to understand.

One thing, though – with current additions – you can stream more than just wal segments through the streaming replication connections. Like – base backups.

What is being shown there then?

Well, to test it, I setup $PGDATA with rather large ( 4GB ) random file (in pg_log to be exact, but it will be streamed too), and started pg_basebackup tool ( will describe it it next blog post ). While sending the backup, pg_stat_replication showed:

-[ RECORD 1 ]----+------------------------------
procpid          | 4997
usesysid         | 16658
usename          | replica
application_name | walreceiver
client_addr      | 127.0.0.1
client_port      | 60627
backend_start    | 2011-01-24 14:40:07.617364+01
state            | STREAMING
sent_location    | 0/D0000B0
-[ RECORD 2 ]----+------------------------------
procpid          | 7000
usesysid         | 16658
usename          | replica
application_name | pg_basebackup
client_addr      | 127.0.0.1
client_port      | 59053
backend_start    | 2011-01-24 15:07:51.004891+01
state            | BACKUP
sent_location    | 0/0

Nice. All seems to be good. Nice addition, will definitely make monitoring easier.