AWS Database Blog

Connect to Oracle from Amazon RDS for PostgreSQL using the oracle_fdw

One of the challenges organizations face when looking to modernize their Oracle database estates is the degree of interoperability between databases, which often manifests as large numbers of database links between these databases. The dilemma here is how to unravel this spiderweb of interconnectivity; it’s very rare that you can migrate all the databases at the same time in a big bang approach, which leads to the need for cross-engine database connectivity.

The Oracle Foreign Data Wrapper (oracle_fdw) has been available as a production-ready extension to the community version of PostgreSQL since April 8, 2014. This extension allows a PostgreSQL database to connect directly with an Oracle database—much like database links do in Oracle databases. As of July 9, 2021 this popular extension has been available in Amazon Relational Database Service (Amazon RDS) for PostgreSQL and as of August 20, 2021 this extension has been available in Amazon Aurora PostgreSQL-Compatible Edition. The extension allows you to connect AWS managed PostgreSQL databases to Oracle databases regardless of where they’re running. You can use the extension with Oracle databases running on-premises, in Amazon Elastic Compute Cloud (Amazon EC2), or Amazon Relational Database Service (Amazon RDS) for Oracle,

In this post, I show you how to configure and utilize oracle_fdw in Amazon RDS for PostgreSQL to allow you to progressively migrate your Oracle databases into PostgreSQL on AWS regardless of whether or not they’re using Oracle database links.

Considerations

Before we start, we must take into account a few important considerations when planning a staggered database migration strategy: network latency and network bandwidth.

Network latency

Network latency is the time taken by the data to travel across a network and back to the source. We measure the round trip under the network latency because computers don’t initiate a new TCP connection until the previous one is complete, and so until the requested data arrives, the database waits before creating a new request.

Although the speed of data transmission is high (similar to the speed of light), many hurdles can contribute to the network latency time. The biggest single contributors are distance and bottlenecks, simply put the greater the distance between the source and destination and the more switches, routers and firewalls encountered along the route the greater the network latency will be.

Network bandwidth

Although it may at times appear that there is a proliferation of database links and interconnectivity, they have all generally been created to address a specific business need, and it’s important to understand that business need as part of the migration planning process.

One particular area to consider is the volume of traffic that is transmitted over the link and potential impact on end-user applications if you have to transmit a high volume of data over a slow network connection. For this reason, databases that transmit large volumes of data between themselves over database links should be considered as a single unit and migrated at the same time.

Overview of solution

Now that we have planned our migration and identified an Oracle database that uses database links that we want to migrate to Amazon RDS for PostgreSQL, we must make a few simple modifications to the new RDS for PostgreSQL database to facilitate this requirement.

All of the examples shown in this post are based on the simple solution architecture that is provided in the following AWS CloudFormation template, PG-Oracle-FDW.json.

The bastion host and RDS for PostgreSQL environments are created using the Free Tier configuration, and the Oracle XE host is configured to use the free Oracle XE database. Therefore, we have no database license requirements to run this; however, the minimum requirement of 1 GB RAM precludes the use of the Free Tier, so this template defaults to a small EC2 host.

The following diagram illustrates this architecture.

Build the environments

All examples in this post use an Oracle XE 18c pluggable database and RDS for PostgreSQL 12 database that are created from the CloudFormation template provided. I use Oracle XE on an EC2 host rather than an RDS for Oracle database so that I can take advantage of the free-to-use Oracle XE database, which doesn’t require any Oracle licenses.

The build of this CloudFormation template takes approximately 45 minutes to complete, with the database host taking the bulk of that time. You can check the progress by connecting to your Linux EC2 hosts using SSH, as ec2-user, and monitoring the log file, Install-Log.txt, which is located in the /tmp subdirectory.

Although you’re not required to connect to the database host to complete this brief tutorial, you can connect to the database host from the bastion host by copying your .pem key into the SSH subdirectory on the bastion host. You can do this either at build time by providing an Amazon Simple Storage Service (Amazon S3) location for the .pem key to the CloudFormation template, or copy it to the bastion host from your laptop after the build of the bastion host is complete.

Connect to the bastion host

