Querying External Data from PostgreSQL Using JDBC FDW

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.

1. Install PostgreSQL 9.5
2. Install Progress DataDirect Oracle JDBC driver
3. Install OpenJDK JDK
4. Build and Install JDBC_FDW

1. Download the Postgres 9.5 RPM file for CentOS and install it by running the following commands.

    

   wget

    

   https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-3.noarch.rpm

   <p></p><p>yum install pgdg-centos95-9.5-3.noarch.rpm</p>

    

2. Install PostgreSQL 9.5 along with development packages that are needed for using the FDW.

    

   yum install postgresql95-server.x86_64 postgresql95-devel.x86_64

    

3. Initiate the database using the following command

    

   /usr/pgsql-9.5/bin/postgresql95-setup initdb

    

4. Configure PostgreSQL to run on start up by running following command.

    

   systemctl enable postgresql-9.5.service

    

5. Start the PostgreSQL Server, by running the command                                                                                                                                                                                                                                                                                            
   <p>service postgresql-9.5 start</p><p></p>
                                                                                                                                                                                                                                                      
6. To test if Postgres is running, run the following command

1. Download DataDirect Oracle JDBC driver from here.
2. To install the driver, you would have to execute the .jar package and you can do it by running the following command in terminal.

    

   java -jar PROGRESS_DATADIRECT_JDBC_ORACLE_ALL.jar

    

3. This will launch an interactive java installer using which you can install the Oracle JDBC driver to your desired location as either a licensed or evaluation installation.

1. To install Open JDK 8 JDK, run this command

    

   yum install java-1.8.0-openjdk-devel.x86_64

    

2. Run the following command to create a Symlink for libjvm.so in /usr/lib/ directory.

1. Download or clone the JDBC_FDW repository from Github.
2. Add Postgres bin path to PATH, by running the following command

    

   export PATH=/usr/pgsql-9.5/bin:$PATH

    

3. 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.

1. 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

    

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

    

   CREATE EXTENSION jdbc_fdw

    

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

    

   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',

   querytimeout '15',

   jarfile '/path/to/Progress/DataDirect/Connect_for_JDBC_51/lib/oracle.jar',

   maxheapsize '600'

   );
4. Create a user mapping by running the following command. Replace the username and password with your Oracle database credentials.

    

   CREATE USER MAPPING for postgres SERVER oracle_server

   OPTIONS(username '<user>', password '<password>')

    

5. Create a foreign table using the server that you have created by running this command.

    

   CREATE FOREIGN TABLE Pokemon(Pokemon_id int, pokemon_name text)

   SERVER oracle_server OPTIONS (query 'SELECT pokemon_id,

   pokemon_name from PETS.POKEMON');

    

6. Once the foreign table is created, you can now just start querying your external data in oracle database from Postgres by running your SQL statements.

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.