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

My twelve-factor app development environment (Unicorn and Pound)

I've been hugely influenced by the twelve-factor app manifesto written by Heroku. I really like building apps on Heroku and think it's pretty great for prototyping things, because it totally abstracts the need to think about devops in the early stages of a project. I don't think Heroku is a panacea (I don't have experience running something large on it), but it's great for getting something going quickly.

That manifesto illustrates how you end up building your app if you launch it on Heroku. I've found the  principles greatly increase my productivity no matter what platforms I'm using. One thing that did take me a little while to figure out: most of my apps are SSL-only, and I always want to interact with them over SSL in my development environment because odd things can creep up in production if you never test SSL locally. (Most of the advice that Rails blogs give always point you towards shutting off SSL in development mode which seems crazy to me)

At first I couldn't quite figure out how to make SSL work while complying with factor 7, Port Binding. I was still using Phusion Passenger with SSL configured as I described in this very old article (which still gets a lot of traffic).

Now here's what I do. I run the Pound reverse proxy using this configuration:

# http://lvh.me
ListenHTTP
  Address 127.0.0.1
  Port    80

  Service
    BackEnd
      Address 127.0.0.1
      Port    8080
    End
  End
End

# https://lvh.me
ListenHTTPS
  Address 127.0.0.1
  Port    443
  Cert    "/usr/local/etc/pound.pem"
  AddHeader "X_FORWARDED_PROTO: https"

  Service
    BackEnd
      Address 127.0.0.1
      Port    8080
    End
  End
End

The /usr/local/etc/pound.pem file is a locally-generated, locally-signed SSL certificate.

When I want to view an SSL-only app in my local browser, I just start Unicorn (my app container of choice) which defaults to port 8080. Then I visit https://lvh.me which is helpfully set to the loopback address 127.0.01. That hits Pound, which terminates the SSL connection (after a warning about the self-signed certificate), and proxies the web request to Unicorn.

This is very similar to what happens when the app runs in production on Heroku's systems, except that they use a Procfile in the root directory of the app to configure Unicorn. Per factor 7, it allows Heroku to bind my Unicorn instance to any arbitrary port:

web: bundle exec unicorn -p $PORT -c config/unicorn.rb