All the commands shown in this post are issued from the bastion host, and again, there is no requirement to connect to the database host. If you’re using your own Oracle database, you can simply change the connection credentials to that database.

To connect to the bastion host, you first need to obtain its public IP address. This is provided as an output parameter in the CloudFormation template, or you can locate it on the Amazon EC2 console.

When you have the public IP address, connect using a string similar to the one shown in the following code, and make sure you connect as the Oracle user to take advantage of the environment that has been set up:

$ ssh -i ~/.ssh/<Your Permissions Key>.pem oracle@<Public DNS Name>

When you’re connected, make sure that you can log in to both the Oracle and PostgreSQL databases.

The connection details for the Oracle database are set up in the tnsnames.ora file during the build process, and you can obtain the connection details for the PostgreSQL database from the output parameter in the CloudFormation template, or on the Amazon RDS console.

$ sqlplus mike_xe/<password>@XEPDB1

$ psql -h postgres-oracle-fdw-1.crrqxazzxunm.eu-west-1.rds.amazonaws.com -d postgres -p 5432 -U mike_pg

If either of these fails, the build process either wasn’t completed, failed, or the security group information wasn’t correctly provided. You must address this before continuing.

Common Failure Reasons

Invalid Password

[oracle@bastian-host ~]$ psql -h postgres-oracle-fdw-1.crrqxazzxunm.eu-west-1.rds.amazonaws.com -d postgres -p 5432 -U mike_pg
Password for user mike_pg: 
psql: error: FATAL:  password authentication failed for user "mike_pg"
FATAL:  password authentication failed for user "mike_pg"

Remember that passwords are case sensitive, this is the password that you used when you created the database through the Cloud Formation Template

Invalid Hostname

[oracle@bastian-host ~]$ psql -h postgres-oracle-fdw-1.crrqxazzxunm.us-east-1.rds.amazonaws.com -d postgres -p 5432 -U mike_pg
psql: error: could not translate host name "postgres-oracle-fdw-1.crrqxazzxunm.us-east-1.rds.amazonaws.com" to address: Name or service not known

There are two ways to check that the hostname is correct;

  1. Check the entry in the Managed Relational Database Services section in the AWS Console
  2. Check the outputs for the Cloud Formation Template, the PostgreSQL DNS name is held in the PostgreSQLEndpointAddress key.

Connection Timeout

[oracle@bastian-host ~]$ psql -h postgres-oracle-fdw-1.crrqxazzxunm.eu-west-1.rds.amazonaws.com -d postgres -p 5432 -U mike_pg
psql: error: could not connect to server: Connection timed out
	Is the server running on host "postgres-oracle-fdw-1.crrqxazzxunm.eu-west-1.rds.amazonaws.com" (172.30.12.141) and accepting
	TCP/IP connections on port 5432?

If you issue the command as shown above and nothing happens initially and the connection eventually times out, then the most likely cause is that the database security group you specified during the Cloud Formation Template creation did not allow connectivity from the Bastian host through to the database host via port 5432.

This is checked by going to the entry in the Managed Relational Database Services section in the AWS Console where the VPC security groups are shown, there should be two, click on each in turn and you will be taken to the security groups section where you can check the inbound rules. There should be a rule in here to allow port 5432 access between you Bastian host and the RDS instance. This may show up as a rule type of PostgreSQL.

Build sample tables

To run this demo, you need to create the following tables in your Oracle and PostgreSQL databases:

  • oracle_fdw_local_parent
  • oracle_fdw_local_child
  • postgres_fdw_local_parent
  • postgres_fdw_local_child

Set up Oracle

Log in to the Oracle database from your bastion host, using the credentials provided when you built the environment, and create the two tables for testing, oracle_fdw_local_parent and oracle_fdw_local_child:

$ sqlplus mike_xe/<password>@XEPDB1 @create_ora_tables.sql


SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 7 11:03:31 2021
Version 19.10.0.0.0

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

Last Successful login time: Tue Dec 07 2021 11:03:22 +00:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

Table created.

Table created.

Then create some test data by running the following script:

$ sqlplus mike_xe/<password>@XEPDB1 @insert_ora_tables.sql

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

Set up PostgreSQL

