SUMMARY: This article explains how to use Foreign Data Wrappers to access data in remote PostgreSQL and Oracle databases.

1. postgres_fdw

2. oracle_fdw

 

There are many methods for accessing remote tables data—the data present in the tables existing on the remote database—such as PostgreSQL’s inbuilt dblink module. One of the most popular methods is accessing the table with the help of PostgreSQL Foreign Data Wrapper (postgres_fdw). In addition to postgres_fdw there are other Foreign Data Wrappers such as mongo_fdw, hadoop_fdw, and mysql_fdw, which can be used to access a MongoDB database, the Hadoop Distributed File System, and data present in a MySQL database, respectively.

postgres_fdw                                                  

For remote access to data in an external PostgreSQL server using postgres_fdw, please refer to the following steps:

Step 1: Create the extension.

edb=# create extension postgres_fdw ;

CREATE EXTENSION

edb=# 

 

Step 2:  Create a foreign server for each remote database to which the user wants to connect. Please ensure that the remote database cluster has the pg_hba.conf entry corresponding to the database server for which the foreign server has to be created.

edb=# CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xx.xx.xx.xx', dbname 'postgres', port '5444');

CREATE SERVER

 

Note: Here xx.xx.xx.xx is the remote machine IP address of the PostgreSQL database server.

 

Step 3: Create the user mapping for the specific users that can access the remote objects on the foreign server. 

For example: here we are using user “enterprisedb” for accessing the foreign object.

edb=# CREATE USER MAPPING FOR enterprisedb SERVER myserver OPTIONS (user 'enterprisedb', password 'edb');

CREATE USER MAPPING

 

This example may seem initially confusing to some users because the username and password have to be indicated inside the small brackets(). It is part of the syntax for user mapping:

CREATE USER MAPPING FOR local_user

        SERVER foreign_server

        OPTIONS (user 'foreign_user', password 'password of foreign_user');

 

To learn more about user mapping refer to the PostgreSQL documentation:

https://www.postgresql.org/docs/11/sql-createusermapping.html.

 

Step 4: Now that the Foreign Data Wrapper setup is complete, we are ready to move forward and access the remote tables. 

The table “test” is the remote table present in the remote database (with host 'xx.xx.xx.xx', dbname 'edb', port '5444' as detailed above in Step 2):

REMOTE_TABLE:

==============

edb=# create table test(id int,name text);

CREATE TABLE

edb=# insert into test values (1,'piyushj');

INSERT 0 1

 

Create a foreign table “test_foreign” in the local database server. 

edb=# 

edb=# CREATE FOREIGN TABLE test_foreign ( id   int, name  text) SERVER myserver;       

CREATE FOREIGN TABLE

edb=# 

 

To learn more about the CREATE FOREIGN TABLE syntax, refer to the PostgreSQL documentation: https://www.postgresql.org/docs/11/sql-createforeigntable.html.

 

Step 5: We can query the foreign table “test_foreign” for accessing the remote table test.

edb=# select * from test_foreign;

 id | name  

----+-------

  1 | piyush

(1 row)

                                     

oracle_fdw

We discussed above the steps for accessing tables present in a remote PostgreSQL database. To access tables present in a remote Oracle database server from a local PostgreSQL database, we can use the oracle_fdw Foreign Data Wrapper, which is covered under the SQL Management of External Data standard.

Let's discuss the steps for building and installing the oracle_fdw wrapper and then configuring it to access the Oracle tables.

 

Step 1:  Download the source code for installing oracle_fdw.

You can download the oracle_fdw code from the link http://laurenz.github.io/oracle_fdw/.

 

Step 2: Prerequisites for installation.

The local environment should have the PostgreSQL installation, and users should install at least Oracle instantclient-basic, instantclient-devel. Note: If the Oracle server and the PostgreSQL server are running on the same machine, then it’s not required to install the instant client—in other words, you can skip this step.

Example

[root@piyush Downloads]# yum localinstall oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager

This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.

Examining oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm: oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64

Marking oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm to be installed

Examining oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm: oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64

Marking oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm to be installed

Resolving Dependencies

--> Running transaction check

---> Package oracle-instantclient12.1-basic.x86_64 0:12.1.0.2.0-1 will be installed

---> Package oracle-instantclient12.1-devel.x86_64 0:12.1.0.2.0-1 will be installed

--> Finished Dependency Resolution

:

:

:

Dependencies Resolved



