Re: How can we tell how far behind the standby is?

From: Brendan Jurd <direvus(at)gmail(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: How can we tell how far behind the standby is?
Date: 2010-11-06 13:45:33
Message-ID: AANLkTi=oVKuZb7kDr-xrqPEt3oumo8d3AiiztHyeiBj3@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 6 November 2010 05:46, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> I'm continuing in my efforts now to document how to deploy and manage
> replication on our wiki.  One of the things a DBA needs to do is to use
> pg_current_xlog_location() (and related functions) to check how far
> behind the master the standby is.
>
> However, there's some serious problems with that:
>
> (1) comparing these numbers is quite mathematically complex -- and, for
> that matter, undocumented.
>

Our solution to this was to strip the slash out of the numbers and
then feed them to `bc` for comparison. The shell script for our
zabbix item looks something like this:

----
#!/bin/bash
errval=-1
primary=$(psql -At -h $1 -p $2 -c "SELECT
replace(pg_current_xlog_location(), '/', '');" postgres)
standby=$(psql -At -h $3 -p $4 -c "SELECT
replace(pg_last_xlog_receive_location(), '/', '');" postgres)

if [ -n "$primary" -a -n "$standby" ]
then
echo $(echo "ibase=16; obase=10; $primary-$standby" | bc)
else
echo $errval
fi

----
I'm posting this snippet a) in the hopes that it might help others,
and b) by way of agreement with Josh's point. Requiring every user
who wants to monitor replication to set something like this up for
themselves is ... not awesome.

Cheers,
BJ

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Korotkov 2010-11-06 13:51:27 Re: Fix for seg picksplit function
Previous Message Peter Eisentraut 2010-11-06 11:35:30 Re: improved parallel make support