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.