Date September 24th, 2015 Author Vitaly Agapov

Please don’t wait for any revelations from this article. This is just a compilation of some notes about configuring links between Oracle Database 11.2 and PostgreSQL 9.4. Everything written below is true for the case of Linux (RHEL/CentOS 7 to be exact) and I cannot guarantee its operability on other platforms or versions.

So let’s get to the point. In the first part of the article I will show how to configure the DB link from Oracle to PostgreSQL using ODBC driver and Oracle Heterogeneous Services (HS) agent. In the second part I will show how to access Oracle databases from PostgreSQL using foreign data wrapper (oracle_fdw).

Accessing PostgreSQL from Oracle

Step 1. Install PgSQL ODBC driver on Oracle server

yum install postgresql94-odbc

If you have only default repositories configured then install this package containing PostgreSQL repo-file: http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm.

Step 2. Configure ODBC

ODBC (Open Database Connectivity) is a standard API for interacting with any DBMS independently of the operating systems or databases themselves. If you have any issues, questions or maybe you are too curious then refer to the ODBC documentation here: http://www.unixodbc.org/odbcinst.html. This documentation was really helpful during my experiments with all these databases.

Foremost we should define the driver in file /etc/odbcinst.ini. To do that add or edit such a section in this file:

[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/pgsql-9.4/lib/psqlodbc.so
FileUsage       = 1
Threading       = 1

Check if the driver configured:

# odbcinst -q -d
[PostgreSQL]

Then we configure the ODBC data source in file /etc/odbc.ini:

[PSQL]
Debug           = 0
CommLog         = 0
ReadOnly        = 1
Driver          = PostgreSQL
Servername      = <pgsql.server.address>
Username        = <username>
Password        = <password>
Port            = 5432
Database        = <database>
Trace           = 0
TraceFile       = /tmp/sql.log

All parameters including ReadOnly should not be set as “Yes” to be switched on or they will not be working. They should be set as 1.
Username and Password are optional and can be removed. We will use them only for checking the connectivity. The actual username and pasword will be set in Oracle while creating the db link. Also Debug, CommLog and Trace parameters can be turned on while testing.

If there is no writing access to /etc to edit files then the connection can defined in ~/.odbc.ini file. And also the Driver parameter can contain the path to the driver library file rather than reference to driver description in odbcinst.ini.

Check the data source:

# odbcinst -q -s
[PSQL]

And check the ODBC connection with isql utility:

# isql -v PSQL
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

If everything is Ok then we will be able to issue SQL statements and get the appropriate responces. After this check the credentials could be removed from odbc.ini if needed.

Step 3. Configure HS agent to use ODBC link

Create init file for new HS instance – $ORACLE_HOME/hs/admin/initPSQL.ora

HS_FDS_CONNECT_INFO = PSQL
HS_FDS_TRACE_LEVEL = ON
HS_FDS_SHAREABLE_NAME = /usr/pgsql-9.4/lib/psqlodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
set ODBCINI=/etc/odbc.ini

As for HS_LANGUAGE then this value is the only one the got it working finally. If I set it as AMERICAN_AMERICA.AL32UTF8 then nothing works. So… Let’s just keep it now.

Step 4. Configure listener

Add this to listener.ora and set the right ORACLE_HOME and SID (PSQL in my case):

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=PSQL)
         (ORACLE_HOME=<oracle/home>)
         (PROGRAM=dg4odbc)
      )
  )

Add this record to tnsnames.ora:

PSQL  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=<oracle.hostname>)(PORT=1521))
    (CONNECT_DATA=(SID=PSQL))
    (HS=OK)
  ) 

To check the tnsnames we can issue command “tnsping PSQL”.

Then we reload the listener:

$ lsnrctl reload
...
The command completed successfully
$ lsnrctl status
...
Service "PSQL" has 1 instance(s).
  Instance "PSQL", status UNKNOWN, has 1 handler(s) for this service...
...

