Oracle to PostgreSQL migrations – A combination of science and art

For me, migrating Oracle databases to PostgreSQL is a combination of science and art. Science is designed to be objective and guided by data and art is subjective and heavily influenced by feelings and opinions. Traditionally, art and science are treated as two separate disciplines, but when they are studied together, it becomes clear how one influences the other.

Cortex, our Oracle to PostgreSQL migration tooling, performs migrations from Oracle databases to PostgreSQL in a highly automated, and syntactic and semantic way. Let’s call this science.

Every implementation of an Oracle database is unique. A proper migration of an Oracle database to PostgreSQL can only be done if all the meta data (stored in the Oracle Catalog Views) of the Oracle database in question is known. This meta data can be syntactically and semantically interpreted and translated by software, but does not in fact give an unambiguous insight into the use case(s) of this Oracle database.

This is where art enters the picture. Art is the human side of migration. In other words, you need to understand why certain solutions were chosen in the Oracle database. Quite often, when combined with the right knowledge and expertise, this information can be extracted from the meta data.

For example, you could look at which Oracle built-in’s and data types are being used. This gives an initial insight into the use cases. When this information is considered in combination with in-depth knowledge of Oracle and PostgreSQL, it can lead to surprising insights that need to be taken into account during a migration in order to achieve the desired result.

We regularly come across use cases in an Oracle database that makes us ask: “why did they apply this?”

Here’s a recent example:

  • During the assessment of an Oracle database (using our proprietary Cortex tool), we came across traces of Advanced Queuing in combination with the use of Oracle Streams.
  • The developer had used Oracle Golden Gate in combination with Oracle Streams to implement logical replication.
  • After verifying this with the developer, we came to the conclusion that whole parts of code, as well as certain tables, were no longer needed if you want to implement the same functionality in PostgreSQL.

Had we only applied the science (doing a straight forward migration with Cortex), this would never have become apparent, or only once it was too late, and would have had a negative impact on the migration.

By combining our knowledge, creativity and human opinions (art), we were able to learn a lot more about the Oracle database so that we could achieve an optimal migration from Oracle to PostgreSQL using and configuring Cortex.

Once again, it’s clear that an in-depth knowledge of both Oracle and PostgreSQL is essential to successfully perform a migration from Oracle to PostgreSQL, no matter how good the migration tooling is. Obviously, using good migration tooling is the foundation for success, but it’s the combination of technical expertise in both Oracle and PostgreSQL, together with creative thinking that’s needed to recognise all the specific nuances and ensure a successful outcome.

So, what’s my final piece of wisdom? Don’t just rely on the science alone, but let your human brain do its job too. Combine this with Cortex and experience how effortless a migration from Oracle to PostgreSQL can be.

Read our latest Cortex white paper

Want to know more?