Installing oracle_fdw in Postgresql-11 on Ubuntu18.04

To install the oracle foreign data wrapper on Postgres, firstly we need to make sure we are able to connect to Oracle from our Ubuntu machine.

For this, make sure that all the Oracle components are installed. I followed the beautiful step by step instructions given here:
https://mikesmithers.wordpress.com/2011/04/03/oracle-instant-client-on-ubuntu-with-added-aliens/

After that, test your oracle connection using this sqlplus format

sqlplus oraschemaname/oraschemapassword@//hostname:1521/Service

e.g sqlplus xx/yy@//abc.def.com:1521/SERVICE

If you have connected succesfully to Oracle then we can now get busy with the installation of oracle_fdw

I downloaded the fdw from here: https://github.com/laurenz/oracle_fdw/releases/

Naturally all credit for everything that I documented here goes to this person who created the oracle_fdw. I am only trying to document my experience of installing and what difficulties i faced.

After downloading the zip file oracle_fdw-ORACLE_FDW_2_1_0.zip, i unzipped it into a folder with the same name oracle_fdw-ORACLE_FDW_2_1_0

The instructions say that one goes into the folder in command prompt and then types make.

/home/ana/Downloads/oracle_fdw-ORACLE_FDW_2_1_0$ make

You need to install postgresql-server-dev-NN for building a server-side extension or libpq-dev for building a client-side application.
make: *** No targets. Stop.

So when you see this, you need to install some missing components of postgres. So we run the following installation:

sudo apt-get install postgresql-server-dev-11

Now rerun make, and it will get all the source files ready to be recompiled.

After the make, one has to install oracle_fdw with $make install

But in order for the installation to work, certain places where postgres is installed should get a write access. When they have a write access then, the installation of the fdw will be a success.

 ana@ana-pg % sudo chmod a+rwx /usr/share/doc
ana@ana-pg % sudo chmod a+rwx /usr/lib/
ana@ana-pg % sudo chmod a+rwx /usr/lib/postgresql/11/lib/
ana@ana-pg % sudo chmod a+rwx /usr/share/postgresql/11/extension/
ana@ana-pg % sudo chmod a+rwx /usr/lib/postgresql/11/lib/bitcode/

Now we run make install

ana@ana-pg % make install
/bin/mkdir -p '/usr/lib/postgresql/11/lib'
/bin/mkdir -p '/usr/share/postgresql/11/extension'
/bin/mkdir -p '/usr/share/postgresql/11/extension'
/bin/mkdir -p '/usr/share/doc/postgresql-doc-11/extension'
/usr/bin/install -c -m 755 oracle_fdw.so '/usr/lib/postgresql/11/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/share/postgresql/11/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql '/usr/share/postgresql/11/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/share/doc/postgresql-doc-11/extension/'
/bin/mkdir -p '/usr/lib/postgresql/11/lib/bitcode/oracle_fdw'
/bin/mkdir -p '/usr/lib/postgresql/11/lib/bitcode'/oracle_fdw/
/usr/bin/install -c -m 644 oracle_fdw.bc '/usr/lib/postgresql/11/lib/bitcode'/oracle_fdw/./
/usr/bin/install -c -m 644 oracle_utils.bc '/usr/lib/postgresql/11/lib/bitcode'/oracle_fdw/./
/usr/bin/install -c -m 644 oracle_gis.bc '/usr/lib/postgresql/11/lib/bitcode'/oracle_fdw/./
cd '/usr/lib/postgresql/11/lib/bitcode' && /usr/lib/llvm-6.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o oracle_fdw.index.bc oracle_fdw/oracle_fdw.bc oracle_fdw/oracle_utils.bc oracle_fdw/oracle_gis.bc

Now we can create an extension in the test database of postgresql-11. For that we login again into psql

ana@ana-pg ~ % sudo -i -u postgres
[sudo] password for ana: 
postgres@ana-pg:~$ psql
psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1))
Type "help" for help.
postgres=# \c testdb;

Now we create an extension using the following:

testdb=# create extension oracle_fdw;
CREATE EXTENSION

To check if postgres has installed the oracle_fdw successfully, log into psql and type

testdb=# select oracle_diag();

oracle_diag
oracle_fdw 2.1.0, PostgreSQL 11.2 (Ubuntu 11.2-1.pgdg18.04+1), Oracle client 12.2.0.1.0
(1 row)

After installing oracle_fdw, we now get hands on and access a table in the oracle schema.

Back to firststeps after installing postgres Import Oracle Foreign Server

Leave a Reply

Your email address will not be published. Required fields are marked *

8 + 2 =