Database autobackup script.

autobackup.sh

#!/bin/bash
# Backup all the things, and delete old backups. (Perfect for a nightly cron job.)

# How many old backups should we keep?
NUM_OLD_BACKUPS=5

# Which databases should we worry about?
DATABASE_PREFIX="foo"

## 

backup_cmd="$(dirname $0)/backup.sh"

function handle_database() {
    # Make a new backup
    $backup_cmd "$database"

    # Delete stale backups?
    num_backups="$(ls $database* 2> /dev/null | wc -l)"
    if [ "$num_backups" -le "$NUM_OLD_BACKUPS" ]; then
        return 0
    fi

    num_to_delete="$((num_backups-NUM_OLD_BACKUPS))"
    ls -t -- $databases* | tail -n $num_to_delete | xargs rm
}


psql -lt | grep "^ $DATABASE_PREFIX" | cut -d '|' -f1 | while read database; do
    handle_database "$database"
done

backup.sh

#!/bin/bash
# Save a gzipped backup of a database into a sensibly-named file.

database="$1"
if [ -z "$database" ]; then
    echo "Which database do you want to back up?"
    exit -1
fi;

title="${database}_$(date -I)"

num="$(ls $title*.sql.gz 2> /dev/null | wc -l)"

backup_file="${title}.sql.gz"
if [ "$num" != "0" ]; then
    backup_file="${title}.${num}.sql.gz"
fi

# PostgreSQL:
pg_dump "$database" | gzip > "$backup_file"

# MySQL:
#mysqldump --add-drop-table -p "$database" | gzip > "$backup_file"