Connect PostgreSQL to Oracle Database using Oracle fdw Foreign Data Wrappers

PostgreSQL’s Foreign Data Wrapper(FDW), allows us to access external data sources as if they were local tables. In this blog post, we’ll explain the details of oracle_fdw, a foreign data wrappers for accessing tables from Oracle database, its benefits, and how to use it. We can also use oracle fdw postgres extension in Amazon AWS RDS as well postgres database hosted in different server. You can also access remote postgresql table from local postgres database using postgres fdw extension. Another fdw extension parquet fdw is used to access parquet file as foreign table from local postgresql database instance.

What is oracle fdw?

The oracle_fdw extension is an example of postgres foreign data wrappers. Foreign data wrappers use to access remote data like database tables hosted in different database instances as well as different files like parquet, csvs located in remote server (like Amazon AWS).

Oracle_fdw allows you to access data in Oracle database table and views via foreign tables. Oracle_fdw accesses the corresponding data in the foreign Oracle database through Oracle Call Interface (OCI) library on the PostgreSQL server hosted in normal postgres server or hosted in Amazon RDS.

You Connect to Oracle from Amazon RDS for PostgreSQL using oracle_fdw. You can also connect to Oracle database and access Oracle data from PostgreSQL hosted in normal postgres database.

How to use oracle fdw postgres?

In order to access Oracle database from PostgreSql we need to configure oracle fdw postgres extension. You need to specified the below settings.

1. Remote Table and grant in Remote Oracle Database: You need to login the remote Oracle database and create table(if table exists then ignore this) and grant required access(Select, Update or Delete) of the table to the user. The user will be used to access the table from postgres. During User Mapping configuration this user has to be linked.
   
2. Install the oracle_fdw extension: oracle_fdw extension can be created using the CREATE EXTENSION command.
   
3. Create a foreign server: You need to define the foreign server using the connection details (host name, port number and database user name) for the remote Oracle database.
   
4. Create a Foreign User: You need to create a user (remote_ora_user) in the foreign Oracle database. You need to grant this user read-only/appropriate access to the table in question, and usage on the schema where your table lives.
   
5. Create User Mappings: User mappings establish the link between a local user in your PostgreSQL database and a user in the foreign Oracle database server. This ensures that the correct permissions to access the Oracle Database tables are in place.
   
6. Create Foreign Tables: Foreign tables represent the remote Oracle database table data in your local PostgreSQL database. You can query(or manipulate) foreign tables just like regular local tables.

1. Configuration in Remote Oracle Server

Before you access the remote oracle tables from postgres using the oracle_fdw extension you need to create the Oracle database table in the remote database. If table is already available you need to give proper grants to access/manipulate the data from postgres. Make sure you create the user mapping using the same Oracle User(admin here).

Oracle Table in the Remote Database

CREATE TABLE employees(
   employee_id NUMBER,
   employee_name varchar2(60),
   salary NUMBER,
   address varchar2(200),
CONSTRAINT employee_pk PRIMARY KEY (employee_id)
);

Grant to Remote Oracle User

GRANT SELECT, INSERT, UPDATE ON employees to admin;

2. Create Extension Oracle fdw Postgres

Firstly, we need to create the oracle fdw postgres extension into PostgreSQL, and then check the foreign data wrappers to make sure foreign data wrappers created correctly.

CREATE EXTENSION oracle_fdw;

After you create the foreign data wrappers you need to validate if the foreign data wrapper created correctly. You can list all the foreign data wrappers using table pg_foreign_data_wrapper. If oracle fdw postgres returns in below statement you are good to go the next steps.

SELECT fdwname FROM pg_foreign_data_wrapper;

3. Create a Foreign Server (associated with the Oracle Database)

You need to create a foreign Oracle server specifying the remote Oracle database connection details (e.g., Oracle Database name, host name or host IP address, Port number) to access it, and then list the foreign servers to make sure it was created correctly. Make sure that the proper access is given in advance so that the remote host can be connected correctly.

CREATE SERVER oracle_fdw_srv 
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver 'enoradev.xxyz.ap-south-1.rds.amazonaws.com:1521/ORCL');

Once you created Oracle Database foreign server, the next step would be grant privileges so that the local postgres user on the PostgreSQL side can access the foreign server.

GRANT USAGE ON FOREIGN SERVER oracle_fdw_srv TO postgres;

4. Create a User Mapping to the Oracle database Foreign Server

Additionally, you need to create a user mapping for the postgres user to the foreign server oracle_fdw_srv, using the remote database user name and password, and then list the user mappings to make sure it was created correctly.

CREATE USER MAPPING FOR postgres
SERVER oracle_fdw_srv 
OPTIONS (user 'oracle_user', password 'oracle123');

You need to validate if user mapping link to the foreign server and remote Oracle database user created correctly

select * from pg_user_mappings;

--OUTPUT
srvid	srvname	umuser	usename	umoptions
-----------------------------------------------
32788	32787	oracle_fdw_srv	16396	postgres	"{user=admin,password=xxxx}"

5. Create the Foreign Table for the FDW

Create the foreign table associated to the Oracle table or view in the foreign server oracle_fdw_srv, subsequently you need to check the foreign tables and display the definition of the table. Plese make sure foreign table was created correctly.

