Using Foreign Data Wrappers to access remote PostgreSQL and Oracle databases

Developer Tools 6.18

Piyush Sharma Senior Systems Engineer

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.


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 ;




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');



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');



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:


        SERVER foreign_server

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


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


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):



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


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



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


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




To learn more about the CREATE FOREIGN TABLE syntax, refer to the PostgreSQL documentation:


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)



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


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.


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

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- oracle-instantclient12.1-basic-

Marking oracle-instantclient12.1-basic- to be installed

Examining oracle-instantclient12.1-devel- oracle-instantclient12.1-devel-

Marking oracle-instantclient12.1-devel- to be installed

Resolving Dependencies

--> Running transaction check

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

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

--> Finished Dependency Resolution




Dependencies Resolved


 Package                                          Arch                     Version                           Repository                                                             Size



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

 oracle-instantclient12.1-devel                   x86_64                               /oracle-instantclient12.1-devel-                   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-                                                                                                                    1/2 

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

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

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


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



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$ export ORACLE_HOME=/usr/include/oracle/12.1/client64

-bash-4.2$ echo $ORACLE_HOME


-bash-4.2$ echo $LD_LIBRARY_PATH



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/ -I/usr/include/oracle/ -I/usr/include/oracle/ -I/usr/include/oracle/ -I/usr/include/oracle/ -I/usr/include/oracle/ -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/  -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/ -I/usr/include/oracle/ -I/usr/include/oracle/ -I/usr/include/oracle/ -I/usr/include/oracle/ -I/usr/include/oracle/ -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/  -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/ -I/usr/include/oracle/ -I/usr/include/oracle/ -I/usr/include/oracle/ -I/usr/include/oracle/ -I/usr/include/oracle/ -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/  -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.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/ -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/ -L/usr/lib/oracle/ -L/usr/lib/oracle/ -L/usr/lib/oracle/ -L/usr/lib/oracle/ -L/usr/lib/oracle/ 

-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 '/opt/PostgreSQL/10/lib/'

/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


psql.bin (10.7.15)

Type "help" for help.

edb=# create extension oracle_fdw;



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' );



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


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 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)


SQL> select * from datawrap;


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

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');



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. 


Data in the Oracle database: 

SQL> select * from datawrap;                                        


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

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');




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;


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

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.


Piyush SharmaSenior Systems Engineer