Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
# defining pgpassword for interactive logging
export PGPASSWORD=postgres
mkdir "$backup_dir$(date '+%d-%b-%Y')"
date_dir="$backup_dir$(date '+%d-%b-%Y')"
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 -f $date_dir/$i-database-$timeslot.backup
echo "Backup and Vacuum complete on $dateinfo for database: $i " >> $logfile
# 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