A simple yet effective PostgreSQL backup script.

6 janvier 2010 — Étienne Pouliot

Many times I have seen administrator creating a backup script to dump their databases that do not address the follow issues :

You don’t want to manually specify which database to backup so that every time a new database is created, it will be automatically backed up. Very common mistake.
You want to save multiple copies of your database, still you don’t want it to fill your hard disk.

Here is what I use to address that :

PostgreSQL backup script    
#!/bin/bash
# Location to place backups.
backup_dir="/home/postgres-backup/"
#String to append to the name of the backup files
backup_date=`date +%d-%m-%Y`
#Numbers of days you want to keep copie of your databases
number_of_days=30
databases=`psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'`
for i in $databases; do
  if [ "$i" != "template0" ] && [ "$i" != "template1" ]; then
    echo Dumping $i to $backup_dir$i\_$backup_date
    pg_dump -Fc $i > $backup_dir$i\_$backup_date
  fi
done
find $backup_dir -type f -prune -mtime +$number_of_days -exec rm -f {} \;