There is sometimes confusion when it comes to tools for migrating from Oracle to PostgreSQL. There are tools for migrating business data from an Oracle database to a PostgreSQL database, and there are also tools for technically migrating / translating the definitions and processes of an Oracle database to a PostgreSQL database.
For the record, this blog is about tools for migrating / translating the definitions and processes of an Oracle database to PostgreSQL.
Technical migration in general
Technically migrating an Oracle database to PostgreSQL involves a lot of work. Roughly speaking, an Oracle database consists of two parts, namely the Data Definition, defined in Data Definition Language (DDL) and Data Manipulation via Data Manipulation Language (DML). The DDL is used to create and modify database objects like tables, indexes, views, and constraints. DML is used to perform operations, using PL/SQL functions, procedures, packages, etc., on the data within those database objects.
The tables and views (part of the DDL) have a relationship (dependencies) among them, but also have a strong relationship (dependencies) with the PL/SQL functions, procedures, packages etc. (part of the DML).
Looking at PostgreSQL, you see a similar setup namely the DDL and the DML, but instead of PL/SQL, PL/pgSQL is used for the DML.
Of course, there are technical differences between an Oracle database and PostgreSQL. For example, PostgreSQL has no packages, PostgreSQL is much stricter in the use of data types, and things like queuing, mailing, etc. are supported in an Oracle database and not in PostgreSQL.
What to expect from a migration tool?
During a technical migration from an Oracle database to PostgreSQL, you need to be able to rely on a number of things supported by the tool you will be using, such as:
- The migration from the Oracle DDL and DML to PostgreSQL DDL and DML is largely automated, maintaining the interrelationships (dependencies) between the DDL and the DML.
- The aspects that technically cannot be (fully) migrated and require manual adjustments are clearly reported so that they can be resolved in a controlled manner and/or replaced with an alternative.
- The different versions of PostgreSQL are taken into account.
- The ability to set parameters to be taken into account during migration to get the most optimal results.
- The migration to community version PostgreSQL, whether or not taking into account common open source extensions such as PostGIS and others, so that no new vendor lock-in will arise.
- The way the results of the tool are delivered, allowing any manual adjustments to be made in a controlled manner and to avoid a lot of searching to find out what the tool has done. Also for auditing purposes you need traceability of the migrated functionality.
- The tool also must take care of assuring non functional quality of the databases like performance and security when migrating to PostgreSQL.
Why is it a must to have an adequate tool?
The above list is obviously not exhaustive, but most migration tools do not meet these requirements.
As a result, the manageability of the migration from Oracle to PostgreSQL becomes almost impossible, many manual adaptations need to be made unnecessarily, which may increase the risk of errors, the costs of a migration will increase and the duration of a migration will become longer.
In summary: Because of the dependencies of all components inside Oracle databases automation is key for an acceptable Oracle-to-PostgreSQL migration result so: Migration automation is not a ‘nice-to-have’ but a necessary requirement for migrations!
Why Cortex?
Splendid Data developed Cortex with the goal of creating a manageable Oracle to PostgreSQL migration process that achieves the following:
- Efficiency. Higher productivity with far less resources, reducing costs and accelerating migration workloads.
- Quality. The automated migration process of Cortex guarantees high-quality migration and reduces the risk of errors that may occur in manual migrations or manual adjustments.
- Speed. The migration process with Cortex is the fastest approach to get to the desired result with a minimal manual intervention.
- Cost. A highly cost-effective solution for organizations looking to migrate their Oracle databases technically to PostgreSQL without any vendor lock-in.
- Control. Supporting tools (like Jira and Azure DevOps) for issue management in combination with Git (for version control), after Cortex did its work.
To learn more about Cortex, download our free Cortex white paper below, or contact us at vasb@fcyraqvqqngn.pbz to schedule a demo.