diff options
| -rwxr-xr-x | psqlbak.sh | 17 |
1 files changed, 17 insertions, 0 deletions
diff --git a/psqlbak.sh b/psqlbak.sh new file mode 100755 index 0000000..513cfd6 --- /dev/null +++ b/psqlbak.sh @@ -0,0 +1,17 @@ +#!/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 |