Steps to create a Database Link from Oracle to PostgreSQL

Jamsher Khan 17 mins0
()

Steps to create a Database Link from Oracle to PostgreSQL

Nowadays many companies are using a mix of RDBMS system for example, some instances are running on Oracle and other instances are running on PostgreSQL, and if we want to access data we need to do it using a heterogeneous connection. Heterogeneous connections allow us to query data from non-Oracle databases using SQL.


In this blog post, we will see How to create a database link from Oracle to PostgreSQL via ODBC.

 

Below are the high-level steps we will follow to set up dblink.
1. Setup PostgreSql User in PostgreSQL Cluster
2. Install PostgreSQL ODBC Drivers in Oracle Server
3. Edit odbc.ini file & Test DSN’s connectivity in Oracle Server
4. Create initPG.ora file in Oracle Server
5. Configure tnsname.ora & listener.ora file in Oracle Server
6. Create DB Link & Test Connectivity in Oracle Server

 

Below are setup details and the same will be used in this demonstration.

Sr. No.DatabaseVersionHostnameIPPort
1Oracle19.3test-machine01192.168.114.1771521
2PostgreSQL13.2test-machine02192.168.114.1765432



Step 1. Setup PostgreSql User: We will create user fdw_user and provides it with ALL grants on the public schema of postgres database. Make sure to update file pg_hba.conf to allow Oracle DB connection. I have added the line “host all all 192.168.114.0/24 scram-sha-256” as I am using the ” 192.168.114.0/24 ” subnet.

[root@test-machine02 ~]# 
[root@test-machine02 ~]# su - postgres
Last login: Tue Aug 24 11:37:05 +03 2021 on pts/1
[postgres@test-machine02]
[postgres@test-machine02]psql
psql (13.2)
Type "help" for help.

postgres=#
postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=#
postgres=# CREATE USER fdw_user WITH ENCRYPTED PASSWORD 'secret';
CREATE ROLE
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 appuser   | Superuser                                                  | {}        |
 fdw_user  |                                                            | {}        |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 repluser  | Replication                                                | {}        |

postgres=#
postgres=# create table employee (id int, first_name varchar(20), last_name varchar(20));
CREATE TABLE
postgres=# insert into employee values (1,'jobin','augustine'),(2,'avinash','vallarapu'),(3,'fernando','camargos');
INSERT 0 3
postgres=# \dt employee
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | employee | table | postgres
(1 row)

postgres=# GRANT ALL ON ALL TABLES IN SCHEMA "public" TO fdw_user;
GRANT
postgres=#
postgres=# exit
[postgres@test-machine02]
[postgres@test-machine02]psql -htest-machine02 -Ufdw_user -W postgres
Password:
psql (13.2)
Type "help" for help.

postgres=> select count(*) from employee;
 count
-------
     3
(1 row)


postgres=> select current_user;
 current_user
--------------
 fdw_user
(1 row)

postgres=>
postgres=> select current_database();
 current_database
------------------
 postgres
(1 row)

postgres=> SELECT current_schema();
 current_schema
----------------
 public
(1 row)

postgres=>

[postgres@test-machine02]pwd
/var/lib/pgsql/13/data
[postgres@test-machine02]cat pg_hba.conf
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             192.168.114.0/24        scram-sha-256
[postgres@test-machine02]


Step 2. Install PostgreSql ODBC Drivers: Use OS command yum install postgresql-odbc to install PostgreSQL ODBC drivers. Please note below command will work only if you have a PostgreSQL repository configured. Follow the link: https://dbsguru.com/install-postgresql-13-on-linux-using-yum-command/ to configure the PostgreSQL repository.

[root@test-machine01 ~]#
[root@test-machine01 ~]# yum install postgresql-odbc
Loaded plugins: langpacks, ulninfo
Repository ol7_UEKR5 is listed more than once in the configuration
Repository ol7_UEKR4 is listed more than once in the configuration
Resolving Dependencies
--> Running transaction check
---> Package postgresql-odbc.x86_64 0:09.03.0100-2.el7 will be installed
--> Processing Dependency: libodbcinst.so.2()(64bit) for package: postgresql-odbc-09.03.0100-2.el7.x86_64
--> Running transaction check
---> Package unixODBC.x86_64 0:2.3.1-14.0.1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                          Arch                                    Version                                           Repository                                   Size
==============================================================================================================================================================================================
Installing:
 postgresql-odbc                                  x86_64                                  09.03.0100-2.el7                                  ol7_latest                                  267 k
Installing for dependencies:
 unixODBC                                         x86_64                                  2.3.1-14.0.1.el7                                  ol7_latest                                  413 k

Transaction Summary
==============================================================================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 680 k
Installed size: 1.8 M
Is this ok [y/d/N]: y
Downloading packages:
(1/2): postgresql-odbc-09.03.0100-2.el7.x86_64.rpm                                                                                                                     | 267 kB  00:00:01
(2/2): unixODBC-2.3.1-14.0.1.el7.x86_64.rpm                                                                                                                            | 413 kB  00:00:01
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                         442 kB/s | 680 kB  00:00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : unixODBC-2.3.1-14.0.1.el7.x86_64                                                                                                                                           1/2
  Installing : postgresql-odbc-09.03.0100-2.el7.x86_64                                                                                                                                    2/2
  Verifying  : postgresql-odbc-09.03.0100-2.el7.x86_64                                                                                                                                    1/2
  Verifying  : unixODBC-2.3.1-14.0.1.el7.x86_64                                                                                                                                           2/2

