#!/bin/bash # Location of the backup logfile. logfile="/var/log/databases_backup.log" # Location to place backups. backup_dir="/backups/databases" # the (super)user of the database user="postgres" password="your password here" host="host" port="5432" #the file to save the passwords #should be home directory of the user that runs this script password_file="/root/.pgpass" #remove password file and make sure the super user can log in #by adding entries for every systemdatabase rm $password_file echo "$host:$port:postgres:$user:$password" > $password_file echo "$host:$port:template0:$user:$password" >> $password_file echo "$host:$port:template1:$user:$password" >> $password_file chmod 0600 $password_file #get an up to date list of databases #skips template0, template1 and postgres (system databases) databases=`psql -h $host -U $user -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | awk {'if($1 !~ /template0|template1|postgres/)print $1'}` touch $logfile timeslot=`date +%u-%A` #backup every database for i in $databases; do #write an entry in .pgpass file for each database echo "$host:$port:$i:$user:$password" >> $password_file timeinfo=`date '+%T %x'` pg_dump $i -h $host -U $user -F c -b -v -f "$backup_dir/$i-$timeslot.backup" 2> $logfile >/dev/null echo "Backup complete at $timeinfo: $backup_dir/$i-$timeslot.gz" >> $logfile done