Privacy Policy
Snippets index

  PostgreSQL ODBC connection from Mac OS X

Install unixODBC with Homebrew:

brew install unixodbc

Install the PostgreSQL ODBC driver:

brew install psqlodbc

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.

References: