There are quite a few blogs about this subject, but I wanted to share how I performed creating this database link from Oracle to PostgreSQL. Though I mention Oracle 19C, these steps will probably work on all Oracle database versions from 11G onward. For the PostgreSQL version, this will work presumably from 11 and onward. Important to know is in this example the Oracle database lives on Red Hat Linux, this is important for the needed client and ODBC software. But this software should also be available on other Linux flavors.
This blog presumes you have full control (as a DBA) over the Oracle database. For what needs to be done on Red Hat, you may need a Linux engineer/sysadmin to perform the necessary tasks there, if you have no root access. We do not need full control over the PostgreSQL database, but we do need some connection details from a PostgreSQL DBA/engineer.
PostgreSQL prerequisites
What you need to know about the PostgreSQL database you want to connect to:
- The FQDN/hostname or IP-address of where the PostgreSQL database resides.
- The portnumber to connect to for PostgreSQL. The default is 5432, but make sure it’s not another port.
- The name of the PostgreSQL database to connect to.
- The PostgreSQL database user account to connect with
- The password that belongs to the above user. The password length must not exceed 30 characters. More on this later.
The PostgreSQL DBA can provide you with these details and you should make sure that the PG DBA has made the necessary pg_hba.conf entry for the Postgres database user.
Installing PostgreSQL software on Red Hat Linux server
In this example the Oracle 19C database is installed on Red Hat Linux server. Here’s what you need to do or get done on Red Hat Linux to get the PostgreSQL client and ODBC driver. If you don’t have root access you can ask your Linux sysadmin to perform these tasks:
Head to the Linux downloads for the Red Hat family from the official PostgreSQL site:
https://www.postgresql.org/download/linux/redhat/
Choose the right version, platform and architecture on that page, so you’ll get a script tailor made to use on your Red Hat or derivative for a PostgreSQL server install. But that’s not what you want, you only want the PostgreSQL client and ODBC driver. So you need to edit this script to be able tp install the right stuff you need. Suppose I chose this for the script:
- Select version:
11 - Select platform:
Red Hat Enterprise, CentOS, Scientific or Oracle version 7 - Select architecture:
x86_64
The result would be:
# Install the repository RPM: sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm # Install PostgreSQL: sudo yum install -y postgresql11-server # Optionally initialize the database and enable automatic start: sudo /usr/pgsql-11/bin/postgresql-11-setup initdb sudo systemctl enable postgresql-11 sudo systemctl start postgresql-11
Now you only need the PostgreSQL client and ODBC-software, so we change the script to this:
# Install the repository RPM: sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm # Install PostgreSQL client and ODBC-driver: sudo yum install -y postgresql11-client postgresql11-odbc
So you change the script to your needs and execute the commands, and accept the dependencies that are suggested.
Test the connection to the PostgreSQL database
First we want to test the connection to the PostgreSQL database from the Red Hat server with Oracle database running. We perform tests a few times on different stages in this walkthrough, because nothing is more annoying than getting an error at the end of this story and not knowing at which point there was be a problem.
So you fire up your ssh connection to your Red Hat Server (again, where your Oracle database resides and where you installed the PostgreSQL client and ODBC driver), and use telnet to do a rudimentary test on the connection:
telnet [FQDN/hostname or IP-address of the PostgreSQL server] [port of the PostgreSQL database]
[oracle@ora19cdbserver]telnet 192.168.178.33 5432 Trying 192.168.178.33… Connected to 192.168.178.30. Escape character is '^]'.
This is a succesfull test to the port. If it stays on Trying … that could mean there is a firewall in the way, Postgres is not listening, the port is wrong or the server is wrong.
After the telnet connection is succesfull, we take the test to the next level, have your PostgreSQL connection details at hand to build your connection string:
psql -h [FQDN/hostname or IP-address] -p [portnumber] -U [database user] -d [databasename]
Let’s see this in action. After passing the connect string you get a prompt for the password, so you type or paste the password in there:
[oracle@ora19cdbserver ~]$ psql -h 192.168.178.33 -p 5432 -U ora2pgusr -d pgdatadb Password for user ora2pgusr: psql (11.10) Type "help" for help. pgdatadb=>
We want to do some further tests on the psql prompt to get some info how we are connected:
pgdatadb=> \conninfo You are connected to database "pgdatadb" as user "ora2pgusr" on host "192.168.178.33" at port "5432". pgdatadb=>
Now let’s see what databases are available and some properties of these databases:
pgdatadb=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- masterdb | pgadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/pgadmin + | | | | | pgadmin=CTc/pgadmin + | | | | | esb=c/pgadmin pgdatadb | pgdatadb | UTF8 | C | C | =Tc/pgdatadb + | | | | | pgdatadb=CTc/pgdatadb + | | | | | esb=c/pgdatadb + | | | | | etl=c/pgdatadb postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (7 rows) pgdatadb=>
We also want to know what relations (database objects like tables, views) are available to this user:
pgdatadb=> \dtv+ List of relations Schema | Name | Type | Owner | Size | Description ----------------------+--------------------------------------+------+---------+---------+------------- ora2pgusr | helloworld | view | pgdatadb | 0 bytes | public | data_staging_v | view | pgdatadb | 0 bytes | (2 rows) pgdatadb=>
So let’s have a closer look at the first table, with the Oracle equivalent for DESC:
pgdatadb-> \d+ helloworld View "ora2pgusr.helloworld" Column | Type | Collation | Nullable | Default | Storage | Description -----------+--------------------------+-----------+----------+---------+----------+------------- greetings | character varying | | | | extended | to | name | | | | plain | on | timestamp with time zone | | | | plain | View definition: SELECT helloworld.greetings, CURRENT_USER AS "to", CURRENT_TIMESTAMP AS "on" FROM public.helloworld; pgdatadb-> quit [oracle@ora19cdbserver ~]$
We got some info about the connection at hand, the databases that are there, which tables and views are present for us and the contents of one of the views.
Now let’s proceed to setting up the ODBC connection.
Setting up the ODBC connection
Check if the odbcinst.ini file is present:
[oracle@ora19cdbserver ~]$ cat /etc/odbcinst.ini # Example driver definitions # Driver from the postgresql-odbc package # Setup from the unixODBC package [PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib/psqlodbcw.so Setup = /usr/lib/libodbcpsqlS.so Driver64 = /usr/lib64/psqlodbcw.so Setup64 = /usr/lib64/libodbcpsqlS.so FileUsage = 1
As you can see this is an an example driver definition, but you can use it right away if you want. Do remember the [PostgreSQL], you should use this throughout this walkthrough. If you don’t want that, change the name in that file right away or copy this entry and add another one with a different name. It’s up to you. By the way, this is a root owned file, so you need sudo or root to edit it, or a friendly Linux sysadmin to do it for you. We go along with this example.
Create odbc.ini, the file is probably not present. This should also be a file in the /etc/ directory, so it should be made by root, so again ask your Linux sysadmin if you don’t have root access. Make sure this is the content according to the PostgreSQL connection details:
[PostgreSQL] Description = pg Driver = /usr/pgsql-11/lib/psqlodbc.so ServerName = 192.168.178.33 Username = ora2pgusr Password =Port = 5432 Database = pgdatadb Trace = yes TraceFile = /tmp/odbctrace.txt
I’ve left the password field empty in this example, but you should enter the password of the ora2pgusr that you were given by the PostgreSQL DBA after the Password =
Now you can test with isql over the ODBC connection, and perform a query to the known helloworld view.
[oracle@ora19cdbserver ~]$ isql -v PostgreSQL +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select 1; +------------+ | ?column? | +------------+ | 1 | +------------+ SQLRowCount returns 1 1 rows fetched SQL> select * from helloworld; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------------------------+ | greetings | to | on | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------------------------+ | Hello world! | ora2pgusr | 2020-12-28 12:43:53.68416 | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------+---------------------------+ SQLRowCount returns 1 1 rows fetched SQL>
Enabling the ODBC connection in Oracle database
Now we get to the Oracle part of being able to use the ODBC connection.
Create the following file:
$ORACLE_HOME/hs/admin/
[oracle@ora19cdbserver ~]$ cd $ORACLE_HOME/hs/admin [oracle@ora19cdbserver ~]$ vim initPostgreSQL.ora
Add this as the content, do mind to look at the database list above to what language.collation to use. For this database it is en_US.UTF-8, so we use this as HS_LANGUAGE. For the HS_FDS_SHAREABLE_NAME, as per an Oracle SR, must point to the ODBC Driver manager and not to the driver library (/usr/pgsql-11/lib/psqlodbc.so):
HS_FDS_CONNECT_INFO = PostgreSQL HS_FDS_TRACE_LEVEL = ON HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so HS_LANGUAGE = en_US.UTF-8 set ODBCINI=/etc/odbc.ini
Add an entry to the listener.ora:
[oracle@ora19cdbserver ~]$ cd $ORACLE_HOME/network/admin [oracle@ora19cdbserver ~]$ vim listener.ora
Add this as content after the existing content, be sure to set ORACLE_HOME to your Oracle home path (do: echo $ORACLE_HOME at your prompt to know the path). But… in an Oracle SR, thanks to another colleague at Ordina, after working for a while there were connection problems (ORA-28500). The engineer in the SR advised us not to use an environment variable in ENVS=. So no $ORACLE_HOME, but the full path to the lib, and also the paths to local libs.
SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=PostgreSQL) (ORACLE_HOME=/ora0/app/ora19000) (PROGRAM=dg4odbc) (ENVS=LD_LIBRARY_PATH=/usr/pgsql-11/lib:/usr/lib64:/ora0/app/ora19000/lib) ) )
Add an entry to the tnsnames.ora:
[oracle@ora19cdbserver ~]$ cd $ORACLE_HOME/network/admin [oracle@ora19cdbserver ~]$ vim tnsnames.ora
add this in the tnsnames.ora:
PostgreSQL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SID = PostgreSQL) ) (HS=OK) )
Now you have to restart your Oracle listener:
[oracle@ora19cdbserver ~]$ lsnrctl stop && lsnrctl start
Test with tnsping:
[oracle@ora19cdbserver ~]$ tnsping PostgreSQL TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-DEC-2020 15:41:11 Copyright (c) 1997, 2020, Oracle. All rights reserved. Used parameter files: /ora0/app/ora19000/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SID = PostgreSQL)) (HS=OK)) OK (0 msec) [oracle@ora19cdbserver admin]$
Now you can create the database link in your Oracle database to the PostgreSQL database. You can make a public database link, but we make a normal database link for a chosen Oracle database schema. I have left the space for the password empty on purpose, you should enter the password for the PostgreSQL user there
CREATE DATABASE LINK PGDATADB_PG CONNECT TO "ora2pgusr" IDENTIFIED BY
The first time I tried this, I got a password that was 32 characters long. Oracle didn’t like that, and gave me a ORA-00972: identifier is too long error, and refused to create the database link. So I had to ask the PostgreSQL sysadmin to shorten the password to 30 characters. After that there was no problem creating the database link.
Now you can test the database link with sqlplus, SQL Developer or other tool of choice. Do keep in mind that tables and view have to be referenced with double quotes, otherwise you’ll get an error:
select * from helloworld@PGDATADB_PG;
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ERROR: relation "HELLOWORLD" does not exist;
No query has been executed with that handle {42P01,NativeErr = 1}
ORA-02063: preceding 3 lines from PGDATADB_PG
This will work though:
select * from "helloworld"@PGDATADB_PG; output: greetings -------------------------------------------------------------------------------- to -------------------------------------------------------------------------------- on --------- Hello world! ora2pgusr 28-DEC-20 1 row selected.
If you use Toad and choose Test on the database link, do know that it will never use double quotes around a table or view, so you’ll always see an error:
*********************************************************************
Link : "PGDATADB_PG"
Error : ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ERROR: relation "GLOBAL_NAME" does not exist;
No query has been executed with that handle {42P01,NativeErr = 1}
ORA-02063: preceding 3 lines from PGDATADB_PG
*********************************************************************
So using Test in TOAD is not a good test in this case. Test with a query with double quotes around the object.
