Hello, I have written a backup script that will vacuum, analyze and backup every 
postgresql database. 

(1) Modify logfile and backup_dir variables to suite your needs.
(2) I have a trust relationship so I am never prompted for a password on connection.
(3) Add an entry to crontab to perform the backups nightly or whenever you wish.
(4) Have fun.

# Crontab starts here.
00 01 * * * /path/to/script/backup > /dev/null 2>&1
#--------------------------------------------------

# Backup script starts here.

#!/bin/bash
# Location of the backup logfile.
logfile="/path/to/logfile.log"
# Location to place backups.
backup_dir="/directory/to/place/backups"
touch $logfile
timeslot=`date +%H-%M`
databases=`psql -h localhost -U postgres -q -c "\l" | sed -n 4,/\eof/p | grep -v 
rows\) | awk {'print $1'}`

for i in $databases; do
        timeinfo=`date '+%T %x'`
        echo "Backup and Vacuum complete at $timeinfo for time slot $timeslot on 
database: $i " >> $logfile
        /server/pgsql/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1
        /server/pgsql/bin/pg_dump $i -h 127.0.0.1 | gzip > 
"$backup_dir/postgresql-$i-$timeslot-database.gz"
done
#-------------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Reply via email to