AWS Database Blog

Heterogenous data sources: Access your data in PostgreSQL from Amazon RDS for Oracle using Oracle Database Gateway

Amazon Relational Database Service (Amazon RDS) for Oracle is a comprehensive, fully managed commercial database service. It simplifies the setup, operation, and scalability of Oracle deployments in the cloud. By handling critical database administration tasks such as provisioning, backups, software patching, monitoring, and hardware scaling, Amazon RDS allows you to concentrate on innovation and application development.

In certain customer scenarios, Amazon RDS for Oracle databases need to connect to external data sources, such as RDS for PostgreSQL. For instance, a customer might want to query PostgreSQL data using a database link from Oracle. This requirement often arises when different applications use distinct databases and there is a data dependency between the applications.

PostgreSQL can establish connections to Oracle databases using a foreign data wrapper (FDW). However, when connecting from RDS for Oracle to PostgreSQL, you need an Oracle Database Gateway for ODBC (DG4ODBC) installed on a separate server such as an Amazon Elastic Compute Cloud (Amazon EC2) instance, because you don’t have access to the underlying operating system of RDS for Oracle. According to Oracle documentation, “Oracle Database Gateway for ODBC can be installed and used on a machine different from the machine where the Oracle Database is installed and used. It is not necessary to obtain a separate license for the machine running Oracle Gateway for ODBC.”

In this post, we walk you through setting up an EC2 instance as a database gateway server. You will install and configure Oracle Database Gateway for ODBC (DG4ODBC), ODBC drivers, a PostgreSQL client, and PostgreSQL libraries. With this setup, you can create database links on RDS for Oracle to connect to PostgreSQL through this gateway.

If you’re using RDS Custom for Oracle, see Using an Oracle Database Gateway to connect Amazon RDS Custom for Oracle to PostgreSQL.

Solution overview

The following diagram provides an overview of the solution. In RDS for Oracle, when a user needs to access data from RDS for PostgreSQL, the solution uses a database link pointing to Oracle Database Gateway for ODBC (DG4ODBC), which uses ODBC drivers and PostgreSQL client libraries to connect to RDS for PostgreSQL and retrieves the data.

Architecture diagram

  1. When a user connects to RDS for Oracle and queries the RDS for PostgreSQL target database, the query is sent to the heterogeneous services (HS) component of the Oracle database gateway over the database link (created for RDS for PostgreSQL).
  2. Upon receiving the request, the DG4ODBC parses the /etc/odbc.ini config file (which has the necessary PostgreSQL connection information and libraries) and retrieves the drivers needed for the connection.
  3. The connection is then established to the PostgreSQL database using the PostgreSQL ODBC drivers and PostgreSQL client libraries.

Prerequisites

For RDS for Oracle databases to connect to external data sources, you need to install Oracle Database Gateway on an EC2 instance along with the required ODBC drivers. The following are the prerequisites for this solution:

  1. For the sake of simplicity, we are using the same virtual private cloud (VPC) to create the services that follow. Also, we are using the same subnet to minimize unexpected network errors. If you need to run the services in different VPCs or different subnets, you can use these scenarios.
  2. Create an EC2 instance with Linux x86-64 OS (check the certified OS version for Oracle Database Gateway from My Oracle Support Document 2617521.1 – Certification Matrix for Oracle Database Gateway Products versions 11.2.x and greater), which will be used to install and run Oracle Database Gateway for ODBC. See Tutorial: Get started with Amazon EC2 Linux instances for guidance on how to launch an EC2 instance. In this post, we’re using the latest AMI for RHEL 8 at the time of writing.
  3. Make sure the EC2 instance meets the minimum hardware and software requirements to run Oracle Database Gateway for ODBC.
  4. Create an RDS for PostgreSQL instance. In this post, we’re using version 15.4, which was the latest version at the time of writing.
  5. Create an RDS for Oracle instance. In this post, we’re using version 19c of RDS for Oracle.
  6. Update the Oracle Database Gateway for ODBC EC2 instance’s security group to allow inbound traffic from the RDS for Oracle instance. In this post, we’re using the default listener on Oracle Database Gateway server, which runs on port 1521. In this case, an inbound rule allowing traffic on port 1521 from RDS for Oracle instance was added.
  7. Update the RDS for PostgreSQL security group to allow inbound traffic from the Oracle Database Gateway for ODBC EC2 instance. By default, RDS for PostgreSQL accepts connections on port 5432. For this solution, we added an inbound rule allowing traffic on port 5432 from Oracle Database Gateway for ODBC EC2 instance.

