I have a cron job that backs up my PG databases and emails them to me everynight. Today I wanted to upgrade my PostgreSQL 8.3 databases to PG 8.4 so I made a few modifications to my script so that I could dump out my PG data, and then restore it under PG 8.4. In case you are wondering I am doing this because Ubuntu Lucid now ships with PG 8.4 (yay!). I also made the script generate another script to restore the databases. So basically the procedure is to run the script below on your 8.3 cluster, shut that down and bring up your 8.4 cluster, and then restore your databases into that. Here follows my little script:

MYDATE=`date +%d-%B-%Y`
MYBACKUPDIR=/home/timlinux/sql_backups
MYRECIPIENT=tim@linfiniti.com

DBLIST=`psql -l \
  | awk '{print $1}' | grep -v "+" | grep -v "Name" | \
  grep -v "List" | grep -v "(" | grep -v "template" | \
  grep -v "postgres"`
for DB in ${DBLIST}
do
  echo "Backing up $DB"
  FILENAME=${MYBACKUPDIR}/PG_${DB}.${MYDATE}.sql.tar.gz
  pg_dump -f ${FILENAME} -x -O -F tar ${DB}
  #If you want to email the database uncomment
  #below (will cause issues if backups are large)
  #mpack -s "Daily $DB PG backup for ${MYDATE}" $FILENAME $MYRECIPIENT
done

echo "Procedure to restore one of your backups:"
echo "createdb "
echo "pg_restore -F t .sql.tar.gz |psql "
echo "Or to restore in a batch make a script like this:"
echo "for FILE in /home/timlinux/sql_backups/*; do DB=\$(echo $FILE | \"
echo "  sed 's\/home\/timlinux\/sql_backups\/PG_//g' | sed 's/.${MYDATE}.sql.tar.gz//g'); "
echo " 'Restoring: \$DB'; createdb \$DB; pg_restore -F t \$FILE |psql \$DB; done"

Update 02 May 2010 Uncommented pg_dump line which was inadvertantly commented in my original post.