Alex Kirpichny

Chief Product Officer, Ispirer Systems

A large number of businesses utilize databases for various purposes: as a storage of business-critical information, for streamlining operations, risk management and others. The goal of the majority of companies, however, is to reduce the operational costs associated with databases. In this case, an open-source PostgreSQL database is a popular destination for migrating.

Today, we will focus on migration from Oracle to PostgreSQL and consider in detail its features and pitfalls. So, let’s dive deep into the topic.

Why migrate to PostgreSQL?

There are several possible reasons for a database migration from Oracle to PostgreSQL. Let’s review some of them:

Cost. Considering that Oracle is a proprietary software, sometimes it implies expensive licensing fees, which can be a significant burden for some organizations. PostgreSQL is an open-source software which means it is free to use and distribute. Performance. PostgreSQL is known for its high performance and scalability which makes it a popular choice for databases with intensive workloads.
Easy to use. PostgreSQL is known for its ease of use and flexibility. It is easy to install and configure, and has a simple, intuitive interface. In contrast, Oracle can be complex and difficult to manage, especially for smaller organizations that do not have dedicated IT staff.
Integration. PostgreSQL database is highly compatible with a wide range of programming languages and development tools, making it an ideal choice for organizations that need to integrate their database with other software systems.
Community support. A large community of PostgreSQL enthusiasts is constantly developing new features and improving the software. This means that there is a wealth of resources available for users, including documentation, tutorials and support forums.

Pre-migration database assessment

A pre-migration database assessment includes analyzing the source system and revealing potential problems. Generally, assessment of a database is provided by a team of seasoned experts, but we decided to optimize this process.

Ispirer team has developed Assessment Wizard with the aim of making a database assessment process quicker and easier. This tool is designed to perform an in-depth analysis of any database and estimate a migration project. Assessment Wizard connects to a source database and collects information about objects, structures inside objects, and statements. With the help of comprehensive reports, generated by the tool, it is possible to estimate migration costs and duration of a migration project with reasonable accuracy. You can check a sample report of Assessment Wizard below:

Migration tools

In light of the fact that each database has its own unique code, it is quite difficult to make an accurate prediction about the level of automation of the migration process. The best way to find it out is to migrate the most representative piece of code. In order to optimize this process, we have created a special tool that automatically analyzes a database and creates a detailed report indicating its volume, number of objects, triggers, indexes, functions and other database features.

As part of the database assessment, we suggest making a test migration of a part of the code and comparing the results with the report of Assessment Toolkit. If the outcome corresponds to the data from the report, you can rely on the assessment results. If the results are quite different, we would recommend that you contact us and we will make a more detailed forecast of your migration project. Often incorrect tool settings can result in poor quality of a test database migration. In such a situation we recommend contacting our support team.

For data, the main criterion is the speed of migration. But not only the speed itself is important, but also the ratio of data volume and downtime. Let’s consider two examples:

The migration task: data volume is 100 GB, and downtime is a week. In this case, the speed of most tools will be more than sufficient and such criteria as database migration quality, usability and price will come to the fore.
The migration task: data volume is 10 TB, and downtime is 8 hours. In such a situation, it is impossible to migrate using a tool only. Apart from using a migration tool you also need to configure a database and the entire environment as well as replicate the data. More on this later.

Schema migration

Migration of the schema and business logic objects is the most laborious and time-consuming stage in a database migration project. For example, manual migration of SQL objects with a total volume of 100k LoC can take 10-20 man-months, depending on the features of the code. However, using an automated solution, this process can be speeded up by 2-4 times.

With its unique SQL features, Oracle can deliver a number of migration issues to dba experts. This database contains a large number of unique features and functions that don’t have direct equivalents in other databases. Let’s consider the main issues:

Native packages. Oracle contains a set of built-in packages that serve a specific purpose. A tremendous number of packages can be used in a database, while each of them can contain dozens of functions. The main issue for migration is that most of the Oracle packages cannot be reproduced in the PostgreSQL database as it doesn’t have its equivalents. Here, moving the logic to the application tier is the best option, and our tool can handle this as well.
System objects. A huge number of system objects in the Oracle database can greatly complicate the database migration process. Addressing system objects in Oracle and Postgres differs greatly, so this task can be a tough nut to crack.
Dynamic SQL. This is one of the hardest parts of automatic migration. It is especially complicated when statements are collected using several statements and conditional operators. More on this topic you can read in our blog post “Dynamic SQL Conversion”.

Equally difficult for migration are spatial data and geographical functions, hierarchical queries, pivot statements, various functions for working with dates, using tabular data types, transactions. Business logic migration is a no less challenging and meticulous process that requires manual corrections. There is no tool available that can completely migrate the Oracle schema to PostgreSQL database without errors and with logic preserved. Ispirer Toolkit, however, can handle this issue almost flawlessly.

Testing

An integral part of a successful project is testing a converted database. We test databases in two ways.

Using test cases of a source database. This option is the simplest, but no less effective. Test cases are taken directly from the scenarios of working with a database from the source application (function calls, queries, etc.). Testing is conducted using these test cases, as well as a snapshot of the source database, with the same scenarios being performed in both cases. In case of differences, we analyze the reasons for that and correct the converted code.
Testing of a new database. If the first testing option is impossible, we develop additional testing scenarios that would reflect the database logic. Extensive experience in testing and debugging the code generated by our tool allows us to easily identify and fix errors, if any.

Optimization

Obviously, each database is different and the code optimized for one database will not necessarily work for another. That is why often the same operations in a target database can take much longer than in a source database. Optimizing the database performance can resolve such issues and make a target database operate efficiently and effectively, which is crucial for business operations.

Data migration

If it is not possible to disable an Oracle database for long enough to transfer data, data replication should be used. It implies ongoing operation of a source database, while a replication tool monitors data changes and transfers them to a PostgreSQL database.

In fact, data migration can be accelerated by:

increasing network speed;
enhancing the performance of the servers running the migration tool, source and target databases; performing parallel migration.

Thus, the speed of data migration depends not only on the tool, but also on multiple other factors. Our seasoned experts will help you find a comprehensive solution that is best suited to your project.

Conclusion

This article provides you with a general overview of database migration from Oracle to Postgres. It describes the main nuances of database migration.

In order to migrate your database from Oracle to PostgreSQL without a hitch, try our Ispirer Toolkit. It supports automatic migration from 30+ databases, including Oracle and PostgreSQL. Our tool automates the migration process, and if you have a specific database, you can customize the toolkit according to your requirements to ensure the maximum level of automation.

You can try the toolkit in action using a free demo license. Start the transformation process of your Oracle database today!