AWS Database Blog

Using an Oracle Database Gateway to connect Amazon RDS Custom for Oracle to PostgreSQL

Amazon Relational Database Service (Amazon RDS) Custom for Oracle allows you to run Oracle databases on AWS, giving you some of the benefits of Amazon RDS automation and the freedom of changing configurations to match different use cases.

While working with customers, we have observed use cases that require a connection to be kept with a different database engine. In this post, we discuss how create a connection between an RDS Custom for Oracle instance and PostgreSQL.

PostgreSQL has the ability to connect to Oracle via a foreign data wrapper (FDW); however, connecting from Oracle to PostgreSQL requires the use of an ODBC Gateway, which doesn’t require any specific license.

Solution overview

The high-level steps for implementing this solution are as follows:

  1. Install ODBC on the RDS Custom for Oracle instance.
  2. Configure an ODBC Gateway according to the PostgreSQL instance you want to connect.
  3. Test the connection between the instances using isql (which comes preinstalled on the RDS Custom for Oracle instance).
  4. Set up a database link inside the Oracle database on Amazon RDS Custom for Oracle to connect to the PostgreSQL database with the configured ODBC settings.
  5. Test the database link.

Prerequisites

As a prerequisite for this solution, you need to have an RDS Custom for Oracle instance running and then pause its automation, so you can install the required tools and configurations. The pause ensures that the modifications don’t interfere with RDS Custom automation.

For more information, refer to Setting up your environment for Amazon RDS Custom for Oracle.

A PostgreSQL instance is also necessary. For this post, we use an Amazon RDS for PostgreSQL version 12 instance.

Make sure that there is network connectivity between the two instances.

Install ODBC

For the ODBC configuration, you need to install the following in the RDS Custom for Oracle host:

  • The PostgreSQL repositories
  • unixODBC
  • The PostgreSQL driver for ODBC

Complete the following steps:

  1. Download and install the PostgreSQL repositories with the following command: 
    sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

  2. Download and install ODBC with the following command: 
    sudo yum install unixODBC.x86_64

  3. Download and install the PostgreSQL ODBC driver with the following command: 
    sudo yum install postgresql12-odbc.x86_64

For this post, we installed ODBC for PostgreSQL version 12 and its drivers. Make sure to install the correct version based on your database and operating system.

Configure an ODBC Gateway

Next, we configure the files necessary for the ODBC Gateway configuration:

  • /etc/odbcinst.ini – Holds information about the ODBC installation and should be configured according to where ODBC and the PostgreSQL ODBC driver were installed on your instance
  • /etc/odbc.ini – Contains the information that will be used by our database link

Complete the following steps:

  1. On the RDS Custom for Oracle instance, edit the /etc/odbcinst.ini file.
  2. Modify the name between the brackets if you wish and make note of it.This name is used on the configuration of the next file to reference it.
  3. Modify the parameter values according to where the PostgreSQL drivers were installed. Make sure your file has the correct path to the PostgreSQL installation.The following template is an example of the /etc/odbcinst.inifile: 
    # Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQLdriver]
Description = ODBC for PostgreSQL
Driver = /usr/pgsql-12/lib/psqlodbcw.so
Setup = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/pgsql-12/lib/psqlodbcw.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
  4. Still on the RDS Custom for Oracle instance, edit the /etc/odbc.ini file.
    The file can hold multiple instance configurations; the value between brackets is the name of the data source.
  5. Set a data source name and make a note of it.
  6. Configure the parameters on the file according to your PostgreSQL instance.

Note that the Driver parameter is the name of the driver configuration we saved earlier.

For this example, the password is configured in plain text on the file. To follow best practices, it is advised to encrypt this information using the dg4pwd utility.

The following template is an example of the /etc/odbc.ini file:

[pgdb]
Driver = PostgreSQLdriver
Description = PostgreSQL ODBC Driver
Database = postgres
Servername = pgtestcinstance.cz117idha32r.eu-central-1.rds.amazonaws.com
Username = postgres
Password = postgres123
Port = 5432
UseDeclareFetch = 1
CommLog = /tmp/pgodbclink.log
Debug = 1
LowerCaseIdentifier = 1

Refer to How to specify as a connection option to learn more about the odbc.ini file parameters. For additional descriptions of the parameters, refer to Connection properties.

Test the connection with isql

You can now use the isql utility to test the connection with the following command:

