Below is a very quick example of what I did to configure a system DSN connection to a Mysql and PostgreSQL database using UnixODBC drivers on Ubuntu Lucid server from command line only.
# Do the obvious, install mysql an postgresql first. :)
# install unixodbc
apt-get install unixodbc unixodbc-dev libmyodbc odbc-postgresql
# cd to /etc/ODBCDataSources
# setup mysql driver, make myodbc_template file
[MySQL]
Description = MySQL driver
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so
#inst template driver
odbcinst -i -d -f myodbc_template
# setup pg driver, make pgodbc_template file
[PostgreSQL]
Description = PostgreSQL driver for Linux & Win32
Driver = /usr/lib/odbc/psqlodbca.so
Setup = /usr/lib/odbc/libodbcpsqlS.so
#inst template driver
odbcinst -i -d -f pgodbc_template
# query available/installed drivers
odbcinst -q -d
#should list Mysql and PostgreSQL
# make sample dsn template called gregtest_dsn
[gregtest]
Driver = /usr/lib/odbc/libmyodbc.so
SERVER = localhost
PORT = 3306
DATABASE = gregtest
OPTION = 3
USER = majorpayne
PASSWORD = ******** < replace with password!
# install dsn
odbcinst -i -s -l -f gregtest_dsn
# query system dsn’s
odbcinst -q -s
#connect
isql -v gregtest
# should connect and be able to run sql statements! :)
Make more DSN’s for other databases by adding more template files and repeating the steps above.
Edit 7/7/11:
I think pgsql odbc should not be /usr/lib/odbc/libodbcpsql.so , but rather /usr/lib/odbc/psqlodbca.so.
I have not tested, but I later realized libodbcpsql.so does not exist. Â Just an FYI.
EDIT March 6th 2016:
Been a long time since I had to play with ODBC stuff. I’ve had a hard time with PostgreSQL! Â Today I had to test some ODBC Postgres connection on Ubuntu Precise 12.04. Â Its getting pretty old now, though! Â But after some struggling, I FINALLY found out why Postgres would not connect. Â My situation was that I could connect just fine using “psql” command line client for Postgres, but “isql” for ODBC would not work. I kept getting errors like this:
[08001][unixODBC]Could not connect to the server; Could not connect to remote socket immedaitely [ISQL]ERROR: Could not SQLConnect
It would never connect to the server. Â I finally found out on another blog:
http://thompsonng.blogspot.com/2014/10/odbc-08001unixodbccould-not-connect-to.html
Wow that was hard to find!!! Â For PostgreSQL you need to have “Servername” Â NOT “Server”. Â OMG!!!
Just some specs, for help to others. Â I have a PG server running on another host on the network with version 9.5 (on a Mac actually). Â And I do have the pg_hba.conf and ports listening and roles setup, so make sure you have that covered and psql works. Â On the Ubuntu 12.04 box I have all the packages for postgres client and odbc for postgres. Â All that is version 9.1. Â Here’s my current /etc/odbcinst.ini and /etc/odbc.ini sections on the client host:
[psql_template] Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so Descrption = PostgreSQL Driver UsageCount = 1
[ets_norion] Driver = psql_template Servername = norion-eth Port = 5432 Database = ets User =Â myuser Password =Â xxxxx Trace = Yes TraceFile = /tmp/psql-odbc.log
That’s about it! Â There’s not a lot of help out there for PostgreSQL ODBC stuff. Â everything I find is similar issues and people reporting some similar troubleshooting and issues, but seems like hardly any one is posting how they solved it, or they give up. Â I know I did years back. Â I could not get this to work on 12.04 , gave up and went all to Mysql. Â Which really make some mad, because now I’m regretting that I didn’t pursue this further.