If you're doing any serious Rails work with Heroku you'll eventually want to run the same database server they are, namely PostgreSQL. One such reason is if you plan on using Geokit, all of the geo math is done at the database level and thus not available on Sqlite.
There are numerous ways of installing it on Mac OS X, (including a nice & easy MacPorts installer) however I opted for the easier Mac installer. Why? Because it installs a nice management console into your Applications folder along with some quick shortcuts to Start & Stop your server. This is nice because I don't plan on using PostgreSQL all the time, only when I work on some projects.
Installing PostgreSQL Database Server
First, download the Precompiled 8.4 binary for Mac OS X 10.4+ from the Postgres website. Alternatively, you might want to use MacPorts. For this, type:
Update: Turns out I had problems with this package. Something about 64 bit ruby, 32 bit gems -- anyway the MacPorts method works for me on Snow Leopard.
sudo port install postgresql83 postgresql83-server
Note that 8.4 is out, but some recommend using the same version that Heroku uses, namely 8.3. For a good MacPort installation walkthrough, check this article.
Next, make sure that the bin folder is in your path.
sudo vim /etc/profile
Find the line that looks like this:
Add your path at the end. Make sure to separate it with a colon to distinguish it from other paths. If you installed with MacPorts, your path is likely `/opt/local/var/postgresql83/bin`, but if you used the installer, then it would be `/Library/PostgreSQL/8.4/bin`. Note that you'll have to reload your terminal (or open a new tab) to pick up this change.
Setting up the pg gem
Next you'll want the pg gem, which is a native adapter for postgres. This is much faster than the postgres-pr gem, which is written in ruby.
Make sure you pick the correct architecture for your installation. This part tripped me up. Following instructions for a MacPorts installation, I used the wrong arch flags. If you used MacPorts to install it, then you'll use `x86_64` (64-bit) but if you used the standalone installer then you'll use `i386` (32-bit).
sudo env ARCHFLAGS='-arch i386' gem install pg
If you get an error like `checking for pg_config... no` then make sure that the correct bin folder is in your path. If instead you get an error like `Can't find the PostgreSQL client library (libpq)` then check your architecture flag and make sure you chose the right one (not for your system, but for the installation of PostgreSQL that you chose).
Hook it up to rails
Now we can alter our database.yml file to utilize postgresql for development.
development: adapter: postgresql database: my_database username: rails password: password
Note that you'll need to create the database & user ahead of time.
Now you should be good to go!