Download the Oracle Database Gateway software

Prepare wget.sh script to download the Oracle Database Gateway software.

  1. Go to the Oracle Software Delivery Cloud and sign in by using Oracle credentials.
  2. Search for Oracle Database Gateways and select 19.3 or higher release of the product. For this post, we selected 19.3.
  3. Choose Continue in the top right corner to view the download queue.
  4. For Platform, select Linux x86-64 and choose Continue.
  5. Review and accept Oracle Standard Terms and Restrictions and choose Continue.
  6. On the next screen, choose the link for WGET Options, and then choose Download .sh. This will download the wget.sh file to your computer.

Install Oracle Database Gateway on Amazon EC2

  1. Connect to your Oracle database Gateway Server EC2 instance using AWS Systems Manager Session Manager.
  2. Connect to ec2-user. 
    sudo su – ec2-user
  3. Create the sh file with the content from wget.sh file generated in the previous procedure. 
    vi wget.sh
chmod 700 wget.sh
  4. Run wget.sh to download the Oracle Database Gateways 19c software. It will download V982066-01.zip. You will need to provide your My Oracle Support credentials. 
    ./wget.sh
  5. Configure swap space (required for Oracle Database Gateway installation).
    The following example creates swap space of 1GB (count=1024) which is the minimum requirement for Oracle Database Gateway installation.

    sudo /bin/dd if=/dev/zero of=/var/swap.1 bs=1M count=1024
sudo /sbin/mkswap /var/swap.1
sudo chmod 600 /var/swap.1
sudo /sbin/swapon /var/swap.1
  6. Install the required packages and unzip the install file. 
    sudo dnf install unzip
sudo dnf install libnsl
sudo dnf install make
sudo dnf install gcc
sudo dnf install libaio
unzip V982066-01.zip
  7. Find the default response file named tg.rsp in the /home/ec2-user/gateways/response folder. Crosscheck the presence of the parameters and update the following parameters within the file.
    • UNIX_GROUP_NAME
    • INVENTORY_LOCATION
    • ORACLE_HOME
    • ORACLE_BASE
    • install.tg.customComponents
    egrep -E "UNIX_GROUP_NAME|INVENTORY_LOCATION|ORACLE_HOME|ORACLE_BASE|oracle.install.tg.customComponents" /home/ec2-user/gateways/response/tg.rsp
vi /home/ec2-user/gateways/response/tg.rsp

UNIX_GROUP_NAME=ec2-user
INVENTORY_LOCATION=/home/ec2-user/oraInventory
ORACLE_HOME=/home/ec2-user/oracle/product/gateway
ORACLE_BASE=/home/ec2-user/oracle
oracle.install.tg.customComponents=oracle.rdbms.hsodbc:19.0.0.0.0
  8. Set the environment variables and crosscheck the values of the environment variables. 
    export ORACLE_HOME=/home/ec2-user/oracle/product/gateway
export PATH=$PATH:/home/ec2-user/oracle/product/gateway/bin
export LD_LIBRARY_PATH=/home/ec2-user/oracle/product/gateway/lib
export TNS_ADMIN=/home/ec2-user/oracle/product/gateway/network/admin
  9. Update .bash_profile with the same environment variables to initialize them whenever the ec2-user logs in.
  10. Run the installer. 
    cd gateways
export CV_ASSUME_DISTID=RHEL8
./runInstaller -silent -waitforcompletion -responseFile /home/ec2-user/gateways/response/tg.rsp
    Starting Oracle Universal Installer...
…
…
The installation of Oracle Database Gateways was successful.
Please check '/home/ec2-user/oraInventory/logs/silentInstall2024-05-06_05-39-07PM.log' for more details.

As a root user, execute the following script(s):
/home/ec2-user/oraInventory/orainstRoot.sh
/home/ec2-user/oracle/product/gateway/root.sh

Successfully Setup Software with warning(s).
The log of this install session can be found at:
/home/ec2-user/oraInventory/logs/installActions2024-05-06_05-39-07PM.log
  11. If runInstaller fails, clean up the following locations and retry the installation. 
    INVENTORY_LOCATION=/home/ec2-user/oraInventory
