Re: Streaming replication status

On Wed, Jan 13, 2010 at 5:47 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> The pieces are coming together...summary:

Thanks for the summary!

> -Also add pg_standbys_xlog_location() on the master: while they could live without it, this really helps out the "alert/monitor" script writer whose use cases keep popping up here.
>
> Details...the original idea from Fujii was:
>
> "I'm thinking something like pg_standbys_xlog_location() [on the primary] which returns
> one row per standby servers, showing pid of walsender, host name/
> port number/user OID of the standby, the location where the standby
> has written/flushed WAL. DBA can measure the gap from the
> combination of pg_current_xlog_location() and pg_standbys_xlog_location()
> via one query on the primary."

This function is useful but not essential for troubleshooting, I think.
So I'd like to postpone it.

> "It seems to me that we should have at least two functions available
> on the slave: latest xlog location received and synced to disk by
> walreceiver (ie, we are guaranteed to be able to replay up to here);
> and latest xlog location actually replayed (ie, the state visible
> to queries on the slave).  The latter perhaps could be
> pg_current_xlog_location()."
>
> So there's the first two of them:  on the slave, pg_current_xlog_location()
> giving the latest location replayed, and a new one named something like
> pg_standby_received_xlog_location().  If you take the position that an
> unreachable standby does provide answers to these questions too (you just
> won't like them), this pair might be sufficient to ship.

Done.

git://git.postgresql.org/git/users/fujii/postgres.git
branch: replication

I added two new functions;

(1) pg_last_xlog_receive_location() reports the last WAL location received
and synced by walreceiver. If streaming replication is still in progress
this will increase monotonically. If streaming replication has completed
then this value will remain static at the value of the last WAL record
received and synced. When the server has been started without a streaming
replication then the return value will be InvalidXLogRecPtr (0/0).

(2) pg_last_xlog_replay_location() reports the last WAL location replayed
during recovery. If recovery is still in progress this will increase
monotonically. If recovery has completed then this value will remain
static at the value of the last WAL record applied. When the server has
been started normally without a recovery then the return value will be
InvalidXLogRecPtr (0/0).

Since it's somewhat odd for me that pg_current_xlog_location() reports the
WAL replay location, I didn't do that. But if the majority feel that it's sane,
I'll merge pg_last_xlog_replay_location() into pg_current_xlog_location().

Thought? Better name?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center