Avoiding the Obstacles; migrating from Oracle to PostgreSQL

The digital transformation has made many companies aware of the need to move to the cloud. With this switch, business becomes scalable, more flexible and IT costs will drop significant. So it isn’t surprising that you are wondering whether it is possible to migrate an Oracle database to PostgreSQL. And since databases are your most expensive, most complex and most valuable asset, we’ve rounded up the key points.

1. 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.

2. Knowledge of both Oracle and PostgreSQL

For a successful migration between both systems, you must of course have a solid knowledge of Oracle and PostgreSQL. Due to the many similarities between Oracle and PostgreSQL, the process will fit seamlessly. The subtle differences can be resolved by a combination of the right migration product and the right knowledge.

3. Oracle Data Objects and Code Objects

Oracle distinguishes between Data Objects – everything to do with database structure, and Code Objects – everything to do with 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 much more complex. The vast majority of the Code Objects are migrated automatically by our Oracle to PostgreSQL migration product Cortex.

4. Select the right product

There are a lot of products available to help you with migrating from Oracle to PostgreSQL. Most of them help you regarding the migration of Oracle Data Objects. However, when it comes to the migration of the Oracle Code Objects, the products vary widely. It’s important that all dependencies between all the objects are taken into account when migrating the Code Objects. You need to ensure the product performs a context related semantic translation for an optimal result, instead of simply performing find and replace on keywords. Added to this, it must migrate to native PostgreSQL to prevent a new vendor lock-in.

5. Data migration planning

The migration of data from Oracle to PostgreSQL is a very important part of the whole process, especially when it comes to the final implementation of the migrated database in production. It is therefore important to determine in advance the size of your database and which restrictions must be taken into account (such as maximum downtime, change data capture, etc.). For example, if 100MB of data needs to be migrated from Oracle to PostgreSQL and there are no restrictions, it’s easy. If 3 TB of data needs to be migrated and no downtime is allowed, then specialized tools are needed to make this possible. Planning is essential.

6. Migration Scoping

Since every database is different you need to understand how complex each specific migration will be. Whether you have 5 or 5,000+ Oracle databases, our Migration Scoping service will give you a valuable insight into their complexity so you are able to plan your migration strategy. With our Migration Scoping service, we can assess every Oracle database you have in production, and discover if the migration will be very simple, simple, medium or complex for each database. A HTML page is created for each database, which gives you key insights into the complexity of the migration. All scoping results are consolidated in an overview and for each database an estimate to perform the actual migration, using Cortex, is included.

Migrate Oracle to PostgreSQL?

Want to know more?