How to install and use PostgreSQL

If you’re a Rails developer and you’re not using Postgres for your database, its likely because you have already invested time and energy installing and configuring MySQL on your Mac. You also probably got use to great tools like Sequel Pro for managing those databases also.

However, it seems the community has really moved towards Postgres. Why? Well I can’t say for everyone, but for me there were two big reasons:

1. Heroku

Learning Rails can be daunting enough, but also learning how to do a production deployment of a Rails application can take it over the edge. To be honest, there really isn’t much of a reason to learn how to do a good production deployment of Rails unless you are personally responsible for taking client or company projects end to end – and even then, why wouldn’t you use Heroku, save yourself the liability of being responsible for managing a production Rails deployment. In my opinion, get really good at Rails. I’m not saying those server management skills aren’t important – I’m saying there are very important and just as specialized as learning Rails itself. So let someone who’s neck deep in operations take care of your app. End rant.

2. PostGIS

This doesn’t get talked about enough, but mobile introduced a huge new skill gap in developers. Those who know how to work with locations and those who don’t. What do I mean? Almost every mobile application has some location based feature. Well if you know mobile development then you are thinking: “That’s easy! iOS gives me the location of a user. done”. True, so does a web browser, that’s not the hard part. The hard part is asking whether that location is near another one… oh ya… you forgot about that part. Now there are a ton of ways to approach this, but PostGIS makes this a much easier task. Also, you think doing a production Rails deployment is hard… deploy a GIS product or server. No thanks.

So how do we install Postgres on OS X? Well there are a couple ways. Here’s the easy way. The way I do it.

Install the Postgres app

Go to http://postgresapp.com and click Download. Then drag that app to your Applications folder and fire it up. Done!

Its almost too simple.

So how do we use this with Rails? Well you will need the pg gem. When you try to install the gem you will likely get an error that it can’t find pg_config. That’s because its packaged in that cool app we just downloaded, so all you have to do is tell the gem where it is. Here’s how you do that:

gem install pg -- --with-pg-config=/Applications/Postgres.app/Contents/Versions/9.3/bin/pg_config

Note: You may have to change the version number, but at the time of this post it was 9.3

Now you just fire up a rails project using:

rails new myapp --database=postgresql

Using a PostgreSQL Client on the Mac

That’s it! Now you could click that Open psql button and start managing your databases from the command line, and there is nothing wrong with that, but you might want a nice tool with an easy to use graphical interface. Unfortunately Sequel Pro… is MySQL only. So the best option I found is PGCommander. Its actually great, but its also $40. If you find something as good and cheaper let me know. I use PGCommander.

Enabling PostGIS for PostgreSQL

Remember the other thing I talked about? How cool PostGIS is. Well another great thing about that Postgres app is that its easily enabled if you are build some cool location based Rails web services!

To enable PostGIS, connect to your database and enter the command:

CREATE EXTENSION postgis;

So that’s it! Now you have a PostgreSQL database on OS X ready for development. You can easily start using PostGIS services if you need and you can manage the databases using PGCommander!