Backing up PostgreSQL databases nightly on FreeBSD

One of the first things I realized I needed when I started using PostgreSQL was a way to automate backups. Originally I created a script that backed up the databases I specified but later I realized I didn't want to have to remember to add new databases to the list. Using Google I found a generic script, after some custom tweaks for FreeBSD I had the script I wanted. 

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

for i in $databases; do
        echo "Backup and Vacuum complete at $timeinfo for time slot $timeslot on database: $i " >> $logfile
        $bin/vacuumdb -z -h localhost -U $username $i >/dev/null 2>&1
        j="60"
        while [ $j -ge 0 ]
        do
                temp0=`expr $j - 1`
                temp1=$j
                j=`expr $j - 1`
                if [ $temp0 -lt 0 ]
                then
                        suffix0=""
                        suffix1=".0"
                else
                        suffix0=".$temp0"
                        suffix1=".$temp1"
                fi
                if [ -f $backup_dir/postgresql-$i-database.gz$suffix0 ]
                then
                        echo "Renaming postgresql-$i-database.gz$suffix0 to postgresql-$i-database.gz$suffix1"
                        mv $backup_dir/postgresql-$i-database.gz$suffix0 $backup_dir/postgresql-$i-database.gz$suffix1
                fi
        done
        if [ $i != 'template0' ]
        then
                $bin/pg_dump -U $username $i -h 127.0.0.1 | gzip > "$backup_dir/postgresql-$i-database.gz"
        fi
done

This script creates 60 backups and when used in conjunction with a cron job you can make that weekly, daily, hourly, etc. I use this to create a daily backup. Now for some people 60 is probably more than they want to store. But that can be changed by changing the start value of the variable j. To make this work for your postgresql database you will most likely want to change the backup_dir and logfile variables. Once completed add a cron job and your done.

0 0 * * * /root/dbBackup.sh >> /dev/null 2>&1

This cron job will run daily at 12:00am and automatically backup all databases with the exception of the template0 database as I have yet to find a way to back this schema up.