Re: pg error log file monitor

>>> Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> wrote:
> I'm looking if some has a script to monitor the PG database error log

> (*.log) ,  if ther are any new messages/errors  written  to this file
to
> get an email or kind of notification.

This is something which was thrown together in a hurry years ago, and
never since touched because it works well enough, so it's hardly a
shining model of best practice; but maybe it will help some.

datestring=$(date '+%F')*
echo "date: $(date +%F_%H%M)" > emailfile
echo "to: dbagroup@wicourts.gov" >> emailfile
echo "subject: Server Vacuum Analyze information " >> emailfile
echo " " >> emailfile
echo "Free space required information " >> emailfile
echo "---------------------------" >> emailfile

grep -Ew2 VACUUM /home/ccsa/vacuum-analyze-bigbird.out >>emailfile
echo "---------------------------" >> emailfile
echo "Space information on Tables " >> emailfile
echo "---------------------------" >> emailfile
grep -E -B3 '^([0-9]{6,12}|[5-9][0-9]{4}) pages contain useful free
space' /home/ccsa/vacuum-analyze-bigbird.out >> emailfile
echo " " >> emailfile
echo "---------------------------" >> emailfile
echo "Information from pg_logs " >> emailfile
echo "---------------------------" >> emailfile

echo Number of Canceling statements due to user request >> emailfile
grep -iE error /var/pgsql/data/cc/pg_log/postgresql-$datestring|grep
-iEc "canceling" >> emailfile
grep -iE error /var/pgsql/data/cc/pg_log/postgresql-$datestring|grep
-iEv "canceling|storage sync"  >> emailfile
echo "---------------------------" >> emailfile
echo "Information from server.log " >> emailfile
echo "---------------------------" >> emailfile
grep -iEb1 "memory|truncate" /opt/ccap/cc/cir/tran/log/server.log|grep
-iEb1 "$datestring" >> emailfile

/usr/sbin/sendmail -t < emailfile

-Kevin