Différences

Cette page vous affiche les différences entre la révision choisie et la version actuelle de la page.

--- scripts:exemples:postgresql [2007/04/06 13:23]
lois nouvelle version du script, plus complète
+++ scripts:exemples:postgresql [2007/08/08 16:55] (Version actuelle)
lois mise à jour v1, ajout v2
@@ Ligne 1: / Ligne 1: @@
+====== 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>
+6 * * * root /root/adm/postgresql.sh vacuum
+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>
+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>

scripts/exemples/postgresql.txt · Dernière modification: 2007/08/08 16:55 par lois