Re: pg_last_xact_replay_timestamp lies

On Mon, Jun 15, 2015 at 9:04 AM, Anton Bushmelev <djeday84(at)gmail(dot)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.
--
Michael