Cannot Connect To PostgreSQL ODBC (Doc ID 2554697.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

configure odbc to PostgreSQL, psql test works
goldengate01 [] isql PGSERVER
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
goldengate01 [] cat /etc/odbc.ini
[ODBC Data Sources]
PGSERVER = PostgreSQL

[PGSERVER]
Description=Connection to PGSERVER
Driver=PostgreSQL
Servername=DNSZZZ.amazonaws.com
Port=8080
Database=DBNAME
Username=service_oracleebus_test01
Password=xxxxxxxx
ReadOnly=No
Servertype=postgres
ServerOptions=
ConnectOptions=
Options=
ReadOnly=no
SSL=true
SSLmode=require

[Default]
Driver = /usr/lib64/psqlodbc.so
InstallDir=/usr/lib64/

> PATH=/etc/odbc.ini:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/XXX/.local/bin:/home/XXX/bin
> ODBCINI=/etc/odbc.ini
./ggsci

but customer is facing below error
GGSCI (ip-10-200-241-92.DBNAME.em.originenergy.com.au) 3> dblogin sourcedb PGSERVER
2019-04-02 14:43:11 WARNING OGG-00552 Database operation failed: Couldn't connect to PGSERVER. ODBC error: SQLSTATE 81 native database error 0. 523 80.
ERROR: Database operation failed: Couldn't connect to PGSERVER. ODBC error: SQLSTATE 81 native database error 0. 523 80Failed to open data source PGSERVER for user .

Hence, customer wants to check whether Goldengate can use ssl to connect to db or not ((Doc ID 2295615.1

"The issue was that Goldengate could not use ssl to connect to db. The pg_hba.conf was configured for the user enforces ssl based connection and that is why it was failing to connect.Currently it is being checked whether or not Oracle Goldengate Gate could use SSL to connect to postgres db."

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.