A look at migrating from Oracle 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.
With the advent of digital transformations, lower costs and the move to the public cloud, many companies are wondering if it’s possible to migrate an Oracle database 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 between them. 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 product.
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). 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. With a good understanding of both Oracle and PostgreSQL they can all be resolved.
Select the right product
There are a lot of products available to help you with migrating from Oracle to PostgreSQL. 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 products 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 for an optimal result. Added to this, it must migrate to native PL/pgSQL to prevent a new vendor lock-in.
Data migration planning
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.