From Oracle® database to PostgreSQL

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.