Обсуждение: Monitoring Replication on Master/Slave Postgres(9.1)

On Mon, Nov 26, 2012 at 5:05 PM, Shams Khan <shams.khan22@gmail.com> wrote:

> I want to know the ways to monitor the replication, whether the master and
> slave server are sync.

On the slave, run "SELECT now() - pg_last_xact_replay_timestamp() AS
time_lag;". That tells you how far behind in time the slave is.

> My question is how do we read these numbers(17A/342A6F78 and 17A/34366C30) I
> never got the same results on both servers. Is that mean slave is not synced
> with master?

With asynchronous replication, the slave will always lag behind the
master. The query I posted above will tell you by how much.

--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/

On 11/28/2012 10:21 AM, Shams Khan wrote:
> ...how do we ensure my replication is working fine?...
>

Below is the core of one of my bash-script tools. It could use some
tweaking (comments welcome) but works well. The script is run every
minute by cron on master and standby servers. It auto-determines whether
the server is currently a master or standby so the same script can be
deployed to all servers.

If a master-server, it updates a one-record test table with a current
timestamp to ensure there is activity on the master.

If a standby-server, it determines the lag based both on the age of
pg_last_xact_replay_timestamp() and on the age of the record in the test
table then returns the worst of the two.

The delay value is set in $standby_delay which is a value in seconds.
It's up to you to decide what constitutes an issue that requires
attention (but remember that 60-seconds does not necessarily indicate a
problem on an idle server). My first-level alert triggers at 130-seconds
and I have never hit that much of a delay.

#!/bin/bash
#
# Check PostgreSQL sync-status
#
# Requires table "sync_status" with column "sync_time" of type timestamp
with time zone


# We need a temp file
tempquery="$(mktemp /tmp/monitor_db_synchronizationXXXXXXXXXX)"

# If master, update sync_status timestamp and return 0. If standby,
check both age
# of log-replay location and of timestamp in sync_status table and set
$standby_delay
# to the greater of the two (in seconds)
#
standby_delay=$(
psql -q --tuples-only --no-align 2>/dev/null <<EOS
\o ${tempquery}
select
case when setting='on' then
'
with logdelay as
     (
     select
     case when
pg_last_xlog_receive_location()=pg_last_xlog_replay_location() then 0::int
     else
         (extract(epoch from now())-extract(epoch from
pg_last_xact_replay_timestamp()))::int
     end as replicadelay
     union
     select
         (extract(epoch from now())-extract(epoch from sync_time))::int
as replicadelay
     from
         sync_status
     )
select
     max(replicadelay)
from
     logdelay
;
'
else
'
begin;
delete from sync_status;
insert into sync_status (sync_time) values (now()) returning 0::int as
replicadelay;
commit;
'
end
from pg_settings where name='transaction_read_only';
\o
\i ${tempquery}
EOS
)

# Cleanup temp file
test -f "${tempquery}" && rm "${tempquery}"

# Do some alert based on the number of seconds of lag between master and
standby here

Cheers,
Steve

The Bucardo check_postgres module contains a hot_standby_delay check function which will calculate the delta between
thexlog position of the master with the slave(s). 

http://bucardo.org/check_postgres/check_postgres.pl.html#hot_standby_delay

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Steve Crawford
Sent: Wednesday, November 28, 2012 1:02 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Fwd: Monitoring Replication on Master/Slave Postgres(9.1)

On 11/28/2012 10:21 AM, Shams Khan wrote:
> ...how do we ensure my replication is working fine?...
>

Below is the core of one of my bash-script tools. It could use some tweaking (comments welcome) but works well. The
scriptis run every minute by cron on master and standby servers. It auto-determines whether the server is currently a
masteror standby so the same script can be deployed to all servers. 

If a master-server, it updates a one-record test table with a current timestamp to ensure there is activity on the
master.

If a standby-server, it determines the lag based both on the age of
pg_last_xact_replay_timestamp() and on the age of the record in the test table then returns the worst of the two.

The delay value is set in $standby_delay which is a value in seconds.
It's up to you to decide what constitutes an issue that requires attention (but remember that 60-seconds does not
necessarilyindicate a problem on an idle server). My first-level alert triggers at 130-seconds and I have never hit
thatmuch of a delay. 

#!/bin/bash
#
# Check PostgreSQL sync-status
#
# Requires table "sync_status" with column "sync_time" of type timestamp with time zone


# We need a temp file
tempquery="$(mktemp /tmp/monitor_db_synchronizationXXXXXXXXXX)"

# If master, update sync_status timestamp and return 0. If standby,
check both age
# of log-replay location and of timestamp in sync_status table and set
$standby_delay
# to the greater of the two (in seconds)
#
standby_delay=$(
psql -q --tuples-only --no-align 2>/dev/null <<EOS
\o ${tempquery}
select
case when setting='on' then
'
with logdelay as
     (
     select
     case when
pg_last_xlog_receive_location()=pg_last_xlog_replay_location() then 0::int
     else
         (extract(epoch from now())-extract(epoch from
pg_last_xact_replay_timestamp()))::int
     end as replicadelay
     union
     select
         (extract(epoch from now())-extract(epoch from sync_time))::int
as replicadelay
     from
         sync_status
     )
select
     max(replicadelay)
from
     logdelay
;
'
else
'
begin;
delete from sync_status;
insert into sync_status (sync_time) values (now()) returning 0::int as
replicadelay;
commit;
'
end
from pg_settings where name='transaction_read_only';
\o
\i ${tempquery}
EOS
)

# Cleanup temp file
test -f "${tempquery}" && rm "${tempquery}"

# Do some alert based on the number of seconds of lag between master and
standby here

Cheers,
Steve



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On 11/28/2012 11:24 AM, Shams Khan wrote:
> Thanks for the response Steve...It was really helpful:
>
> Below are some doubts I wanted to clarify..please read and suggest.
> Can we also check if replication was broken earlier...somehow due to
> power failure of some other reasons in past...The reason I am asking
> is I am getting some discrepancies in data between master and
> slave...? I want to know the reason of it...
>
What sort of discrepancies? I am unaware of situations in a properly
configured and functioning system where the standby could be anything
other than lagging the master but I didn't dig through release notes to
see if there were any related bugs.

Cheers,
Steve