ORACLE_HOME=/home/ec2-user/oracle/product/gateway
ORACLE_BASE=/home/ec2-user/oracle
  12. In this example, as indicated by the output, installation of Oracle Database Gateways was successful. Run orainstRoot.sh and root.sh as the root user to complete the post-installation requirements. 
    sudo /home/ec2-user/oraInventory/orainstRoot.sh
    Changing permissions of /home/ec2-user/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /home/ec2-user/oraInventory to ec2-user.
The execution of the script is complete.
    sudo /home/ec2-user/oracle/product/gateway/root.sh
    Check /home/ec2-user/oracle/product/gateway/install/root_ip-172-31-4-24.us-west-2.compute.internal_2024-05-06_17-41-23-797626409.log for the output of root script
  13. It’s recommended to apply latest database release update on your Oracle Database Gateway installation. At the time of writing this post, Database Release Update 19.24.0.0.0 was the latest database release update available.

Install ODBC drivers, PostgreSQL client and libraries

  1. Install the Unix ODBC Driver Manager. 
    sudo dnf install unixODBC
  2. Install the latest PostgreSQL repository RPM on the EC2 instance that’s hosting the gateway server. 
    cd /home/ec2-user
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  3. Disable the built-in PostgreSQL module. 
    sudo dnf -qy module disable postgresql
  4. Install the latest PostgreSQL client and libraries. 
    sudo dnf install postgresql15

    Note: This will also install postgresql15-libs.

  5. Install the latest PostgreSQL ODBC drivers. 
    sudo dnf install postgresql15-odbc
  6. Define the PostgreSQL ODBC data source by configuring ODBC parameters in /etc/odbc.ini.
    This example uses RDS for PostgreSQL as a data source. Values for Database, Servername, and Port need to be set to appropriate values to connect to your PostgreSQL instance. The following is an example of /etc/odbc.ini configured to provide those values.

    sudo vi /etc/odbc.ini
    [pgdb]
Driver = /usr/pgsql-15/lib/psqlodbcw.so
Description = PostgreSQL ODBC Driver
Database = postgres
Servername = myrdspginstance.************.**-****-*.rds.amazonaws.com
Port = 5432
UseDeclareFetch = 1
CommLog = /tmp/pgodbclink.log
Debug = 1
LowerCaseIdentifier = 1
## to enable ssl
SSLMode = verify-full
  7. Use wget to download the RDS certificate bundle to connect to a PostgreSQL instance over SSL. 
    wget https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
  8. Add an ingress rule to the RDS for PostgreSQL security group to allow traffic from the EC2 instance running the Oracle ODBC gateway security group.
  9. Test the PostgreSQL connection by exporting the following parameter (update .bash_profile with the parameter for permanency), connect to the PostgreSQL database using isql and execute the SQL command to query the database name with isql. 
    export PGSSLROOTCERT=/home/ec2-user/global-bundle.pem
    isql -v pgdb <username> <password>
    select "datname" from "pg_database";
  10. A successful connection will look like the following: 
    isql -v pgdb postgres Welcome123
    +-------------------------+
| Connected!              |
| sql-statement           |
| help [tablename]        |
| echo [string]           |
| quit                    |
|                         |
+-------------------------+

    Run a test select statement as below:

    select "datname" from "pg_database";
    +--------------------------+
| datname                  |
+--------------------------+
| template0                |
| template1                |
| postgres                 |
| rdsadmin                 |
+--------------------------+
SQLRowCount returns -1
4 rows fetched

Possible errors

  • Errors like either of the following examples mean that there are issues with either the SSL configuration or the certificate.

Example 1:

$ isql -v pgdb
[08001][unixODBC]FATAL: no pg_hba.conf entry for host "172.31.33.191", user "postgres", database "postgres", no encryption
[ISQL]ERROR: Could not SQLConnect

Example 2:

$ isql -v pgdb

[08001][unixODBC]root certificate file "/home/ec2-user/.postgresql/root.crt" does not exist

Either provide the file or change sslmode to disable server certificate verification.

[ISQL]ERROR: Could not SQLConnect<
  • Errors like the following mean that the connection is timing out and the security group isn’t allowing connections from the EC2 instance running the Oracle ODBC gateway server to PostgreSQL.

Example 1:

