[Driver Manager]Can't open lib 'PostgreSQL' : file not found

[Gaurav2]

Hi All,
 
We are migrating the server from RHEL 7 to RHEL 8. 
We have a PostgreSQL target. 
 
From new box, RHEL 8, we are able to connect via command line to PostgreSQL but while trying to test connection from the Qlik UI, we are getting the below error:
 

Cannot connect to ODBC provider
RetCode: SQL_ERROR SqlState: 01000 NativeError: 0 Message: [unixODBC][Driver Manager]Can't open lib 'PostgreSQL' : file not found

Below is the o/p of site_arep_login.sh and /etc/odbcinst.ini. With same settings, it was connecting in RHEL 7.

[root@**]# cat site_arep_login.sh
# enter site specific settings here
#export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/u01/app/oracle/product/193000/client_1/lib/
export NLS_LANG='ENGLISH_UNITED KINGDOM.AL32UTF8'
export ORACLE_HOME=/u01/app/oracle/product/193000/client_1/
export TNS_ADMIN=/u01/app/oracle/product/193000/client_1/network/admin/tnsnames.ora
export LD_LIBRARY_PATH=/usr/lib:/usr/lib64:/usr/pgsql-14/lib:/opt/snowflake_odbc/lib:/u01/app/oracle/product/193000/client_1/lib/:$LD_LIBRARY_PATH



[root@**]# cat /etc/odbcinst.ini
# Driver from the postgresql-odbc package
# Setup from the unixODBC package

PostgreSQL]
Description = PostgreSQL ODBC driver
Driver = /usr/pgsql-14/lib/psqlodbc.so
Setup = /usr/pgsql-14/lib/psqlodbcw.so
Debug = 0
CommLog = 1
UsageCount = 2

[SnowflakeDSIIDriver]
APILevel=1
ConnectFunctions=YYY
Description=Snowflake DSII
Driver=/opt/snowflake_odbc/lib/libSnowflake.so
DriverODBCVer=03.52
SQLLevel=1

[SushilKumar]

Hello @Gaurav2 

Seems QR is not able to find Postgres SQl Such error occurs when you try to connect to a PostgreSQL database using a ODBC application (database library connection), you see the 

to resolve that .

First, install the ODBC PostgreSQL driver adapter:

sudo apt -y install odbc-postgresql

Regards,
Sushil Kumar

[Gaurav2]

Hi @SushilKumar , I can't find odbc-postgresql.

But we have already installed postgresql14-odbc-16.00.0000-1PGDG.rhel8.x86_64.rpm

[DesmondWOO]

Hi @Gaurav2 ,

Regarding your odbcinst.ini, could you double check whether the PostgreSQL entry has the bracket [ in front of it?

[root@**]# cat /etc/odbcinst.ini
# Driver from the postgresql-odbc package
# Setup from the unixODBC package

PostgreSQL]
Description = PostgreSQL ODBC driver
Driver = /usr/pgsql-14/lib/psqlodbc.so
Setup = /usr/pgsql-14/lib/psqlodbcw.so

Regards,
Desmond

[Gaurav2]

sorry, it was copy error. 
In actual it have :

[root@**]# cat /etc/odbcinst.ini
# Driver from the postgresql-odbc package
# Setup from the unixODBC package

[PostgreSQL]
Description = PostgreSQL ODBC driver
Driver = /usr/pgsql-14/lib/psqlodbc.so
Setup = /usr/pgsql-14/lib/psqlodbcw.so
Debug = 0
CommLog = 1
UsageCount = 2

[SnowflakeDSIIDriver]
APILevel=1
ConnectFunctions=YYY
Description=Snowflake DSII
Driver=/opt/snowflake_odbc/lib/libSnowflake.so
DriverODBCVer=03.52
SQLLevel=1

[Gaurav2 · Accepted solution]

We have resolved this issue. Everything was fine. The solution was very weird. 

We tried to remove UnixODBC driver and reinstalled it (same version). 

And then used the default /etc/odbcinst.ini file and just edit and updated the path for Driver and Setup. It worked.

So the main problem was not to remove anything from default /etc/odbcinst.ini. Just edit and update the path. 

[john_wang]

Hello @Gaurav2 ,

Thanks for the detailed information, however would you please confirm the below line in file "/etc/odbcinst.ini" ?

Driver = /usr/pgsql-14/lib/psqlodbc.so

Looks to me it does not work, the accurate one should be Driver64 like:

Driver64    = /usr/pgsql-13/lib/psqlodbcw.so

BTW, I've summarized the steps in article PostgreSQL ODBC client on Linux for PostgreSQL Target Endpoint.

Hope it helps.

John.