This is a handy online command to dump a list of postgress databases into a format that can be script processed:

psql -h localhost -p 25432 -U postgres -q -c “\l” | sed -n 4,/\eof/p | grep -v rows\) | awk {‘print $1’}

Example usage:

#!/bin/bash
LOGFILE=”/path/to/db-backups-`date +%Y%m%d`.log”
BACKUPDIR=”/directory/to/place/backups”
touch $LOGFILE
DSTAMP=`date +%Y%m%d-%H%M%S`
DBLIST=`psql -h localhost -U postgres -q -c “\l” | sed -n 4,/\eof/p | grep -v rows\) | awk {‘print $1’}`

echo “$TSTAMP|Starting Backups ” >> $LOGFILE
for i in $DBLIST; do

TSTAMP=`date ‘+%Y-%m-%d|%T’`
echo “$TSTAMP|Backup database: $i ” >> $LOGFILE
pg_dump $i -h 127.0.0.1 | gzip > “$BACKUP/$DSTAMP-$i.gz”


done

TSTAMP=`date ‘+%Y-%m-%d|%T’`
echo “$TSTAMP|Done! ” >> $LOGFILE

Enjoy!
Advertisements