PostgreSQL ODBC connection from Mac OS X

Wow! Almost a whole (sabbatic?) year has passed since my last post! I really was neither in the mood, nor motivated for writing. But after having sorted out a little some other aspects of my life, I’m back, stronger than ever! ­čÖé

Among the things I’ve recently had to deal with, is the ODBC connections and Mac (yes, I own a Mac for some time now ­čśë ). And I’ve finally found an easy way to create ODBC connections using unixODBC as I do in Linux. I’m surprised, because it works incredibly well:

First thing to do is installing unixODBC for Mac OS X. Personally, I think the simplest way to do it is using Homebrew, that fantastic open source package tool which makes Ruby to call my attention (disclaimer: I’m a zealot pythonista! ;)). It’s an example of a good work very well done (however, using Homebrew is not mandatory, there are other projects long ago: Fink and MacPorts).

Install unixODBC with Homebrew:
brew install unixodbc

Once installed, it’s time to compile the PostgreSQL ODBC driver (not available in Homebrew yet, unfortunately, so we’ll do it by hand):

  1. Download the ODBC driver source code. Pick a recent one and uncompress it in a directory of your choice, then open a terminal and ‘cd’ into it.
  2. Compile it following the typical command sequence:

If everything went ok, you should have the PostgreSQL drivers installed as 2 library files located at /usr/local/lib/ and /usr/local/lib/

And that’s all. Now we have to define an ODBC driver entry for PortgreSQL in the file /usr/local/etc/odbcinst.ini in a similar fashion to this one:

Add this entry to the odbcinst.ini file and we are done. Now we can define our DSNs and start using ODBC!

To do so, create if not exists the file .odbc.ini in your $HOME directory and add the desired DSN, like this one:

This define a User DSN named ib3db (with its user and password). Now to test it, just type:
isql -v ib3db
And it should work.

Remote connection

The previous DSN example was created for a local postgreSQL database. If we wanted to connect to a remote one, there are many configurations, but for me the easiest and safe one is to create an SSH tunnel:
ssh -C -N -L 60000:localhost:5432
This will map our local port 60000 to the remote port 5432 at the machine (we’ll obviously need to update the Port = 5432 line in the previous example with Port = 60000). The command will keep running. To shutdown the tunnel, just kill the ssh command pressing CTRL-C.

You can also use extra flags, like -C (above) to compress the data transmitted to the tunnel (recommended for slow -e.g. modem- lines, but not for faster ones), and also use -N (do not execute anything remotely) so a remote ssh window is not opened, for example.