Skip to content

Postgres 2 Oracle database link

Oracle FDW

Postgres foreign data wrapper for Oracle database

Introduction

This is part 2 of #fdw foreign data wrappers series of posts. In part 1 it was described what foreign data mean in Postgres as well as provided an example of connection to MySQL database. In this post we will deal with accessing data in Oracle database.

oracle_fdw

In order to connect to Oracle database from Postgres it's required to manually compile and install oracle_fdw extension to your postrges database. If you use Postgres on Windows, which is not a common thing, you can benefit and use precompiled binaries available on mentioned above GitHub repo. After installing them you will be able to execute CREATE EXTENSION command from within your postgres database and setup foreign server metadata via CREATE SERVER command with target oracle server details.

Docker image

Similarly to MySQL I've created a postgres image with oracle_fdw extension installed. You could pick it up from docker hub. It's built upon official postgres debian based image. At the time of writing it was corresponding to postgres 12.4 version. Section below describes how to use the image.

Demo

Demo infrastructure is spinned up via docker compose files. For our case we need two instances. One for Oracle and one for Postgres. Inside oracle instance we will create a single table and populate it with 3 rows. From postgres instance we will select, insert and delete rows from this table. After that we will select from this table under oracle instance again to confirm that data has changed accordingly.

For oracle image we will use the only available oracle image on docker hub - 12.2.0.1.

Note: For some reason launching compose file configuration via podman on linux doesn't start oracle services within container. This makes it impossible to connect to oracle database. In order to workaround this we will create network and two containers manually. If you know how to overcome this, please share your approach in a comments.

Note: If you use docker then just replace podman with docker in all commands below.

Let's create network mynet. In case of SE linux we need to do this in a rootfull mode. Thus all commands are prefixed via sudo. 

$ sudo podman network create mynet
/etc/cni/net.d/mynet.conflist

$ sudo podman network ls
NAME    VERSION  PLUGINS
podman  0.4.0    bridge,portmap,firewall,tuning
mynet   0.4.0    bridge,portmap,firewall,dnsname

Create oracle instance and add it to the mynet network. It takes around 1.5 mins to make database up and running within container. 

$ sudo podman run -d --name oracle -h oracle -p 1521:1521 --network mynet docker.io/store/oracle/database-enterprise:12.2.0.1

Create postgres instance and add it to the mynet network. 

$ sudo podman run -d --name postgres -h postgres -p 5432:5432 --network mynet -e POSTGRES_PASSWORD=postgres chumaky/postgres_oracle_fdw

Oracle image consist of root container orclcdb database and one pluggable orclpdb1 database. Administrator account sys has Oradoc_db1 password.

Connect to pluggable database within oracle instance. 

$ sqlplus sys/Oradoc_db1@//localhost:1521/orclpdb1.localdomain as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Create test user with t(id int) table and populate it with three values 1, 2, 3. 

SQL> create user test identified by test default tablespace users temporary tablespace temp quota unlimited on users;
User created.

SQL> grant connect to test;
Grant succeeded.

SQL> grant create table to test;
Grant succeeded.

SQL> conn test/test@//localhost:1521/orclpdb1.localdomain
Connected.

SQL> create table t as select rownum id from dual connect by level <= 3;
Table created.

SQL> alter table t add constraint t_pk primary key (id);
Table altered.

SQL> select * from t;

 ID
----------
 1
 2
 3

Start within postgres instance interactive psql shell connected to default postgres database as default postgres superuser account. 

$ sudo podman exec -it postgres psql postgres postgres
psql (12.4 (Debian 12.4-1.pgdg100+1))
Type "help" for help.

postgres=#

Create foreign server to access oracle database. Also create oratab foreign table object which will be pointed to the test.t table in oracle database. 

=> create schema oracle_fdw;
CREATE SCHEMA
=> create extension oracle_fdw schema oracle_fdw;
CREATE EXTENSION
=> create server oracle foreign data wrapper oracle_fdw options (dbserver '//oracle:1521/orclpdb1.localdomain');
CREATE SERVER
=> create user mapping for postgres server oracle options (user 'test', password 'test');
CREATE USER MAPPING
=> create foreign table oratab (id int options (key 'true')) server oracle options (schema 'TEST', table 'T');
CREATE FOREIGN TABLE

Select data from the foreign table. 

=> select * from oratab;
 id
----
  1
  2
  3
(3 rows)

DML operations also work 

=> insert into oratab values (4);
INSERT 0 1
=> delete from oratab where id = 1;
DELETE 1

=> select * from oratab;
 id
----
  2
  3
  4
(3 rows)

Login into oracle instance as test user and check that data is already changed. 

$ sqlplus test/test@//localhost:1521/orclpdb1.localdomain

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from t;

  ID
----------
  2
  3
  4

It should be added that in order to enable DML operations against foreign table corresponding source table in oracle database must have a PK or UK constraint defined. Otherwise only SELECT is allowed.

Summary

oracle_fdw extension allows SELECT and DML operations to be triggered from postgres and applied to oracle database. For quickier setup there is a chumaky/postgres_oracle_fdw docker image is available on docker hub. It contains oracle_fdw extension pre-installed as well as oracle client with sqlplus utility.

Comments