Skip to content

Instantly share code, notes, and snippets.

@cavb
Created October 3, 2017 16:54
Show Gist options
  • Save cavb/b52ce7cc83fa3c99daaf34e607d6a57a to your computer and use it in GitHub Desktop.
Save cavb/b52ce7cc83fa3c99daaf34e607d6a57a to your computer and use it in GitHub Desktop.
Backups postgres database
#!/bin/bash
logfile="/var/lib/postgresql/backups/pgsql.log"
backup_dir="/var/lib/postgresql/backups"
touch $logfile
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
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 -i -F c -b $i -h 127.0.0.1 -f $backup_dir/$i-database-$timeslot.backup
echo "Backup and Vacuum complete on $dateinfo for database: $i " >> $logfile
done
echo "Done backup of databases " >> $logfile
tail -15 /backup/pgsql.log | mailx cristian@kausana.cl
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment