Infrastructure at your Service

Daniel Westermann

Connecting your PostgreSQL instance to an Oracle database

For integrating data from other systems PostgreSQL has the concept of foreign data wrappers. Many of these exist for different types of systems. In this post I’ll look into how you may connect PostgreSQL to Oracle.

The home of the foreign data wrapper for Oracle (as most of the PostgreSQL stuff) is on github. If you do not want to clone the git repository you can download it from the PostgreSQL extension network, too.

For building oracle_fdw you’ll need to download the Basic and SDK package of the Oracle instance client. For simplicity I’ll use the rpm versions.

Make sure that pg_config is in your path and you have installed both the Oracle instant client basic and sdk packages:

postgres@oel7:/home/postgres/$ which pg_config
/u01/app/postgres/product/94/db_1/bin/pg_config
postgres@oel7:/home/postgres/$ unzip oracle_fdw-1.2.0.zip
postgres@oel7:/home/postgres/$ sudo yum localinstall oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
postgres@oel7:/home/postgres/$ cd oracle_fdw-1.2.0
postgres@oel7:/home/postgres/oracle_fdw-1.2.0/$ make
postgres@oel7:/home/postgres/oracle_fdw-1.2.0/$ make install

Quite easy. Let’s see if it really works:

postgres@oel7:/home/postgres/oracle_fdw-1.2.0/ [PG1] psql postgres
psql (9.4.1dbi services)
Type "help" for help.

postgres=# create extension oracle_fdw;
ERROR:  could not load library "/u01/app/postgres/product/94/db_1/lib/oracle_fdw.so": libclntsh.so.12.1: cannot open shared object file: No such file or directory

Ok, we need to set LD_LIBRARY_PATH to include the Oracle libraries and restart PostgreSQL:

postgres@oel7:/home/postgres/oracle_fdw-1.2.0/$ export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/
postgres@oel7:/home/postgres/oracle_fdw-1.2.0/$ pg_ctl -D /u02/pgdata/PG1 restart -m fast
postgres@oel7:/home/postgres/oracle_fdw-1.2.0/$ psql postgres
psql (9.4.1dbi services)
Type "help" for help.

postgres=# create extension oracle_fdw;
CREATE EXTENSION

Better. Foreign data wrappers work by defining foreign servers and user mappings:

postgres=# create server oracle foreign data wrapper oracle_fdw options (dbserver '//192.168.22.242/PROD.local' );
CREATE SERVER
postgres=# create user mapping for postgres server oracle options (user 'A', password 'a');
CREATE USER MAPPING

My Oracle database has a user called “A” with “a” as the password. Now we need to create the foreign table that should look like the table in Oracle and once this is done we should be able to select:

postgres=# create foreign table INTERVAL_SALES ( prod_id int not null, time_id date, amount_sold numeric) server oracle options (schema 'A', table 'INTERVAL_SALES');
CREATE FOREIGN TABLE
postgres=# select count(*) from INTERVAL_SALES;
 count 
-------
     3
(1 row)

Cool. We even can write to Oracle (Make sure to read the readme of oracle_fdw for understanding the “key” option which is mandatory for updates):

 -- oracle
SQL> select * from "A"."INTERVAL_SALES";

   PROD_ID TIME_ID   AMOUNT_SOLD
---------- --------- -----------
	 1 04-MAY-15	       1
	 2 08-MAY-16	       1
	 3 13-MAY-17	       1
-- postgres
postgres=# insert into INTERVAL_SALES values ( 4, current_date, 5 );
INSERT 0 1

Lets check the data on the Oracle side:

--oracle
SQL> select * from "A"."INTERVAL_SALES";

   PROD_ID TIME_ID   AMOUNT_SOLD
---------- --------- -----------
	 1 04-MAY-15	       1
	 2 08-MAY-16	       1
	 3 13-MAY-17	       1
	 4 18-AUG-15	       5

Looks good. You now have access to the data hosted in an Oracle database and can start working with it.

Again: make sure you read the readme of oracle_fdw, especially the “problems” and “options” sections.

11 Comments

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure