On-Premises Legacy Oracle Database to PostgreSQL Database as a Service

gowebing 2024-11-30

Introduction

In addition to already existing triggers like Digital Transformation, Datacenter consolidation, DevOps Automation, and adoption of new technologies, COVID-19 Pandemic has proved to be another major trigger that has forced CIOs and CTOs to re-think about the migration of on-premises workloads to the cloud so that the application and IT teams can access the applications and continue with their daily operations irrespective of their work locations.

One emerging use case of cloud migration is on-premises Oracle database to PostgreSQL Database as a Service(DBaaS) migration. In this article, we will understand why Oracle to PostgreSQL DBaaS migration is important and how it can be achieved in terms of tools and approaches to be used for schema and data migration.

On-premises Oracle to PostgreSQL DBaaS - Why?

There are 2-fold benefits of Oracle to PostgreSQL DBaaS migration - One of them being PostgreSQL inherent advantages over Oracle database and the other one being DBaaS advantages over on-premises database.

PostgreSQL Inherent Advantages Over Oracle Include

PostgreSQL is opensource and has no licensing cost, compared to per processor-based / user-based license for Oracle database.
No operational overhead of audits for licenses being used.
No expensive add-ons like Oracle as a wide variety of tools and extensions are available for PostgreSQL from community and third-parties.
Also, It can be seen from the DB-Engines Ranking that PostgreSQL is getting more popular in the last few years.

DB engines ranking of RDBMSs

DBaaS Advantages Over an On-premises Database Include

Performance - Horizontal Scaling, Read Replicas
High Availability - SLA of 99.99%.
Security – Data encryption, SSL
Fully Managed – Monitoring, Alerting, Logging, Backup, Disaster Recovery

On-Premises Oracle to PostgreSQL DBaaS — How?

On-premises Oracle to PostgreSQL DBaaS Migration consists of two steps - Schema Migration and Data Migration. Some of the tools that can help to achieve schema and data migration are shown in the diagram below.

Database migration workflow

Ora2Pg is open-source and supports automated schema conversion and migration and a one-time full data load. However, it does not capture incremental data changes, and also the setup of the tool requires some amount of effort and time. It is a good choice when migrating PostgreSQL schema on Azure or GCP clouds. AWS Schema conversion tool supports migration to PostgreSQL on AWS only and cannot be used with other cloud database services.

Migration Architecture

The diagram below shows the migration architecture for on-premises Oracle to Azure Database for PostgreSQL using the Ora2Pg tool installed on Azure virtual machine. For Data migration, Azure Data Migration Service is used so that it can also capture the incremental data changes until the cutover is planned. Similar architecture can be applied to other cloud service providers as well.

Ora2Pg Installation and Project Setup

Ora2Pg is best suitable for installation on Linux VM as it is easy to install the Perl modules and run export/import shell scripts on Linux VM compared to Windows VM. A few pre-requisites software required to install Ora2Pg include - Perl, Oracle Clients from the Oracle Downloads site, and DBD::Oracle Perl Modules, the detailed instructions of which are available on the Ora2Pg website.

After installation, setup the Ora2Pg project by generating the migration template from the command line.

ora2pg --project_base ./ --init_project test_project

This command will generate the folder structure required for migration along with shell scripts for exporting schema and data from the Oracle database and importing into the PostgreSQL database.

file structure

Then edit the config/ora2pg.conf file to provide the Oracle database connection details.

ORACLE_DSN dbi:Oracle:host=<host-name>;sid=<SIDname>;port=1521

ORACLE_USER hr

ORACLE_PWD hr

Ensure that the firewall is enabled on the Oracle Server to allow traffic on port 1521 from the Ora2Pg tool to connect to the database. Test the connection to the Oracle database and generate the Migration assessment report which will provide the inventory details of the Oracle schema to be migrated along with the complexity and effort estimation.

ora2pg -t SHOW_VERSION -c config/ora2pg.conf

Database migration report

Schema Migration

Run the export.sh shell script provided by Ora2Pg to export the schema and data. sh export_schema.sh

This will generate the PostgreSQL compliant Schema and data scripts. Some data type corrections might be needed. e.g. bigint to numeric for primary keys.

Create Azure Database for PostgreSQL on Azure Portal and run the import.sh shell script provided by Ora2Pg to import the schema into PostgreSQL databases.

sh import_all.sh -d postgres -o demouser@demopgserver -U demouser@demopgserver -h demopgserver.postgres.database.azure.com

The same export and import scripts can be used for one-time data load as well, however, to get the advantage of Continuous Data Capture(CDC) until the cutover is executed, we will use Azure Data Migration Service.

Data Migration

Create Azure Data Migration Service(DMS) on Azure Portal using the Premium SKU and enable the firewall on Oracle DB Server to allow inbound traffic on port 1521. Also, enable TLS 1.2 on Oracle Server.

Under the Azure DMS, create Oracle to PostgreSQL migration project and provide the source Oracle database details. Database migration report

Download the OCI driver from the Oracle downloads site and install it on Azure DMS by uploading it. Provide the target PostgreSQL connection details. Test the Source and Target connections.

Downloading driver

Map the Oracle Database Schema to PostgreSQL Schema along with the tables from which the data needs to be migrated

Migrating fields

Run the One-time Full Load Data Migration Activity and keep the service running to capture incremental data changes. Full download activity

Finally, based on the downtime planned with the Business users, the cutover can be executed.

Security Consideration for Deployment of PostgreSQL on Public Cloud

In the case of sensitive data, public access via the Internet to the Azure Database for PostgreSQL should be avoided. Ideally, the application accessing the database should be moved to a private Azure virtual network, and access to the PostgreSQL should be enabled via service endpoints. If that is not possible than individual IP addresses should be whitelisted to allow selected on-premises application servers and users to access the publicly hosted database. Also, SSL should be enabled to avoid a man-in-the-middle attack. Azure Key Vault should be used for storing the database credentials. Azure Log Analytics should be used to enable auditing and monitoring of the access and connections being made to the publicly hosted database.

Conclusion

We saw how to move the on-premises Oracle database to the Azure Database for PostgreSQL. A similar approach can be used for other cloud service providers. In the case of AWS, it provides dedicated tools and services for Schema and Data Migration. In the case of GCP, Ora2Pg can be used for schema migration and third-party data migration tools like Striim can be used to achieve continuous data capture.

Moving out of the on-premises Oracle database to a publicly hosted DBaaS can provide benefits in terms of licensing cost, operational cost, and administration effort. However, precaution should be taken around the security of data by building the right access policies, granting the least access privileges to users and backend servers, and enabling the monitoring and auditing of data access.