Postgres Pro
Downloads
Home   >   mailing lists

Thread: Checking Postgres Streaming replication delay

Checking Postgres Streaming replication delay

From
Patrick B
Date:
Hi guys,

I'm using this query to measure the delay between a Master and a Streaming Replication Slave server, using PostgreSQL 9.2.

SELECT
 pg_last_xlog_receive_location() receive,
 pg_last_xlog_replay_location() replay,
 (
 extract(epoch FROM now()) -
 extract(epoch FROM pg_last_xact_replay_timestamp())
 )::int lag;

In your opinion, is that right?

Cheers
Patrick

Re: Checking Postgres Streaming replication delay

From
Venkata B Nagothi
Date:

On Mon, Oct 31, 2016 at 11:57 AM, Patrick B <patrickbakerbr@gmail.com> wrote:
Hi guys,

I'm using this query to measure the delay between a Master and a Streaming Replication Slave server, using PostgreSQL 9.2.

SELECT
 pg_last_xlog_receive_location() receive,
 pg_last_xlog_replay_location() replay,
 (
 extract(epoch FROM now()) -
 extract(epoch FROM pg_last_xact_replay_timestamp())
 )::int lag;

In your opinion, is that right?
Yes, thats right.

Regards,

Venkata B N
Database Consultant

Re: Checking Postgres Streaming replication delay

From
Patrick B
Date:


2016-10-31 15:54 GMT+13:00 Venkata B Nagothi <nag1010@gmail.com>:

On Mon, Oct 31, 2016 at 11:57 AM, Patrick B <patrickbakerbr@gmail.com> wrote:
Hi guys,

I'm using this query to measure the delay between a Master and a Streaming Replication Slave server, using PostgreSQL 9.2.

SELECT
 pg_last_xlog_receive_location() receive,
 pg_last_xlog_replay_location() replay,
 (
 extract(epoch FROM now()) -
 extract(epoch FROM pg_last_xact_replay_timestamp())
 )::int lag;

In your opinion, is that right?
Yes, thats right.

Regards,

Venkata B N
Database Consultant



Thanks!!!!

Re: Checking Postgres Streaming replication delay

From
Jim Nasby
Date:
On 10/31/16 3:39 PM, Patrick B wrote:
> |(
> ||extract(epoch FROMnow())-
> ||extract(epoch FROMpg_last_xact_replay_timestamp())
> ||)::int lag|

You could certainly simplify it though...

extract(epoch FROM now()-pg_last_xact_replay_timestamp())
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461