SaltyCrane Blog — Notes on JavaScript and web development

Postgres backup with cron

So I accidentally deleted my Postgres database on my public server instead of my local server last night. Luckily, I was still in the testing phase. I had been meaning to figure out how to backup Postgres but had been putting it off. Now I've been provided the proper motivation.

The PostgreSQL documentation has a whole chapter devoted to backup. There it describes three methods of backup: SQL dump, file system level backup, and continuous archiving. I chose SQL dump.

Manual SQL dump

To do a manual dump, here is what I did. (As you might guess, handsoncards_db is my database name.)

su postgres
pg_dump handsoncards_db > /tmp/testdump

Schedule backups with cron

To perform backups at regular intervals I used cron. Logged in as root, I created a file handsoncards_db_backup in /etc/cron.d. (Note, I'm running Ubuntu Intrepid. Cron will automatically start running this new job without a restart.)

# m  h  dom mon dow user     command
  45 3  *   *   *   postgres pg_dump handsoncards_db > /srv/backups/handsoncards_db_backup

This will create a backup at 3:45am every day. Be sure to put a newline at the end of the file.

Finally I created the backup directory and made postgres the owner.

mkdir -p /srv/backups
chown postgres:postgres /srv/backups

Restore a database from the dump file

If necessary, delete the old database. Then create a new databse and restore from the dump file.

su postgres
psql template1
CREATE DATABASE handsoncards_db OWNER django_user ENCODING 'UTF8';
psql handsoncards_db < /srv/backups/handsoncards_db_backup

NOTE: if you get a permission denied error when trying to restore, check the Unix permissions on the backup file and all the parent directories.


#1 Ian commented on :

Just what I was looking for. Thanks!

#2 gf commented on :

better use pg_dumpall instead of pg_dump