Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#!/bin/bash
# defining pgpassword for interactive logging
export PGPASSWORD=postgres
backup_dir="/mnt/backups/pgbackups/postgisdb/"
mkdir "$backup_dir$(date '+%d-%b-%Y')"
date_dir="$backup_dir$(date '+%d-%b-%Y')"
logfile="$date_dir/pgsql.log"
touch $logfile
# scan through present databases
databases=`psql -h localhost -U postgres -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | grep -v template0 | grep -v template1 | awk {'print $1'}`
echo "Starting backup of databases " >> $logfile
# backup loop for databases
for i in $databases; do
dateinfo=`date '+%Y-%m-%d %H:%M:%S'`
timeslot=`date '+%Y%m%d%H%M'`
/usr/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1
/usr/bin/pg_dump -U postgres -Fc -b $i -h 127.0.0.1 -f $date_dir/$i-database-$timeslot.backup
echo "Backup and Vacuum complete on $dateinfo for database: $i " >> $logfile
done
# print out completion message into the logfile
echo "Done backup of databases " >> $logfile
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment