RDS Oracle PostgreSQL Integration: 2 Easy Methods

This article introduces RDS Oracle & PostgreSQL and discusses some easy steps to migrate an Oracle database to a PostgreSQL database using 2 methods. The first method relies on drivers and data schema-based tools to establish the required connection. The second method uses an automated Data Pipeline to set up the RDS Oracle PostgreSQL integration Read along to decide which method suits you the best!

What is Amazon RDS?

Amazon Relational Database Service (RDS) is a managed services collection designed by Amazon Web Services (AWS) to make it easier for users to install, operate, and scale cloud databases. The SQL database service provider helps multiple database engines to manage tasks (backup, recovery, patching, data migration, etc.) and store and organize data.

Users can build new apps, innovate, deploy, and maintain relational databases with Amazon RDS without worrying about additional expenses and time consumption. Amazon RDS is not a database but a group of services that help in the management of relational databases. Administrators use AWS Management Console and AWS Command Line Interfaces to control and deploy Amazon RDS instances. 

The leading service provider also supports various features like replication that helps in creating read replicas, Storage, monitoring, patching, public key encryption, backup, and recovery. Also, by using Amazon RDS, users can save on additional expenses and need to pay only for what they use.

Learn more about Amazon RDS here.

What is RDS Oracle?

An Oracle Database is a flexible and cost-effective relational database management system designed to manage, store and retrieve all data. It also helps fix problems associated with information management, prevents unauthorized access, and delivers robust solutions for failure recovery.

Amazon RDS for Oracle is a commercial database that helps in installing, operating, and scaling Oracle deployments. It enables operators to manage time-consuming database administration tasks and improves focus on innovation and application development. Amazon RDS for Oracle supports two licensing models – License Included and Bring-Your-Own-License (BYOL).

To learn more about RDS Oracle, visit here.

What is PostgreSQL?

PostgreSQL is an advanced open-source relational database system that supports SQL and JSON querying. It is used for most web and mobile applications as well as for analytics applications. At present, most startups and corporations use PostgreSQL to support their dynamic websites and applications. 

It also supports multiple programming languages, including Python, Java, C#, Ruby, etc. You can learn more about PostgreSQL, here.

Methods to Set Up RDS Oracle PostgreSQL Integration

It is easy to connect RDS Oracle to PostgreSQL and transfer data between these tools using the following methods:

• Method 1: RDS Oracle PostgreSQL Integration Using SQL Drivers and AWS Schema Tools
• Method 2: RDS Oracle PostgreSQL Integration Using Hevo Data

Method 1: RDS Oracle PostgreSQL Integration Using SQL Drivers and AWS Schema Tools

Follow the below-listed steps to migrate an Oracle database to a PostgreSQL database:

• Step 1: Set up the SQL Drivers and AWS Schema Conversion Tool on your system
• Step 2: Configure Oracle Source Database
• Step 3: Configure PostgreSQL Target Database
• Step 4: Convert the Oracle Schema to PostgreSQL using AWS SCT
• Step 5: Generate an AWS DMS Replication Instance
• Step 6: Produce AWS DMS Source and Target Endpoints
• Step 7: Generate and Run AWS DMS Migration Tasks
• Step 8: Perform Data Transfer Using the RDS Oracle PostgreSQL Integration

Step 1: Set up the SQL Drivers and AWS Schema Conversion Tool on your system

• Visit the Oracle official website and download the JDBC driver for the Oracle database release.
• Also, download the PostgreSQL driver.
• Now, set up the AWS SCT and the essential JDBC drivers. Go to AWS SCT > Settings > Global Settings> Select Driver and press the browse button for Oracle Driver Path. Make sure to locate the JDBC Oracle driver and press ok.
• Similarly, select browse PostgreSQL Driver Path and locate the JDBC PostgreSQL driver.
• Click ok.

Step 2: Configure Oracle Source Database

For using Oracle as the source for AWS Database Migration Service (AWS DMS), make sure the ARCHIVELOG MODE is enabled. This feature provides data to LogMiner that helps read information from the archive logs. Generally, 24 hours are sufficient to retain archive logs. This helps AWS DMS in capturing changes.

To capture change data, AWS DMS must ensure supplemental logging and identification key logging is on in your source database.

1. If the Oracle database is an AWS RDS database make sure to connect as an administrative user and retain archive logs on your AWS RDS source for a day using the following command

exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24);

However, if the Oracle source is an AWS RDS database and you have enabled backups and then locate it in ARCHIVELOG mode

2. To enable supplemental logging at the database level, run the following command

In Oracle SQL:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

In RDS:

exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');

3. To enable identification key supplemental logging to run the following command

In Oracle SQL:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

In RDS:

exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY');

4. To enable supplemental logging at the table level run the following command

ALTER TABLE table_name ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

5. Create a user for AWS SCT

CREATE USER oracle_sct_user IDENTIFIED BY password;
GRANT CONNECT TO oracle_sct_user;GRANT SELECT_CATALOG_ROLE TO oracle_sct_user;GRANT SELECT ANY DICTIONARY TO oracle_sct_user;

​​

Step 3: Configure PostgreSQL Target Database

• You must create schemas if they do not exist when migrating the PostgreSQL database.
• For connecting with the target database create the AWS DMS user and grant necessary privileges.

CREATE USER postgresql_dms_user WITH PASSWORD 'password';ALTER USER postgresql_dms_user WITH SUPERUSER;

• Generate a user for AWS SCT

