Skip site navigation (1) Skip section navigation (2)

Re: Measurin the lag between a master and a replica

From: Sergey Konoplev <sergey(dot)konoplev(at)postgresql-consulting(dot)com>
To: Ali Pouya <alipouya2(at)gmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Measurin the lag between a master and a replica
Date: 2012-07-19 15:52:10
Message-ID: CAL_0b1vjHUvT4_5yLaKXfkakRBa_CNrWKTYLe25pWqJodDs=sw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
On Thu, Jul 19, 2012 at 7:17 PM, Ali Pouya <alipouya2(at)gmail(dot)com> wrote:
> 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: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204

In response to

pgsql-admin by date

Next:From: Umer AsgharDate: 2012-07-19 16:19:39
Subject: Postgres Database got down
Previous:From: francescoboccacci@libero.itDate: 2012-07-19 15:38:50
Subject: autovacuum: found orphan temp

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group