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?

Current references: postgresql.org/docs/9.3/static/backup.html | postgresql.org/docs/9.3/static/app-pgdump.html — odigity
– odigity, Commented Jan 29, 2015 at 20:43

Veraticus · Accepted Answer · 2013-09-10 16:07:07Z

75

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.

answered Sep 10, 2013 at 16:07

Veraticus

16.1k3 gold badges43 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

croceldon Over a year ago

The problem here is that the pg_restore restores the database register_production, and I want the data restored to register_development on my local machine.

user3304086 Over a year ago

Hi! I have a problem with this command. I receive the following error: pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation schema_migrations . What could be a problem? Thanks!

Al D Over a year ago

Sorry for hijacking. I've been trying to restore the database as per your command above. It looks like it's working as in I see a load of commands. But when I go in and have a look, none of the data has copied in. Any ideas?

TomDavies Over a year ago

I had the same issue as AI D, the pg_restore just dumps the the contents to STDOUT. I fixed this by adding the -d <database_name> to the pg_restore command.

Jai Kumar Rajput Over a year ago

I'm unable to find this psql file in my tmp folder. where it will generate ?

|

Bob Gilmore · Accepted Answer · 2013-09-10 16:05:24Z

1

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.

answered Sep 10, 2013 at 16:05

Bob Gilmore

14k13 gold badges49 silver badges55 bronze badges

Collectives™ on Stack Overflow

How to backup/restore Rails db with Postgres?

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related