Backup a PostgreSQL Database via E-Mail

Wed, Oct 17 2012

Backups are important and should obvioulsy not be stored on the same server. Sending a dump of my PostgreSQL database per e-mail once a day seemed like an easy and viable solution – as long as it does not get too big. Gmail accounts provide more than enough space for storing small backups which is why I set up an additional account in order to not pollute my main inbox.

This is the small script I wrote to do so:

#!/bin/bash
EMAIL="db_bak_account@gmail.com"
DATABASE="my_database"
DATE=$(date +"%Y-%m-%d")
FILE="/tmp/pg_dump_$DATABASE_$DATE.sql.gz"

pg_dump --inserts $DATABASE | gzip > $FILE
mutt -s "PG Dump $DATABASE $DATE" -a $FILE -- $EMAIL < /dev/null

You might have to install and configure mutt to be able to send e-mails with attachements. This tutorial shows how to do that.

Store the file somewhere, execute chmod +x pg_bak.sh and schedule the script as a cronjob via crontab -e.

# m h  dom mon dow   command
0 4 * * * /path/to/script/pg_bak.sh

Now a backup of your database should be sent to the provided e-mail-address every day.

comments powered by Disqus