infinite possibility

1. write a shell script

#!/bin/bash

#backup.sh

logfile=”/backups/logfile.log”

# Location to place backups.

backup_dir=”/backups”

touch $logfile

timeslot=`date +%y%m%d_%H%M`

databases=(“db1″ “db2″ “db3″)

# get length of an array

tLen=${#databases[@]}

for (( i=0; i<${tLen}; i++ )); do

echo “Vacuum Starting at `date` for database: ${databases[$i]} ” >> $logfile

timeinfo=`date ‘+%T %x’`

psql -c “vacuum verbose analyze” -d ${databases[$i]} -U postgres >> $logfile

echo “Vacuum completed. Backup Starting at `date` for database: ${databases[$i]} ” >> $logfile

pg_dump ${databases[$i]} -h 127.0.0.1  -U postgres | gzip > “$backup_dir/${databases[$i]}-$timeslot-backup.gz”

echo “Backup and Vacuum complete at `date` for database: ${databases[$i]} ” >> $logfile

done

2. Add an entry to crontab to execute the script above
2.1. open terminal and login as postgres: $ su – postgres
2.2. list cron job: $ crontab -l
2.3. edit cron job:
2.3.1. $ crontab -e #in terminal vi editor will open)
2.3.2. i for insert mode
2.3.3. enter entry. for instance, to run it every 1 am:

00 01 * * * path_to_backup.sh > /dev/null

2>&1
2.3.4. ESC key to exist edit mode
2.3.5. :wq to save and quit