Tuesday, June 19, 2007

How to backup PostgreSQL on OS X and *nix

It's wise to backup your database if you've got anything important in there (like the internet's next big thing). You could read through the documentation to see what else is going on or just run the following commands and get the job done.

To backup:
pg_dump -U your_username --clean --no-owner your_db_name > backup.sql

--clean tells Postgres to generate SQL to drop all objects in the database before recreating them. Comes in handy when you want to reinitialize your database and skip the createdb process.
--no-owner tells Postgres to disregard ownership in the dump and give it to the user who restores from that file. Not necessary but I use it when I restore prod data from my host to my local development db.

To restore:
psql -U your_username -d db_to_restore < backup.sql

Sanity check:
psql -U your_username -d your_db_name
=# \d
(should return a list of relations)
=# select * from some_table
(should show some expected data)

No comments: