PostgreSQL is a dominant, open sourced database that’s used by many data storage and access needs. Along with PostgreSQL, users/Organizations can also have their data in different databases or cloud based applications. This raises the need for querying the external data from Postgres when you want to migrate your data or perform data analysis. To facilitate these kind of use cases, PostgreSQL has a feature called Foreign Data Wrappers, which essentially allows you to access external data as if it was a Postgres table. For anyone familiar with SQL Server, Foreign data wrappers for Postgres is similar to SQL Server’s Linked Server with ODBC/OLE DB.
In this tutorial, we will walk you through how to connect to Oracle database from Postgres using an FDW for JDBC driver. The FDW that we will use is JDBC_FDW, an open source extension that can be found on Github, which leverages Progress DataDirect Oracle JDBC driver. The tutorial will walk you through from installing PostgreSQL, to accessing your external data, as the extension is not up to date (which may might be an issue for people trying this out for the first time). Also note that the JDBC_FDW extension has support until Postgres 9.5 when this tutorial was written.
##Switch user to postgres
su – postgres
Note: If you encounter the following error, restart PostgreSQL by running the following command. If that didn’t work restart your machine.
service postgresql-9.5 restart
CREATE EXTENSION jdbc_fdw
CREATE SERVER oracle_server FOREIGN DATA WRAPPER jdbc_fdw OPTIONS( drivername 'com.ddtek.jdbc.oracle.OracleDriver', url 'jdbc:datadirect:oracle://10.0.0.1:1521;ServiceName=XE',
CREATE USER MAPPING for postgres SERVER oracle_server
>', password '<
CREATE FOREIGN TABLE Pokemon(Pokemon_id int, pokemon_name text)
SERVER oracle_server OPTIONS (query 'SELECT pokemon_id,
pokemon_name from PETS.POKEMON');
SELECT * FROM Pokemon;
Note: If you get an error saying “JDBC Driver class not found”, make sure that the location of oracle.jar file is accessible by the user ‘Postgres’
The following are the tasks that you would be performing before you can access your external data on Postgres. Note that this tutorial assumes you are working on CentOS 7.
>yum install pgdg-centos95-9.5-3.noarch.rpm</
yum install postgresql95-server.x86_64 postgresql95-devel.x86_64
systemctl enable postgresql-9.5.service
>service postgresql-9.5 start</
java -jar PROGRESS_DATADIRECT_JDBC_ORACLE_ALL.jar
yum install java-1.8.0-openjdk-devel.x86_64
ln -s /usr/lib/jvm/java-1.8.0-
make install USE_PGXS=1
Now that you have leared how to access external data from Postgres using JDBC FDW, feel free to try our other JDBC Drivers for Salesforce, CDH Hive, Eloqua, Marketo and others as per your use case when you want to access external data from PostgreSQL. Keep in mind that the procedure remains almost the same and you would have to change only the JDBC configuration details when you are creating Server and create a new foreign table to access that data.
Disclaimer: We do not provide any guarantee for the JDBC_FDW extension for any kind of production usage. Use it at your own risk.
Connect any data source to any application
Discussions, tips and tricks for
DataDirect Connect drivers
Copyright © 2018 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.