Simple Postgresql Cron Backup

Here is a very simple method to backup your postgresql DB using a cron job and the pg_dump command. The main problem people encounter when trying to automate the pg_dump command is the password input. Now you could easily create a local user with no password, but why would you want to do that.

The first thing you will want to do is login as the postgresql user that was created during setup, you want to do this to circumvent the interactive password of pg_dump. You will do the rest of the instructions as this user.

su postgres

Now the process is rather straightforward from here. I like to make a bash script, then run that daily with cron. The script I like usually looks something like this:

#!/bin/bash
cd /path/to/backup/
pg_dump dbname > dbname.$(date.%F).dat.sql
tar -cf dbname.$(date.%F).tar dbname.$(date.%F).dat.sql
gzip dbname.$(date.%F).tar
rm -f dbname.$(date.%F).dat.sql

This creates the dump, tar and zips the file then removes the source.
Now launch the crontab editor and have it run your script.

crontab -e

I use the following to run it at 1am everyday:

0 1 * * *      /path/to/script.sh

It doesn’t get much easier than that. Now exit the postgres user and that simple backup job will continue to run daily until you stop it.