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@xxxxxxxxxxxxxx
On 06/15/2015 04:24 AM, Michael Paquier wrote:
On Mon, Jun 15, 2015 at 9:04 AM, Anton Bushmelev <djeday84@xxxxxxxxx> 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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
