Postgres Backup Script

From Wiki
Jump to: navigation, search

Place this script in the postgres user's home directory and add to their crontab.

#!/bin/bash
##############################
#Script by Michael Watters
##############################

#Substitute your postgresql root username in the below given line
#PGUSER=postgres
#Substitute your postgresql root password in the below given line
#PGPASSWORD=mypostgre
#export PGUSER PGPASSWORD

logfile="/var/log/pg_backup.log"

# Location to place backups.
backup_dir="/backup/pgsql"
touch $logfile
timeslot=`date +%m-%d`
databases=$(psql -q -c "SELECT datname FROM pg_database" |grep -v "rows" | awk 'NR>2 {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
        /usr/bin/vacuumdb -z $i >/dev/null 2>&1
        /usr/bin/pg_dump $i | gzip > "$backup_dir/postgresql-$i-$timeslot-database.gz"
done