My Notepad

In order to give best performance to your postgresql database, it's crucial to use vaccum and analyze command to clean up your system regularly. On linux system, this can be easily implemented with help of cron.

A typical cron script that runs daily has been listed below. It not only clean the database, but also backup the database into a dump file:

----------
#!/bin/sh

/usr/local/pgsql/bin/vacuumdb --all --analyze

DATESTAMP=`date +%B_%d_%Y__%H:%M:%S`
BACKUPDIR=/usr/local/pgsql/backup
FILENAME=pg_daily_backup__${DATESTAMP}.bz

/usr/local/pgsql/bin/pg_dumpall | /usr/bin/bzip2 > $BACKUPDIR/$FILENAME
---------------

One thing needs to make sure it whether root the command can be ran as root - normally only the user postgres has the privilege to do so.