Postgres database dump, compress and upload to s3.

pgBackup.sh

#!/usr/bin/env bash
 
##
# @author Jay Taylor [@jtaylor]
#
# @date 2013-10-02
#
# Postgres database dump, compress and upload to s3.
#
# Make sure this script runs from a directory with enough space to hold and compress the db dump.
#
# ==Installation==
#
# Install to root user's cron like so:
#
# 0 0 * * * /data/pg_log/pgBackup.sh | /usr/bin/logger -t pgBackup
#

# NB: There must be a unique database name which begins with $prefix.
dbNamePrefix='db'


test $EUID -ne 0 && echo "error: this script must be run as root" 1>&2 && exit 1

cd "$(dirname "$0")"


# Automatically find the database name.
dbName=$(sudo -upostgres psql --list | tail --lines=+4 | sed 's/^ //' | grep "^${dbNamePrefix}" | cut -d' ' -f1)
rc=$?
test $rc -ne 0 && echo "error: failed to find the database name, attempt resulted in exit status ${rc}" 1>&2 && exit $rc
test -z "${dbName}" && echo "error: failed to find the database name, attempt resulted in an empty dbName" 1>&2 && exit 1

fileName="${dbName}-$(date +%Y%m%d_%H%I%S).sql"

echo "$(date) dump started"
sudo -upostgres pg_dump --blobs --create --schema=public "${dbName}" > "${fileName}"
rc=$?
test $rc -ne 0 && echo "error: dumping database ${dbName} failed with exit status ${rc}" && exit $rc

echo "$(date) dump finished, compression starting"
nice --adjustment=19 xz --compress --keep --force "${fileName}"
rc=$?
test $rc -ne 0 && echo "error: compressing ${fileName} failed with exit status ${rc}" && exit $rc

echo "$(date) compression finished, upload starting"
s3cmd --config=/etc/s3cmd put "${fileName}.xz" "s3://postgres-backups/${dbName}/${fileName}.xz"
rc=$?
test $rc -ne 0 && echo "error: S3 upload of ${fileName} failed with exit status ${rc}" && exit $rc

echo "$(date) upload finished"
rm -f "${fileName}" "${fileName}.xz"