Log in to the PostgreSQL database from your bastion host using the credentials provided when you built the environment and create the two tables for testing, postgres_fdw_local_parent and postgres_fdw_local_child:

$ psql -h postgres-oracle-fdw-1 -d postgres -p 5432 -U mike_pg -f create_pg_tables.sql

Password for user mike_pg: 
CREATE TABLE
CREATE TABLE

Then create some test data by running the following script:

$ psql -h postgres-oracle-fdw-1 -d postgres -p 5432 -U mike-pg -f insert_pg_tables.sql

Password for user mike_pg: 
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1

Enable oracle_fdw

Before you can use oracle_fdw, you have to enable it in the database, which is as straightforward as creating the oracle_fdw extension:

$ psql -h postgres-oracle-fdw-1 -d postgres -p 5432 -U mike_pg

CREATE EXTENSION oracle_fdw;

Create a connection from PostgreSQL to Oracle

After you enable the extension, you need to tell PostgreSQL how to connect to the Oracle database. This is similar to creating a database link in Oracle.

You create the connection with two sets of commands.

The first defines the connect string to the Oracle database. For this demo, the Oracle host DNS address is the private IPv4 DNS obtained from the output parameter in the CloudFormation template or from the Amazon EC2 console.

A useful strategy is to initiate an SQL*Plus string to the database from the bastion host to make sure that the connect string is valid. See the following code:

$ sqlplus mike_xe/<password>@//<oracle host DNS Address>:1521/XEPDB1

$ psql -h postgres-oracle-fdw-1 -d postgres -p 5432 -U mike_pg

CREATE SERVER oracle_remote
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS
(
  dbserver '//<oracle host DNS Address>:1521/XEPDB1'
);

Note: If the DNS Service is not fully configured in your environment you may need to use the IP Address of the Oracle host in place of the DNS Address

The next command defines the connection credentials. Make sure you use the correct password encapsulated with quotes—this has to be an Oracle user with the necessary read, write and delete permissions on the base tables that will be accessed.:

GRANT USAGE ON FOREIGN SERVER oracle_remote TO mike_pg;

CREATE USER     MAPPING
FOR    mike_pg
SERVER oracle_remote
OPTIONS
(
   user     'mike_xe',
   password '<password>'
);

Create remote tables

In Oracle, you use a synonym or a view to access a remote table over the database link. PostgreSQL is slightly different in that we define a foreign table to access the remote data with the following code. We use OPTIONS (KEY 'true') in the definition to denote the primary key. This allows updates and deletes to be performed through the foreign table.

$ psql -h postgres-oracle-fdw-1 -d postgres -p 5432 -U mike_pg

CREATE FOREIGN
TABLE  postgres_fdw_remote_parent
(
  code        SERIAL        OPTIONS (KEY 'true')          NOT NULL,
  name        TEXT                                        NOT NULL,
  created     TIMESTAMP     DEFAULT  clock_timestamp()    NOT NULL,
  expiry_date TIMESTAMP     DEFAULT  clock_timestamp() + '2 years',
  order_date  TIMESTAMP     NULL,
  order_value NUMERIC(20,2) NULL,
  description TEXT          NULL,
  updated     TIMESTAMP     NULL
)
SERVER oracle_remote
OPTIONS
(
  SCHEMA 'MIKE_XE',
  TABLE  'ORACLE_FDW_LOCAL_PARENT'
);

CREATE FOREIGN
TABLE   postgres_fdw_remote_child 
(
  code        SERIAL        OPTIONS (KEY 'true')          NOT NULL,
  parent      INTEGER                                     NOT NULL,
  name        TEXT                                        NOT NULL,
  created     TIMESTAMP     DEFAULT  clock_timestamp()    NOT NULL,
  expiry_date TIMESTAMP     DEFAULT  clock_timestamp() + '2 years',
  order_date  TIMESTAMP     NULL,
  order_value NUMERIC(20,2) NULL,
  description TEXT          NULL,
  updated     TIMESTAMP     NULL
)
SERVER oracle_remote
OPTIONS
(
  SCHEMA 'MIKE_XE',
  TABLE  'ORACLE_FDW_LOCAL_CHILD'
);

Test the links

To test the links, first select Oracle data from the Oracle database:

