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
/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.
Just what I was looking for. Thanks!
better use pg_dumpall instead of pg_dump