Database interoperability is a standard requirement in enterprise environments. For customers in search of to entry Oracle information from a PostgreSQL occasion, the native PostgreSQL extension oracle_fdw (International Information Wrapper) is a wonderful and environment friendly answer.
Nonetheless, the query usually arises: How will we carry out the reverse? How can an Oracle SQL question execute a SELECT assertion in opposition to a desk residing on a distant PostgreSQL server?
The straightforward reply is: oracle_fdw doesn’t assist this reverse route. The skilled answer requires using the PostgreSQL ODBC driver along side Oracle’s commonplace integration mechanisms.
Now we have efficiently examined and documented a working configuration. This weblog submit walks you thru the steps to attain seamless bi-directional connectivity (within the Oracle-to-PostgreSQL route). Beneath is a step-by-step information to organising this important hyperlink, permitting you to question PostgreSQL tables immediately out of your Oracle database utilizing an ordinary database hyperlink.
Why Question PostgreSQL from Oracle?
There are a number of explanation why we would want to make use of each Oracle and PostgreSQL databases in a enterprise surroundings. The coexistence of Oracle and PostgreSQL is not only widespread—it’s usually a purposeful transfer to maximise operational and monetary advantages. Many organizations are nonetheless utilizing current Oracle functions and wish to migrate to PostgreSQL for brand new workloads similar to information aggregation and superior reporting, to familiarize themselves with the PostgreSQL database for OLTP and OLAP duties. They’re additionally exploring future migration plans to PostgreSQL. This concurrently creates a secure surroundings for a complete, sensible evaluation of PostgreSQL, permitting organizations to totally consider its suitability with out impacting the manufacturing surroundings. It additionally facilitates a smoother transition, enabling a gradual and cost-effective transfer sooner or later. Moreover, this demo permits Oracle database directors to be taught PostgreSQL sooner, and vice versa, bringing PostgreSQL nearer to Oracle and including sensible data for managers.
Actual-world use circumstances embody:
- Actual-time reporting throughout legacy Oracle and trendy PostgreSQL programs
- Information validation throughout PostgreSQL migrations
- Cross-database joins for consolidated dashboards
- Legacy software integration with out code modifications
Instance: Run SELECT COUNT(*) FROM postgres_schema.desk@PG_LINK immediately in Oracle utility instruments.
Structure Overview
Key Elements:
-
- DG4ODBC: Oracle’s Heterogeneous Providers agent
- postgresql-odbc: Official ODBC driver for PostgreSQL
- unixODBC: ODBC driver supervisor
- PostgreSQL database, Oracle database
When Oracle queries a PostgreSQL desk by way of a database hyperlink, the request is routed by way of Oracle’s Heterogeneous Providers agent (DG4ODBC). DG4ODBC palms the question to the system’s ODBC layer, managed by unixODBC, which selects and masses the PostgreSQL ODBC driver. The PostgreSQL ODBC driver then establishes a community connection to the PostgreSQL server, executes the question, and returns the outcomes. These outcomes circulation again by way of the identical chain—ODBC driver → unixODBC → DG4ODBC, earlier than Oracle converts them into native Oracle information buildings and shows them as in the event that they got here from an area desk.
Notice: The steps under give attention to the configuration required for interoperability. Whereas we’ve ensured the PostgreSQL aspect is appropriately configured, assist for Oracle-specific elements (Gateways, Listener) needs to be directed towards Oracle assist sources.
Step-by-Step Configuration
Put together PostgreSQL (Create Person and Check Information)
Firstly, we have to create a person and grant entry to permit question the info:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
— Create devoted person CREATE USER dblinkuser ENCRYPTED PASSWORD ‘Password$Secret’;
— Create and populate take a look at desk CREATE TABLE take a look at (id INTEGER); INSERT INTO take a look at VALUES (GENERATE_SERIES(1,1000));
— Confirm information SELECT COUNT(*) FROM take a look at; depend ———– 1000 (1 row)
— Grant schema and desk entry GRANT USAGE ON SCHEMA public TO dblinkuser; GRANT SELECT ON ALL TABLES IN SCHEMA public TO dblinkuser; |
Set up PostgreSQL ODBC Driver on Oracle Host
|
[oracle@vagrant01 ~]$ sudo yum set up postgresql–odbc –y |
Output anticipated:
|
Putting in: postgresql–odbc x86_64 10.03.0000–3.el8_6 ol8_appstream 430 ok Putting in dependencies: unixODBC x86_64 2.3.7–1.el8 ol8_appstream 458 ok Whole obtain measurement: 888 ok Put in: postgresql–odbc–10.03.0000–3.el8_6.x86_64 unixODBC–2.3.7–1.el8.x86_64 |
Confirm the driving force exists:
|
# ls /usr/lib64/psqlodbc.so /usr/lib64/psqlodbc.so |
Configure ODBC Information Supply (/and so on/odbc.ini)
|
# /and so on/odbc.ini [PG] Description = PostgreSQL by way of ODBC Driver = /usr/lib64/psqlodbc.so ServerName = 127.0.1.128 Port = 5432 Database = postgres Username = dblinkuser Password = Password$Secret |
Safety Notice: In manufacturing, keep away from plaintext passwords. Use .pgpass, ODBC credential information, or one other methodology. You possibly can think about using a .pgpass file protected by strict OS-level permissions.
For extra data, please go to the official hyperlink: https://www.postgresql.org/docs/present/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 # Surroundings variables set ODBCINI=/and so on/odbc.ini |
As a result of the listener usually runs below Oracle utility startup scripts that don’t supply person shell profiles. Consequently, the ODBC surroundings won’t be accessible when DG4ODBC begins, resulting in complicated connection failures. That’s why it’s higher to set ODBCINI immediately within the initPG.ora file as a result of it ensures that DG4ODBC at all times masses the right ODBC configuration no matter how the Oracle listener is began.
Configure Oracle Community Information
Edit tnsnames.ora
|
vi $ORACLE_HOME/community/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/community/admin/listener.ora |
Add the next 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) ) ) |
Restart Listener:
Check TNS Decision:
Anticipated output:
|
Used TNSNAMES adapter to resolve the alias Making an attempt to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.1.128)(PORT = 1521)) (CONNECT_DATA = (SID = PG)) (HS = OK)) OK (0 msec) |
Create Database Hyperlink in Oracle to PostgreSQL
|
SQL> CREATE DATABASE LINK PG CONNECT TO “dblinkuser” IDENTIFIED BY “Password$Secret” USING ‘PG’; Database hyperlink created. |
Notice: Double quotes round “dblinkuser” protect case sensitivity.
Question PostgreSQL from Oracle
|
SQL> SELECT * FROM “public”.“take a look at”@PG WHERE ROWNUM < 10; id ————— 1 2 3 4 5 6 7 8 9 |
|
SQL> SELECT COUNT(*) FROM “public”.“take a look at”@PG; COUNT(*) ————— 1000 |
Nice! Oracle is now studying reside information from PostgreSQL.
The profitable question execution confirms that we are able to question a PostgreSQL desk immediately from the Oracle database, offering the required reverse interoperability usually wanted by customers acquainted with oracle_fdw.
Conclusion
- This configuration is not formally supported by Oracle for PostgreSQL—use at your individual danger.
- Efficiency could also be slower than anticipated latency for native connections, relying on the community and information quantity. Querying PostgreSQL from Oracle by way of DG4ODBC is inherently slower than native connections. The primary components contributing to that is translation overhead. As a result of DG4ODBC and the PostgreSQL ODBC driver introduce additional processing to transform Oracle SQL to ODBC calls, after which ODBC outcomes again into Oracle codecs. Apart from, information should journey over TCP/IP between Oracle and PostgreSQL, which might add delays, particularly for giant outcome units.
Briefly: The first efficiency affect comes from the mixture of translation overhead and the community/information quantity, relatively than the community alone.
- Information sorts and superior options could not map completely; at all times take a look at completely.
Whereas oracle_fdw elegantly handles PostgreSQL to Oracle entry, the reverse route requires DG4ODBC + PostgreSQL ODBC. This lab-validated setup proves:
You can run learn queries inside Oracle SQL, reliably and with full SQL compatibility between PostgreSQL and Oracle.
The important thing to enabling Oracle-to-PostgreSQL reporting lies squarely with the extremely succesful and standards-compliant PostgreSQL ODBC driver. This answer ensures seamless information entry, enabling you to make the most of PostgreSQL for vital reporting and analytics whereas planning your complete migration technique.
