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.
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.
Note: If you encounter the following error, restart PostgreSQL by running the following command. If that didn’t work restart your machine.
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’
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.