SUMMARY: This article demonstrates how to use the PostgreSQL client interface psqlODBC driver in Oracle to access PostgreSQL data.
1. Server and software version details
2. Configuration
Oracle Database Gateways have the ability to transparently access non-Oracle system data from within the Oracle environment. Oracle has tailored gateways to many systems, and they are specifically coded for the target non-Oracle systems. As part of a generic technology solution to connect to a non-Oracle system, it offers Database Gateway for ODBC. This generic solution addresses the need for accessing data in other database systems for which Oracle does not have a tailored solution. Oracle Database Gateway for ODBC makes it possible to integrate with other databases such as MySQL, PostgreSQL, and even non-relational targets like Excel. In this post, we will configure an Oracle Database Gateway for ODBC (Heterogeneous Gateway) and make a connection to PostgreSQL v12 using PostgreSQL ODBC Connector.
From the diagram above, we can understand the Oracle gateway process flow:
- Client sends a query to the Oracle database via the Oracle Database Link created using Heterogeneous ODBC Data source.
- Oracle HS Agent translates the SQL dialect to an SQL Statement that the non-Oracle database (PostgreSQL) understands and sends it via ODBC DSN.
- Gateway retrieves the data from PostgreSQL using the translated SQL Statement and returns the data to the Oracle Database.
- Finally, the Oracle Database passes the query results to the client application.
Let’s configure the above setup and try to retrieve data from PostgreSQL via psqlODBC.
Note: This blog uses PostgreSQL ODBC connector in the configuration, but some steps can be used for other ODBC drivers (including EDB ODBC and others).
Servers and software version details
The following server and software versions were used in the configuration. Software versions are not specific to this setup, however. The same steps can be used with the latest versions of the database/drivers.
- Oracle Server
- OS: CentOS 7.x
- Host: 172.16.210.133
- Oracle DB/Port: MIGDB/1521
- User/Password: miguser/miguser
- Software:
- Oracle 11g
- Latest unixODBC Driver Manager
- PostgreSQL ODBC Driver (psqlODBC)
- PostgreSQL Server
- OS: CentOS 7.x
- Host: 172.16.210.134
- PostgreSQL DB/Port: postgres/5432
- User/Password: postgres/admin
- Software:
- PostgreSQL v12
Configuration
Installation of Oracle and PostgreSQL servers is not covered here. If you have not installed these yet, please refer to the documentation below.
On PostgreSQL server
- At this point, the PostgreSQL v12 database should be up and running. For installation and configuration, please refer to PostgreSQL Quick Installation Guide.
On Oracle server
1. At this point, the Oracle 11g database should be up and running. For installation and configuration, please refer to Oracle Installation Guide.
2. Install the latest unixODBC Driver Manager:
[root@master ~]# yum install unixODBC*
3. Install the PostgreSQL ODBC Driver (psqlODBC):
[root@master ~]# yum install postgresql12-odbc*
4. Create an ODBC Data Source Name (DSN) for the Driver.
The unixODBC Driver Manager provides the odbcinst, odbc_config, and isql command line utilities used to configure and test the driver. Using odbcinst or odbc_config utilities, we can locate the unixODBC Driver Manager files location to pass driver information to create the DSN:
[root@master ~]# odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
…
[root@master ~]# odbc_config --odbcini --odbcinstini
/etc/odbc.ini
/etc/odbcinst.ini
From the above output, we can see two files (odbcinst.ini / odbc.ini) that we need to modify to enable the drivers we want to use. Basically, odbcinst.ini is a registry and configuration file for ODBC drivers in an environment, while odbc.ini is a registry and configuration file for ODBC DSNs (Data Source Names).
5. Configure the psqlODBC driver libraries in ODBC Driver file odbcinst.ini (create it if it doesn't exist) and add the lines below to the bottom of the file. These lines makes an entry for the driver:
[pgodbc]
Description = ODBC for PostgreSQL
Driver = /usr/lib/psqlodbcw.so
Setup = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/lib64/psqlodbcw.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
6. Create a Data Source Name (DSN) in the odbc.ini file; that driver manager reads this file to determine how to connect to the database using driver details specified in odbcinst.ini:
[pgdsn]
Driver = pgodbc
Description = PostgreSQL ODBC Driver
Database = postgres
Servername = 172.16.210.134
Username = postgres
Password = admin
Port = 5432
UseDeclareFetch = 1
CommLog = /tmp/pgodbclink.log
Debug = 1
LowerCaseIdentifier = 1
7. Using the isql utility, test the ODBC connection (psqlODBC) to the PostgreSQL Database DSN created:
[root@master ~]# isql pgdsn
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select current_database(),inet_server_port();
+-----------------------------------------------------+-----------------+
| current_database | inet_server_port|
+-----------------------------------------------------+-----------------+
| postgres | 5432 |
+-----------------------------------------------------+-----------------+
SQLRowCount returns -1
1 rows fetched
8. Using the DSN, create the Oracle Database Gateway for the ODBC (HS) service handler
- Create file initDSN.ora in location $ORACLE_HOME/hs/admin. In our case, pgdsn is the DSN, and we need to create a file called initpgdsn.ora:
[oracle@rgvt admin]$ more initpgdsn.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = pgdsn
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
- Adjust the listener ($ORACLE_HOME/network/admin/listener.ora) by adding the DSN entry in SID_LIST_LISTENER:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
(SID_DESC=
(SID_NAME = pgdsn)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(ENVS="LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/u01/app/oracle/product/11.2.0/db_1")
(PROGRAM=dg4odbc)
)
)
- Adjust the tnsname ($ORACLE_HOME/network/admin/tnsname.ora) by adding the DSN entry:
pgdsn =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA=(SID=pgdsn))
(HS=OK)
)
- Restart the Oracle Listener so the DSN related entries made to the networking files can take effect:
$ lsnrctl stop
$ lsnrctl start
- After restarting the Oracle listener, it will create an Oracle HS handler with a DSN name (pgdsn):
$ lsnrctl status | grep pgdsn
Service "pgdsn" has 1 instance(s).
Instance "pgdsn", status UNKNOWN, has 1 handler(s) for this service...
9. Now, create an Oracle database link using the DSN to access the PostgreSQL database:
SQL> create database link pglink connect to "postgres" identified by "admin" using 'pgdsn';
Database link created.
10. Access PostgreSQL data using Oracle Database Link created:
SQL> select count(*) from "pg_class"@pglink;
COUNT(*)
----------
894
Finally, consolidation of data in one database system is required for business, and it increases the mobility of the application. Today, most of the RDBMS support data access to different databases via extensions, modules, gateways and so on. This post highlights one of the application requirements to access PostgreSQL databases in Oracle via ODBC Driver. There are a variety of PostgreSQL ODBC Drivers available in the market, the steps demonstrated in this blog can be used to access PostgreSQL database with other ODBC drivers.