PostgreSQLのバックアップ

postgresql-backup.sh

#!/bin/sh

set -e
set -u

POSTGRESQLUSER="postgres"
POSTGRESQLHOST="localhost"

BACKUPDIR="/srv/postgresql-backup-datastore"
# 25 days (24 * 25 = 600 hours)
SAVED=600

LOCKFILE="/var/run/postgresql_backup.lock"
RETRIES=3
SLEEPTIME=15
# 2 hours (60 * 60 * 2 = 7200sec)
LOCKTIMEOUT=7200

# Require lockfile command in procmail package.
lockfile -${SLEEPTIME} -r $RETRIES -l $LOCKTIMEOUT $LOCKFILE >/dev/null 2>&1
if [ $? -ne 0 ] ; then
    # lock failed.
    ERRMSG="PostgreSQL backup - Still running."
    echo $ERRMSG
    exit 1
fi

SELFID=`id | sed -e 's/uid=//' -e 's/(.*//'`
if [ $SELFID -ne 0 ]; then
    # failed
    ERRMSG="You are not root, You cannot execute this script."
    echo $ERRMSG 1>&2
    exit 100
fi

TMPDIR=/tmp/postgresql-backup.$$
mkdir -p $TMPDIR

trap "exit 1" HUP INT PIPE QUIT TERM
trap "rm -f ${TMPDIR}/*.dump; rmdir ${TMPDIR}; rm -f ${LOCKFILE}" EXIT

DATABASES=`
    psql \
        --host=${POSTGRESQLHOST} \
        --user=${POSTGRESQLUSER} \
        --list \
        --tuples-only \
        --pset="format=unaligned" \
        --pset="fieldsep=," \
    | sed 's!^postgres=CTc/postgres$!!g' \
    | cut --delimiter=',' --fields=1 \
    | sed 's/^template[01]$//g' \
    | sed '/^$/d'
`
if [ $? -ne 0 ]; then
    # failed
    ERRMSG="Failed to listup the databases on the PostgreSQL server."
    echo $ERRMSG 1>&2
    exit 200
fi

TODAY=`date +%Y%m%d`

BACKUP=${TMPDIR}/all-databases.${TODAY}.dump
pg_dumpall \
    --host=${POSTGRESQLHOST} \
    --username=${POSTGRESQLUSER} \
    > $BACKUP
if [ $? -eq 0 ]; then
    echo "Successful in the preparation of the all databases backup."
else
    ERRMSG="Failed in the preparation of the all databases backup."
    echo $ERRMSG 1>&2
    exit 201
fi

for DBNAME in $DATABASES
do
    BACKUP=${TMPDIR}/${DBNAME}.${TODAY}.dump
    pg_dump \
        --host=${POSTGRESQLHOST} \
        --username=${POSTGRESQLUSER} \
        --format=custom \
        $DBNAME > $BACKUP
    if [ $? -eq 0 ]; then
        echo "Successful in the preparation of the database backup: ${DBNAME}"
    else
        # warning
        $ERRMSG "Failed in the preparation of the database backup: ${DBNAME}"
        echo $ERRMSG 1>&2
    fi
done

if [ ! -d $BACKUPDIR ]; then
    # failed
    ERRMSG="Datastore of backup does not exist: ${BACKUPDIR}"
    echo $ERRMSG 1>&2
    exit 202
fi

/usr/sbin/tmpwatch $SAVED $BACKUPDIR
mv ${TMPDIR}/*.dump ${BACKUPDIR}/

echo "Backup of the database on PostgreSQL server was completed."