Thursday, October 31, 2013

Fetching your Heroku production database schema into local storage

I noticed recently that the structure.sql checked-in to the repository for Staq's main Rails app had fallen slightly out of sync with what was running on our Heroku production database. After a little digging, I came up with the following command. Hopefully this saves you a bit of time - it just mimics what rake db:structure:dump would do for you, but redirects the output to your local storage:

pg_dump -i -s -x -O -f db/structure.sql `heroku config:get DATABASE_URL`

Update 1: the above command does not include the schema_migrations table, so I now also run this:

pg_dump -a -i -t schema_migrations -x -O `heroku config:get DATABASE_URL`

Update 2: I now do it one fell swoop like this:

pg_dump -a -i -t schema_migrations -x -O `heroku config:get DATABASE_URL` && pg_dump -a -i -t schema_migrations -x -O `heroku config:get DATABASE_URL` >> db/structure.sql

No comments: