Downloads

Blog

Bridging the Gap: Querying PostgreSQL Tables from an Oracle Database

December 17, 2025

Author

Nam Dinh Phuong

Insight for DBAs

PostgreSQL

Share this Post:

Database interoperability is a common requirement in enterprise environments. For users seeking to access Oracle data from a PostgreSQL instance, the native PostgreSQL extension oracle_fdw (Foreign Data Wrapper) is an excellent and efficient solution.

However, the question often arises: How do we perform the reverse? How can an Oracle SQL query execute a SELECT statement against a table residing on a remote PostgreSQL server?

The simple answer is: oracle_fdw does not support this reverse direction. The professional solution requires utilizing the PostgreSQL ODBC driver in conjunction with Oracle’s standard integration mechanisms.

We have successfully tested and documented a working configuration. This blog post walks you through the steps to achieve seamless bi-directional connectivity (in the Oracle-to-PostgreSQL direction). Below is a step-by-step guide to setting up this crucial link, allowing you to query PostgreSQL tables directly from your Oracle database using a standard database link.

Why Query PostgreSQL from Oracle?

There are several reasons why we might need to use both Oracle and PostgreSQL databases in a business environment. The coexistence of Oracle and PostgreSQL is not just common—it’s often a purposeful move to maximize operational and financial benefits. Many organizations are still using existing Oracle applications and want to migrate to PostgreSQL for new workloads such as data aggregation and advanced reporting, to familiarize themselves with the PostgreSQL database for OLTP and OLAP tasks. They are also exploring future migration plans to PostgreSQL. This simultaneously creates a safe environment for a comprehensive, practical assessment of PostgreSQL, allowing organizations to fully evaluate its suitability without impacting the production environment. It also facilitates a smoother transition, enabling a gradual and cost-effective move in the future. Furthermore, this demo allows Oracle database administrators to learn PostgreSQL faster, and vice versa, bringing PostgreSQL closer to Oracle and adding practical knowledge for managers.

Real-world use cases include:

- Real-time reporting across legacy Oracle and modern PostgreSQL systems

- Data validation during PostgreSQL migrations

- Cross-database joins for consolidated dashboards

- Legacy application integration without code changes

Example: Run SELECT COUNT(*) FROM postgres_schema.table@PG_LINK directly in Oracle utility tools.

Architecture Overview

Key Components:

- - - DG4ODBC: Oracle’s Heterogeneous Services agent
  - - postgresql-odbc: Official ODBC driver for PostgreSQL
  - - unixODBC: ODBC driver manager

- PostgreSQL database, Oracle database

When Oracle queries a PostgreSQL table through a database link, the request is routed through Oracle’s Heterogeneous Services agent (DG4ODBC). DG4ODBC hands the query to the system’s ODBC layer, managed by unixODBC, which selects and loads the PostgreSQL ODBC driver. The PostgreSQL ODBC driver then establishes a network connection to the PostgreSQL server, executes the query, and returns the results. These results flow back through the same chain—ODBC driver → unixODBC → DG4ODBC, before Oracle converts them into native Oracle data structures and displays them as if they came from a local table.

Note: The steps below focus on the configuration required for interoperability. While we’ve ensured the PostgreSQL side is correctly configured, support for Oracle-specific components (Gateways, Listener) should be directed toward Oracle support resources.

Step-by-Step Configuration

Prepare PostgreSQL (Create User and Test Data)

Firstly, we need to create a user and grant access to allow query the data:

-- Create dedicated user
CREATE USER dblinkuser ENCRYPTED PASSWORD 'Password$Secret';

-- Create and populate test table
CREATE TABLE test (id INTEGER);
INSERT INTO test VALUES (GENERATE_SERIES(1,1000));

-- Verify data
SELECT COUNT(*) FROM test;
 count
-------
  1000
(1 row)

-- Grant schema and table access
GRANT USAGE ON SCHEMA public TO dblinkuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dblinkuser;

-- Create dedicated user

CREATE USER dblinkuser ENCRYPTED PASSWORD 'Password$Secret';

-- Create and populate test table

CREATE TABLE test (id INTEGER);

INSERT INTO test VALUES (GENERATE_SERIES(1,1000));

-- Verify data

SELECT COUNT(*) FROM test;

count

-------

1000

(1 row)

-- Grant schema and table access

GRANT USAGE ON SCHEMA public TO dblinkuser;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO dblinkuser;

Install PostgreSQL ODBC Driver on Oracle Host

[oracle@vagrant01 ~]$ sudo yum install postgresql-odbc -y

1	[oracle@vagrant01 ~]$ sudo yum install postgresql-odbc -y

Output expected:

Installing:
 postgresql-odbc x86_64 10.03.0000-3.el8_6 ol8_appstream 430 k
Installing dependencies:
 unixODBC       x86_64 2.3.7-1.el8         ol8_appstream 458 k
Total download size: 888 k
Installed:
  postgresql-odbc-10.03.0000-3.el8_6.x86_64  unixODBC-2.3.7-1.el8.x86_64

Installing:

postgresql-odbc x86_64 10.03.0000-3.el8_6 ol8_appstream 430 k

Installing dependencies:

unixODBC x86_64 2.3.7-1.el8 ol8_appstream 458 k

Total download size: 888 k

Installed:

postgresql-odbc-10.03.0000-3.el8_6.x86_64 unixODBC-2.3.7-1.el8.x86_64

Verify the driver exists:

# ls /usr/lib64/psqlodbc.so
/usr/lib64/psqlodbc.so

1 2	# ls /usr/lib64/psqlodbc.so /usr/lib64/psqlodbc.so

Configure ODBC Data Source (/etc/odbc.ini)

# /etc/odbc.ini
[PG]
Description = PostgreSQL via ODBC
Driver = /usr/lib64/psqlodbc.so
ServerName = 127.0.1.128
Port = 5432
Database = postgres
Username = dblinkuser
Password = Password$Secret

# /etc/odbc.ini

[PG]

Description = PostgreSQL via ODBC

Driver = /usr/lib64/psqlodbc.so

ServerName = 127.0.1.128

Port = 5432

Database = postgres

Username = dblinkuser

Password = Password$Secret

Security Note: In production, avoid plaintext passwords. Use .pgpass, ODBC credential files, or another method. You can consider using a .pgpass file protected by strict OS-level permissions.

For more information, please visit the official link: https://www.postgresql.org/docs/current/libpq-pgpass.html

Configure DG4ODBC Initialization File

# vi $ORACLE_HOME/hs/admin/initPG.ora 
# initPG.ora - DG4ODBC initialization file
HS_FDS_CONNECT_INFO = PG
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
# Environment variables
set ODBCINI=/etc/odbc.ini

# vi $ORACLE_HOME/hs/admin/initPG.ora

# initPG.ora - DG4ODBC initialization file

HS_FDS_CONNECT_INFO = PG

HS_FDS_TRACE_LEVEL = 0

HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so

HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9

# Environment variables

set ODBCINI=/etc/odbc.ini

Because the listener often runs under Oracle utility startup scripts that do not source user shell profiles. As a result, the ODBC environment might not be available when DG4ODBC starts, leading to confusing connection failures. That’s why it is better to set ODBCINI directly in the initPG.ora file because it ensures that DG4ODBC always loads the correct ODBC configuration regardless of how the Oracle listener is started.

Configure Oracle Network Files

Edit tnsnames.ora

vi $ORACLE_HOME/network/admin/tnsnames.ora
Add the following entry:
PG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.1.128)(PORT = 1521))
    (CONNECT_DATA =
      (SID = PG)
    )
    (HS = OK)
  )

vi $ORACLE_HOME/network/admin/tnsnames.ora

Add the following entry:

PG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.1.128)(PORT = 1521))

(CONNECT_DATA =

(SID = PG)

)

(HS = OK)

)

Then, edit the listener.ora

vi $ORACLE_HOME/network/admin/listener.ora

1	vi $ORACLE_HOME/network/admin/listener.ora

Add the following SID descriptor:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PG)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (PROGRAM = dg4odbc)
    )
  )

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PG)

(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)

(PROGRAM = dg4odbc)

)

Restart Listener:

lsnrctl restart

1	lsnrctl restart

Test TNS Resolution:

tnsping PG

1	tnsping PG

Expected output:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.1.128)(PORT = 1521)) (CONNECT_DATA = (SID = PG)) (HS = OK))
OK (0 msec)

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.1.128)(PORT = 1521)) (CONNECT_DATA = (SID = PG)) (HS = OK))

OK (0 msec)

Create Database Link in Oracle to PostgreSQL

SQL> CREATE DATABASE LINK PG
  CONNECT TO "dblinkuser"
  IDENTIFIED BY "Password$Secret"
  USING 'PG';
Database link created.

SQL> CREATE DATABASE LINK PG

CONNECT TO "dblinkuser"

IDENTIFIED BY "Password$Secret"

USING 'PG';

Database link created.

Note: Double quotes around “dblinkuser” preserve case sensitivity.

Query PostgreSQL from Oracle

SQL> SELECT * FROM "public"."test"@PG WHERE ROWNUM < 10;
        id
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

SQL> SELECT * FROM "public"."test"@PG WHERE ROWNUM < 10;

----------

SQL> SELECT COUNT(*) FROM "public"."test"@PG;
  COUNT(*)
----------
      1000

SQL> SELECT COUNT(*) FROM "public"."test"@PG;

COUNT(*)

----------

1000

Great! Oracle is now reading live data from PostgreSQL.

The successful query execution confirms that we can query a PostgreSQL table directly from the Oracle database, providing the necessary reverse interoperability often sought after by users familiar with oracle_fdw.

Conclusion

- This configuration is not officially supported by Oracle for PostgreSQL—use at your own risk.

- Performance may be slower than expected latency for native connections, depending on the network and data volume. Querying PostgreSQL from Oracle via DG4ODBC is inherently slower than native connections. The main factors contributing to this is translation overhead. Because DG4ODBC and the PostgreSQL ODBC driver introduce extra processing to convert Oracle SQL to ODBC calls, and then ODBC results back into Oracle formats. Besides, data must travel over TCP/IP between Oracle and PostgreSQL, which can add delays, especially for large result sets.

In short: The primary performance impact comes from the combination of translation overhead and the network/data volume, rather than the network alone.

- Data types and advanced features may not map perfectly; always test thoroughly.

While oracle_fdw elegantly handles PostgreSQL to Oracle access, the reverse direction requires DG4ODBC + PostgreSQL ODBC. This lab-validated setup proves:

You can run read queries inside Oracle SQL, reliably and with full SQL compatibility between PostgreSQL and Oracle.

The key to enabling Oracle-to-PostgreSQL reporting lies squarely with the highly capable and standards-compliant PostgreSQL ODBC driver. This solution ensures seamless data access, enabling you to utilize PostgreSQL for critical reporting and analytics while planning your comprehensive migration strategy.