Uploading contents of a PostgreSQL database to Heroku without db:push

I just created a new Rails app on my local machine and want to deploy it to Heroku. I've done this before and the uploading of the code typically works great - and it did today.

Then I wanted to upload the contents of a small PostgreSQL database to Heroku - and that's where I ran into issues.

Heroku has recently switched from a Heroku gem to Heroku Toolbelt which I installed onto my Mac OS X machine.

I should be able to run 'heroku db:push' from my Rails app and the data would get moved over using the taps gem. But it failed with this error:

$ heroku db:push
! Taps Load Error: cannot load such file -- taps/operation
! You may need to install or update the taps gem to use db commands.
! On most systems this will be:
!
! sudo gem install taps

I have taps installed, so what is going on? Well, I'm not sure but I run my rubies under rbenv which allows you to manage multiple versions - and Heroku's client doesn't seem able to figure out where the gems are located with rbenv.

I dug around in /usr/local/heroku and hacked /usr/local/heroku/lib/heroku/command/db.rb to tell me what it thought the load path for ruby gems was, using this line:

STDERR.puts "DEBUG: load path  #{$:}"

The paths given in that were all in /usr/local/heroku/vendor/gems and my gem paths were nowhere to be found. I tried installing the taps, sqlite3 and sequel gems to that directory and I was able to get a bit further but I still couldn't get all the way - so I gave up on that path.

And then I found mention of a page on Heroku's site that deals with the pgbackups addon. Part of that describes how to import from a PostgreSQL backup directly, without having to use the heroku clients or taps.

I followed that and it worked great - here are the steps involved. Your starting data must be in PostgreSQL and you need to have an account on Amazon S3 (and know how to use it)

1: On your machine create a backup of your PostgreSQL database - 'mydb' is your database name and 'myuser' is your database user.

$ pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydatabase > mydb.dump

2: Transfer that to an Amazon S3 bucket and either make it world readable or set a temporary access url.

3: Check that you can get the file back out of S3 using curl

$ curl 'https://s3.amazonaws.com/mybucket/mydb.dump' > tmp
$ ls -l tmp

4: Enable the pgbackups addon in Heroku

$ heroku addons:add pgbackups

5: Get the name of the Heroku database that you will restore to, by capturing a backup from the empty heroku db:

$ heroku pgbackups:capture

HEROKU_POSTGRESQL_CRIMSON_URL (DATABASE_URL)  ----backup--->  b001

Capturing... done
Storing... done

6: Restore to that database from the S3 URL

$ heroku pgbackups:restore HEROKU_POSTGRESQL_CRIMSON_URL 'https://s3.amazonaws.com/mybucket/mydb.dump'

7: Test it out - my app was already up so I just had to browse to the appropriate page and there were all my records.

8: Remove your S3 file

DONE !