Installed:
  postgresql-odbc.x86_64 0:09.03.0100-2.el7

Dependency Installed:
  unixODBC.x86_64 0:2.3.1-14.0.1.el7

Complete!
[root@test-machine01 ~]#


Step 3. Edit odbc.ini file & Test DSN’s connectivity: Create file odbc.ini under /etc directory. Add PostgreSQL Cluster and User details. Please note we are using [PG] as a header for your block. Use command isql -v pg to test DNS connectivity for PostgreSQL Database.

[root@test-machine01 ~]#
[root@test-machine01 ~]# vi /etc/odbc.ini
[PG]
Description = PG
Driver = /usr/lib64/psqlodbc.so
ServerName = test-machine02
Username = fdw_user
Password = Root@1234
Port = 5432
Database = postgres
[Default]
Driver = /usr/lib64/libodbcpsqlS.so
:wq!
[oracle@test-machine01 ~]$

[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ isql -v pg
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>


Step 4. Create initPG.ora file: Create file initPG.ora under directory $ORACLE_HOME/hs/admin and add below parameters. Please note we are using PG as our SID name and in parameter, HS_FDS_CONNECT_INFO = PG is the same Header we using in file: odbc.ini

[oracle@test-machine01 ~]$
[oracle@test-machine01 ~]$ cd $ORACLE_HOME/hs/admin
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ pwd
/u01/app/oracle/product/19.3.0/db_1/hs/admin
[oracle@test-machine01 admin]$ ls -l
total 16
-rw-r--r--. 1 oracle oinstall 1170 Apr 17  2019 extproc.ora
-rw-r--r--. 1 oracle oinstall  489 Apr 17  2019 initdg4odbc.ora
-rw-r-----. 1 oracle oinstall  407 Nov  2  2020 listener.ora.sample
-rw-r-----. 1 oracle oinstall  244 Nov  2  2020 tnsnames.ora.sample
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ vi initPG.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = PG
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME=/tmp/ora_hs_trace.log
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
:wq!
[oracle@test-machine01 admin]$


Step 5. Configure tnsnames.ora & listener.ora file: Configure tnsnames.ora & listener.ora same as below. Please note here SID = PG is the SID name we created in Step 4. Once the configuration is done perform tnsping pg.

[oracle@test-machine01 admin]$ pwd
/u01/app/oracle/product/19.3.0/db_1/network/admin
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ vi tnsnames.ora
PG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = PG)
    )
    (HS = OK)
  )
:wq!
[oracle@test-machine01 admin]$

[oracle@test-machine01 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-SEP-2021 15:23:26

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-machine01)(PORT=1521)))
The command completed successfully
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ vi listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (PROGRAM = dg4odbc)
      (SID_NAME = PG)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)
     )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
:wq!
[oracle@test-machine01 admin]$

[oracle@test-machine01 admin]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-SEP-2021 15:24:33

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.3.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/test-machine01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test-machine01)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-machine01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                29-SEP-2021 15:24:33
Uptime                    0 days 0 hr. 0 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/test-machine01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test-machine01)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "pg" has 1 instance(s).
  Instance "pg", status UNKNOWN, has 1 handler(s) for this service...
Service "test01" has 1 instance(s).
  Instance "test01", status READY, has 1 handler(s) for this service...
Service "test01XDB" has 1 instance(s).
  Instance "test01", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@test-machine01 admin]$

[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ tnsping pg

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 29-SEP-2021 15:25:06

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521))) (CONNECT_DATA = (SID = PG)) (HS = OK))
OK (0 msec)
[oracle@test-machine01 admin]$


Note: Instead of stop/start listener you can also use reload listener after modification in file listener.ora.

Step 6. Create DB Link & Test Connectivity: Once all the above steps are done successfully. We are ready to create dblink, connect to Oracle Database, and use Create database link command to create database link. Please note we need to use “ “ around username as PostgreSQL is case sensitive. You will receive an error “FATAL: password authentication failed for user “FDW_USER”” if we forget to use ” “. Once DBLink is created try to access PostgreSQL table: employee.

[oracle@test-machine01 ~]$
[oracle@test-machine01 ~]$ sqlplus sys as sysdba


SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 29 15:28:14 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>
SQL> CREATE DATABASE LINK PG_DBLINK  CONNECT TO "fdw_user"  IDENTIFIED BY secret USING 'PG';

Database link created.
SQL>
SQL>  select * from "public"."employee"@PG_DBLINK;

        id first_name                               last_name
---------- ---------------------------------------- ----------------------------------------
         1 jobin                                    augustine
         2 avinash                                  vallarapu
         3 fernando                                 camargos

SQL>


Click here to know more about Steps to create a Database Link from Oracle to MySQL


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.


Related Article

, , , ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Related Articles