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.