PostgreSQL Oracle FDW... in 8i?!

So one of our clients is still stuck running an old Oracle 8i database. Please, no comments on how there’s no more support and they should have moved off this long ago. We know. They know. Moving on…

The introduction of a new Oracle Foreign Data Wrapper peaked our interest. Could it possibly still work with 8i? Working with our SA team, they found that the oldest client libraries still available from Oracle are 10.2. We’re not exactly sure when Oracle dropped 8i support from their client libraries, so instead of experimenting at this time they went with known working client libraries for our currently used client which is 10.1.0.2.0 (looking at the package info). So they compiled up some packages for our Solaris environment and off I went.

With the packages installed, setting up the extension couldn’t have been easier

CREATE EXTENSION oracle_fdw;

This was my first attempt with using Foreign Data Wrappers, so the next hour or so was spent reading the oracle_fdw docs and jumping around the postgres docs to see how it all works. We already have a connection between PostgreSQL and Oracle working with the dbi_link package, so the Oracle Client connection was already setup and working (explaining that setup is a little out of scope for this blog post). The commands to create the server, user mapping & foreign table follow…

CREATE SERVER oracle_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver
'ORACLE_DBNAME');

CREATE USER MAPPING FOR CURRENT_USER SERVER oracle_server OPTIONS (user 'oracle_user',
password '######');

CREATE FOREIGN TABLE keith.fdw_test (
    userid      numeric,
    username    text,
    email       text
)
SERVER oracle_server;

Then run a SELECT and see…

pgsql=# SELECT * FROM keith.fdw_test;

 userid | username |       email
--------+----------+-------------------
      1 | keith    | keith@example.com
(1 row)

It works! This will make the (hopeful) migration off of Oracle 8i that much easier.

Could this possibly be faster than dbi_link for replicating data from Oracle to Postgres? Will be working on rewriting some of our data replication functions to use the FDW and run comparisons. I’ll share the results in a future post.