Backup & Restore PostgreSQL Database

In a lot of cases I prefer PostgreSQL as database management system. Here I will show how to backup a PostgreSQL database and delete old backups automatically with cron. There is also an restore script which can be used if necessary.

Create backup.sh and restore.sh and modify the required properties.

backup.sh:

#!/bin/sh
DATABASE=database
PGUSER=user
PGPASSWORD=password
BACKUPDIR=/backup/data
BACKUPTIME="7"
# author: Oemer Guersoy <www.guersoy.net>
# backup
echo "Backup database $DATABASE."
bufilename=$BACKUPDIR/backup_`date '+%Y%m%d'`.gz
pg_dump --file=$bufilename --format=t --username=$PGUSER $DATABASE
echo "Done."
#delete
find_files=`find $BACKUPDIR -maxdepth 1 -mtime +$BACKUPTIME`
echo "Looking for backups older then $BACKUPTIME days."
echo "----"
if [ -z "$find_files" ]; then
echo "None Found!"
else
echo "$find_files"
fi
echo "----"
find $BACKUPDIR -mtime +$BACKUPTIME -delete
echo "Done."

restore.sh:

#!/bin/sh
DATABASE=database
PGUSER=user
PGPASSWORD=password
# author: Oemer Guersoy <www.guersoy.net>
if [ $# -ne 1 ] || [ ! -f $1 ]
 then
 echo "Usage: $0 <backupfile>"
 exit 1
fi
bufilename=$1
echo "Dropping database $DATABASE"
dropdb -U $PGUSER $DATABASE
echo "Creating database $DATABASE"
createdb -U $PGUSER $DATABASE
echo "Restoring database $DATABASE from $bufilename"
pg_restore --verbose --username=$PGUSER --dbname=$DATABASE --format=t $bufilename
echo "Done!"

Make the scripts executable.

chmod +x backup.sh restore.sh

Edit your crontab.

crontab -e

Add the following command to backup your database every midnight.

00 00 * * * /backup/backup.sh

A secure place to store youre backups is an NFS file system which is running in mirroring RAID mode.