PostgreSQLのVACUUM

postgresql_vacuum.sh

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