Administration de PostgreSQL

Sur une base de /etc/init.d/skeleton, peut être encore largement amélioré :

#!/bin/sh -e
# /etc/init.d/postgresql
# $Id
#
PGDIR="/usr/local/pgsql"
PGBIN="$PGDIR/bin"
PGDATA="/usr/local/pgsql/data"
PGLOG="/var/log/postgresql.log"
DESC="Postgresql database server"
USER="postgres"
 
test -x $PGDIR || exit 0
 
 
case "$1" in
    start)
        echo -n "Starting $DESC: "
        su - $USER -- $PGBIN/pg_ctl -D $PGDATA start
        echo -n ""
        ;;
    stop)
        echo -n "Stopping $DESC: "
        su - $USER -- $PGBIN/pg_ctl -D $PGDATA -m f stop
        echo -n ""
        ;;
    reload)
        echo -n "Reloading configuration $DESC: "
        su - $USER -- $PGBIN/pg_ctl -D $PGDATA reload
        echo -n ""
        ;;
    restart|force-reload)
        echo -n "Restarting $DESC: "
        su - $USER -- $PGBIN/pg_ctl -D $PGDATA -m f stop
        sleep 1
        su - $USER -- $PGBIN/pg_ctl -D $PGDATA start
        echo -n ""
        ;;
    status)
        echo -n "$DESC current status: "
        su - $USER -- $PGBIN/pg_ctl -D $PGDATA status
        echo -n ""
        ;;
    *)
        echo "Usage: $0 {start|stop|restart|reload|force-reload|status}"
        echo -n ""
        exit 1
        ;;
esac
 
exit 0

À faire tourner par cron ou équivalent. Exemple avec /etc/crontab :

46 6 * * * root /root/adm/postgresql.sh vacuum
00 7 * * * root /root/adm/postgresql.sh backup

#!/bin/bash
# manipulations sur le serveur postgresql, via cron
#
# $Id: postgresql-adm-v1.sh 72 2007-08-08 11:18:26Z maintenance $
 
PGDIR="/usr/local/pgsql"
PGBIN="$PGDIR/bin"
PGDATA="/usr/local/pgsql/data"
PGOPTIONS='-c client_min_messages=error'
DESC="Postgresql database server"
USER="postgres"
DATE=$(date +%Y%m%d)
TMPFILE=$(mktemp /tmp/postgresql-adm.XXXXXXXX) || exit 1
HOST=$(hostname)
BACKUPDIR="/opt/postgres-data"
 
test -x $PGDIR || exit 0
 
case "$1" in
        status)
        echo -n "$DESC current status: " >> $TMPFILE 2>&1
        su - $USER -c "$PGBIN/pg_ctl -D $PGDATA status" >> $TMPFILE 2>&1
        echo "" >> $TMPFILE 2>&1
        ;;
        vacuum)
        echo -n "$DESC vacuuming all databases: " >> $TMPFILE 2>&1
        export $PGOPTIONS >> /dev/null 2>&1
        su - $USER -c "$PGBIN/vacuumdb --full --analyze --all" >> $TMPFILE 2>&1
        su - $USER -c "$PGBIN/reindexdb --all --quiet" >> $TMPFILE 2>&1
#        su - $USER -c "$PGBIN/reindexdb --all" >> $TMPFILE 2>&1
        echo -n "" >> $TMPFILE 2>&1
        ;;
        analyze)
        echo -n "$DESC analyzing all databases: " >> $TMPFILE 2>&1
        su - $USER -c "$PGBIN/vacuumdb --analyze --all" >> $TMPFILE 2>&1
        echo -n "" >> $TMPFILE 2>&1
        ;;
        backup)
        echo "$DESC backuping: " >> $TMPFILE 2>&1
        mkdir -p $BACKUPDIR >> $TMPFILE 2>&1
        su - $USER -c "$PGBIN/pg_dumpall > $BACKUPDIR/dumpall.sql" >> $TMPFILE 2>&1
        su - $USER -c "savelog -c 15 -p -j $BACKUPDIR/dumpall.sql" >> $TMPFILE 2>&1
        echo ""
        ;;
        *)
        echo "Usage: $0 {status|vacuum|analyze|backup}"
        echo ""
        exit 1
        ;;
esac
 
if [ -s $TMPFILE ]
        then /bin/cat $TMPFILE | /usr/bin/mailx -s "$HOST : postgresql"  admin@example.com -- -f root@example.com
fi
/bin/rm -f $TMPFILE
 
exit 0

Toujours en crontab, /etc/crontab :

46 6 * * * root /root/adm/postgresql-adm.sh

#!/bin/bash
# manipulations sur le serveur postgresql, via cron
#
# $Id: postgresql-adm-v2.sh 72 2007-08-08 11:18:26Z maintenance $
 
PGDIR="/usr/local/pgsql"
PGBIN="$PGDIR/bin"
PGDATA="/usr/local/pgsql/data"
PGOPTIONS='-c client_min_messages=error'
DESC="db server"
USER="postgres"
DATE=$(date +%Y%m%d)
TMPFILE=$(mktemp /tmp/postgresql-adm.XXXXXXXX) || exit 1
HOST=$(hostname)
BACKUPDIR="/opt/postgres-data"
 
test -x $PGDIR || exit 1
 
export $PGOPTIONS >> /dev/null 2>&1
 
# status
echo "=====================================" >> $TMPFILE 2>&1
echo "$DESC current status: " >> $TMPFILE 2>&1
/bin/su - $USER -c "$PGBIN/pg_ctl -D $PGDATA status" >> $TMPFILE 2>&1
echo "" >> $TMPFILE 2>&1
 
# vacuum
echo "=====================================" >> $TMPFILE 2>&1
echo "$DESC vacuuming all databases: " >> $TMPFILE 2>&1
/bin/su - $USER -c "$PGBIN/vacuumdb --full --analyze --all" >> $TMPFILE 2>&1
echo "" >> $TMPFILE 2>&1
 
# index
echo "=====================================" >> $TMPFILE 2>&1
echo "$DESC reindexing all databases: " >> $TMPFILE 2>&1
/bin/su - $USER -c "$PGBIN/reindexdb --all" >> $TMPFILE 2>&1
echo "" >> $TMPFILE 2>&1
 
# analyze
# FIXME: C'est peut-être de trop avec le vacuum d'au-dessus...
echo "=====================================" >> $TMPFILE 2>&1
echo "$DESC analyzing all databases: " >> $TMPFILE 2>&1
/bin/su - $USER -c "$PGBIN/vacuumdb --analyze --all" >> $TMPFILE 2>&1
echo "" >> $TMPFILE 2>&1
 
# backup
echo "=====================================" >> $TMPFILE 2>&1
echo "$DESC backuping: " >> $TMPFILE 2>&1
/bin/mkdir -p $BACKUPDIR >> $TMPFILE 2>&1
/bin/su - $USER -c "$PGBIN/pg_dumpall > $BACKUPDIR/dumpall.sql" >> $TMPFILE 2>&1
/bin/su - $USER -c "/usr/bin/savelog -c 15 -p -j $BACKUPDIR/dumpall.sql" >> $TMPFILE 2>&1
echo "" >> $TMPFILE 2>&1
 
# mail recapitulatif
if [ -s $TMPFILE ]
        then /bin/cat $TMPFILE | /usr/bin/mailx -a "From: $HOST <root@example.com>" -s "$HOST : postgresql"  admin@example.com --
fi
/bin/rm -f $TMPFILE
 
exit 0