Note that the listener should be always reloaded after changing the odbc ini-files to catch the changes.

Step 5. Create db link

create database link PG_LINK connect to "userschema" identified by "password" using 'PSQL';
select * from "some_pg_table"@PG_LINK;

Now we can create a synonym for the table:

create synonym some_pg_table for "some_pg_table"@PG_LINK;

But we should take into consideration that all identifiers in Oracle are automatically converted to uppercase and in PostgreSQL they otherwise converted to lowercase. This causes some troubles because we cannot just select:

select some_field from some_pg_table;

And we are forced to use quotes:

select "some_field" from some_pg_table;

Accessing Oracle from PostgreSQL

Step 1. Setup oracle_fdw

oracle_fdw is a third-party software. It is an extension for PostgreSQL which implements Foreign Data Wrapper for accessing Oracle databases. Its source code can be taken from
https://github.com/laurenz/oracle_fdw/releases/ and you can build and RPM using this spec-file: https://github.com/agapoff/RPM-specs/tree/master/oracle_fdw

Step 2. Configure foreign server

Run this under psql:

CREATE EXTENSION IF NOT EXISTS oracle_fdw WITH SCHEMA public;
COMMENT ON EXTENSION oracle_fdw IS 'foreign data wrapper for Oracle';
CREATE SERVER oradb_my_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//my.ora.server:1521/SID');
CREATE USER MAPPING FOR my_postgres_user server oradb_my_server OPTIONS (password 'password', user 'userschema');
GRANT ALL PRIVILEGES ON FOREIGN DATA WRAPPER oracle_fdw TO my_postgres_user;
GRANT USAGE ON FOREIGN SERVER oradb_aserver_fire_cons TO my_postgres_user;
CREATE FOREIGN TABLE some_table ( field1 integer NOT NULL, field2 varchar(32) NOT NULL) SERVER oradb_my_server OPTIONS (TABLE 'ORA_TABLE');

And seems like that is all. It is obviously much easier to access Oracle from PgSQL than vice versa. And there are no problems with uppercase and lowercase – data wrapper does all the work.

So Long, and Thanks for all the Fish.

Tags: Linux, Oracle, PostgreSQL
Category: Linux, Oracle | 5 Comments »

Comments

5 комментариев на “Links between Oracle and PostgreSQL, or There and Back Again”

1. Jan Robertson
   
   6 January 2016 - 19:12

   Hi Vitaly, this is a very clear and useful manual for the project I am working on now. I still have one problem and that is how to build oracle_fdw. My build raises numerous errors and I suspect that I am missing something. My question is: do you have a rpm for this fdw that I can install on centos 6? It would be very helpful to me.

    

   Kind regards,

   Jan Robertson

   email: info@robertson-services.nl

   M: +31 6 51917568

2. Vitaly Agapov
   
   7 January 2016 - 10:44

   Hi Jan,

   I have RPM for CentOS7 and for CentOS5:

   http://agapoff.name/files/oracle_fdw-1.0.0-1.el5.x86_64.rpm

   http://agapoff.name/files/oracle_fdw94-1.2.0-2.el7.centos.x86_64.rpm

   You can try to use one of them.

3. Gareth
   
   8 September 2016 - 10:35

   When connecting to Postrgresql fro Oracle, there are some table identifiers and column identifiers > 30 cahracters. Oracle does not like chars > 30, so any ideas on how to over vcome, get round?

4. Vitaly Agapov
   
   25 October 2016 - 9:28

   You may try to create a synonym-like view in PgSQL:

   CREATE VIEW short AS SELECT * FROM some_long_name;

   And then just create a synonym in Oracle:

   create synonym some_pg_table for "short"@PG_LINK;

   This might work

5. David
   
   20 December 2018 - 19:35

   Hi Vitaly, thanks for this useful manual… maybe you have a RPM for PostgreSQL 11 on CentOS 7? I have a lot of problems building it from the git source.

Leave a comment