Thread: Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client - SOLVED

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

From

"Stefan Viljoen"

Date:

22 July 2015, 10:31:10

Hi guys

Firstly, thanks for all the ideas and  assistance.

I managed to solve this on Centos 6.5 by downgrading to Postgres 9.0 and
ODBC is now working fine:

---
[root@asterisk ~]# isql -v pgdb-cdr
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
[root@asterisk ~]#
---

So apparently there is an incompatibility between the relatively old
unixODBC current on an updated Centos 6.5 instance and the psqlodbc driver
from Postgres 9.4

I did not change or touch my /etc/odbcinst.ini or /etc/odbc.ini

ODBC is now apparently working fine on my Centos 6.5 box to Postgres 9.0

I did not test 9.1, 9.2 or 9.3, but I know for sure 9.0's psqlodbc driver is
now working with unixODBC allowing me to access my Postgres database via
isql.

Thanks all

Regards

Stefan

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

From

Tom Lane

Date:

22 July 2015, 13:37:41

"Stefan Viljoen" <viljoens@verishare.co.za> writes:
> I managed to solve this on Centos 6.5 by downgrading to Postgres 9.0 and
> ODBC is now working fine:

Hm.  That should not have fixed anything, because there for sure have not
been any basic protocol-level changes since 9.0.

Looking back at your earlier messages, I notice that you are using
the Red Hat-supplied unixODBC package:

> # yum list installed | grep ODBC
>
> unixODBC.x86_64         2.2.14-14.el6   @base
> unixODBC-devel.x86_64   2.2.14-14.el6   @base

but the Postgres ODBC driver seems to be coming from PGDG:

> # 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

An all-Red-Hat solution for this would look like

Installing:
 postgresql-odbc   x86_64   08.04.0200-1.el6    rhel-6-workstation-rpms   267 k
Installing for dependencies:
 postgresql-libs   x86_64   8.4.20-3.el6_6      rhel-6-workstation-rpms   202 k
 unixODBC          x86_64   2.2.14-14.el6       rhel-6-workstation-rpms   378 k

So now I'm suspicious that there's some incompatibility between the ODBC
driver that Devrim is shipping and the admittedly-pretty-old unixODBC
manager in RHEL6.

(I haven't worked for Red Hat in a couple of years, but I'm fairly sure
that when I left, their ODBC packages would still talk to current Postgres
server versions, because I recall testing that.  However, I never tested
the combination of their unixODBC with the PGDG packaging of the ODBC
driver...)

            regards, tom lane

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

From

"Stefan Viljoen"

Date:

22 July 2015, 13:44:32

Hi Tom

Thanks for the answer.

Yes, it turned out to be an incompatibility of some kind.

I downgraded the Postgress instance to 9.0 and immediately everything started working.

I suspect that the very old unixODBC (2.2.14) that ships with a fully updated Centos 6.5 (also old) is simply
incompatiblewith the psqlodbc driver shipped with Postgres 9.4 

Everything is now working fine on Postgres 9.0

Maybe put up a note somewhere relevant that (maybe) Centos 6.5 stock unixODBC cannot talk to Postgres 9.4's ODBC
driver?

9.0 definitely works like a charm (at least in my Centos 6.5 instance here.)

Thanks for the time you took to assist with this.

Kind regards,

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday,July 22, 2015 03:38 PM
To: Stefan Viljoen
Cc: pgsql-odbc@postgresql.org; Devrim GÃœNDÃœZ
Subject: Re: [ODBC] Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC
commandlineclient - SOLVED 

"Stefan Viljoen" <viljoens@verishare.co.za> writes:
> I managed to solve this on Centos 6.5 by downgrading to Postgres 9.0
> and ODBC is now working fine:

Hm.  That should not have fixed anything, because there for sure have not been any basic protocol-level changes since
9.0.

Looking back at your earlier messages, I notice that you are using the Red Hat-supplied unixODBC package:

> # yum list installed | grep ODBC
>
> unixODBC.x86_64         2.2.14-14.el6   @base
> unixODBC-devel.x86_64   2.2.14-14.el6   @base

but the Postgres ODBC driver seems to be coming from PGDG:

> # 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

An all-Red-Hat solution for this would look like

Installing:
 postgresql-odbc   x86_64   08.04.0200-1.el6    rhel-6-workstation-rpms   267 k
Installing for dependencies:
 postgresql-libs   x86_64   8.4.20-3.el6_6      rhel-6-workstation-rpms   202 k
 unixODBC          x86_64   2.2.14-14.el6       rhel-6-workstation-rpms   378 k

So now I'm suspicious that there's some incompatibility between the ODBC driver that Devrim is shipping and the
admittedly-pretty-oldunixODBC manager in RHEL6. 

(I haven't worked for Red Hat in a couple of years, but I'm fairly sure that when I left, their ODBC packages would
stilltalk to current Postgres server versions, because I recall testing that.  However, I never tested the combination
oftheir unixODBC with the PGDG packaging of the ODBC 
driver...)

            regards, tom lane

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

From

jaime soler

Date:

29 July 2015, 13:05:27

El mié, 22-07-2015 a las 15:47 +0200, Stefan Viljoen escribió:
> Hi Tom
>
> Thanks for the answer.
>
> Yes, it turned out to be an incompatibility of some kind.
>
> I downgraded the Postgress instance to 9.0 and immediately everything started working.
>
> I suspect that the very old unixODBC (2.2.14) that ships with a fully updated Centos 6.5 (also old) is simply
incompatiblewith the psqlodbc driver shipped with Postgres 9.4 

unixODBC should be at least 2.3.1 for postgresql 9.3 and 9.4 .
Enterprisedb ships unixODBC-2.3.1.tar.bz2 with pgodbc driver, so I
suppose postgresql-odbc ( redhat package)  have to update its
dependencies.
>
> Everything is now working fine on Postgres 9.0
>
> Maybe put up a note somewhere relevant that (maybe) Centos 6.5 stock unixODBC cannot talk to Postgres 9.4's ODBC
driver?
>
> 9.0 definitely works like a charm (at least in my Centos 6.5 instance here.)
>
> Thanks for the time you took to assist with this.
>
> Kind regards,
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday,July 22, 2015 03:38 PM
> To: Stefan Viljoen
> Cc: pgsql-odbc@postgresql.org; Devrim GÃœNDÃœZ
> Subject: Re: [ODBC] Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql
ODBCcommandline client - SOLVED 
>
> "Stefan Viljoen" <viljoens@verishare.co.za> writes:
> > I managed to solve this on Centos 6.5 by downgrading to Postgres 9.0
> > and ODBC is now working fine:
>
> Hm.  That should not have fixed anything, because there for sure have not been any basic protocol-level changes since
9.0.
>
> Looking back at your earlier messages, I notice that you are using the Red Hat-supplied unixODBC package:
>
> > # yum list installed | grep ODBC
> >
> > unixODBC.x86_64         2.2.14-14.el6   @base
> > unixODBC-devel.x86_64   2.2.14-14.el6   @base
>
> but the Postgres ODBC driver seems to be coming from PGDG:
>
> > # 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
>
> An all-Red-Hat solution for this would look like
>
> Installing:
>  postgresql-odbc   x86_64   08.04.0200-1.el6    rhel-6-workstation-rpms   267 k
> Installing for dependencies:
>  postgresql-libs   x86_64   8.4.20-3.el6_6      rhel-6-workstation-rpms   202 k
>  unixODBC          x86_64   2.2.14-14.el6       rhel-6-workstation-rpms   378 k
>
> So now I'm suspicious that there's some incompatibility between the ODBC driver that Devrim is shipping and the
admittedly-pretty-oldunixODBC manager in RHEL6. 
>
> (I haven't worked for Red Hat in a couple of years, but I'm fairly sure that when I left, their ODBC packages would
stilltalk to current Postgres server versions, because I recall testing that.  However, I never tested the combination
oftheir unixODBC with the PGDG packaging of the ODBC 
> driver...)
>
>             regards, tom lane
>
>
>

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

