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:

After that, test your oracle connection using this sqlplus format

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

e.g sqlplus xx/yy@//

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:

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, 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 '/usr/lib/postgresql/11/lib/'
/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;

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

testdb=# select oracle_diag();

oracle_fdw 2.1.0, PostgreSQL 11.2 (Ubuntu 11.2-1.pgdg18.04+1), Oracle client
(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:


To quit psql type


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 $(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


sudo apt-key adv --keyserver --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