boriel.com

Hacks, science and curiosities

PostgreSQL ODBC connection from Mac OS X

– 🕓 3 min read

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:

    ./configure make make install

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

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:

[PostgreSQL Unicode]
Description     = PostgreSQL ODBC driver (Unicode version)
Driver          = psqlodbcw.so
Debug           = 0
CommLog         = 1
UsageCount      = 1

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:

[ib3db]
Driver      = PostgreSQL Unicode
ServerName  = localhost
Port        = 5432
Database    = ib3db
Username    = iberouser
Password    = mypass1234
Protocol    = 9.1.6
Debug       = 1

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 user@remotehost.com
This will map our local port 60000 to the remote port 5432 at the remotehost.com 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.