CREATE USER postgresql_sct_user WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE database_name TO postgresql_sct_user;GRANT USAGE ON SCHEMA schema_name TO postgresql_sct_user;GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO postgresql_sct_user;GRANT ALL ON ALL SEQUENCES IN SCHEMA schema_name TO postgresql_sct_user;

Step 4: Convert the Oracle Schema to PostgreSQL using AWS SCT

It is essential to convert the Oracle schema to a PostgreSQL schema before migrating data. Follow the following steps to convert schema using AWS SCT.

• Install AWS SCT and go to the File menu and select new project. Name the new project as AWS Schema Conversion Tool Oracle to PostgreSQL and add folder location, then press ok.
• For adding source Oracle database, select add source > Oracle > press next.

• Add connection name, type, Server name, server port, username, password, Oracle SID, and then select Test Connection.
• Press ok and close the alert box.
• Now, select connect button to link with the Oracle DB instance.
• For adding a target PostgreSQL database, select Add target and choose Amazon RDS for PostgreSQL. Again, add the above-listed parameter like connection name, username, etc., and press test connection.
• Press ok to close the alert box and select connect option to link Amazon RDS for the PostgreSQL DB instance.
• From the left panel, select schema to migrate and target Amazon RDS for the PostgreSQL database from the right panel. Now, select create mapping option.
• Go to the main view and choose convert schema from the left panel.
• Select Yes if you confirm it.
• For checking the database migration assessment report, select Assessment Report View.
• To create migration rules, you can use AWS SCT (optional)

Step 5: Generate an AWS DMS Replication Instance

After converting the schema, the next step is data migration. AWS DMS replication instance caches the transaction logs and performs data migration between source and target.

• Go to the AWS Management Console and choose AWS DMS. Now, press on Create migration button. Only users with an AWS Identity and Access Management (IAM) will have permission to access AWS DMS.
• Now, press on the Next tab to begin a database migration.
• Specify all information related to the replication instance on the Create replication instance page.
• For the advanced section, fill in other available parameters.
• Lastly, click next.

Step 6: Produce AWS DMS Source and Target Endpoints

After creating the replication instance, specify source and target database endpoints via the AWS Management Console. However, testing is available only after a replication instance has been created.

• Add all the information necessary for the source Oracle database and the target PostgreSQL database. Once the replication instance and endpoints are live, press the run test button for testing connectivity.
• On the target database, add foreign key constraints and triggers. If you do not have one, prefer creating a script that enables this feature.
• Also, on the target database drop secondary indexes (optional).
• Lastly, press next.

Step 7: Generate and Run AWS DMS Migration Tasks

Select the type of migration and schema for migrating using an AWS DMS task.

• Visit the Create Task page and specify the task. 

• From the task settings, select Do nothing or Truncate options under the sub-heading Target table preparation mode. Select Full LOB mode for replicating entire LOBs and Limited LOB mode for replicating only up to a certain size. By selecting the Enable logging button, you can see all the errors or warnings of the task and timely troubleshoot issues.

• Avoid making changes to the default settings in the Advanced Tab
• Go to Table mappings and choose the JSON tab. Now, enable JSON editing.
• Lastly, as you select the create task button, all the tasks will start functioning.

Step 8: Perform Data Transfer Using the RDS Oracle PostgreSQL Integration

For moving connections to PostgreSQL database from Oracle database:

• Stop all dependencies and activities of the oracle database.
• List and kill all pending sessions.
• Close down listeners on the Oracle database
• Turn off automated jobs and time monitoring options on the Oracle database. (optional)
• Allow the AWS DMS task to apply final changes. Go to the AWS DMS console and pause all tasks by pressing the Stop button.
• Adding rollback tasks is optional. If you enable this option, make sure to complete the setup.
• Enable triggers on connecting to the PostgreSQL database.

That’s it! Your RDS Oracle PostgreSQL integration is in place.

Method 2: RDS Oracle PostgreSQL Integration Using Hevo Data

Hevo Data, a No-code Data Pipeline helps you directly transfer data from RDS Oracle and 100+ other data sources (including 40+free data sources), Data Warehouses, or a destination of your choice such as PostgreSQL in a completely hassle-free & automated manner. Hevo allows you to move data to the desired data destination.

Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination.

You can establish the RDS Oracle PostgreSQL connection via Hevo Data using the following 2 steps:

• Configure Source: Establish your Oracle RDS account as the source for your Hevo Pipeline as shown in the below image. You can learn more about this configuration, here.

• Authenticate Destination: Set up PostgreSQL as you destination with Hevo, as shown in the below image. You can learn more about this step, here.

More Reasons to Choose Hevo Data

• Fully Managed: It requires no maintenance as Hevo is a fully automated platform.
• Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
• Fault-Tolerant: Hevo is capable of detecting anomalies in the incoming data and informs you instantly. All the affected rows are kept aside for correction so that it doesn’t hamper your workflow.
• Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
• Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
• Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within pipelines.
• Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Sign up here for a 14-Day Free Trial!

Conclusion

This article introduced you to RDS Oracle and PostgreSQL. It further provided an step-by-step guide of 2 easy methods to set up your RDS Oracle PostgreSQL integration. The first method leverages drivers & schema tools to set up the required connection. However, it is a manual method and you need to have prior knowledge of RDS Oracle to implement it. The second method relies on Hevo Data’s automated pipeline to establish a connection between RDS Oracle and PostgreSQL.

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources like RDS Oracle and a wide variety of Desired Destinations, with a few clicks. Hevo Data with its strong integration with 100+ sources (including 40+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of learning about the RDS Oracle PostgreSQL! Let us know in the comments section below!