Approaches and Tools for Oracle to PostgreSQL Migration

When comparing Oracle and PostgreSQL, the second optionoffers many important advantages likeopen-source, low total cost of the ownership, customization and flexibility. Those are reasons for database migration, although moving from Oracle to PostgreSQL is challenging and time-consuming. That is why the person responsible for IT infrastructure must carefully evaluate cons and pros to find out is the database migration worth the efforts.

Probably, the key reason of Oracle to PostgreSQL database migrationis reducing expenses. Oracle licensesarecostly, especially for large corporate scale data warehouses. Thatprice accumulated with high salaries of Oracle specialists, makes this database management systemfar more expensive than PostgreSQL. PostgreSQL is open-source DBMS that is free to install and use, also supplied with many open-source libraries, APIs and modules.

PostgreSQL provides more flexibility compared to Oracle because it is licensed under an open-source model and is readily available from public cloud providers such as AWS. Opting for PostgreSQL reduces the risk of vendor lock-in, giving organizations the liberty and flexibility to utilize the database system that suits their requirements best.Additionally, PostgreSQL’s customization capabilities are superior to Oracle’s. Being an open-source database, PostgreSQL supports numerous extensions and add-ons that can considerably enhance its performance, many of which are free of charge. Conversely, Oracle typically charges for similar functionalities, resulting in additional costs.

Database Migration Path

The migration process can be divided into several phases, including assessment, schema migration, intermediate testing, data migration, and final testing. The first step involves assessing the application to determine the level of difficulty involved in migrating from Oracle to Postgres. This requires a comprehensive analysis of technology-related factors such as client, application server, data access, and database features to evaluate compatibility.

In the schema migration phase, the user’s schema in Oracle, which shares the same name as the user, needs to be converted to PostgreSQL’s schema, which is not synonymous with the user. By default, newly created objects in PostgreSQL will be placed in the public schema if a schema is not explicitly specified. This setup in PostgreSQL offers the advantage of enabling a user to create multiple schemas without creating separate users, as well as the ability to grant permissions to others for creating objects in those schemas.

Before continue with further steps of the database migration, the converted entriesmust be thoroughly tested on a sample dataset. Sample data should be loaded into Postgres from a development or testing environment, and an application connection should be set up using appropriate data access drivers. The accuracy of the conversion should be verified by loading the same fragment of data in both the Oracle and PostgreSQL databases and querying it to confirm that the SQL results are identical. Any issues revealed during the functional testing phase should be reviewed and addressed accordingly.

Performance testing is also critical during the migration phase as some of the Oracle built-in transactions or features functionality may differ slightly in PostgreSQL, and the application may experience some variance. This phase is essential for identifying any differences and fixing them at the application, data access (drivers), and database level through proper tuning.

Techniques of the Data Migration

There are three common types of database migration techniques: Snapshot, Snapshot in Parallel, and Change Data Capture (CDC). Choosing the most suitable method that fits the allowed downtime window is crucial.

The Snapshot Approach involves capturing the source database state as a snapshot and applying it to the target database all at once. While this is a straightforward approach, no WRITE operations are allowed on the source database during the snapshot process, resulting in application downtime.

The Snapshot – Piecewise (Parallel) Approach is a variation of the snapshot method, where data objects are split into chunks and snapshots are taken in parallel. This approach reduces the snapshot duration and downtime window, but application downtime is still required.

The Change Data Capture (CDC) Approach involves capturing and tracking changes in real-time on the source database and replaying them on the target database. CDC methods can use triggers or transaction logs. While this method allows the Oracle database to be accessed for both reading and writing during migration, it requires replication software and may have slight performance overhead in trigger-based CDC.

Oracle to PostgreSQL Migration Tools

There are a number of free and commercial tools to migrate Oracle databases to PostgreSQL server. Free tools often require manual post-processing while commercial software provides fully automated database migration. One of such commercial solutions is Oracle toPostgreSQL converter developed by Intelligent Converters software company. The product implements parallel piecewise snapshot method of database migration through intuitive wizard-style interface automating overall procedure within just a few button-clicks.

User interface of Oracle to PostgreSQL converter includes 7 wizard screens:

Select conversionscenario – either direct migration to PostgreSQL database or export into PostgreSQL script containing SQL-statements to create tables, indexes, constraints, views and insert data.

Connecting Oracle server – on this step you have to enter all detailsof Oracle database server.

Connecting PostgreSQL server (for ‘Move to PostgreSQL server’ scenario selected on the 1^st wizard screen) –provide all details for PostgreSQL.

Select log file and the destination – enter name of PostgreSQL database or script file and the log file to enable logging (otherwise, it will be disabled).