PostgreSQLのメンテナンススクリプト

VACUUMを実行するバッチのスクリプト

常態的に日次で実行したいので、「FULL」のオプションはつけていません。
「FULL」のオプションがないので、テーブルのロックなどは発生しないと考えています。
不定期的なサーバメンテナンスのタイミングで、手動で「FULL」のVACUUMを実行することで、日次では回収しないディスク領域の改修など対応します。
同時実行の排他に「lockfile」コマンドを使用しているので、「procmail」パッケージに依存しています。

#!/bin/sh

set -e
set -u

POSTGRESQLUSER="postgres"
POSTGRESQLHOST="localhost"

LOCKFILE="/var/run/postgresql_vacuum.lock"
RETRIES=3
SLEEPTIME=15
# 1 hours (60 * 60 * 1 = 3600sec)
LOCKTIMEOUT=3600

# 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 vacuum - 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

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

for DBNAME in $DATABASES
do
    # Not lock table because it is not in full mode.
    # Analyze with vacuum.
    vacuumdb \
        --host=${POSTGRESQLHOST} \
        --username=${POSTGRESQLUSER} \
        --analyze \
        --quiet \
        $DBNAME
    if [ $? -eq 0 ]; then
        echo "Successful in vacuum the PostgreSQL database: ${DBNAME}"
    else
        # warning
        ERRMSG="Failed in vacuum the PostgreSQL database: ${DBNAME}"
        echo $ERRMSG 1>&2
    fi
done

echo "Vacuum the database on PostgreSQL server was completed."

バックアップを実行するバッチのスクリプト

バックアップは必要になった時の用途にあわせて、ロールやテーブル空間などの情報も含めたデータベースクラスタの全内容のダンプと、個別のデータベースのダンプをとります。
部分的な復元などリストアする際に何かと都合がいいので、ダンプはpg_dumpのカスタムダンプ書式で書き出しています。
ついでに古いバックアップファイルの掃除もしますが、楽で便利だなと思ったので「tmpwatch」コマンドを使ってみました。
こちらも、同時実行の排他に「lockfile」コマンドを使用しているので、「procmail」パッケージに依存しています。

#!/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."