PostgreSQL Backup Scripts

daily.sh

#!/bin/bash

filepath="/data/postgresql/9.1/main-backups"

cd $filepath

for database in `ls $filepath/daily`; do
  filename=`date +"$filepath/daily/$database/$database-daily-%w-%a.dmp" | tr '[:upper:]' '[:lower:]'`

  if [ -e $filename ]; then
    echo "deleting: $filename"
    rm $filename
  fi

  if [ `psql -c 'select datname from pg_database;' -P 'footer' -At | grep "^$database$"` ]; then
    echo "dumping:  $database, into: $filename"
    pg_dump -U postgres -Fc -f $filename $database
  else
    echo "error:    $database does not exist"
  fi
done

if [ -e "$filepath/daily-globals" ]; then
  filename=`date +"$filepath/daily-globals/globals-daily-%w-%a.dmp" | tr '[:upper:]' '[:lower:]'`

  if [ -e $filename ]; then
    echo "deleting: $filename"
    rm $filename
  fi

  echo "dumping:  globals, into: $filename"
  pg_dumpall -U postgres -g -f $filename
fi

hourly.sh

#!/bin/bash

filepath="/data/postgresql/9.1/main-backups"

cd $filepath

for database in `ls $filepath/hourly`; do
  filename=`date +"$filepath/hourly/$database/$database-hourly-%I.dmp"`

  if [ -e $filename ]; then
    echo "deleting: $filename"
    rm $filename
  fi

  if [ `psql -c 'select datname from pg_database;' -P 'footer' -At | grep "^$database$"` ]; then
    echo "dumping:  $database, into: $filename"
    pg_dump -U postgres -Fc -f $filename $database
  else
    echo "error:    $database does not exist"
  fi
done

if [ -e "$filepath/hourly-globals" ]; then
  filename=`date +"$filepath/hourly-globals/globals-hourly-%I.dmp"`

  if [ -e $filename ]; then
    echo "deleting: $filename"
    rm $filename
  fi

  echo "dumping:  globals, into: $filename"
  pg_dumpall -U postgres -g -f $filename
fi