sinatra + heroku + postgres

Now that the (read-only) web service was working locally on my machine, I wanted to see if I could push it to Heroku.

The first thing I did was to dump my Postgres database. Since the basic/free Heroku account only supports databases with at most 10k rows, I truncated the zip code database. There are a total of 42k+ rows, so chomping off this many rows is significant, but I’m mostly doing this just as a demonstration.

Usually, you’d run pg_dump to export an entire Postgres database to a file. However, pg_dump is an all-or-nothing command and since the zip code database is too big, I first created a copy of the database with the right number of rows. I also shifted things around so that the truncated table matches the name that DataMapper expects.

CREATE TABLE zipcodes_full AS SELECT * FROM zipcodes;
DROP TABLE zipcodes;
CREATE TABLE zipcodes AS SELECT * FROM zipcodes_full LIMIT 10000;

Then I ran pg_dump on the smaller table. Note that pg_dump is a command-line utility, so I ran this from a bash shell. [Note: make sure you’re inside the Postgres.app’s bin folder so you’re using the right version of Postgres.]

./pg_dump -t zipcodes_truncated mydb > /path/to/dump/file/mydb.sql

To create a Heroku project, I first installed the Heroku toolbelt and then ran:

heroku create zipcode-lookup

To push a Postgres database to Heroku, you need to add a database to the Heroku app. To restore a database from a *.sql file, the file needs to be HTTP-accessible (I used Dropbox for this). DATABASE_URL references the application’s current database URL.

heroku addons:add heroku-postgresql:hobby-dev
heroku addons:add pgbackups
heroku pgbackups:restore DATABASE_URL 'https://dl.dropboxusercontent.com/u//mydb.sql'

Note that if you do not run heroku addons:add heroku-postgresql:hobby-dev, then you are likely to see errors like ! Your app has no databases.
Now if I ping Heroku, I can verify that all 10k rows were uploaded

$ heroku pg
=== HEROKU_POSTGRESQL_MAROON_URL (DATABASE_URL)
Plan:        Hobby-dev
Status:      Available
Connections: 2/20
PG Version:  9.3.3
Created:     2014-10-16 09:57 UTC
Data Size:   7.7 MB
Tables:      1
Rows:        10000/10000 (In compliance)
Fork/Follow: Unsupported
Rollback:    Unsupported

For the final step, push the Sinatra code to Heroku. Note that before doing this, you should update the line of code that references the database URL

git push heroku master

Now when I go to http://zipcode-lookup.herokuapp.com/zipcode/10456, you’ll see the appropriate JSON result.

{"city":"BRONX","state":"NY","lat":40.84,"lng":-73.87}

Note, if you run into trouble, you can always look at the logs to see the error messages:

heroku logs --tail

Refs:
http://www.postgresql.org/docs/9.0/static/sql-createtableas.html
https://devcenter.heroku.com/articles/heroku-postgres-import-export#import-to-heroku-postgres