=========================================================================================================================================================================================

 Package                                          Arch                     Version                           Repository                                                             Size

=========================================================================================================================================================================================

Installing:

 oracle-instantclient12.1-basic                   x86_64                   12.1.0.2.0-1                      /oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64                   185 M

 oracle-instantclient12.1-devel                   x86_64                   12.1.0.2.0-1                      /oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64                   1.9 M



Transaction Summary

=========================================================================================================================================================================================

Install  2 Packages



Total size: 187 M

Installed size: 187 M

Is this ok [y/d/N]: y

Downloading packages:

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

  Installing : oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64                                                                                                                    1/2 

  Installing : oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64                                                                                                                    2/2 

  Verifying  : oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64                                                                                                                    1/2 

  Verifying  : oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64                                                                                                                    2/2 



Installed:

  oracle-instantclient12.1-basic.x86_64 0:12.1.0.2.0-1                                        oracle-instantclient12.1-devel.x86_64 0:12.1.0.2.0-1                                       



Complete!

 

Step 3:  Ensure that pg_config is in your path, and ORACLE_HOME as well as LD_LIBRARY_PATH are configured.

which pg_config

/opt/PostgreSQL/10/bin/pg_config .  ------ We are using the PG-10 installation 



-bash-4.2$ export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib

-bash-4.2$

-bash-4.2$ export ORACLE_HOME=/usr/include/oracle/12.1/client64



-bash-4.2$ echo $ORACLE_HOME

/usr/include/oracle/12.1/client64

-bash-4.2$ echo $LD_LIBRARY_PATH

/usr/lib/oracle/12.1/client64/lib

 

We downloaded the version oracle_fdw-2.2.0 for the installation. 

cd /opt/oracle_fdw-2.2.0/

 

We are using the make and make install commands to install oracle_fdw. The command make  is used to build the software. This runs a series of tasks defined in a Makefile to build the finished program from its source code. The make install command will copy the built program and its libraries and documentation to the correct locations.

This usually means that the program’s binary will be copied to a directory on your PATH, the program’s manual page will be copied to a directory on your MANPATH, and any other files it depends on will be safely stored in the appropriate place.

bash-4.2$ make



gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fPIC -I/usr/include/oracle/12.1/client64/sdk/include -I/usr/include/oracle/12.1/client64/oci/include -I/usr/include/oracle/12.1/client64/rdbms/public -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/opt/PostgreSQL/10include/server -I/opt/PostgreSQL/10/include/internal -I/opt/local/Current/include -D_GNU_SOURCE -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include/libxml2 -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include  -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -I/mnt/hgfs/edb-postgres.auto/server/source/libmm-edb.linux-x64/inst/include  -c -o oracle_fdw.o oracle_fdw.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fPIC -I/usr/include/oracle/12.1/client64/sdk/include -I/usr/include/oracle/12.1/client64/oci/include -I/usr/include/oracle/12.1/client64/rdbms/public -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/opt/PostgreSQL/10/include/server -I/opt/PostgreSQL/10//include/internal -I/opt/local/Current/include -D_GNU_SOURCE -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include/libxml2 -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include  -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -I/mnt/hgfs/edb-postgres.auto/server/source/libmm-edb.linux-x64/inst/include  -c -o oracle_utils.o oracle_utils.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fPIC -I/usr/include/oracle/12.1/client64/sdk/include -I/usr/include/oracle/12.1/client64/oci/include -I/usr/include/oracle/12.1/client64/rdbms/public -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/opt/PostgreSQL/10/include/server -I/opt/PostgreSQL/10include/internal -I/opt/local/Current/include -D_GNU_SOURCE -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include/libxml2 -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include  -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -I/mnt/hgfs/edb-postgres.auto/server/source/libmm-edb.linux-x64/inst/include  -c -o oracle_gis.o oracle_gis.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/opt/PostgreSQL/10lib   -L/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/lib -L/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/lib  -L/opt/local/Current/lib -L/mnt/hgfs/edb-postgres.auto/server/source/libmm-edb.linux-x64/inst/lib -Wl,--as-needed -Wl,-rpath,'/opt/PostgreSQL/10/lib',--enable-new-dtags  -L/usr/include/oracle/12.1/client64 -L/usr/include/oracle/12.1/client64/bin -L/usr/include/oracle/12.1/client64/lib -L/usr/include/oracle/12.1/client64/lib/amd64 -lclntsh -L/usr/lib/oracle/19.3/client/lib -L/usr/lib/oracle/19.3/client64/lib -L/usr/lib/oracle/18.5/client/lib -L/usr/lib/oracle/18.5/client64/lib -L/usr/lib/oracle/18.3/client/lib -L/usr/lib/oracle/18.3/client64/lib -L/usr/lib/oracle/12.2/client/lib -L/usr/lib/oracle/12.2/client64/lib -L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib -L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib 



