====== postgresql replication monitor ======

<code bash>
#!/bin/bash
# ---------------
#< By k2patel >
# ---------------- 
#        \   ^__^
#         \  (oo)\_______
#            (__)\       )\/\
#                ||----w |
#                ||     ||
#
#############################
# Requirement Specification
#############################
# - It require `bc`
# - Only monitor streaming replication status


############################
## Define Custom Variables
############################

## Define Nagios utils
LIBEXEC="/usr/lib64/nagios/plugins"
. $LIBEXEC/utils.sh

## Exit Codes
STATE_OK=0
STATE_WARNING=1
STATE_CRITICAL=2
STATE_UNKNOWN=3

## Primary Server Information	
export p_host=127.0.0.1
export p_port=5436

## Secondary Server Information (generally not used unless you have diff. port)
## In case of diff. port please change resules in codes section.
export s_host=localhost
export s_port=5432

## Psql / bc Location
export psql=/usr/pgsql-9.0/bin/psql
export bc=/usr/bin/bc

## Delay Limit
export dlylmt=1000000
export wrnlmt=100000


###########################
## Customization End here
###########################


## Results in codes
prime_num=$($psql -U postgres -t -c "SELECT pg_current_xlog_location()" -h$p_host -p$p_port)
sec_num=$($psql -U postgres -t -c "select pg_last_xlog_receive_location()" -h$s_host -p$s_port)
thr_num=$($psql -U postgres -t -c "select pg_last_xlog_replay_location()" -h$s_host -p$s_port)

pr_num=$(echo $prime_num | awk -F / '{print $2}')
sr_num=$(echo $sec_num | awk -F / '{print $2}')
tr_num=$(echo $thr_num | awk -F / '{print $2}')
#sr_num=7AE0EE48

pr_int_num=$(echo "ibase=16; $pr_num" | $bc)
sr_int_num=$(echo "ibase=16; $sr_num" | $bc)
tr_int_num=$(echo "ibase=16; $tr_num" | $bc)


#echo "Prime $pr_int_num"
#echo "Secon $sr_int_num"
#echo "thrir $tr_int_num"


if [[ $pr_int_num -eq '' || $sr_int_num -eq '' || $tr_int_num -eq '' ]]; then
        echo "Stream Has no Value to compare : CRITICAL"
        exit $STATE_CRITICAL
else
if [[ $pr_int_num -eq $sr_int_num && $pr_int_num -eq $tr_int_num && $sr_int_num -eq $tr_int_num ]] ; then
        echo "Stream: Primary: $pr_int_num Secondary: $sr_int_num Import: $tr_int_num : OK"
        exit $STATE_OK
else
        if [[ $pr_int_num -eq $sr_int_num ]] ; then
                if [[ $pr_int_num -ne $tr_int_num ]] ; then
                if [ $(bc <<< $pr_int_num-$sr_int_num) -lt $wrnlmt ]; then
                echo "Stream: Primary: $pr_int_num Secondary: $sr_int_num Import: $tr_int_num : BEHIND : OK"
                exit $STATE_OK
                else
                echo "Stream: Primary: $pr_int_num Secondary: $sr_int_num Import: $tr_int_num : WARNING"
                exit $STATE_WARNING
                fi
                fi
        else
#               echo $(bc <<< $pr_int_num-$sr_int_num)
                if [ $(bc <<< $pr_int_num-$sr_int_num) -gt $dlylmt ]; then
                echo "Stream: Primary: $pr_int_num Secondary: $sr_int_num Import: $tr_int_num : CRITICAL"
                exit $STATE_CRITICAL
                else
                if [ $(bc <<< $pr_int_num-$sr_int_num) -lt $wrnlmt ]; then
                echo "Stream: Primary: $pr_int_num Secondary: $sr_int_num Import: $tr_int_num : BEHIND : OK"
                exit $STATE_OK
                else
                echo "Stream: Primary: $pr_int_num Secondary: $sr_int_num Import: $tr_int_num : WARNING"
                #echo "pgprod2.mcfina.com;$pr_int_num;$sr_int_num;$tr_int_num;1;WARNING"
                #echo "WARNING - Stream is behind"
                exit $STATE_WARNING
                fi
                fi
        fi
        echo "Stream: Primary: $pr_int_num Secondary: $sr_int_num Import: $tr_int_num : UNKNOWN"
        exit $STATE_UNKNOWN
fi

fi

</code>