Hi Ray, We are already using the following query:
SELECT CASE WHEN pg_last_xlog_receive_location( ) = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay; We cannot use pg_xlog_location_diff as we use postgresql 9.1. Regards, Granthana On Fri, Jan 17, 2014 at 8:24 PM, Ray Stell <ste...@vt.edu> wrote: > > On Jan 17, 2014, at 5:07 AM, Granthana Biswas <granth...@zedo.com> wrote: > > Yes it's purely for monitoring purpose. > > > I use the pg_controldata cmd locally and via bash/ssh shared keys and > compare various values that seem interesting such as "Time of latest > checkpoint, Latest checkpoint location." My interest is recoverability and > checkpoints seemed relevant at the time. > > I found a comment in the docs: > > http://www.postgresql.org/docs/9.2/static/functions-admin.html > > "pg_xlog_location_diff calculates the difference in bytes between two > transaction log locations. It can be used with pg_stat_replication or > some functions shown in Table > 9-59<http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE>to > get the replication lag." > > and > "The functions shown in Table > 9-60<http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE>provide > information about the current status of the standby. These > functions may be executed both during recovery and in normal running." > These look interesting wrt lag studies and seem to work on the stby: > > template1=# select pg_last_xlog_receive_location(); > pg_last_xlog_receive_location > ------------------------------- > 18/9E000000 > (1 row) > > template1=# select pg_last_xlog_replay_location(); > pg_last_xlog_replay_location > ------------------------------ > 18/9E000000 > (1 row) > > > >