$ isql -v pgdb
[08001][unixODBC]connection to server at "myrdspginstance.cqi8jtq89ehb.us-west-2.rds.amazonaws.com" (172.31.41.150), port 5432 failed: Connection timed out
Is the server running on that host and accepting TCP/IP connections?

Example 2:

[ISQL]ERROR: Could not SQLConnect
  • Errors like the following mean that the username or password specified in /etc/odbc.ini is incorrect.

Example 1:

$ isql -v pgdb

[08001][unixODBC]connection to server at "myrdspginstance.cqi8jtq89ehb.us-west-2.rds.amazonaws.com" (172.31.41.150), port 5432 failed: FATAL:  password authentication failed for user "postgres"

Example 2:

[ISQL]ERROR: Could not SQLConnect

Configure Oracle Net for the gateway

  1. Create an initialization file for your Oracle Database Gateway for ODBC using <Oracle Database Gateway Home>/hs/admin/init<sid>.ora. Here <sid> is the Oracle system identifier used for the gateway. 
    vi $ORACLE_HOME/hs/admin/initpgdb.ora
    HS_FDS_CONNECT_INFO = pgdb #pgdb is the DSN from /etc/odbc.ini
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
set ODBCINI=/etc/odbc.ini
HS_NLS_NCHAR=UCS2
  2. Configure <Oracle Gateway Home>/network/admin/listener.ora with the following configuration including the certificate environment PGSSLROOTCERT=/home/ec2-user/global-bundle. 
    vi $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /home/ec2-user/oracle/product/gateway/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ip-172-31-4-24.us-west-2.compute.internal)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST=
    (SID_DESC =
      (SID_NAME = pgdb)
      (ORACLE_HOME = /home/ec2-user/oracle/product/gateway)
      (ENVS = "PGSSLROOTCERT=/home/ec2-user/global-bundle.pem,LD_LIBRARY_PATH=/usr/pgsql-15/lib:/usr/lib64:/home/ec2-user/oracle/product/gateway/lib")
      (PROGRAM = dg4odbc)
    )
  )
  3. Start the listener. 
    lsnrctl start

Create a database link in RDS for Oracle and test the connection to RDS PostgreSQL

  1. Connect to your RDS for Oracle instance as the master user using tools such as SQL* Plus or SQL Developer.
  2. Create a database link to connect to the ODBC connection for PostgreSQL in RDS for Oracle. Replace username, password, HOST, PORT and SID to match your environment. 
    create public database link pgdblink connect to "postgres" identified by "Welcome123" using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-172-31-4-24.us-west-2.compute.internal)(PORT=1521))(CONNECT_DATA=(SID=pgdb))(HS=OK))';
  3. Run the following SQL query to test the connection. 
    select "datname" from "pg_database"@pgdblink;

A successful connection will show the databases present in the RDS for PostgreSQL instance, as shown in the following screenshot of SQL Developer.

Output in SQL Developer

Clean up

After testing is complete and if the Oracle Database Gateway EC2 instance is no longer required, you can terminate the instance. Also, if the RDS instances are no longer required, you can delete them.

Conclusion

In this post, we showed you how to configure a secure (SSL/TLS) database connection between an RDS for Oracle instance and an RDS for PostgreSQL instance using an Oracle Database Gateway and ODBC drivers. For more information about Amazon RDS for Oracle, see Working with RDS for Oracle. For more information about RDS for PostgreSQL, see Working with RDS for PostgreSQL.

About the Authors

Jennifer Kwon is a senior Database Product Owner of several of the Oracle Database Gateway services at the Boeing Company. In the past, she managed several database management systems and connectivity between them, including Oracle, SQL Server, Teradata, Hana, DB2 and IMS. In the past few years, as the focus of technology has shifted toward the cloud and open source, she has brought practical experience and expertise to enable successful cloud implementation of modern data stores and legacy databases. Outside of work, she finds joy in developing wildlife artworks with a story in them and exhibiting her work with other artists to share her view of nature with peace and harmony.

Arnab Chowdhury is a Specialist Solutions Architect with Amazon Web Services specializing in databases. Arnab works with customers in the North American automotive and manufacturing industries, guiding them through the process of optimizing and migrating their database workloads to AWS.

Vishal Patil is a Senior Worldwide Specialist Solutions Architect at Amazon Web Services. In his role, he specializes in assisting organizations with the migration and optimization of their Oracle workloads to AWS.