On 6 November 2010 05:46, Josh Berkus <josh@agliodbs.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