sqlplus mike_xe/<password>@XEPDB1

SET LINESIZE 132
SELECT a.code, b.code, b.description
FROM   oracle_fdw_local_parent a, oracle_fdw_local_child b 
WHERE  a.code = b.parent;
      CODE	 CODE DESCRIPTION
---------- ---------- -------------------------------------------------------
	 1	    1 First Child in Oracle
	 1	    2 Second Child in Oracle
	 2	    3 Third Child in Oracle
	 2	    4 Fourth Child in Oracle

Then select PostgreSQL data from the PostgreSQL database:

$ psql -h postgres-oracle-fdw-1 -d postgres -p 5432 -U mike_pg

SELECT a.code, b.code, b.description
FROM   postgres_fdw_local_parent a, postgres_fdw_local_child b 
WHERE  a.code = b.parent;
 code | code |        description         
------+------+----------------------------
    1 |    1 | First Child in PostgreSQL
    1 |    2 | Second Child in PostgreSQL
    2 |    3 | Third Child in PostgreSQL
    2 |    4 | Fourth Child in PostgreSQL
(4 rows)

Next, select Oracle data from the PostgreSQL database:

$ psql -h postgres-oracle-fdw-1 -d postgres -p 5432 -U mike_pg

SELECT a.code, b.code, b.description
FROM   postgres_fdw_remote_parent a, postgres_fdw_remote_child b 
WHERE  a.code = b.parent;
 code | code |      description       
------+------+------------------------
    1 |    1 | First Child in Oracle
    1 |    2 | Second Child in Oracle
    2 |    3 | Third Child in Oracle
    2 |    4 | Fourth Child in Oracle
(4 rows)

Now update, insert, and delete a row in the Oracle database from PostgreSQL:

$ psql -h postgres-oracle-fdw-1 -d postgres -p 5432 -U mike_pg

DELETE FROM postgres_fdw_remote_child
WHERE  code = 1;

DELETE 1

UPDATE postgres_fdw_remote_child
SET    description = 'Updated from PostgreSQL'
WHERE  code = 2;

UPDATE 1

INSERT INTO postgres_fdw_remote_child
       (parent, name,   description)
VALUES (2,     'Five', 'Fifth Child from PostgreSQL');

INSERT 0 1

Check the results in Oracle from PostgreSQL:

$ psql -h postgres-oracle-fdw-1 -d postgres -p 5432 -U mike_pg

SELECT a.code, b.code, b.description
FROM   postgres_fdw_remote_parent a, postgres_fdw_remote_child b 
WHERE  a.code = b.parent;
 code | code |         description         
------+------+-----------------------------
    1 |    2 | Updated from PostgreSQL
    2 |    3 | Third Child in Oracle
    2 |    4 | Fourth Child in Oracle
    2 |    1 | Fifth Child from PostgreSQL
(4 rows)

Finally, check the Oracle data from the Oracle database:

sqlplus mike_xe/<password>@XEPDB1

SET LINESIZE 132
SELECT a.code, b.code, b.description
FROM   oracle_fdw_local_parent a, oracle_fdw_local_child b 
WHERE  a.code = b.parent;
      CODE       CODE DESCRIPTION
---------- ---------- -------------------------------------------------------
	 1	    2 Updated from PostgreSQL
	 2	    3 Third Child in Oracle
	 2	    4 Fourth Child in Oracle
	 2	    1 Fifth Child from PostgreSQL

Clean up

To avoid incurring future charges, delete the resources used for this solution. You can do this by removing the CloudFormation template from your account.

Conclusion

In this post, we discussed some key considerations regarding the use of inter-database connectivity through the use of Oracle database links and the PostgreSQL Foreign Data Wrapper, oracle_fdw.

We walked you through the process of configuring your RDS for PostgreSQL database to facilitate the use of selects, inserts, updates, and deletes across that Foreign Data Wrapper, which allows you to accelerate your database modernization journey even in scenarios where cross-database communication is required for extended periods of time.

If you have any questions or comments about this post, please use the comments section.

About the Author

Mike Revitt is a Database Migration Specialist working in the Database Freedom team at AWS, with over 30 years of experience in database and application migration, modernization, design, development, and integration.