Re: Correct query to check streaming replication lag

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 <stellr(at)vt(dot)edu> wrote:

>
> On Jan 17, 2014, at 5:07 AM, Granthana Biswas <granthana(at)zedo(dot)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)
>
>
>
>