Foreign Data Wrapper for PostgreSQL Part 2 : Create Server , User Mapping and Foreign Table

After installation of foreign data wrapper which can be read in this blog post , in order to access data from PostgreSQL database we must create server, user mapping and foreign table.

Creation of server provides connection information of foreign data resource that foreign data wrapper uses to access.

create server oracle_SERVER foreign data wrapper oracle_fdw options (dbserver '//10.1.X.X/DB_INSTANCE_NAME');

Next step which is creation of user mapping contains a mapping of user to a foreign server.

create user mapping for $POSTGRESQL_USER server oracle_SERVER options(user 'oracle_user',password 'oracle_user_password');

$POSTGRESQL_USER is the PostgreSQL database user which is mapped to foreign server.

Foreign server owner can create user mapping for any user in server.Also a user which has usage privilege for foreign server can create user mapping for his own user.

Finally,foreign table is created with following command,

create foreign table foreign_table (user_id numeric,user_name text) server oracle_SERVER options (schema 'oracle_schema_name',table 'oracle_table');

With psql interface you can select foreign_table.

user_id and user_name are table fields which are identical to Oracle table

Source :
http://www.postgresql.org/docs/9.1/static/sql-createserver.html
http://www.postgresql.org/docs/9.1/static/sql-createusermapping.html
http://www.postgresql.org/docs/9.1/static/sql-createforeigntable.html

Installation of Oracle Foreign Data Wrapper for PostgreSQL

If you are working on both Oracle and PostgreSQL databases , sometimes you need to access each database from other. My project contains accessing Oracle database from PostgreSQL database and this took me to search how to do it and found foreign data wrappers.Foreign data wrapper based on accessing to data that is not in PostgreSQL database.

Following link contains all FDW information for PostgreSQL .

https://wiki.postgresql.org/wiki/Foreign_data_wrappers

In order to install Oracle FDW , below Oracle Instant Client rpm files and oracle_fdw extension are needed.

Oracle FDW - https://github.com/laurenz/oracle_fdw

Oracle Instant Client - http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

Install oracle-instantclient-basic, oracle-instantclient-sqlplus, oracle-instantclient-devel packages for your version.

After installation below envinroments are set.

export PATH=$PATH:/usr/pgsql-9.3/bin
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin:$LD_LIBRARY_PATH

Extract downloaded oracle_fdw.master.zip file and change into directory.Software installation is done with following commands

$make

$make install

Now oracle_fdw shared library has been installed in the PostgreSQL library directory and oracle_fdw.control and the SQL files are in the PostgreSQL extension directory.

Following step is done to install extension in a database with superuser.

$psql -d {Your database name}

$CREATE EXTENSION oracle_fdw;

ERROR:  could not load library "/usr/pgsql-9.3/lib/oracle_fdw.so": libclntsh.so.11.1: cannot open shared object file: No such file or directory

  

Above error can be seen while creating extension because of library missing which is actually exists.In order to solve this error, Oracle library path is added to system library list.

$echo /usr/lib/oracle/11.2/client64/lib> /etc/ld.so.conf.d/oracle.conf

$ldconfig

Executing same command for install extension completed successfully after library arrangement.

$CREATE EXTENSION oracle_fdw;

CREATE EXTENSION