Splendid Insights

Database Migration

From Oracle® database to PostgreSQL

Databases are your most expensive, most complex and your most valuable asset. We’ve collated the key areas to consider in your planning.

A look at migrating from Oracle to PostgreSQL

With the advent of digital transformations, lower costs and the move to the cloud, many companies are wondering if it’s possible to migrate Oracle databases to PostgreSQL. Naturally, they wish to understand the challenges they might encounter. Here we look into what these might be.

Ownership of intellectual property

An Oracle database may only be migrated if it is clear who owns the intellectual property. Only the owner of the intellectual property is entitled to migrate the technology. This needs to be checked before any migration can begin.

Knowledge of both Oracle and PostgreSQL

Naturally, you’ll need to possess a strong knowledge of Oracle and PostgreSQL for a successful migration. And due to the similarities between Oracle and PostgreSQL that process becomes all the more seamless. However, there are subtle differences, which can easily be overcome with a combination of additional knowledge and the right migration solution.

Oracle Data Objects and Code Objects

Oracle distinguishes between Data Objects – everything that has to do with the structure of the database, and Code Objects – everything that has to do with the processing logic (PL/SQL and SQL). In general, given the similarities between Oracle and PostgreSQL, the Data Objects are easy to migrate to PostgreSQL. Migrating the Code Objects (in Oracle PL/SQL and in PostgreSQL PL/pgSQL) is far more complex, but not something to be afraid of. Using the right solution, which is able to migrate PL/SQL to PL/pgSQL, most of the complexity (like dependencies between the objects, Oracle packages to native PostgreSQL, etc.) will be solved automatically. This combined with a good understanding of both Oracle and PostgreSQL it can all be resolved.

Select the right solution to migrate from Oracle to PostgreSQL

There are a lot of solutions available to help you with migrating from Oracle to PostgreSQL, read our blog Ora2Pg vs Cortex. Most of them provide the right support regarding the migration of Oracle Data Objects. However, when it comes to the migration of the Oracle Code Objects, the solutions vary widely. The difference is mainly in the way they perform the migration. It’s important that all dependencies between the objects are taken into account when migrating the Code Objects. You need to ensure you perform a context related semantic translation in the most effective and efficient way for an optimal result. Added to this, it must migrate to native PL/pgSQL to prevent a new vendor lock-in.

Data migration

Of course, the migration of data from Oracle to PostgreSQL is a key part of the entire process, especially when it comes to the final deployment of the migrated database in production. It’s important to determine in advance the size of your database and what restrictions need to be taken into account (such as maximum downtime, change data capture, etc.). For example, if 100 MB of data needs to be migrated from Oracle to PostgreSQL and there are no restrictions, that’s simple. If 3 TB of data needs to be migrated and there must be zero downtime, specialised tools will be needed to make it possible. Planning is vital.

 

Explore our proven Solutions

Migration Scoping. Small investment. Smart review. Great insights.

We give you in no time insight into the complexity of your Oracle databases so you are able to plan your migration strategy.

Learn more

Cortex. Migrate your Oracle databases to PostgreSQL with ease

Delivers the effortless migration of any Oracle database to native PostgreSQL without any vendor lock-in.

Learn more

Read our latest Cortex white paper

Latest news from our blog

Cortex 2.4 is released

Cortex 2.4 is released

Our aim was to finalise this new release of Cortex before the end of 2022 and we succeeded. This new release supports PostgreSQL 15 (including native merge statement), it has functionality to include external SQL’s (queries) to a migration from Oracle to PostgreSQL,...

read more
Continuous development of Cortex

Continuous development of Cortex

We work on Cortex every day to improve its capabilities. For the last months of 2022, we have been working hard to deliver a new release of Cortex. In this new release of Cortex we have further improved our translation engines for migrating the Oracle Data and Code...

read more