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

Hi Nick

>Can I just check, when you say "unixODBC drivers" you don't mean the age
old version of the postgres driver that used to be part of the default
unixODBC build? If it is, don't touch that, its only there as a example.

Unsure.

Here's what I did to install unixODBC and postgres 9.4

- I installed unixODBC via yum on Centos 6.5 and updated it via yum

- I edited /etc/yum.repos.d/CentOSBase.repo and in the [base] section added
exlucde=postgresql*

- I edited /etc/yum.repos.d/CentOSBase.repo and in the [updates] section
added exlucde=postgresql*

- I added a file pdgd-94-centos.repo which 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
---

- Afer doing that I installed the following postgres packages from the
postgres repo added above:

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

- I then configured pg_hba.conf and started the postgress instance via

service postgresql-9.4 start
chkconfig postgresql-9.4 on

- I set up odbc.ini as detailed before:

---
[pgdb-cdr]
Driver = /usr/pgsql-9.4/lib/psqlodbc.so
;Driver=PostgreSQL
ServerName=/tmp
Port=5432
UserName=asteriskcdruser
Password=1ndestruc#
Database=asteriskcdrdb
ReadOnly=No
Protocol=9.4
Trace=Yes
TraceFile=/tmp/sql.log
ConnSettings=
DSN=pgdb
ServerType=Postgres
Debug=1
DebugFile=/var/log/postgresql_debug.log
---

in which I explicitly select the driver in the above location, not another
driver somewhere else?

isql fails to connect to postgres with

---
isql -v pgdb-cdr dbuser dbpass
[S1000][unixODBC]The database does not exist on the server
or user authentication failed.
[ISQL]ERROR: Could not SQLConnect
[root@jhbasterisk yum.repos.d]#
---

while psql itself works fine:

---
#psql -h 127.0.0.1 -U asteriskcdruser -d asteriskcdrdb
psql (9.4.4)
Type "help" for help.

asteriskcdrdb=> \list
                                       List of databases
     Name      |  Owner   | Encoding |   Collate   |    Ctype    |
Access privileges
---------------+----------+----------+-------------+-------------+----------
--------------------
 asteriskcdrdb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
=Tc/postgres                +
               |          |          |             |             |
postgres=CTc/postgres       +
               |          |          |             |             |
asteriskcdruser=CTc/postgres
 postgres      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres                 +
               |          |          |             |             |
postgres=CTc/postgres
 template1     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres                 +
               |          |          |             |             |
postgres=CTc/postgres
(4 rows)

asteriskcdrdb=>
---

Does this nonetheless mean I'm using the incorrect driver and that is what
is causing the unixODBC utlity isql to no be able to connect to postgres?

Thanks

Kind regards

Stefan