A collection of computer systems and programming tips that you may find useful.
Brought to you by Craic Computing LLC, a bioinformatics consulting company.

Wednesday, October 24, 2012

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


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



zakstern said...

Thank you for this. This solved a days worth of frustration for me.

MFH said...

OMG all day long ive been trying to push my db to heroku and this helps. thankssss

MFH said...

OMG all day long ive been trying to push my db to heroku and this helps. thankssss

Blogger said...

There is a chance you're qualified to get a free $1,000 Amazon Gift Card.

Archive of Tips