One-liner to list all databases to backup

Just a one-liner to get a list of all the databases to backup – which should work for postgres 9.

psql -l | tail -n+4 | tail -r | tail -n+3 | awk '{ print $1 }'| grep -v "test$\|dev$\|^template\|^postgres$\|^|"

You could use this in a backup script, like so:

set -e

BKPDIR="/var/backups/pgsql"
mkdir -p $BKPDIR
DAY=$(date +%d)
DAY_OF_WEEK=$(date +%u)
MONTH=$(date +%m)
FULL_MONTH=$(date +%B | tr 'A-Z' 'a-z')
YEAR=$(date +%Y)
for LINE in `psql -l | tail -n+4 | tail -r | tail -n+3 | tail -r | awk '{ print $1 }'| grep -v "test$\|dev$\|^template\|^postgres$\|^|"`; do
  DAILY_OUT="$BKPDIR/$LINE-$YEAR$DAY$MONTH-daily.sql"
  pg_dump $LINE > $DAILY_OUT
  if [[ $DAY_OF_WEEK == "6" ]] ; then
    WEEKLY_OUT="$BKPDIR/$LINE-$YEAR$DAY$MONTH-weekly.sql"
    cp $DAILY_OUT $WEEKLY_OUT
    MONTHLY_OUT="$BKPDIR/$LINE-$FULL_MONTH-monthly.sql"
    if [[ ! -f $MONTHLY_OUT ]]; then
      cp $DAILY_OUT $MONTHLY_OUT
    fi
  fi
  find $BKPDIR -type f -mtime +7 | grep "daily.sql$" | xargs rm
  find $BKPDIR -type f -mtime +28 | grep "weekly.sql$" | xargs rm
  find $BKPDIR -type f -mtime +168 | grep "monthly.sql$" | xargs rm
done

Update

My good friend ramirez pointed out that on Linux (not BSD), you can get away without having to use the tail -r and use head instead.

psql -l | tail -n+4 | head -n-3 | awk '{ print $1 }'| grep -v "test$\|dev$\|^template\|^postgres$\|^|"

Another friend of mine Steve Woodcock pointed out alternatives of formatting the output

psql --no-align --tuples-only -c select datname from pg_database where not datistemplate

psql -l | awk '/^ \w+/ {print $1}'