Shell Scripts

[Shell Script] – To check replication status in PostgreSQL

#===========Pg_Replication_Status============
#!/bin/bash
export PATH=/edbas96/bin:$PATH
export EDBHOME=/edbas96
export PGDATA=/data
export PGDATABASE=edb
export PGPORT=5432
export PGLOCALEDIR=/edbas96/share/locale

HOSTNAME=$(hostname | /usr/bin/tr '[a-z]' '[A-Z]')

MAILTO="Your_Email_Id@domain.com"
ipadr=$(/sbin/ifconfig  | grep 'inet'| grep -v '127.0.0.1' | awk 'FNR==2{print $0} ' |awk '{ print $2}')
time=`date +%H-%M`
BACKUP_USER=bkp_user
RSTATUS=1

PATH=/edbas96/bin:$PATH; export  PATH

repli_hosts=`psql -X -U $BACKUP_USER -d edb format=unaligned -P t -q -c "select  client_addr  from pg_stat_replication ; " `

arr=($repli_hosts)

pg_strm=$(psql -X -U $BACKUP_USER -d edb  format=unaligned -P t -q -c "select  count(*)  from pg_stat_replication where state !='streaming'  and state !='backup' ; ")

#echo "$repli_hosts $pg_strm "

if [ $pg_strm -ne 0 ]; then

     pg_repl_details=$(psql -U $BACKUP_USER -d edb   -q -c "select    usename,application_name,client_addr, state, pg_xlog_location_diff(sent_location, replay_location) as replay_lag from pg_stat_replication ; ")
    echo " Postgres replication not working properly on $ipadr $HOSTNAME $time $pg_repl_details " |mail  -s "Critical:DBA Team-Postgres Replication on $HOSTNAME incident "  $MAILTO

 fi

########Function to check Replication Delay in Bytes#######

repl_byt_lag()
{
    ip_slv=$1

    pg_byt_lag=$(psql -X -U $BACKUP_USER  -d edb  format=unaligned -P t -q -c "select  pg_xlog_location_diff(sent_location, replay_location) as replay_lag from pg_stat_replication  where client_addr='$ip_slv'; ")
 #echo "$pg_byt_lag"

if [ "$pg_byt_lag" -gt "200000" ];then #Change this value as per your needs
    echo "Replication Lag on  $ip_slv in bytes  $pg_byt_lag from $host"  |mail  -s "Warning:DBA Team-Postgres Replication Delay on $HOSTNAME  "  $MAILTO
fi
}
for ip in "${arr[@]}"
do
 repl_byt_lag $ip
done

Review
4
Comments Rating 0 (0 reviews)

About the author

BestTechReads

Add Comment

Click here to post a comment

Sending

Categories

Categories