From

"Stefan Viljoen"

Date:

29 July 2015, 13:14:01

Hi Jaime

Thanks for confirming that. I suspected as much when I tcpdumped the traffic to the localhost and saw that the db names
werebeing munged when a connection from the old unixODBC was attempted... 

Kind regards.

-----Original Message-----
From: jaime soler [mailto:jaime.soler@gmail.com]
Sent: Wednesday,July 29, 2015 03:05 PM
To: Stefan Viljoen
Cc: pgsql-odbc@postgresql.org; 'Devrim GÃœNDÃœZ'
Subject: Re: [ODBC] Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC
commandlineclient - SOLVED 

El mié, 22-07-2015 a las 15:47 +0200, Stefan Viljoen escribió:
> Hi Tom
>
> Thanks for the answer.
>
> Yes, it turned out to be an incompatibility of some kind.
>
> I downgraded the Postgress instance to 9.0 and immediately everything started working.
>
> I suspect that the very old unixODBC (2.2.14) that ships with a fully
> updated Centos 6.5 (also old) is simply incompatible with the psqlodbc
> driver shipped with Postgres 9.4

unixODBC should be at least 2.3.1 for postgresql 9.3 and 9.4 .
Enterprisedb ships unixODBC-2.3.1.tar.bz2 with pgodbc driver, so I suppose postgresql-odbc ( redhat package)  have to
updateits dependencies. 
>
> Everything is now working fine on Postgres 9.0
>
> Maybe put up a note somewhere relevant that (maybe) Centos 6.5 stock unixODBC cannot talk to Postgres 9.4's ODBC
driver?
>
> 9.0 definitely works like a charm (at least in my Centos 6.5 instance
> here.)
>
> Thanks for the time you took to assist with this.
>
> Kind regards,
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday,July 22, 2015 03:38 PM
> To: Stefan Viljoen
> Cc: pgsql-odbc@postgresql.org; Devrim GÃœNDÃœZ
> Subject: Re: [ODBC] Postgres 9.4 + unixODBC on Centos 6.5 problem
> connecting localhost postgres instance with isql ODBC commandline
> client - SOLVED
>
> "Stefan Viljoen" <viljoens@verishare.co.za> writes:
> > I managed to solve this on Centos 6.5 by downgrading to Postgres 9.0
> > and ODBC is now working fine:
>
> Hm.  That should not have fixed anything, because there for sure have not been any basic protocol-level changes since
9.0.
>
> Looking back at your earlier messages, I notice that you are using the Red Hat-supplied unixODBC package:
>
> > # yum list installed | grep ODBC
> >
> > unixODBC.x86_64         2.2.14-14.el6   @base
> > unixODBC-devel.x86_64   2.2.14-14.el6   @base
>
> but the Postgres ODBC driver seems to be coming from PGDG:
>
> > # 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
>
> An all-Red-Hat solution for this would look like
>
> Installing:
>  postgresql-odbc   x86_64   08.04.0200-1.el6    rhel-6-workstation-rpms   267 k
> Installing for dependencies:
>  postgresql-libs   x86_64   8.4.20-3.el6_6      rhel-6-workstation-rpms   202 k
>  unixODBC          x86_64   2.2.14-14.el6       rhel-6-workstation-rpms   378 k
>
> So now I'm suspicious that there's some incompatibility between the ODBC driver that Devrim is shipping and the
admittedly-pretty-oldunixODBC manager in RHEL6. 
>
> (I haven't worked for Red Hat in a couple of years, but I'm fairly
> sure that when I left, their ODBC packages would still talk to current
> Postgres server versions, because I recall testing that.  However, I
> never tested the combination of their unixODBC with the PGDG packaging
> of the ODBC
> driver...)
>
>             regards, tom lane
>
>
>