Sign in

On-Premise Legacy Oracle Database to PostgreSQL Database as a Service — DZone Cloud

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.

On-Premise 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-premise databases.

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 Comparision of Oracle and PostgreSQL Databases

DBaaS advantages over an on-premise 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-Premise Oracle to PostgreSQL DBaaS — How?

On-premise 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.

Migration Architecture

The diagram below shows the migration architecture for on-premise 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.

Migration Architecture for On-premises Oracle Database to Azure Database for PostgreSQL

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.

Folder structure generated by the Ora2Pg tool
Oracle Database Assessment report generated by the Ora2Pg tool

Schema Migration

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

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.

Azure Database Migration Service

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-premise 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-premise 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-premise 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.