Forum OpenACS Q&A: Re: ODBC-connection to Postgres database

Collapse
Posted by Jerry Asher on
I did this (once again) just yesterday, using the RH 8 distribution to get unixODBC.  For pg I am using the RHDB 2.0.  To reiterate some of what has been said....

In postgresql.conf ensure that pc can connect via sockets.

tcpip_socket = true

then in pg_hba.conf, give yourself a raging security hole (or do better with your own configuration.)  For testing through a firewall, I use ssh tunneling to get the remote client to the localhost.... so I am using just localhost connections at the moment, and then on Windows, I create an ssh tunnel.

host all 127.0.0.1 255.255.255.255 trust

Still it took me too long to get the odbc.ini working, and that's because I was using the one in /etc.  I finally got it to work after copying /etc/odbc.ini to ~postgres/.odbc.ini (or generally to ~/.odbc.ini for whichever user you are using.)

Now my understanding is that /etc/odbc.ini should represent system dsns, but I'm wondering if the RH distribution of unixodbc expects that file somewhere else.  Anyone know where?

You can test it locally with isql (the -v flag is very useful) and/or with the simple apps found in "Using ODBC with Red Hat Database".  If you get ViewTable to connect and to work, you're in.

The gborg pg driver for windows appears to work fine.  Unfortunately, the driver setup doesn't include a connection test routine (other driver setups do do that), but you can use either excel or access to quickly test the connection.

You can also check various debug/trace settings by including these in your odbc.ini:

trace = yes
tracefile = /tmp/psql.log
debug = yes
debugfile = /tmp/pdbg.log
commlog = yes

Now, I didn't actually find log files with *those* names in /tmp, but two log files *do* show up, and they can help you understand if you're making the connection or not.

Are you using RH's unixodbc distribution?  For some mysterious reason they appear to fail to include the gui odbc config tools, ODBCConfig and DataManager.  Anyone know why?