Thursday, October 31, 2013

Fetching your Heroku production database schema into local storage

Updated Aug 23rd, 2014: the old commands I posted here did not work with Postgres 9.3's pg_dump. New commands listed below, including schema migrations dump.

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 connected to your production database. Will also update the list of schema migrations:

pg_dump -v -i -s -x -O -f db/structure.sql --exclude-table=****** --exclude-table=***** -d DATABASE_NAME -U USER_NAME -h HOSTNAME && pg_dump -v -a -t schema_migrations -x -O -d DATABASE_NAME -U USER_NAME -h HOST_NAME >> db/structure.sql