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