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:

[email protected]:/home/postgres/$ which pg_config
/u01/app/postgres/product/94/db_1/bin/pg_config
[email protected]:/home/postgres/$ unzip oracle_fdw-1.2.0.zip
[email protected]:/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
[email protected]:/home/postgres/$ cd oracle_fdw-1.2.0
[email protected]:/home/postgres/oracle_fdw-1.2.0/$ make
[email protected]:/home/postgres/oracle_fdw-1.2.0/$ make install

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

[email protected]:/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:

[email protected]:/home/postgres/oracle_fdw-1.2.0/$ export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/
[email protected]:/home/postgres/oracle_fdw-1.2.0/$ pg_ctl -D /u02/pgdata/PG1 restart -m fast
[email protected]:/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.

16 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure