DBA Tips: Connecting PostgreSQL to an Oracle database (oracle

PostgreSQL provides foreign data wrappers (FDW) as a mechanism for accessing various external data sources.

This article explains how to access Oracle databases using oracle_fdw, a foreign data wrapper for Oracle databases.

1. Download and Install Postgres server and components

[root] #

dnf -y install postgresql14-server.x86_64 postgresql14.x86_64 postgresql14-devel.x86_64

dnf -y install redhat-rpm-config.noarch

dnf -y install gcc.x86_64

2. Create user and postgres database

[postgres] $ postgres=#

CREATE USER stock WITH password '***';
alter user stock WITH SUPERUSER;
alter user stock WITH createdb;
CREATE DATABASE dbquik WITH OWNER = stock ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TABLESPACE = pg_default CONNECTION LIMIT = -1;

3. Download and Install Oracle Instant Client

-- download packages from patfrom oracle.com

https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

-- installation

[root] #

yum -y install oracle-instantclient19.15-basic-19.15.0.0.0-2.x86_64.rpm oracle-instantclient19.15-devel-19.15.0.0.0-2.x86_64.rpm oracle-instantclient19.15-sqlplus-19.15.0.0.0-2.x86_64.rpm

cd /usr/lib/oracle/19.15/client64/lib/
mkdir ./oci
ln -s /usr/include/oracle/19.15/client64 /usr/lib/oracle/19.15/client64/lib/oci/include

4. Add variables to postgres profile (~/.bash_profile)

[postgres] $

export PATH=/usr/pgsql-14/bin:/usr/sbin:/usr/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/lib/oracle/19.15/client64/bin
export PGDATA=/srv/postgres/data/14
export ORACLE_HOME=/usr/lib/oracle/19.15/client64
export LD_LIBRARY_PATH=/usr/include/oracle/19.15/client64:/usr/lib/oracle/19.15/client64/lib

5. Download and install oracle_fdw

-- download and unzip to tmp/oracle_fdw-master

https://github.com/laurenz/oracle_fdw/tree/master

-- set up environment variables

#[root]
export PATH=/usr/pgsql-14/bin:/usr/sbin:/usr/bin:/usr/sbin:/usr/bin:/sbin:/bin
export PGDATA=/srv/postgres/data/14
export ORACLE_HOME=/usr/lib/oracle/19.15/client64
export LD_LIBRARY_PATH=/usr/include/oracle/19.15/client64:/usr/lib/oracle/19.15/client64/lib
-- installation

cd /tmp/oracle_fdw-master
make
make install

6. Create extension for postgres database dbquik
[postgres] $ postgres=#
\c dbquik
CREATE EXTENSION oracle_fdw;
SELECT * FROM pg_extension;

7. Configure oracle_fdw as PostgreSQL superuser like this
CREATE SERVER ora_dbquik FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.0.77:1521/ora_dbquik');

8. Let's allow a user to use the foreign server
GRANT USAGE ON FOREIGN SERVER ora_dbquik TO stock;

9. Connect to PostgreSQL as pguser and define
CREATE USER MAPPING FOR stock SERVER ora_dbquik OPTIONS (user 'ora_stock', password '***');
-- test connection
SELECT oracle_diag('ora_dbquik');

10. Create the foreign table that should look like the table in Oracle

[postgres] $ postgres=#

\c dbquik

CREATE FOREIGN TABLE QUOTES_ANALYST_15M

( DT_TRADING timestamp, COD_TOOL varchar(30), PRICE_LOW numeric, PRICE_HIGH numeric, PRICE_OPEN numeric, PRICE_CLOSE numeric, RSI numeric, STOCHASTIC numeric, BB_UP numeric, BB_DOWN numeric, BB_SMA numeric)

SERVER ora_dbquik OPTIONS (schema 'STOCK', table 'QUOTES_ANALYST_15M');

11. List Foreign tables

[postgres] $ postgres=#

\c dbquik

\detr