SaltyCrane Blog — Notes on JavaScript and web development

Postgres backup with cron

Date:  |  Modified:  |  Tags: cardstore, linux, sql, ubuntu  |  5 Comments

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
exit

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';
\q
psql handsoncards_db < /srv/backups/handsoncards_db_backup
exit

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.

Comments


#1 Ian commented on :

Just what I was looking for. Thanks!


#2 gf commented on :

better use pg_dumpall instead of pg_dump


#3 John Nilson commented on :

It is the well-known fact, that to protect your database it is necessary to make scheduled backups! As for me the easiest way to make scheduled Postgresql database backups is to use Postgresql-Backup tool (<http://postgresql- backup.com/)>

disqus:2754942439


#4 Ranjan Kumar Gochhayat commented on :

Hi

I am following this
---------In crontab

# m h dom mon dow user command  
45 3 * * * postgres pg_dump psdb > /test/db_backup/"testsql".sql

Then...........

mkdir -p /test/db_backup  
chown postgres:postgres /test/db_backup

it is generating testsql.sql file with zero bytes.

Please guide me

disqus:3130097647


#5 Budi commented on :

yes this is occured to me too

disqus:3719937011