Dblink between Oracle & PostgreSQL

One of our databases was migrated from Oracle to Postgresql, mainly for a costs issue. Weeks later the database was migrated we need to make a report joinning data from one of our Oracle databases and this postgresql. We found a few ways to make it, but we finally decided to use Oracle Database Gateway.

For keep the same version of the Oracle database I decided to use 12c version, but you can use the 18 or 19 version if you want.

Download the software https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle12c-linux-12201-3608234.html

You can install Oracle Database Gateway in either of the following configurations:

  • On the same computer as an existing Oracle database but in a different Oracle home.
  • On a system with no Oracle database.
  • On the same computer as the Oracle database, and in the same Oracle home directory. Note that in this case, the Oracle database and the gateway must be at the same release level.

Now we are going to install in the same server where the database is but in a different Oracle Home.

ORACLE_BASE = /u01/app/oracle
ORACLE_HOME = /u01/app/oracle/product/gateway/12.2.0/dbhome_1

Hardware requirements for a installation for ODBC

Uncompress the software and execute the installer. The installer is the same that you already know for another Oracle products, so it’s really easy to install. There are no special questions to answer in it. I only have to say, the installer will create a new listener… I won’t use this listener and we’ll remove it later.

For the driver installation we’ll use the driver packed with redhat.

yum install postgresql-odbc unixODBC unixODBC-devel

Verify that the PostgreSQL driver has installed correctly

[root@labo1234 ~]# odbcinst -q -d
[PostgreSQL]
[root@labo1234 ~]#

Check the current the odbc driver configuration

[root@labo1234 ~]# grep -v ^# /etc/odbcinst.ini
 
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib64/psqlodbcw.so
Setup           = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1
 
[root@labo1234 ~]#

Create the DSN

[root@labo1234 ~]# cat /etc/odbc.ini
[TSMPGSQL]
Driver = PostgreSQL
Description = PostgreSQL Data Source
Servername = 10.70.11.43
Port = 5432
UserName = my_postgresql_username
Password = my_postgresql_password
Database = my_database_name
ReadOnly = no
ServerType = Postgres
ConnSettings = UseServerSidePrepare=1
ByteaAsLongVarBinary=1
Optimizer=0
Ksqo=0
TraceFile = /var/log/odbc-TSMPGSQL-trace.log
Debug = No
DebugFile = /var/log/odbc-TSMPGSQL-debug.log
 
[root@labo1234 ~]#

Make a odbc connection test

[root@labo1234 ~]# isql TSMPGSQL
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

Ok, the odbc is ready. Let’s go now with the Oracle Gateway.

Create the init file (remember, this ORACLE_HOME is from the gateway, not your database)

[@labo1234 ~]$ cd $ORACLE_HOME/hs/admin
[@labo1234 admin]$ cp -a initdg4odbc.ora initPSQLNTE.ora

Set the initialization parameters

[@labo1234 admin]$ pwd
/u01/app/oracle/product/gateway/12.2.0/dbhome_1/hs/admin
[@labo1234 admin]$ grep -v ^# initPSQLNTE.ora
 
HS_FDS_CONNECT_INFO = TSMPGSQL
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_TRACE_LEVEL = 255
HS_FDS_TRACE_FILE_NAME = /tmp/odbc_trace.trc
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
 
set ODBCINI=/etc/odbc.ini
[@labo admin]$

Remove the new created listener… in this case my database installation is an Oracle Restart, so, I will use srvctl.

[@labo1234 ~]$ srvctl remove listener -l LISTENER_GATEWAY

Add this to our current runing listener.ora (in the grid home in this example)

SID_LIST_LISTENER=
   (SID_LIST=
      (SID_DESC=
         (SID_NAME=PSQLNTE)
         (ORACLE_HOME=/u01/app/oracle/product/gateway/12.2.0/dbhome_1)
         (PROGRAM=dg4odbc)
         (ENV="LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/gateway/12.2.0/dbhome_1/lib")
      )
   )

Add this to the tnsnames.ora (database home)

PSQLNTE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = labo1234)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PSQLNTE)
    )
    (HS = OK)
  )

Restart the listener…. A reload must me enough but I prefer make a restart.

[@labo1234 ~]$ srvctl stop listener -listener LISTENER
[@labo1234 ~]$ srvctl start listener -listener LISTENER

Now, in the database create our test dblink

SQL> create public database link DBTEST connect to "my_postgresql_username" identified by "my_postgresql_password" using 'PSQLNTE';
 
Database link created.
 
SQL>

And try to make a select though the dblink

SQL> select count(*) from "te3_data"@DBTEST;
 
  COUNT(*)
----------
   6951048
 
SQL>

…. and done!