How to Use Oracle on Azure in PostgreSQL

Why integrate PostgreSQL with Oracle on Azure?

Integrating PostgreSQL with Oracle on Azure lets teams query operational Oracle data from analytics-oriented PostgreSQL without ETL delays. Using the oracle_fdw extension, PostgreSQL can read Oracle tables in real time, speed reporting, and keep a single SQL surface.

How do you install oracle_fdw on Azure hosts?

Install Oracle Instant Client 19c or later, then compile oracle_fdw from source or use your package manager. On Ubuntu: sudo apt-get install postgresql-15-oracle-fdw.Restart PostgreSQL and verify with CREATE EXTENSION oracle_fdw;.

What is the syntax to create a foreign server?

Use CREATE SERVER to register the remote Oracle instance, providing the Azure private endpoint, port, and service name. Add OPTIONS such as nls_lang if needed for character sets.

CREATE SERVER azure_oracle
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//my-ora.private.azure:1521/ORCLPDB1');

How do you map PostgreSQL users to Oracle credentials?

Create a user mapping so PostgreSQL knows which Oracle login to use.Store sensitive passwords in pg_passfile or secure GUCs.

CREATE USER MAPPING FOR app_user
SERVER azure_oracle
OPTIONS (user 'ORA_APP', password '********');

How can you import Oracle ecommerce tables?

Run IMPORT FOREIGN SCHEMA or define tables manually. The example below pulls the Orders, Products, and OrderItems tables.

IMPORT FOREIGN SCHEMA ecommerce
LIMIT TO (Orders, Products, OrderItems)
FROM SERVER azure_oracle
INTO public;

What does an end-to-end query look like?

After importing, query the foreign tables like local ones.Joins, aggregates, and CTEs are allowed; oracle_fdw pushes filters to Oracle when possible.

SELECT o.id,
c.name,
SUM(oi.quantity * p.price) AS order_total
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
JOIN OrderItems oi ON oi.order_id = o.id
JOIN Products p ON p.id = oi.product_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY o.id, c.name
ORDER BY order_total DESC;

Which best practices improve reliability?

Keep Instant Client versions in sync, restrict foreign tables to needed columns, monitor oracle_fdw wait events, add indexes on Oracle for pushed-down predicates, and place both servers in the same Azure region.

What are the most common mistakes?

Using mismatched client libraries causes ORA-12514 or segmentation faults—always match major versions.Forgetting to quote uppercase Oracle identifiers returns “column does not exist”; create lowercase synonyms or quote identifiers.

How do you drop the integration safely?

Revoke usage, drop foreign tables, and run DROP SERVER azure_oracle CASCADE;. Remove the extension only when no servers depend on it.

When should you switch to ETL instead?

Use oracle_fdw for real-time reads under light to moderate load. For heavy analytics or transformations, move data via Azure Data Factory or logical replication into PostgreSQL.

.