#!/bin/bash
#
## Daily PostgreSQL maintenance: vacuuming and backuping.
#
###
set -e
for DB in $(psql -l -t -U postgres -hlocalhost |awk '{ print $1}' |grep -vE '^-|:|^List|^Name|template[0|1]|postgres|\|'); do
    echo "[`date`] Maintaining $DB"
    echo 'VACUUM' | psql -U postgres -hlocalhost -d $DB
    DUMP="/home/lxf/bak/postgres/$DB.`date '+%Y%m%d'`.sql.gz"
    pg_dump -U postgres -hlocalhost $DB | gzip -c > $DUMP
    PREV="$DB.`date -d'1 day ago' '+%Y%m%d'`.sql.gz"
    md5sum -b $DUMP > $DUMP.md5
    if [ -f $PREV.md5 ] && diff $PREV.md5 $DUMP.md5; then
        rm $DUMP $DUMP.md5
    fi
done