Usually the requests we get are around getting data from Oracle into PostgreSQL, but sometimes also the opposite is true and so it happened recently. Depending on the requirements, usually real time vs. delayed/one-shot, there are several options when you want to read from Oracle into PostgreSQL. One common of way of doing this is to use the foreign data wrapper for Oracle (the post is quite old but still valid) or to use some kind of logical replication when data needs to be up to date. The question is what options do you have for the other way around? When it comes to logical replication there are several tools out there which might work for your needs but what options do you have that compare more to the Oracle foreign data wrapper when data does not need to be up to date?
Quite old, but still available and usable is ODBC and if you combine this with Oracle’s Database Heterogeneous Connectivity this gives you one option for reading from data PostgreSQL into Oracle. Initially I wanted to write that down in document for the customer, but as we like to share here, it turned out to be become a blog post available to everybody.
My target Oracle system is an Oracle Database 21c Express Edition Release 21.0.0.0.0 running on Oracle Linux 8.10:
[oracle@ora ~]$ cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="8.10"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="8.10"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Oracle Linux Server 8.10"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:8:10:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://github.com/oracle/oracle-linux"
ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8"
ORACLE_BUGZILLA_PRODUCT_VERSION=8.10
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=8.10
[oracle@ora ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Fri Mar 6 04:14:38 2026
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> set lines 300
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
SQL>
My source system is a PostgreSQL 17.5 running on openSUSE Leap 16:
postgres@:/home/postgres/ [175] cat /etc/os-release
NAME="openSUSE Leap"
VERSION="16.0"
ID="opensuse-leap"
ID_LIKE="suse opensuse"
VERSION_ID="16.0"
PRETTY_NAME="openSUSE Leap 16.0"
ANSI_COLOR="0;32"
CPE_NAME="cpe:/o:opensuse:leap:16.0"
BUG_REPORT_URL="https://bugs.opensuse.org"
HOME_URL="https://www.opensuse.org/"
DOCUMENTATION_URL="https://en.opensuse.org/Portal:Leap"
LOGO="distributor-logo-Leap"
postgres@:/home/postgres/ [175] ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host noprefixroute
valid_lft forever preferred_lft forever
2: enp1s0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 52:54:00:01:dd:de brd ff:ff:ff:ff:ff:ff
altname enx52540001ddde
inet 192.168.122.158/24 brd 192.168.122.255 scope global dynamic noprefixroute enp1s0
valid_lft 3480sec preferred_lft 3480sec
inet6 fe80::b119:5142:93ab:b6aa/64 scope link noprefixroute
valid_lft forever preferred_lft forever
postgres@:/home/postgres/ [175] psql -c "select version()"
version
------------------------------------------------------------------------------------
PostgreSQL 17.5 dbi services build on x86_64-linux, compiled by gcc-15.0.1, 64-bit
(1 row)
postgres@:/home/postgres/ [175] psql -c "show port"
port
------
5433
(1 row)
postgres@:/home/postgres/ [175] psql -c "show listen_addresses"
listen_addresses
------------------
*
(1 row)
postgres@:/home/postgres/ [175] cat $PGDATA/pg_hba.conf | grep "192.168.122"
host all all 192.168.122.0/24 trust
So far for the baseline.
Obviously. the first step is to have a ODBC connection working from the Oracle host to the PostgreSQL host, without involving the Oracle database. For this we need unixODBC and on top of that we need the ODBC driver for PostgreSQL. Both are available as packages on Oracle Linux 8 (should be true for any distribution based on Red Hat), so that is easy to get installed:
[oracle@ora ~]$ sudo dnf install -y unixODBC postgresql-odbc
Last metadata expiration check: 0:09:06 ago on Fri 06 Mar 2026 01:38:40 AM EST.
Dependencies resolved.
=============================================================================================
Package Architecture Version Repository Size
=============================================================================================
Installing:
postgresql-odbc x86_64 10.03.0000-3.el8_6 ol8_appstream 430 k
unixODBC x86_64 2.3.7-2.el8_10 ol8_appstream 453 k
Installing dependencies:
libpq x86_64 13.23-1.el8_10 ol8_appstream 199 k
libtool-ltdl x86_64 2.4.6-25.el8 ol8_baseos_latest 58 k
Transaction Summary
=============================================================================================
Install 4 Packages
Total download size: 1.1 M
Installed size: 3.4 M
Downloading Packages:
(1/4): libtool-ltdl-2.4.6-25.el8.x86_64.rpm 778 kB/s | 58 kB 00:00
(2/4): libpq-13.23-1.el8_10.x86_64.rpm 2.2 MB/s | 199 kB 00:00
(3/4): postgresql-odbc-10.03.0000-3.el8_6.x86_64.rpm 4.4 MB/s | 430 kB 00:00
(4/4): unixODBC-2.3.7-2.el8_10.x86_64.rpm 14 MB/s | 453 kB 00:00
---------------------------------------------------------------------------------------
Total 9.9 MB/s | 1.1 MB 00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : libpq-13.23-1.el8_10.x86_64 1/4
Installing : libtool-ltdl-2.4.6-25.el8.x86_64 2/4
Running scriptlet: libtool-ltdl-2.4.6-25.el8.x86_64 2/4
Installing : unixODBC-2.3.7-2.el8_10.x86_64 3/4
Running scriptlet: unixODBC-2.3.7-2.el8_10.x86_64 3/4
Installing : postgresql-odbc-10.03.0000-3.el8_6.x86_64 4/4
Running scriptlet: postgresql-odbc-10.03.0000-3.el8_6.x86_64 4/4
Verifying : libtool-ltdl-2.4.6-25.el8.x86_64 1/4
Verifying : libpq-13.23-1.el8_10.x86_64 2/4
Verifying : postgresql-odbc-10.03.0000-3.el8_6.x86_64 3/4
Verifying : unixODBC-2.3.7-2.el8_10.x86_64 4/4
Installed:
libpq-13.23-1.el8_10.x86_64 libtool-ltdl-2.4.6-25.el8.x86_64 postgresql-odbc-10.03.0000-3.el8_6.x86_64 unixODBC-2.3.7-2.el8_10.x86_64
Complete!
Having that in place, lets check which configuration we need to touch:
[oracle@ora ~]$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[oracle@ora ~]$ odbc_config --odbcini --odbcinstini
/etc/odbc.ini
/etc/odbcinst.ini
odbcinst.ini is used to configure one or more ODBC drivers, odbc.ini is used to configure the data sources. There are several examples in the driver configuration file, but we’re only interested in PostgreSQL:
[oracle@ora ~]$ grep pgodbc -A 6 /etc/odbcinst.ini
[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
For the data source get the IP address/hostname, port, user and password for your PostgreSQL database and adapt the configuration below:
[oracle@ora ~]$ cat /etc/odbc.ini
[pgdsn]
Driver = pgodbc
Description = PostgreSQL ODBC Driver
Database = postgres
Servername = 192.168.122.158
Username = postgres
Password = postgres
Port = 5433
UseDeclareFetch = 1
CommLog = /tmp/pgodbclink.log
Debug = 1
LowerCaseIdentifier = 1
If you got it all right, you should be able to establish a connection to PostgreSQL using the “isql” utility:
[oracle@ora ~]$ isql -v pgdsn
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select datname from pg_database;
+----------------------------------------------------------------+
| datname |
+----------------------------------------------------------------+
| postgres |
| template1 |
| template0 |
+----------------------------------------------------------------+
SQLRowCount returns -1
3 rows fetched
SQL> quit;
[oracle@ora ~]$
This proves, that connectivity from the Oracle host to the PostgreSQL database is fine and ODBC is working properly.
Now we need to tell the Oracle Listener and the Oracle database how to use this configuration. This requires a configuration for the listener and a configuration for the heterogeneous services. For configuring the listener we need to know which configuration file the listener is using, but this is easy to find out:
[oracle@ora ~]$ lsnrctl status | grep "Listener Parameter File"
Listener Parameter File /opt/oracle/homes/OraDBHome21cXE/network/admin/listener.ora
The content that needs to go into this file is:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /opt/oracle/product/21c/dbhomeXE/)
)
(SID_DESC=
(SID_NAME = pgdsn)
(ORACLE_HOME = /opt/oracle/product/21c/dbhomeXE/)
(ENVS="LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/opt/oracle/product/21c/dbhomeXE/lib/)
(PROGRAM=dg4odbc)
)
)
LD_LIBRARY_PATH must include the PATH to the ODBC driver, and “PROGRAM” must be “dg4odbc”.
Continue by adding the configuration for the heterogeneous services, which in my case goes here:
[oracle@ora ~]$ cat /opt/oracle/homes/OraDBHome21cXE/hs/admin/initpgdsn.ora
HS_FDS_CONNECT_INFO = pgdsn
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_TRACE_FILE_NAME = /tmp/hs.trc
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
set ODBCINI=/etc/odbc.ini
Create the connection definition in tnsnames.ora:
[oracle@ora ~]$ cat /opt/oracle/homes/OraDBHome21cXE/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/homes/OraDBHome21cXE/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora.it.dbi-services.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
pgdsn =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST = ora.it.dbi-services.com)(PORT = 1521))
(CONNECT_DATA=(SID=pgdsn))
(HS=OK)
)
Restart the listener and make sure that the service “pgdsn” shows up:
[oracle@ora ~]$ lsnrctl stop
LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 06-MAR-2026 08:22:52
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora.it.dbi-services.com)(PORT=1521)))
The command completed successfully
[oracle@ora ~]$ lsnrctl start
LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 06-MAR-2026 08:22:53
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Starting /opt/oracle/product/21c/dbhomeXE//bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 21.0.0.0.0 - Production
System parameter file is /opt/oracle/homes/OraDBHome21cXE/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/ora/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora.it.dbi-services.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora.it.dbi-services.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 21.0.0.0.0 - Production
Start Date 06-MAR-2026 08:22:53
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /opt/oracle/homes/OraDBHome21cXE/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/ora/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora.it.dbi-services.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "pgdsn" has 1 instance(s).
Instance "pgdsn", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Finally, create a database link in Oracle and verify that you can ask for data from PostgreSQL:
[oracle@ora ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Fri Mar 6 08:27:23 2026
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> create database link pglink connect to "postgres" identified by "postgres" using 'pgdsn';
Database link created.
SQL> select "datname" from "pg_database"@pglink;
datname
--------------------------------------------------------------------------------
postgres
template1
template0
SQL>
That’s it.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/FRJ_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2024/04/SIT_web.png)