From Oracle to PostgreSQL: a comprehensive guide

Alexandr Kirpichny

Product Owner, Ispirer Systems

Many businesses utilize databases for various purposes: to store business-critical information, streamline operations, manage risk, and more. The goal of most companies, however, is to reduce the operational costs associated with databases. In this case, an open-source database, PostgreSQL, is a popular destination for migrating.

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

Why migrate to PostgreSQL?

Oracle and PostgreSQL are two systems that stand out in the data market. Oracle database is powerful. It may have served you well for years, handling large-scale operations with precision. But over time, the expenses—license costs, vendor lock-in, and expensive add-ons—start to outweigh the benefits.

Meanwhile, PostgreSQL is an open-source, highly scalable alternative. It’s gaining popularity in the tech world because it's versatile and backed by a vibrant community. Besides, it shows high cost-effectiveness. Functionality like foreign data wrapper (FDWs) for integrating external data, user-defined stored procedures, and flexibility in customization make PostgreSQL a tempting alternative.

Comparison of Oracle and PostgreSQL Databases

There are several possible reasons for a migration from Oracle to PostgreSQL. We’ve wrapped up the primary key aspects in the table below:

So, you decide to go for a database migration. But transitioning isn’t just about switching Oracle and PostgreSQL. It’s about adapting every aspect of migrating from Oracle database to PostgreSQL. This includes PL/SQL, data structure, every single file, and business logic. It all starts with the preliminary assessment of the migration demands and how to ensure data integrity.

Pre-migration database assessment

An assessment before database migration includes analyzing the source system and revealing potential problems. Generally, a team of seasoned experts assesses a database, but we decided to optimize this process.

The Ispirer team has developed Assessment Wizard. It aims to make the assessment process quicker and easier. This tool performs an in-depth analysis of any database and estimates a migration project. Assessment Wizard connects to a source database with Oracle-specific features. Then, it collects information about objects, structures inside objects, and statements.

With comprehensive reports, you can estimate Oracle database migration scope with reasonable accuracy. Knowing the volume of work required, you will be able to assume the costs. It gives information in different views: charts or tables. Check a sample Oracle and PostgreSQL report of Assessment Wizard below:

Migration tools

Each database has its unique code, including Oracle and PostgreSQL. That's why it is quite difficult to make an accurate prediction about how automated the database migration process can be. The best way to find it out is to migrate the most representative piece of code. As part of the migrating from Oracle database assessment, we suggest a test migration of a part of the code.

Then, you can compare the results with the Assessment Wizard report. If the outcome corresponds to the data from the report, you can rely on the assessment results. If the results differ, contact us and we will make a more detailed forecast of your migration project. Besides, incorrect settings can result in poor quality of a test database migration. Contact our support team to fix configurations and perform the Oracle and PostgreSQL assessment again.

For data, the main criterion is the speed of migration. While the speed is important, the ratio of data volume and downtime also matters. Let’s consider two examples:

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

Schema migration

Schema and business logic object migration is the most resource-intensive 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. It depends on the code contents. Luckily, an automated solution can speed up the process by 2-4 times.

Oracle can deliver several migration issues to DBA experts. This database contains a large number of Oracle-specific features and functions that are hard to convert. They don’t have direct equivalents in other databases. Let’s consider the main issues:

• Native packages. Oracle database contains a set of built-in packages for specific purposes. Many packages populate a database, and each of them can contain dozens of functions. You cannot reproduce most of the Oracle packages in the PostgreSQL database as it doesn’t have their equivalents. Here, moving the logic to the application tier is the best option, and our tool can handle this well.
• Database system objects. A huge number of system objects in an Oracle database complicate the database migration process. Addressing system objects while migrating from Oracle to Postgres differs much, 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 SQL queries use several statements and conditional operators. More on this topic you can read in our blog post “Dynamic SQL Conversion”.

Spatial data and geographical functions, hierarchical queries, pivot statements, date functions, tabular data types, and transactions are also hard to migrate. Business logic migration is a no less challenging and meticulous process. It requires many manual corrections. Oracle databases often rely on stored procedures to handle business logiс in the database layer. You need to recreate them in PostgreSQL using its procedural language (PL/pgSQL) or alternatives.

No tool can perform the Oracle schema to PostgreSQL database migration without errors in files and with logic preserved. Ispirer Toolkit, however, can handle this issue almost flawlessly.

Testing

An integral part of successful moving from Oracle database is testing migrated data. We test converted databases in two ways.

• Using test cases of a source Oracle database. This option is the simplest, but no less effective. Test cases happen directly from the scenarios of working with a database from the source application (function calls, queries, etc.). The testing procedure uses these test cases, as well as a snapshot of the source database. The same scenarios apply to both cases. In case of differences, we analyze the triggers for that and correct the converted code.
• Testing of a new database. If the first testing option is impossible, we develop additional testing scenarios to reflect the database logic. We have extensive experience in testing and debugging the code generated by our tool. This allows us to easily identify and fix errors, if any.

Optimization

Each database is different and the application code optimized for one database will not necessarily work for another. Oracle and PostgreSQL are no exception. 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. It can make a target database operate efficiently, which is crucial for business operations.

Data migration

If you cannot disable your Oracle databases long enough to transfer data, use data replication. It implies the ongoing operation of a source database. A replication tool will monitor data changes and transfer them to a PostgreSQL database.

Database 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 best suited to your project.

Conclusion

You've got a general overview of database migration from Oracle to Postgres. Now you know all the main nuances of converting objects automatically.

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 to the best possible extent. It also gives complexity indexes for all elements to evaluate the time and work scope needed for the project. If you have a specific database, you can customize the toolkit according to your requirements for maximum automation.

Start the transformation process of your Oracle to PostgreSQL migration today!