Accessing Oracle Schema from Postgresql-11 Database on Ubuntu 18.04 using oracle_fdw

Firstly login to 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 that we are in the test database testdb, we can create a schema here.

testdb=# create schema test;

Now in this test schema, we will try to add the foreign oracle server called “oradb”. Now this server will be accessed by postgres user rtam. Therefore we create a user mapping of rtam to oracle

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//hostname:1521/service');
GRANT USAGE ON FOREIGN SERVER oradb TO rtam;
CREATE USER MAPPING FOR rtam SERVER oradb OPTIONS (user 'oracle_schema_name', password 'oracle_schema_password');

Now lets try a simple experiment. Let us say we have a schema “scott” in oracle. We create a table here called test

CREATE TABLE scott.test
(
id NUMBER,
msg VARCHAR2 (30 CHAR)
);

Now in Postgresql, in our testdb, in our test schema, we run the following in psql

CREATE FOREIGN TABLE test.test (
id integer NOT NULL,
msg character varying(30))
SERVER oradb OPTIONS (schema 'SCOTT', table 'TEST');

If we now do a select on test.test, we will see the values of oracle table in the Postgresql Database.

So basically under Foreign Tables one sees the oracle tables directly accessible to Postgres.

One can also Import the oracle foreign table schemas directly into postgresql using a command:

IMPORT FOREIGN SCHEMA "SCOTT" LIMIT TO
(TABLE1,TABLE2,...) FROM SERVER oradb INTO postgres_schema_name;

Note that when using Oracle names, always use caps. Also in the above schema name, “” have to be used to make sure that Postgres interprets the oracle schema name correctly. Postgresql otherwise tends to search for lower case names and does not find anything.

Back to Import Oracle Foreign Server

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

First Steps after installing Postgres-11 on Ubuntu 18.04

After installation we need to login to postgresql to create database, roles, schemas etc

When postgresql11 is installed, there is a default superuser postgres created, which does not have any password. We just login to the database using this superuser

sudo -i -u postgres

Now we can enter into psql, which is the database commandline

postgres@ana-pg:~$ psql
psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1))
Type "help" for help.

Now in the command line of psql, we create a database

create database testdb;

To see the list of databases installed:

\list

To quit psql type

\q

Now we create another superuser other than postgres

postgres@ana-pg:~$ createuser --interactive
Enter name of role to add: rtam
Shall the new role be a superuser? (y/n) y
postgres@ana-pg:~$ alter user rtam with password 'partha';

With this new superuser rtam one can add a new server in pgAdmin4. Here we just need to click on “Add New Server”. Then name the server anything. Under Connections Tab, hostname is localhost. 

The username and password are that of the new user we created rtam

The Add New Server will not work with user as postgres, because postgres is created by default in Ubuntu without a password. Now one can see in pgAdmin4 that the database testdb has been created.

back to installing postgres          Installing oracle_fdw

Installing Postgresql-11 on Ubuntu 18.04

Steps involved in installing Postgresql-11 on Ubuntu 18.04.02 are:

We get the package to be installed from the APT repository

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update

You might get an error message, about missing public key, that looks like this: The following signatures couldn’t be verified because the public key is not available: NO_PUBKEY 7FCC7D46ACCC4CF8

Solution:

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 7FCC7D46ACCC4CF8
sudo apt-get update

Now we can install Postgresql Database and the pgadmin tool that is a graphical tool for database administration.

sudo apt-get install postgresql-11 pgadmin4

Now we setup the database -> First Steps to Setup Database