UnixServerAdmin

Server Administration & Management

pgsql_backup.sh

##################################################
# pgsql_backup.sh
##################################################
#!/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
##################################################

Advertisements

December 18, 2011 Posted by | PgSQL, Shell Script | , | Leave a comment