Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client - Mailing list pgsql-odbc

Hi Adrian

Thanks for replying!

>> Hi List
>>
>> The plot thickens - I have now resorted to doing a tcpdump of the
>> postgres port and then trying to connect via isql.
>>
>> The tcpdump command used was
>>
>> ---
>> tcpdump -vvvvv -x -X -s 65535 -i lo 'port 5432' -w post.pcap
>> ---
>>
>> This has revealed that in the post.pcap file (even just viewing the
>> raw packet data with vi in the terminal) that the REASON postgress
>> keeps rejecting isql login attempts is that isql apparently passes the
>> database name only partially, or corrupts it randomly...

>Not sure, but here is what I see.

>First on my machine openSUSE 13.1

>unixODBC 2.3.1-3.1.2

Ok that is much more recent than my unixODBC as provided by the official
Centos 6.5 repositories, mine is unixODBC 2.2.14, even after a

yum update unixodbc

>psqlodbc 09.03.0400

See below, I have this same 0400 version installed via yum from the
repository URL indicated below.

>odbcinst.ini

>[Postgres]
>Description             =
>Driver          = /usr/local/lib/psqlodbcw.so
>Driver64                = /usr/lib
>Setup           = /usr/lib/unixODBC/libodbcdrvcfg1S.so
>Setup64         = /usr/lib
>UsageCount              = 1
>CPTimeout               =
>CPReuse         =

>[ODBC]
>Trace           = No
>TraceFile               = /tmp/sql.log
>ForceTrace              = No
>Pooling         = No

Ok, I changed my driver to refer to psqlodbcw.so, NOT psqlodbc.so.

>odbc.ini

>[Production]
>Description             = Postgres
>Driver          = Postgres
>Host            = localhost
>Database                = production
>Port            =

Ok, I lacked a "Host" line (having a "ServerName=localhost" line instead) so
I added a "Host" line like this one above...

>aklaver@panda:~> isql -v Production
>+---------------------------------------+
>| Connected!                            |
>Where Production is pointing at a 9.4 database.

>I know we are not talking apples to apples, but at least it proves it is
possible.

Agreed.

>Now my observations.

>1) In my odbcinst.ini the Driver being pointed to is psqlodbcw.so instead
of psqlodbc.so in your odbcinst.ini. This leads to

Ok, I changed mine to psqlodbcw.so as well. No change.

>2) In your original post you have:

># yum list installed | grep postgres

>postgresql94.x86_64     9.4.4-1PGDG.rhel6
>postgresql94-contrib.x86_64
>postgresql94-devel.x86_64
>postgresql94-docs.x86_64
>postgresql94-libs.x86_64
>postgresql94-odbc.x86_64
>postgresql94-odbc-debuginfo.x86_64
>postgresql94-server.x86_64

Correct.

>Yet the Postgres yum repo:

>http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/

>has

>postgresql94-odbc-09.03.0300-1PGDG.rhel6.x86_64.rpm

Ok...

>So is it possible you have a version mismatch in the Postgres ODBC driver
and the Postgres server?

Ok, this is completely possible...

>I may have missed it, but do you know what version of psqlodbc you are
using or more to the point where is postgresql94-odbc.x86_64 is coming from?

I have

postgresql94-odbc-09.03.0400-1PGDG.rhel6.x86_64

e. g. I have the 0400 version like you have.

postgresql94-odbc.x86_64 is coming from the above URL you listed -
yum.postgresql.org/9.4/redhat/rhel-6-x86_64/

I know this due to how I installed Postgres 9.4 on my Centos 6.5 box:

- I edited /etc/yum.repos.d/CentOS-Base.repo and added a line

exclude=postgresql*

to the [base] and [updates] sections of CentOS-Base.repo

- I added a file

pgdg-94-centos.repo

to /etc/yum.repos.d

- This file contains

---
[pgdg94]
name=PostgreSQL 9.4 $releasever - $basearch
baseurl=http://yum.postgresql.org/9.4/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-94

[pgdg94-source]
name=PostgreSQL 9.4 $releasever - $basearch - Source
failovermethod=priority
baseurl=http://yum.postgresql.org/srpms/9.4/redhat/rhel-$releasever-$basearc
h
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-94
---

- I then ran

yum install postgresql94
yum install postgresql94-odbc
yum install postgresql94-devel
yum install postgresql94-libs
yum install postgresql94-contrib
yum install postgresql94-docs

to install postgres.

So it appears my problem is an old unixODBC installation?

Should I uninstall the yum ODBC package in Centos 6.5 and try to compile and
install the current unixODBC version from source?

Wonder why the latest MariaDB is working fine with the 2.2 version of
unixODBC I have, while postgres 9.4 won't....

Thanks for the help.

Regards

Stefan