Postgres Content Check

Randomly, I find my content mysteriously changing, and I'm wondering when, why and how the content changed. Seems to happen on my photoblog.

This morning I wrote a quick little script to check the content of my currently running database vs the previous backup. I know this wouldn't scale very well, but given my postgres database only has a couple hundred MB of content, it does the trick and should let me know when my content changes. Publishing this post will be my first live test of the script. At some point, I can add additional logic to print the difference, or maybe convert to an NRPE nagios check. But hey, it's early on Saturday morning and I have a lot of chores to get done.

Maybe I'm alone in this problem and this certainly isn't the most elegant solution, but I figured I'd share it anyway. Obviously this would not make sense if I allowed commenting via the mezzanine CMS, or any other interactive site for that matter, but my content is static. I use the external service Disqus for commenting so that doesn't change anything in my database.

#!/bin/bash
# compare md5 hash of previous backup with current

PG_DUMPALL=/usr/bin/pg_dumpall
GZIP=/bin/gzip
BACKUPDIR="/backups/postgres/"
LASTBACKUPFILE=`ls /backups/postgres/ | tail -n 1`
LOCKFILE="/var/lib/pgsql/9.3/pg_check.lock"
MD5SUM=/usr/bin/md5sum
ZCAT=/bin/zcat
AWK=/bin/awk

test -f $LOCKFILE
if [ $? -eq 0 ] ; then
    echo "ERROR lockfile exists"
    exit 1;
fi
 
# create lockfile
/bin/touch $LOCKFILE
if [ $? -ne 0 ] ; then
    echo "ERROR creating lockfile"
    exit 1;
fi

# get current pg_dump md5 hash
PGMD5HASH=`$PG_DUMPALL | $MD5SUM | $AWK '{print $1}'`
echo $PGMD5HASH

# get previous backup md5
PREVIOUSMD5HASH=`$ZCAT $BACKUPDIR$LASTBACKUPFILE |$MD5SUM | $AWK '{print $1}'`
#PREVIOUSMD5HASH="FOO"
echo $PREVIOUSMD5HASH

if [ $PGMD5HASH != $PREVIOUSMD5HASH ] ; then
  echo "Postgres content hashes do not match, content changed!" | /bin/mail -s "POSTGRES CONTENT CHANGED" 617XXXXXXX@messaging.sprintpcs.com;
fi

# remove lockfile
/bin/rm -f $LOCKFILE
if [ $? -ne 0 ] ; then
    echo "ERROR removing lockfile"
    exit 1;
fi
Currently unrated

(Comments)

Comments