====== Administration de PostgreSQL ======

===== Arrêt / Démarrage =====

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

<code bash>
#!/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
</code>



===== Administration =====

====En taches separees====

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

<code bash>
46 6 * * * root /root/adm/postgresql.sh vacuum
00 7 * * * root /root/adm/postgresql.sh backup
</code>

<code bash>
#!/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

</code>

====One shot====

Toujours en //crontab//, ''/etc/crontab'' :

<code bash>
46 6 * * * root /root/adm/postgresql-adm.sh
</code>

<code bash>
#!/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
</code>