The below code snipet was created to check the number of rows in a PostgreSQL database, log the output to a file, and warn if the number of rows is over a certain number. There are numerous variables in the code snipet below that will need to be modified for it to work properly including message, notify, dbuser, and logfile. This code was created on CentOS some other items could be different including the location of the date command.
This script can be run as follows: checkrows $host $database
[quickcode]
#!/bin/bash
#
# Name: checkrows
# Usage: checkrows $host $database
# This script was created to check the number of rows in a table and warn if there are more than a certain number
# Warn if two arguements are not provided when issuing the command
if [ “x$2” = “x” ]
then
echo ‘usage: checkemailqueue $host $database_name’
exit -1
fi
# Variables
message=”MESSAGE TO DISPLAY HERE”
date=`/bin/date`
notify=EMAIL_ADDRESS_TO_NOTIFY
logfile=LOGFILE_NAME
dbuser=DB_USERNAME
# Provide the number of lines and cut out the unecessary output to provide only the number of rows
# Will also warn if there is some other problem with the output of psql_output
psql_output=`/usr/bin/psql -h $1 -U $dbuser -d $2 -c “select count(*) from emails”`
if [ $? -ne 0 ]
then
echo $date – There was a problem processing the number of rows for $2 on $HOSTNAME >> $logfile
echo “There was a problem processing the number of rows for $2 on $HOSTNAME” | mail -s “ERROR Running checkemailqueue” $notify
exit -1
else
num_rows=`echo “$psql_output”| head -n 3 | tail -n 1`
fi
if [ $num_rows = “” ]
then
echo $date – There was an error running checkrows
elseif [ $num_rows -gt 10 ]
echo $HOSTNAME has $num_rows email messages in the emails table of the $2 database. | mail -s “$message” $notify
echo $date – WARNING: $HOSTNAME has more than $num_rows emails in queue >> $logfile
else
echo $date – $HOSTNAME has $num_rows emails in queue on $HOSTNAME >> $logfile
fi
[/quickcode]
