[ADMIN] Measurin the lag between a master and a replica

[Ali Pouya]

Hi,
I am using a master and a replica with PostgreSQL 9.1.3.
I would like to measure the replication lag between my servers (in time
units).

On the replica I can use the function pg_last_xact_replay_timestamp().
But on the master I only can use pg_current_xlog_location() which does not
return a timestamp. I do not know any function returning the timestamp.

Is there any way to get the timestamp relative to the result of
pg_current_xlog_location() ?

Thanks for your help
Best regards
Ali Pouya

[Sergey Konoplev]

Post by Ali Pouya
On the replica I can use the function pg_last_xact_replay_timestamp().
But on the master I only can use pg_current_xlog_location() which does not
return a timestamp. I do not know any function returning the timestamp.
Is there any way to get the timestamp relative to the result of
pg_current_xlog_location() ?

According to mine own research of this problem there are no way to get it.

What I would do to monitor the replication lag is

1. execute this on master SELECT txid_current(), that will create a
minimal wal entry
2. compare pg_last_xact_replay_timestamp() with current time on replica.

assuming that clocks are in sync on both servers of course.
--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: ***@gmail.com Skype: gray-hemp Phone: [phone]

[David Kerr]

Post by Ali Pouya
Hi,
I am using a master and a replica with PostgreSQL 9.1.3.
I would like to measure the replication lag between my servers (in time units).
On the replica I can use the function pg_last_xact_replay_timestamp().
But on the master I only can use pg_current_xlog_location() which does not return a timestamp. I do not know any function returning the timestamp.
Is there any way to get the timestamp relative to the result of pg_current_xlog_location() ?
Thanks for your help
Best regards
Ali Pouya

I was looking into this last month and some nice folks on the list pointed me to this formula
for calculating lag. (to be run on the slave) similar to what Sergey said.

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;

[Sergey Konoplev]

Post by David Kerr
I was looking into this last month and some nice folks on the list pointed me to this formula
for calculating lag. (to be run on the slave) similar to what Sergey said.
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;

If you have a connection problem this will not reflect the situation.
--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: ***@gmail.com Skype: gray-hemp Phone: [phone]

[David Kerr]

Post by Sergey Konoplev

Post by David Kerr
I was looking into this last month and some nice folks on the list pointed me to this formula
for calculating lag. (to be run on the slave) similar to what Sergey said.
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;

If you have a connection problem this will not reflect the situation.

Good caveat! monitor the connection status separately as well!