Database backup/restore via S3

Backup/Restore script to auto initiate or backup a database.

Usage:

./postgres.sh backup all
./postgres.sh backup somedb
./postgres.sh restore somedb

PostgreSQL:

#!/usr/bin/env bash

set -euo pipefail

cd `dirname $0`
# lock it
PIDFILE="/tmp/$(basename "${BASH_SOURCE[0]%.*}.pid")"
exec 200>${PIDFILE}
flock -n 200 || ( echo "${BASH_SOURCE[0]} script is already running. Aborting . ." && exit 1 )
PID=$$
echo ${PID} 1>&200


TIMESTAMP=$(date '+%Y_%m_%d__%H_%M_%S')

mkdir -p /var/backups/databases
cd /var/backups/databases

function backup(){
    DB=${1}
    FILENAME=${TIMESTAMP}.dump
    ROUTE="${DB}/${ENV}"
    DIR=/var/backups/databases/${ROUTE}
    FILE="${DIR}/${FILENAME}"

    mkdir -p "${DIR}"
    cd "${DIR}"

    echo Backing up ${DB}

    if [ "${DB}" == "all" ]; then
        FILE="${FILE}.bz2"
        pg_dumpall -U postgres -h localhost --no-password | bzip2 > ${FILE}
    else
        pg_dump -U postgres --no-owner --no-acl -h localhost -f ${FILE}  --format=custom --compress=9 --no-password ${DB}
    fi

    echo Uploading ${DB}
    /usr/bin/s3cmd put ${FILE} s3://backups/db/${ROUTE}/${FILENAME}
    rm -f ${FILE}

    echo ${FILENAME} > latest
    /usr/bin/s3cmd put latest "s3://backups/db/${ROUTE}/latest"
    rm -f latest
}

function restore(){
    DB=${1}
    ROUTE="${DB}/${ENV}"
    DIR=/var/backups/databases/${ROUTE}

    mkdir -p "${DIR}"
    cd "${DIR}"

    /usr/bin/s3cmd get --force s3://backups/db/${ROUTE}/latest
    /usr/bin/s3cmd get --force s3://backups/db/${ROUTE}/$(cat latest) - > latest.dump

    echo Restoring ${DB}
    if [ "${DB}" == "all" ]; then
        bzcat latest.dump | psql -U postgres
    else
        pg_restore -v --jobs=4 -U postgres --no-owner -d ${DB} latest.dump
    fi

    rm -f latest
    rm -f latest.dump
}


for i in "${@:2}"
do
    $1 $i
done

MySQL/MariaDB

#!/usr/bin/env bash

set -euo pipefail

cd `dirname $0`
# lock it
PIDFILE="/tmp/$(basename "${BASH_SOURCE[0]%.*}.pid")"
exec 200>${PIDFILE}
flock -n 200 || ( echo "${BASH_SOURCE[0]} script is already running. Aborting . ." && exit 1 )
PID=$$
echo ${PID} 1>&200

TIMESTAMP=$(date '+%Y_%m_%d__%H_%M_%S')

mkdir -p /var/backups/databases
cd /var/backups/databases

function backup(){
    DB=${1}
    FILENAME=${TIMESTAMP}.sql.bz2
    ROUTE="${DB}/${ENV}"
    DIR=/var/backups/databases/${ROUTE}
    FILE="${DIR}/${FILENAME}"

    mkdir -p "${DIR}"
    cd "${DIR}"

    echo Backing up ${DB}

    mysqldump -uroot --skip-lock-tables --single-transaction --add-drop-table ${DB} | bzip2 > ${FILE}

    echo Uploading ${DB}
    /usr/bin/s3cmd put ${FILE} s3://backups/db/${ROUTE}/${FILENAME}
    rm -f ${FILE}

    echo ${FILENAME} > latest
    /usr/bin/s3cmd put latest "s3://backups/db/${ROUTE}/latest"
    rm -f latest
}

function restore(){
    DB=${1}
    ROUTE="${DB}/${ENV}"
    DIR=/var/backups/databases/${ROUTE}

    mkdir -p "${DIR}"
    cd "${DIR}"

    /usr/bin/s3cmd get --force s3://backups/db/${ROUTE}/latest
    /usr/bin/s3cmd get --force s3://backups/db/${ROUTE}/$(cat latest) - > latest.sql.bz2

    bzcat latest.sql.bz2 | mysql -uroot ${DB}

    rm -f latest
    rm -f latest.sql.bz2
}

for i in "${@:2}"
do
    $1 $i
done