Oracle RDS to Azure PostgreSQL migration using Ora2pg

Author – Prashant Pawar, Associate Cloud Engineer

We know that database is most crucial to any company or organization. This is because the database stores all the important details about the company. PostgreSQL is the world’s most advanced open source database. PostgreSQL community is very strong and they are continuously improving existing PostgreSQL features and also add new features. Whether migrating a database or an application from Oracle to PostgreSQL with only one type of database knowledge, there are few things to know about the differences between the two database systems.

In this blog we will cover how to install Ora2pg tool to migrate from Oracle DB to PostgreSQL database. In the next blog, we will see how to use Ora2pg tool to generate assessment report and data migration.

About Ora2pg

Ora2Pg is a free tool used to migrate an Oracle or MySQL database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into your PostgreSQL database.

Why migrate from Oracle to PostgreSQL?

1. Cost: As you may know Oracle license cost is very expensive and there is additional cost for some features like partitioning and high availability. So overall, it’s very expensive.
2. Flexible open source licensing and easy availability from public cloud providers like Azure.
3. Benefit from open source add-ons to improve performance.
4. Compatibility: PostgreSQL also clearly has the edge when it comes to compatibility with operating systems, which is extremely crucial in today’s diverse and complex development environments.

Pre-Requisite

1. DBI (database interface module)
2. DBD::Oracle module
3. DBD::Pg module

We are going to use a script for the automation of installing the above modules. This script will download and install all the dependencies that are required to install Ora2pg tool. You can download the installora2pg.sh script from below link-

https://github.com/microsoft/DataMigrationTeam/tree/master/IP%20and%20Scripts/PostgreSQL%20Migration%20and%

20Assessment%20Tools

Before getting started,

Pre-Requisites for Linux: This script requires RedHat/Ubuntu 18+/Debian 10/Centos 7 or later.

So, Let’s get started..

Installation Steps

1. SSH into Linux VM
2. # sudo su
3. # yum update -y
4. Download and install oracle instant client basic + SDK package from oracle.com
5. To download the rpm package-

# wget https://download.oracle.com/otn_software/linux/instantclient/19800/oracle- instantclient19.8-basic-19.8.0.0.0-1.x86_64.rpm

# wget https://download.oracle.com/otn_software/linux/instantclient/19800/oracle-instantclient19.8-devel-19.8.0.0.0-1.x86_64.rpm

6. To install the package-

# sudo yum install oracle-instantclient19.8-basic-19.8.0.0.0-1.x86_64.rpm

# sudo yum install oracle-instantclient19.8-devel-19.8.0.0.0-1.x86_64.rpm

7. Download script file from above link then-

# nano <filename>.sh

Paste the above script in the file and save it.

8. To run the script

# bash <filename>.sh

The process takes few minutes to complete. After completion of the process we can see that the Ora2pg tool is installed successfully.

9. After that we need to set environment variable for Ora2pg

# export PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/bin

10. When we run ora2pg help command, we can see different options available.

So, by following this process we can install Ora2pg tool for Oracle RDS to PostgreSQL migration. In the next part of this blog, we will see how to use Ora2pg tool for generating assessment report and data migration.