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 !

Rails migration add_column and Postgresql

Just ran into a difference between MySQL and Postgresql when running a migration that added a new column to an existing table.

The intent of the original line was to add a new boolean column called 'active' and set the default value to true:

    add_column :people, :active, :boolean, :default => true

This works fine on MySQL but with Postgresql the default value was not used on existing records. It appears that Postgresql sees that NULL is an acceptable value for the column and uses that as the value.

The way to fix this is to add a 'null' attribute to the call like this:

    add_column :people, :active, :boolean, :default => true, :null => false

Now, running rake db:migrate sets all existing records to true

 

Setting up PostgreSQL on Mac OS X 10.6

I needed to set up PostgreSQL on my Mac in order to troubleshoot a problem with a Rails application.

Here are the steps that I followed:

1: Using Homebrew, install and build PostgreSQL
Homebrew will give you commands to create a Launch Agent that starts the server on a reboot

$ brew install postgresql
$ cp /usr/local/Cellar/postgresql/9.0.3/org.postgresql.postgres.plist ~/Library/LaunchAgents
$ launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist

2: Setup PostgreSQL
You should look into setting administration user - I skipped that for my purposes

$ initdb /usr/local/var/postgres
The files belonging to this database system will be owned by user "jones".
This user must also own the server process.
[...]

3: Create a specific database and verify that it is running

$ createdb mydb
$ psql mydb
mydb-# select version();
mydb-# \h

4: Install the PostgreSQL ruby gem
You'll see different names for this gem - just use 'pg'. Using the ARCHFLAGS env variable is important. I did not need to specify the explicit path to the Homebrew installation of the PostgreSQL software.

$ env ARCHFLAGS="-arch x86_64" gem install pg