-bash-4.2$ make install

/bin/mkdir -p '/opt/PostgreSQL/10/lib'

/bin/mkdir -p '/opt/PostgreSQL/10/share/postgresql/extension'

/bin/mkdir -p '/opt/PostgreSQL/10/share/postgresql/extension'

/bin/mkdir -p '/opt/PostgreSQL/10/doc/postgresql/extension'

/usr/bin/install -c -m 755  oracle_fdw.so '/opt/PostgreSQL/10/lib/oracle_fdw.so'

/usr/bin/install -c -m 644 .//oracle_fdw.control '/opt/PostgreSQL/10/share/postgresql/extension'

/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql  '/opt/PostgreSQL/10/share/postgresql/extension/'

/usr/bin/install -c -m 644 .//README.oracle_fdw '/opt/PostgreSQL/10/doc/postgresql/extension'

 

Step 4:  Create the extension, server, and user mapping.

Create extension:

-bash-4.2$ ./psql -p 5490 -d postgres

Password:

psql.bin (10.7.15)

Type "help" for help.



edb=# create extension oracle_fdw;

CREATE EXTENSION

 

As we defined the foreign server and user mapping in the case of postgres_fdw, here we have to also define the foreign server and user mapping.

Create foreign server:

edb=# create server oracle foreign data wrapper oracle_fdw options (dbserver '//xx.xx.xx.xx/orcl' );

CREATE SERVER

 

Note: Here xx.xx.xx.xx is the oracle_server machine IP address where Oracle is running.

Syntax 

create server <server_name> foreign data wrapper oracle_fdw options (dbserver '//<oracle_servefr_IP>/<sid>' );

 

Create user mapping:

edb=# create user mapping for enterprisedb server oracle options (user 'system', password 'manager');

CREATE USER MAPPING

Syntax 

create user mapping for <local_postgres_user> server oracle options (user '<oracle_user>', password '<oracle_passwd>');

 

Step 5: Pick a table from the Oracle database to be accessed from PostgreSQL.

SQL> desc datawrap

 Name   Null?    Type

 ----------------------------------------- -------- ----------------------------

 ID    NUMBER(38)

 NAME    VARCHAR2(50)



SQL> select * from datawrap;



ID NAME

---------- --------------------------------------------------

1 piyush

2 sharma

 

Step 6: Create a foreign table in PostgreSQL and access the data.

db=# create foreign table datawrap_test2 (id int, name varchar(50)) server oracle options (schema 'SYSTEM', table 'DATAWRAP');

CREATE FOREIGN TABLE

 

Note: We have to provide the Oracle schema name and table name in capitals 

edb=# select * from datawrap_test2;

 id |  name  

----+--------

  1 | piyush

  2 | sharma

(2 rows)

 

We can see that the Oracle table data is easily accessible from the PostgreSQL database. You can also update, delete, and insert data in the remote Oracle tables by updating, deleting, and inserting the data locally in the foreign tables. 

Example

Data in the Oracle database: 

SQL> select * from datawrap;                                        



ID NAME

---------- --------------------------------------------------

1 piyush

2 sharma

 

Data in the PostgreSQL foreign table:

postgres=# select * from datawrap_test2;

 id |  name  

----+--------

  1 | piyush

  2 | sharma

(2 rows)

 

We will insert data locally in the PostgreSQL foreign table, and that data will get inserted in the remote Oracle tables.

Insert data in the PostgreSQL foreign table:

postgres=# insert into  public.datawrap_test2 values(3,'dba');

INSERT 0 1

postgres=# 

postgres=# 

postgres=# select * from datawrap_test2;

 id |  name  

----+--------

  1 | piyush

  2 | sharma

  3 | dba

(3 rows)

 

Verify the row insertion in the remote Oracle table:

SQL> select * from datawrap;



ID NAME

---------- --------------------------------------------------

1 piyush

2 sharma

3 dba

 

In a similar way, we can also delete and update the data. However, to use UPDATE or DELETE, at least one foreign table column must be marked as the primary key column.