Querying External Data from PostgreSQL Using JDBC FDW

Introduction

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.

Back to top

Querying External Data

Switch to user ‘postgres’ and run the psql console by running these commands.

##Switch user to postgres

su – postgres

##Run psql

Psql

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

Setup the Extension JDBC_FDW by running the following command in psql console

CREATE EXTENSION jdbc_fdw

Next, create a server that uses jdbc_fdw extension using the following commands. Replace the details as you see.

Build and Install JDBC_FDW

Open you terminal and navigate to the JDBC_FDW directory that you have installed and run the following command that will build and install the extension.

make install USE_PGXS=1

Back to top

All Done

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.

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.