[hcoop/scripts.git] / hcoop-all-db-backup

#!/bin/bash

#
# This script backs up every database to its own file, gzipped.
# docelic@hcoop.net, Tue Aug 28 13:38:26 EDT 2007
#

BACKUP_ROOT=/srv/backup/database
BACKUP_DAYS=7

if ! test -d "$BACKUP_ROOT"; then
        echo "Backup root directory '$BACKUP_ROOT' inaccessible."
        exit 1
fi

# change cwd; default will be /root which postgres user can't chdir into
cd /tmp

MYSQL_DBS=`sudo -H mysql -Bse 'show databases' | xargs`
echo "Mysql Databases are: $MYSQL_DBS"
PGSQL_DBS=`sudo -u postgres psql template1 -tAc 'SELECT datname FROM pg_database WHERE datistemplate = false;' | xargs`
echo "Postgres Databases are: $PGSQL_DBS"

# Delete oldest
rm -rf "$BACKUP_ROOT/mysql.$BACKUP_DAYS"
rm -rf "$BACKUP_ROOT/postgres-9.$BACKUP_DAYS"

# Rotate backup dirs
for day in `seq $BACKUP_DAYS -1 1`; do
        to=".$day"
        let day_before=" $day - 1"
        if test "$day" = "1"; then
                from=''
        else
                from=".$day_before"
        fi

        mv -f "$BACKUP_ROOT/mysql$from" "$BACKUP_ROOT/mysql$to" 2>/dev/null
        mv -f "$BACKUP_ROOT/postgres-9$from" "$BACKUP_ROOT/postgres-9$to" 2>/dev/null
done

# backups should not be world-readable, mask all o permissions
umask 0027

# Create current dump dirs
mkdir --mode=770 "$BACKUP_ROOT/mysql" "$BACKUP_ROOT/postgres-9"

# Perform MYSQL backup
for db in $MYSQL_DBS; do
        sudo -H mysqldump --single-transaction "$db" | pigz - > "$BACKUP_ROOT/mysql/$db.gz"
done

# Perform PGSQL backup
for db in $PGSQL_DBS; do
        sudo -H -u postgres pg_dump -Fc "$db" > "$BACKUP_ROOT/postgres-9/$db.pg"
done