How to backup/restore Rails db with Postgres?

Question

I do the following on my server:

 pg_dump -O -c register_production > register.sql

Then, after copying register.sql to my local environment, I try:

 psql register_development < register.sql

This appears to work, but when I try to launch the Rails site locally, I get this:

 PG::UndefinedTable: ERROR:  relation "list_items" does not exist at character 28

How can I restore everything (including relations) from the server db to my local dev db?

Answer 1

I use this command to save my database:

pg_dump -F c -v -U postgres -h localhost <database_name> -f /tmp/<filename>.psql

And this to restore it:

pg_restore -c -C -F c -v -U postgres /tmp/<filename>.psql

This dumps the database in Postgres' custom format (-F c) which is compressed by default and allows for reordering of its contents. -C -c will drop the database if it exists already and then recreate it, helpful in your case. And -v specifies verbose so you can see exactly what's happening when this goes on.

Answer 2

Does the register_development database exist before you run the psql command? Because that form will not create it for you.

See http://www.postgresql.org/docs/8.1/static/backup.html#BACKUP-DUMP-RESTORE for more information.