Configure links between Oracle Database and Aurora PostgreSQL-Compatible
Created by Jeevan Shetty (AWS), Bhanu Ganesh Gudivada (AWS), Sushant Deshmukh (AWS), Uttiya Gupta (AWS), and Vikas Gupta (AWS)
Environment: PoC or pilot | Source: Oracle Database | Target: Aurora PostgreSQL-Compatible |
R Type: Replatform | Workload: Oracle; Open-source | Technologies: Migration; Databases |
AWS services: Amazon Aurora; Amazon EC2 Auto Scaling; Amazon Route 53 |
Summary
As part of the migration to the Amazon Web Services (AWS) Cloud, you can modernize your applications to use cloud-native databases. Migrating from Oracle Database to Amazon Aurora PostgreSQL-Compatible Edition is one such step toward modernization. As part of that migration, native Oracle database links also require conversion.
Using a database link, one database can access objects in another database. After migration from Oracle Database to Aurora PostgreSQL-Compatible, the database links from the Oracle Database server to other Oracle Database servers must be converted to PostgreSQL-to-Oracle database links.
This pattern shows how you can set up database links from an Oracle Database server to the Aurora PostgreSQL-Compatible database. Because database links are one-way, the pattern also covers converting database links from the PostgreSQL database to Oracle Database.
After migration and conversion from Oracle Database to an Aurora PostgreSQL-Compatible database, the following steps are required to set up database links between databases:
To set up a database link with Oracle Database as the source and Aurora PostgreSQL-Compatible as the target, Oracle Database Gateways must be configured for communication between heterogeneous databases.
If you are setting up a database link between Aurora PostgreSQL-Compatible version 12.6 and earlier as the source database and Oracle Database as the target, the oracle_fdw extension is not available natively. Instead, you can use the postgres_fdw extension in the Aurora PostgreSQL-Compatible database and configure oracle_fdw in a PostgreSQL database created on Amazon Elastic Compute Cloud (Amazon EC2). This database acts as an intermediary between the Aurora PostgreSQL-Compatible database and Oracle Database. This pattern includes two options for setting up the database link with Aurora PostgreSQL 12.6 and earlier:
Configure the EC2 instance in an Amazon EC2 Auto Scaling group with an Amazon EC2 startup script that updates an internal Domain Name System (DNS) entry in Amazon Route 53.
Configure the EC2 instance in an Amazon EC2 Auto Scaling group, with a Network Load Balancer for high availability (HA).
If you are setting up a database link between Aurora PostgreSQL-Compatible version 12.7 and later, you can use the oracle_fdw extension.
Prerequisites and limitations
Prerequisites
Limitations
Product versions
Architecture
Source technology stack
Before migration, the source Oracle database can access objects in other Oracle databases using database links. This works natively between Oracle databases on premises or in the AWS Cloud.
Target technology stack
Option 1
Amazon Aurora PostgreSQL-Compatible Edition
PostgreSQL database on an Amazon EC2 instance
Amazon EC2 Auto Scaling group
Amazon Route 53
Amazon Simple Notification Service (Amazon SNS)
AWS Identity and Access Management (IAM)
AWS Direct Connect
Option 2
Amazon Aurora PostgreSQL-Compatible Edition
PostgreSQL database on an Amazon EC2 instance
Amazon EC2 Auto Scaling group
Network Load Balancer
Amazon SNS
Direct Connect
Option 3
Target architecture
Option 1
The following diagram shows database link setup using the oracle_fdw and postgres_fdw extensions, with HA provided by an Amazon EC2 Auto Scaling group and Route 53.
An Aurora PostgreSQL-Compatible instance with the postgres_fdw extension connects to the PostgreSQL database on Amazon EC2.
The PostgreSQL database with the oracle_fdw extension is in an Auto Scaling group.
The PostgreSQL database on Amazon EC2 uses Direct Connect to connect to Oracle Database on premises.
Oracle Database is configured with Oracle Database Gateways for connections from Oracle Database to the PostgreSQL database on AWS.
IAM grants permission to Amazon EC2 to update Route 53 records.
Amazon SNS sends alerts for automatic scaling actions.
The Domain Name configured in Route 53 points to the PostgreSQL Amazon EC2 instance IP address.
Option 2
The following diagram shows database link setup using the oracle_fdw and postgres_fdw extensions, with HA provided by an Auto Scaling group and a Network Load Balancer.
An Aurora PostgreSQL-Compatible instance with the postgres_fdw extension connects to the Network Load Balancer.
The Network Load Balancer distributes the connection from the Aurora PostgreSQL-Compatible database to the PostgreSQL database on Amazon EC2.
The PostgreSQL database with the oracle_fdw extension is in an Auto Scaling group.
The PostgreSQL database on Amazon EC2 uses Direct Connect to connect to Oracle Database on premises.
Oracle Database is configured with Oracle Database Gateways for connections from Oracle Database to the PostgreSQL database on AWS.
Amazon SNS sends alerts for automatic scaling actions.
Option 3
The following diagram shows database link setup using the oracle_fdw extension in an Aurora PostgreSQL-Compatible database.
An Aurora PostgreSQL-Compatible instance with the oracle_fdw extension uses Direct Connect to connect to Oracle Database.
Oracle Database Gateways set up on Oracle Server enables connectivity through Direct Connect to the Aurora PostgreSQL-Compatible database.
Tools
AWS services
Amazon Aurora PostgreSQL-Compatible Edition is a fully managed, ACID-compliant relational database engine that helps you set up, operate, and scale PostgreSQL deployments.
AWS Direct Connect links your internal network to a Direct Connect location over a standard Ethernet fiber-optic cable. With this connection, you can create virtual interfaces directly to public AWS services while bypassing internet service providers in your network path.
Amazon Elastic Compute Cloud (Amazon EC2) provides scalable computing capacity in the AWS Cloud. You can launch as many virtual servers as you need and quickly scale them up or down. In this pattern, options 1 and 2 use an EC2 instance to host a PostgreSQL database.
Amazon EC2 Auto Scaling helps you maintain application availability and allows you to automatically add or remove Amazon EC2 instances according to conditions you define.
AWS Identity and Access Management (IAM) helps you securely manage access to your AWS resources by controlling who is authenticated and authorized to use them.
Amazon Route 53 is a highly available and scalable DNS web service.
Amazon Simple Notification Service (Amazon SNS) helps you coordinate and manage the exchange of messages between publishers and clients, including web servers and email addresses.
Elastic Load Balancing (ELB) distributes incoming application or network traffic across multiple targets. For example, you can distribute traffic across Amazon Elastic Compute Cloud (Amazon EC2) instances, containers, and IP addresses in one or more Availability Zones. This pattern uses a Network Load Balancer.
Other services
Epics
| Task | Description | Skills required |
|---|
Create an EC2 instance and configure the oracle_fdw PostgreSQL extension. | Create an EC2 instance with the Amazon Linux 2 operating system. Note: Amazon Linux 2 is nearing end of support. For more information, see the Amazon Linux 2 FAQs. To install PostgreSQL, log in to the EC2 instance as ec2-user, and run the following commands. sudo su - root
sudo tee /etc/yum.repos.d/pgdg.repo<<EOF
[pgdg12]
name=PostgreSQL 12 for RHEL/CentOS 7 - x86_64
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64
enabled=1
gpgcheck=0
EOF
sudo yum install -y postgresql12-server
sudo yum install postgresql12-devel
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12
Download the oracle_fdw source code from GitHub. mkdir -p /var/lib/pgsql/oracle_fdw/
cd /var/lib/pgsql/oracle_fdw/
wget https://github.com/laurenz/oracle_fdw/archive/refs/heads/master.zip
unzip master.zip
Install Oracle Instant Client and set up the Oracle environment variables. yum install https://download.oracle.com/otn_software/linux/instantclient/1912000/oracle-instantclient19.12-basic-19.12.0.0.0-1.x86_64.rpm
yum install https://download.oracle.com/otn_software/linux/instantclient/1912000/oracle-instantclient19.12-devel-19.12.0.0.0-1.x86_64.rpm
export ORACLE_HOME=/usr/lib/oracle/19.12/client64export LD_LIBRARY_PATH=/usr/lib/oracle/19.12/client64/lib:$LD_LIBRARY_PATH
Make sure that pg_config is referring to the correct version. which pg_config
Compile oracle_fdw. cd /var/lib/pgsql/oracle_fdw/oracle_fdw-master
make
make install
Note: If you receive an error saying that oci.h is missing, add the following in Makefile: To PG_CPPFLAGS, add -I/usr/include/oracle/19.12/client64 To SHLIB_LINK, add -L/usr/lib/oracle/19.12/client64/lib
For more information, see the oracle_fdw repository. Log in to the PostgreSQL database and create the oracle_fdw extension. sudo su - postgres
psql postgres
create extension oracle_fdw;
Create a PostgreSQL user that will own the foreign tables. CREATE USER pguser WITH PASSWORD '<password>';
GRANT CONNECT ON DATABASE postgres TO pguser;
Create the foreign data wrapper. Substitute the following values with your Oracle Database server details: <Oracle DB Server IP>
<Oracle DB Port>
<Oracle_SID>
create server oradb foreign data wrapper oracle_fdw options (dbserver '//<Oracle DB Server IP>:<Oracle DB Port>/<Oracle_SID>');
GRANT USAGE ON FOREIGN SERVER oradb TO pguser;
To create the user mapping and a foreign table that maps to the Oracle table, connect to the PostgreSQL database as pguser, and run the following command. Note that in the example code, DMS_SAMPLE is used as the Oracle schema containing the NAME_DATA table, and dms_sample is its password. Replace them as necessary. create user mapping for pguser server oradb options (user 'DMS_SAMPLE', password 'dms_sample');
Note: The following example creates a foreign table in PostgreSQL for a table in Oracle Database. A similar foreign table must be created for every Oracle table that requires access from the PostgreSQL instance. CREATE FOREIGN TABLE name_data(
name_type CHARACTER VARYING(15) NOT NULL,
name CHARACTER VARYING(45) NOT NULL
) SERVER oradb OPTIONS (schema 'DMS_SAMPLE', table 'NAME_DATA');
select count(*) from name_data;
Configure the PostgreSQL database on the EC2 instance so that it can locate the Oracle libraries during PostgreSQL database startup. This is required by the oracle_fdw extension. sudo systemctl stop postgresql-12
Note: Edit the /usr/lib/systemd/system/postgresql-12.service file to include the environment variables so that systemctl startup will find the Oracle libraries required by oracle_fdw. # Oracle Environment Variables
Environment=ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
Environment=LD_LIBRARY_PATH=/u01/app/oracle/product/12.2.0.1/db_1/lib:/lib:/usr/lib
sudo systemctl start postgresql-12
| Cloud administrator, DBA |
| Task | Description | Skills required |
|---|
Set up a private hosted zone in Amazon Route 53. | Create a private hosted zone in Amazon Route 53. Make a note of Domain Name, which will be associated with an EC2 instance. Add an "A" record using simple routing policy that resolves to the EC2 instance IP address, containing the oracle_fdw PostgreSQL extension. After saving the "A" record, make a note of the Hosted zone ID of the Domain Name from step 1. This will be used to create the appropriate IAM policy.
| DBA, Cloud administrator |
Create an IAM role that will be attached to an EC2 instance. | To create an IAM role that will be attached to the EC2 instance, use the following policy. Replace <Hosted zone ID> with information captured in the previous story. {
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": "route53:ChangeResourceRecordSets",
"Resource": "arn:aws:route53:::hostedzone/<Hosted zone ID>"
},
{
"Sid": "VisualEditor1",
"Effect": "Allow",
"Action": "route53:ListHostedZones",
"Resource": "*"
}
]
}
| Cloud administrator, DBA |
Create an EC2 launch template. | Create an AMI of the EC2 instance that contains the oracle_fdw PostgreSQL extension. Use the AMI to create an EC2 launch template. To allow connection from the Aurora PostgreSQL-Compatible instance to the PostgreSQL database on the EC2 instance, associate the IAM role that you created earlier and attach security groups. In the User Data section, add the following commands, changing Hosted zone ID and Domain Name to the appropriate values. Then choose Create launch template. #!/bin/bash
v_zone_id='Hosted zone ID'
v_domain_name='Domain Name'
v_local_ipv4=$(curl -s http://169.254.169.254/latest/meta-data/local-ipv4)
aws route53 change-resource-record-sets --hosted-zone-id $v_zone_id --change-batch '{"Changes":[{"Action":"UPSERT","ResourceRecordSet":{"Name":"'$v_domain_name'","Type":"A","TTL":10,"ResourceRecords":[{"Value":"'$v_local_ipv4'"}]}}]}'
| Cloud administrator, DBA |
Set up the Auto Scaling group. | To set up an Auto Scaling group, use the launch template that you created in the previous step. Configure appropriate VPC and subnets that will be used to launch the EC2 instance. Option 1 setup does not use Load Balancer. Set the Desired, Minimum, and Maximum capacity to 1 under Scaling policies. To send alerts to the operations team, add notifications for events such as Launch or Terminate. Review the configuration, and choose Create Auto Scaling group.
On completion, the Auto Scaling group starts the EC2 instance containing the oracle_fdw PostgreSQL extension, which connects to Oracle Database. Note: When you need to access a new Oracle table or change the structure of an Oracle table, those changes must be reflected in the PostgreSQL foreign table. After you implement the changes, you must create a new AMI of the EC2 instance and use it to configure the launch template. | Cloud administrator, DBA |
Configure the postgres_fdw extension in the Aurora PostgreSQL-Compatible instance. | Configure postgres_fdw in the Aurora PostgreSQL-Compatible instance. This connects to the PostgreSQL database on Amazon EC2, which acts as an intermediate node between the Aurora PostgreSQL-Compatible instance and Oracle Database. Connect to the Aurora PostgreSQL-Compatible instance and run the following commands. create extension postgres_fdw;
CREATE SERVER pgoradb FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'postgres', host 'Domain Name', port '5432');
CREATE USER MAPPING for postgres SERVER pgoradb OPTIONS (user 'pguser', password '<password>');
CREATE FOREIGN TABLE data_mart.name_data(
name_type CHARACTER VARYING(15) NOT NULL,
name CHARACTER VARYING(45) NOT NULL
) SERVER pgoradb OPTIONS (schema_name 'public', table_name 'name_data');
select count(*) from data_mart.name_data;
This completes the setup of a database link from Aurora PostgreSQL-Compatible to Oracle Database. The solution provides a disaster recovery (DR) strategy, in case the EC2 instance hosting the PostgreSQL database fails. The Auto Scaling group starts a new EC2 instance and updates the DNS with the IP address of the new EC2 instance. This ensures that the foreign tables in the Aurora PostgreSQL-Compatible instance can access the Oracle tables without manual intervention. | Cloud administrator, DBA |
| Task | Description | Skills required |
|---|
Create an EC2 launch template. | Create an AMI of the EC2 instance that contains the oracle_fdw PostgreSQL extension. Use the AMI to create an EC2 launch template.
| Cloud administrator, DBA |
Set up a target group, Network Load Balancer, and Auto Scaling group. | To create a target group, choose Instances as the target type. For Protocol, choose TCP, and for Port, choose 5432. Then choose the VPC where you want the target group, and select the appropriate Health check. Create an internal Network Load Balancer in the VPC. Configure the load balancer to listen on protocol:port TCP:5432. Set the Default action as Forward to, and choose the target group that you created. Set up an Auto Scaling group using the launch template that you created. Configure the Auto Scaling group with the appropriate VPC and subnets that will be used to launch the EC2 instances. For the Load Balancing option, choose Attach to an existing load balancer, and select the Target Group that you created. For Health checks, select ELB. Set the Desired and Minimum capacity to 2, and set the Maximum capacity to a higher number, as required to support the load with HA, under Scaling policies. To send alerts to the operations team, add notifications for events such as Launch or Terminate. Review the configuration, and choose Create Auto Scaling group.
On completion, the Auto Scaling group starts the desired number of EC2 instances containing the oracle_fdw PostgreSQL extension that connects to Oracle Database. Note: When you need to access a new Oracle table or change the structure of an Oracle table, those changes must be reflected in the PostgreSQL foreign table. After you implement the changes, you must create a new AMI of the EC2 instance and use it to configure the launch template. | Cloud administrator, DBA |
Configure the postgres_fdw extension in the Aurora PostgreSQL-Compatible instance. | Configure postgres_fdw in the Aurora PostgreSQL-Compatible instance. This connects to PostgreSQL Database on EC2 through a Network Load Balancer. The PostgreSQL instance on EC2 acts as an intermediate node between Aurora PostgreSQL-Compatible instance and Oracle Database. Connect to the Aurora PostgreSQL-Compatible instance and run the following commands. create extension postgres_fdw;
CREATE SERVER pgoradb FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'postgres', host 'DNS name of Network Load Balancer', port '5432');
CREATE USER MAPPING for postgres SERVER pgoradb OPTIONS (user 'pguser', password '<password>');
CREATE FOREIGN TABLE data_mart.name_data(
name_type CHARACTER VARYING(15) NOT NULL,
name CHARACTER VARYING(45) NOT NULL
) SERVER pgoradb OPTIONS (schema_name 'public', table_name 'name_data');
select count(*) from data_mart.name_data;
This completes the setup of database link from Aurora PostgreSQL-Compatible to Oracle Database. In case of EC2 hosting the PostgreSQL database fails, the Network Load Balancer identifies the failure and stops the traffic to failed EC2 instance. The Auto Scaling group starts a new EC2 instance and registers it with the load balancer. This ensures that after the original EC2 instance fails, the foreign tables in the Aurora PostgreSQL-Compatible instance can access the Oracle tables without manual intervention. | Cloud administrator, DBA |
| Task | Description | Skills required |
|---|
Configure the oracle_fdw extension in the Aurora PostgreSQL-Compatible instance. | For Aurora PostgreSQL-Compatible database version 12.7 and later, the oracle_fdw extension is natively available. This eliminates the need the create the intermediate PostgreSQL database on an EC2 instance. The Aurora PostgreSQL-Compatible instance can connect to Oracle Database directly. To create the oracle_fdw extension, log in to the Aurora PostgreSQL-Compatible instance, and run the following command. create extension oracle_fdw;
Create the foreign data wrapper. Substitute the following values with your Oracle Database server details: <Oracle DB Server IP>
<Oracle DB Port>
<Oracle_SID>
create server oradb foreign data wrapper oracle_fdw options (dbserver '//<Oracle DB Server IP>:<Oracle DB Port>/<Oracle_SID>');
To create the user mapping and a foreign table that maps to the Oracle table, run the following command.
Note that in the example code, DMS_SAMPLE is used as the Oracle schema containing the NAME_DATA table, and dms_sample is its password. Replace them as necessary. Also, Foreign Table has to be created in the Aurora PostgreSQL-Compatible instance for access to every other Oracle table. create user mapping for postgres server oradb options (user 'DMS_SAMPLE', password 'dms_sample');
CREATE FOREIGN TABLE name_data(
name_type character varying(15) OPTIONS (key 'true') NOT NULL,
name character varying(45) OPTIONS (key 'true') NOT NULL
)SERVER oradb OPTIONS (schema 'DMS_SAMPLE', table 'NAME_DATA');
A similar foreign table must be created for every Oracle table that requires access from the PostgreSQL instance.
| Cloud administrator, DBA |
| Task | Description | Skills required |
|---|
Configure the gateway in the on-premises Oracle Database server. | As the root user, install the latest unixODBC driver manager. sudo yum install unixODBC*
Install the PostgreSQL ODBC driver (psqlODBC). sudo wget https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install pgdg-redhat-repo-latest.noarch.rpm
sudo yum install postgresql12-odbc
Create an ODBC Data Source Name (DSN) for the driver. The unixODBC driver manager provides the odbcinst, odbc_config, and isql command line utilities used to configure and test the driver. Using odbcinst or odbc_config utilities, you can locate the unixODBC driver manager files to pass driver information to create the DSN. odbcinst -j
The following code shows example output. unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
odbc_config --odbcini --odbcinstini
/etc/odbc.ini
/etc/odbcinst.ini
From the example output, you can see the odbcinst.ini and odbc.ini files. Basically, odbcinst.ini is a registry and configuration file for ODBC drivers in an environment, while odbc.ini is a registry and configuration file for ODBC DSNs. To enable the drivers, you need to modify these two files. Configure the psqlODBC driver libraries in the ODBC driver file /etc/odbcinst.ini, and add the following lines to the end of the file. These lines make an entry for the driver. [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
Create a DSN in the /etc/odbc.ini file. The driver manager reads this file to determine how to connect to the database using the driver details specified in odbcinst.ini. Replace the following parameters with actual values: [pgdsn]
Driver=/usr/pgsql-12/lib/psqlodbc.so
Description=PostgreSQL ODBC Driver
Database=<PostgreSQL Database Name>
Servername=<Aurora PostgreSQL Endpoint>
Username=<PostgreSQL username>
Password=<PostgreSQL password>
Port=<PostgreSQL Port>
UseDeclareFetch=1
CommLog=/tmp/pgodbclink.log
Debug=1
LowerCaseIdentifier=1
Using the isql utility, test the ODBC connection (psqlODBC) to the PostgreSQL database DSN that you created. isql -v pgdsn
The following code shows example output. +---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
quit
Using the DSN, create the gateway for the ODBC (HS) service handler. As the oracle user, create a file initDSN.ora in location $ORACLE_HOME/hs/admin. In this case, pgdsn is the DSN, so you need to create a file called initpgdsn.ora. more initpgdsn.ora
The following code shows example output. # 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=pgdsn
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_TRACE_FILE_NAME=/tmp/ora_hs_trace.log
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
Adjust the listener ($ORACLE_HOME/network/admin/listener.ora) by adding the DSN entry in SID_LIST_LISTENER. more $ORACLE_HOME/network/admin/listener.ora
The following code shows example output. SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(SID_NAME = pgdsn)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
(ENVS="LD_LIBRARY_PATH=/lib64:/usr/lib:/usr/lib64:/u01/app/oracle/product/12.2.0.1/db_1")
(PROGRAM=dg4odbc)
)
)
Adjust the tnsname ($ORACLE_HOME/network/admin/tnsnames.ora) by adding the DSN entry. more $ORACLE_HOME/network/admin/tnsnames.ora
The following code shows example output. pgdsn=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=pgdsn))(HS=OK))
Restart the Oracle listener so that the DSN-related entries made to the networking files can take effect, changing <Listener Name> with the appropriate Oracle listener name. lsnrctl stop <Listener Name>
lsnrctl start <Listener Name>
After you restart the Oracle listener, it will create an Oracle HS handler with a DSN name (pgdsn). Use the DSN to create an Oracle database link to access the PostgreSQL database by logging in to Oracle Database. create public database link pgdb connect to "postgres" identified by "postgres" using 'pgdsn';
Access the PostgreSQL data by using the Oracle database link created. select count(*) from "pg_tables"@pgdb;
| DBA |
Related resources
Additional information
Although the oracle_fdw extension is available with Aurora PostgreSQL-Compatible version 12.7 and later, this pattern includes solutions for earlier versions of Aurora PostgreSQL-Compatible databases, because many customers support older versions of Aurora PostgreSQL-Compatible databases, and upgrading a database involves multiple levels of application and performance testing. Also, the database link feature is extensively used, and providing options for all versions of Aurora PostgreSQL-Compatible is the objective of this article.