isql pgdb

If the connection is successful, a Connected! message shows in the output.

If the connection fails, it could be that the file configuration wasn’t done properly. Use the -v option of isql to get additional information for troubleshooting:

isql -v pgdb

After the connection is set up, it’s time to set up our database link.

Create and configure the database link

First, we configure the Oracle Database service handler, which provides the information of how to connect to the ODBC driver to our database link.

  1. On the RDS Custom for Oracle database, go to the $ORACLE_HOME/hs/admin folder.
    This folder inside ORACLE_HOME stores information related to the Oracle heterogeneous service, which you can use to operate Oracle with heterogeneous engines. For more information, refer to Database Heterogeneous Connectivity User’s Guide.
  2. Create a file named init<datasource>.ora and use the name of the data source that you configured in the odbc.ini file.
    For this example, we name the file initpgdb.ora.
    Note that you need to use sudo su <user> to switch to the user that owns the Oracle Home (the default Oracle Home owner on Amazon RDS Custom for Oracle is rdsdb).
  3. Edit the init<datasource>.ora file and set the following parameters:
    1. HS_FDS_CONNECT_INFO – This should reference the data source name configured in the odbc.ini file.
    2. HS_FDS_SHAREABLE_NAME – This should point to the ODBC Driver Manager library, which in this example was installed under /usr/lib64/libodbc.so. Different operational systems could have it on different folders.
    3. set ODBCINI – The path to where the odbc.ini file is.

    The following template is an example of what the file should look like.

    We also set the HS_LANGUAGE and HS_NLS_NCHAR parameters, because the ODBC driver might not support the character set from our Oracle database. For more information, refer to the support document Doc ID 2325424.1. The workaround is to explicitly configure the character settings with these two parameters.

    Next, we configure two more files, tnsnames.ora and listener.ora. These files can be found on the $ORACLE_HOME/network/admin path.

  4. Edit the tnsnames.ora file and add a new entry.
    This file contains the information that is used by the database link to open the connection.
  5. Because it’s using an ODBC Gateway installed on the same machine where our Oracle database is running, we can set the HOST parameter to localhost.
  6. We also need to tell Oracle that we’re using Oracle heterogeneous services by specifying the HS=OK parameter.
    The tnsnames entry should look like the following code:

    pgdb =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=pgdb))
(HS=OK)
)

    Note that the port configuration here must match the port used by your listener; the default is 1521.

  7. Edit the listener.ora file and add an entry for our data source so connections can be directed to the ODBC Gateway. The default listener on Amazon RDS Custom for Oracle already has a SID_LIST_LISTENER configured as SID_LIST_<listener name>. For this post, we added a new entry under SID_LIST_L_ORCL_001, as shown in the following screenshot. It’s also recommended to specify the LD_LIBRARY_PATH to avoid any conflicts with other ODBC drivers that might be already installed on the instance. This parameter should include the path for $ORACLE_HOME/lib and the ODBC drivers, as shown in the following example.
  8. Now you can log in to SQL*Plus using your configured primary user to create the database link and reference the entry just added on tnsnames.ora: 
    CREATE PUBLIC DATABASE LINK pgdblink
CONNECT TO "postgres"
IDENTIFIED BY "postgres123"
USING 'pgdb';

Test the database link

Now that we have our database link created, we can perform tests on the PostgreSQL views, such as pg_database. We have to pass the PostgreSQL objects between double quotation marks because PostgreSQL stores objects in lowercase by default, whereas Oracle stores them in uppercase.

Conclusion

In this post, we demonstrated how to configure a database connection between an RDS Custom for Oracle and PostgreSQL instance using the ODBC drivers. For more information about Amazon RDS Custom for Oracle, refer to Working with RDS Custom for Oracle. For more in-depth information on how to migrate to Amazon RDS Custom for Oracle, see Physical migration of Oracle databases to Amazon RDS Custom using Data Guard.

About the Authors

Marcio Fernando da Cruz is a Database Specialist Solutions Architect based in Amsterdam. He assists customers in finding the best migration strategy and modernising their databases and architectures on AWS with a focus on Oracle and MySQL.

Tom Harper is the Manager of the EMEA Relational Databases Specialist Team, based in Manchester, UK. He works in the enterprise space to support innovation in the database domain. His team helps customers adopt the best migration strategy and design database architectures on AWS with relational managed solutions.