Re: pg_last_xact_replay_timestamp lies

use this 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())::INTEGER
END
AS replication_lag;

found at
http://www.postgresql.org/message-id/CADKbJJWz9M0swPT3oqe8f9+tfD4-F54uE6Xtkh4nERpVsQnjnw@mail.gmail.com


On 06/15/2015 04:24 AM, Michael Paquier wrote:
> On Mon, Jun 15, 2015 at 9:04 AM, Anton Bushmelev <djeday84@gmail.com> wrote:
>> Hello, thank t for response, measure in bytes may bemore correct, but to
>> bring it to the customer? :) I think it is easier to say that the standby
>> database lags behind master no more than 15 minutes, than the fact that it
>> differs for 1 megabyte.
>> ps: sorry for my English
>>
>>
>> On 06/15/2015 02:57 AM, Michael Paquier wrote:
>>> Isn't your mistake the fact that you rely on the assumption that
>>> replication lag measured in terms of timestamp is a good thing while
>>> it should be estimated in terms of byte difference by comparing WAL
>>> positions between the master and its standbys?
> Comparing pg_last_xact_replay_timestamp() with now() to measure
> replication lag makes little sense: this function shows the timestamp
> of the *last transaction replayed* during recovery (see here:
> http://www.postgresql.org/docs/devel/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL
> ). Hence if your master server has no activity for a certain amount of
> time, meaning that no transactions could be replayed on the standby,
> this will continuously increase.