CREATE FOREIGN TABLE foreign_employee(
employee_id int OPTIONS (key 'true'),
employee_name varchar(64),
salary real,
address varchar(200))
SERVER oracle_fdw_srv OPTIONS (SCHEMA 'ADMIN' , TABLE 'EMPLOYEES');

6. Accessing Oracle Table from Postgres

You created the complete configuration in remote Oracle database as well as local postgres database. Now you are ready to use the foreign table, execute a sql query using the newly created foreign table in previous step in postgres to confirm that you can access it the remote Oracle data.

SELECT * FROM foreign_employee

Foreign User in Oracle Database

You need to have access to remote database user(e.g., admin). Alternatively, you need to create a remote user in Oracle database and grants access to the oracle table.

Data Manupulation of Oracle Data from postgres

If the remote oracle user(admin) is authorize to insert, update or delete operations then you can use the foreign table for insert, update/delete operation like from local database

--Insert into foreign table
INSERT INTO foreign_employees(employee_id,employee_name,salary,address) 
VALUES (101, 'Jow Singh', 45000, 'Andheri West, Mumbai');

--UPDATE foreign table
UPDATE foreign_employees
   SET address = 'Goregaon, Mumbai' 
 WHERE employee_id=101;

--Insert into foreign table
INSERT INTO foreign_employees(employee_id,employee_name,salary,address) 
VALUES (101, 'Jow Singh', 45000, 'Andheri West, Mumbai');

Complete Example of oracle fdw in PostgreSql

--In Oracle Database
---------------------------------------------------
-------Make sure grant executed after login into the remote database
----1. Create table in Oracle Database
CREATE TABLE employees(
employee_id NUMBER,
employee_name VARCHAR2(60),
salary NUMBER,
address VARCHAR2(200),
CONSTRAINT employee_pk PRIMARY KEY (employee_id)
);
----2. Grant access of remote table to the remote user
GRANT SELECT, INSERT, UPDATE ON employees to admin;

-----In PostgreSql Database------------------------

--1. Create Extension Postgres Foreign Data Wrapper
CREATE EXTENSION IF NOT EXISTS oracle_fdw;

--2. Create a Foreign Server
CREATE SERVER ora_fdw_server
FOREIGN DATA WRAPPER postgres_fdw
--oracle_fdw options
OPTIONS (dbname 'ora_fdw', host 'ec2-xx-xxx-176-55.eu-west-1.compute.amazonaws.com', port '5432')

--3. Create a User Mapping
CREATE USER MAPPING FOR postgres
SERVER ora_fdw_server
OPTIONS (user 'admin', password 'oracle123');

--4. Create Foreign Table for oracle_fdw Foreign Data Wrapper
CREATE FOREIGN TABLE foreign_employees(
    employee_id integer OPTIONS (key 'true'),
    employee_name varchar(64),
    salary real,
    address varchar(200)
    ) SERVER ora_fdw_server
    OPTIONS (table_name 'EMPLOYEES');

--5. Grant usgae of  Remote SERVER to the user
GRANT USAGE ON FOREIGN SERVER ora_fdw_server TO postgres;

--6. Insert records into Remote table from local daatabse
INSERT INTO foreign_employees(employee_id,employee_name,salary,address) 
VALUES (101, 'Jow Singh', 45000, 'Andheri West, Mumbai');

--7. Access the table from remote database
---postgres Foreign Data Wrapper select example
select * from foreign_employees;

The above example shows how to install postgres oracle_fdw extension.

Important Information of Oracle fdw Postgres

Effectively using Oracle_fdw requires understanding its nuances beyond basic mechanisms. This is a PostgreSQL interface for Oracle data. Here’s a peek into the lesser-known:

Pushdown Prowess:

• Beyond basic WHERE and JOIN pushdown, complex expressions with functions like SUBSTR, TO_CHAR, and CASE can also be pushed to Oracle, minimizing data movement.
• UDFs (User-Defined Functions) on the Oracle side can be leveraged for advanced pushdown, but ensure compatibility and performance implications.

Transaction Tidbits:

• While SERIALIZABLE might cause issues, READ COMMITTED isolation can offer a balance between consistency and concurrency for updates.
• Though Prepared Statements aren’t officially supported, some users report success with prepared cursors for improved performance in specific scenarios.

Data Type Delights:

• For number data types, pay close attention to precision and scale differences. Even minor variations can lead to rounding errors or truncation.
• Oracle_fdw offers type mapping options to bridge data type discrepancies gracefully.

Constraint Conundrums:

• While aligning constraints ensures smooth operations, be aware of limitations with CHECK constraints. Complex CHECKs might not push down, requiring local evaluation.
• For foreign tables referencing multiple Oracle tables, ensure constraints are defined on the referenced tables themselves for proper enforcement.

Bonus Beyond Pushdown:

• Materialized views on the Oracle side can be accessed as foreign tables, offering significant performance gains for frequently accessed data subsets.
• Partitioning both foreign and local tables can further optimize queries by leveraging partition pruning on both sides.

By delving into these often overlooked aspects, you can unlock the full potential of Oracle fdw postgres and achieve remarkable performance and data management agility.