How to install the Oracle Client in PostgreSQL

Galaxy Glossary

How do I install the Oracle Instant Client and oracle_fdw for PostgreSQL?

Install the Oracle Instant Client and oracle_fdw so PostgreSQL can query Oracle tables through foreign data wrappers.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Table of Contents

Why install the Oracle client for PostgreSQL?

Installing the Oracle Instant Client plus the oracle_fdw extension lets PostgreSQL connect to Oracle databases and treat remote Oracle tables as regular Postgres tables—ideal for migrations, reporting, or hybrid stacks.

What prerequisites should I check?

64-bit Linux, build tools (gcc, make), libaio, matching Postgres server headers, and Oracle Instant Client 19c+ (Basic or Basic Lite).

How do I install the Oracle Instant Client?

sudo apt-get update && sudo apt-get install -y libaio1 alien wget https://download.oracle.com/otn_software/linux/instantclient/21_9/ oracle-instantclient-basiclite-21.9.0.0.0-1.x86_64.rpm sudo alien -i oracle-instantclient-basiclite-21.9.0.0.0-1.x86_64.rpm # make libraries visible echo "export LD_LIBRARY_PATH=/usr/lib/oracle/21.9/client64/lib:$LD_LIBRARY_PATH" | \ sudo tee /etc/profile.d/oracle.sh

How do I build and load `oracle_fdw`?

git clone https://github.com/laurenz/oracle_fdw.git cd oracle_fdw make && sudo make install -- inside psql CREATE EXTENSION IF NOT EXISTS oracle_fdw;

How do I create a foreign server and map users?

CREATE SERVER oracle_srv FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//ora-host:1521/ORCLPDB1'); CREATE USER MAPPING FOR current_user SERVER oracle_srv OPTIONS (user 'ora_user', password 'secret');

How can I import Oracle ecommerce tables?

IMPORT FOREIGN SCHEMA ecommerce FROM SERVER oracle_srv INTO public OPTIONS (case 'lower');

What does a cross-database query look like?

SELECT c.name, o.total_amount FROM customers c -- Oracle (foreign) JOIN orders o USING(id) -- PostgreSQL local WHERE o.order_date > CURRENT_DATE - INTERVAL '30 days';

Best practices for production?

Pin Instant Client versions, use least-privilege Oracle users, monitor session counts, and automate installs with Docker or Ansible.

Common pitfalls to avoid?

Missing LD_LIBRARY_PATH causes libclntsh.so not found; mismatched 32-/64-bit libraries prevent oracle_fdw from compiling.

How do I upgrade or uninstall?

Replace the Instant Client directory, recompile oracle_fdw, then ALTER EXTENSION oracle_fdw UPDATE. To remove, DROP EXTENSION oracle_fdw and delete client libraries.

Why How to install the Oracle Client in PostgreSQL is important

How to install the Oracle Client in PostgreSQL Example Usage


-- Show low-stock products sourced from Oracle while joining Postgres orders
SELECT p.id,
       p.name,
       p.stock,
       SUM(oi.quantity) AS units_sold_last_30d
FROM   products    p         -- foreign (Oracle)
JOIN   orderitems  oi ON oi.product_id = p.id  -- foreign (Oracle)
JOIN   orders      o  ON o.id        = oi.order_id -- local (Postgres)
WHERE  o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP  BY p.id, p.name, p.stock
HAVING p.stock < 10
ORDER  BY p.stock;

How to install the Oracle Client in PostgreSQL Syntax


-- OS-level installation (Ubuntu example)
sudo apt-get update && sudo apt-get install -y libaio1 alien
wget https://download.oracle.com/otn_software/linux/instantclient/21_9/oracle-instantclient-basiclite-21.9.0.0.0-1.x86_64.rpm
sudo alien -i oracle-instantclient-basiclite-21.9.0.0.0-1.x86_64.rpm

-- Build oracle_fdw extension
cd /usr/src
git clone https://github.com/laurenz/oracle_fdw.git
cd oracle_fdw
make && sudo make install

-- PostgreSQL commands
CREATE EXTENSION IF NOT EXISTS oracle_fdw;
CREATE SERVER oracle_srv FOREIGN DATA WRAPPER oracle_fdw
  OPTIONS (dbserver '//ora-host:1521/ORCLPDB1');
CREATE USER MAPPING FOR CURRENT_USER
  SERVER oracle_srv
  OPTIONS (user 'ora_user', password 'secret');

-- Import Oracle ecommerce schema
IMPORT FOREIGN SCHEMA ecommerce
  FROM SERVER oracle_srv
  INTO public
  OPTIONS (case 'lower');

-- Cross-database join using local and foreign tables
SELECT p.name, p.price, oi.quantity, o.order_date
FROM   products    p              -- Oracle table
JOIN   orderitems  oi ON oi.product_id = p.id  -- Oracle table
JOIN   orders      o  ON o.id       = oi.order_id   -- Postgres table
WHERE  p.stock < 10
ORDER  BY p.stock;

Common Mistakes

Ignoring the LD_LIBRARY_PATH. Without exporting the Instant Client path, PostgreSQL fails to find <code>libclntsh.so</code>; fix by adding the client directory to <code>LD_LIBRARY_PATH</code> or <code>/etc/ld.so.conf.d</code> and running <code>ldconfig</code>.
Mixing 32-bit Oracle libraries with a 64-bit PostgreSQL build. The compiler or runtime loader will complain about wrong ELF class. Always download the 64-bit Instant Client if PostgreSQL is 64-bit (the default on modern servers).

Frequently Asked Questions (FAQs)

Can I use Homebrew on macOS?

Yes. Run brew install oracle-instantclient, then build oracle_fdw with make. Remember to update DYLD_LIBRARY_PATH.

Does this require superuser rights in PostgreSQL?

You need superuser or a role with CREATE EXTENSION privilege to install oracle_fdw. After installation, regular users can query the foreign tables.

Is oracle_fdw production-ready?

Yes. Many companies run it in production. Test thoroughly, monitor connection counts, and pin client versions to avoid breaking changes.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.