Le blog d’Étienne Pouliot , consultant en systèmes informatiques

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 {} \;

Obviously, this script run under Linux.

This script need to run under the postgres user.  The best way to do that is to insert it into crontab :

# su – postgres

$vi backup_all_db.sh

(insert above script)

$chmod 700  backup_all_db.sh

$crontab -e

And add a line like this one :

3 0 * * * /var/lib/pgsql/backup_all_db.sh

Enjoy!