Skip to main content

Oracle to PostgreSQL Migration: Non-Disruptive Migration Plan | NineData

The key to migrating Oracle to PostgreSQL is not only moving the data, but also controlling cutover risk while the business keeps running. NineData is suitable for Oracle replacement, modernization, and cost-reduction migration scenarios, and can be used for full migration, incremental sync, and final cutover.

Applicable Scenarios​

  • Oracle replacement and cost reduction: Gradually migrate core workloads to PostgreSQL to reduce long-term licensing and maintenance costs.
  • Heterogeneous database migration: Handle differences in data types, object structures, and SQL behavior between Oracle and PostgreSQL.
  • Smooth business cutover: Use full migration, incremental sync, and data comparison to shorten the final cutover window.

Issues That Must Be Addressed When Migrating from Oracle to PostgreSQL​

  • Business Availability: The migration must be carried out without affecting business operations. In other words, the migration should not result in downtime, which means many considerations must be taken into account: How to migrate existing and incremental data completely? How to handle performance fluctuations during migration? How to achieve a smooth switch of applications? And so on.
  • Table Structure Initialization and Change Linkage: When the number of tables to be migrated is huge, and DDL occurs in the source Oracle during the migration process, how to achieve an efficient and stable database migration is undoubtedly a significant challenge.
  • Migration Data Quality: During the large-scale data migration process, how to ensure data consistency; when the business migration switch is abnormal, how to effectively roll back to ensure the availability of the business.
  • How to Minimize Losses in Case of Migration Failure: PostgreSQL and Oracle have significant differences in functionality and performance, and the migration complexity is high. When the business encounters migration failure, how to effectively ensure the availability of the business is also a key issue that the business needs to consider.

If these issues can be resolved smoothly, the migration process will be quite smooth. So, how to solve them? There are, of course, methods. For the above 4 issues, NineData has absolute authority to speak, let's first see how it solves these problems:

  • Non-Disruptive Business Migration: NineData provides structural migration, full data migration, and CDC incremental data migration capabilities based on redo log. During the database migration process, the source Oracle can continue to provide services normally. NineData can automatically complete structural migration, full data migration, and automatically initiate real-time monitoring, collection, parsing, and replication capabilities of redo log. The incremental update data of the source Oracle will be replicated in real-time to the target PostgreSQL. When NineData enters the incremental data migration phase and the replication is delay-free, the business can perform read-only verification in PostgreSQL and use NineData's data comparison tool for data consistency verification. After the business verification is passed, the business can switch to downtime. It can be seen that the entire migration process has a very short business downtime.
  • Strong Replication Performance: During the database migration process, the migration speed is undoubtedly an important factor affecting whether the business can be successfully switched. In this process, NineData has carried out in-depth performance optimization for Oracle > PG data replication, based on log analysis, intelligent sharding, dynamic batch accumulation, data merging, and unique data formats, effectively ensuring the performance of full data replication and incremental data replication. Currently, NineData's full replication performance is up to 200 GB/hour, and incremental data replication performance is up to 20,000 records/second.
  • Comprehensive Migration Rollback Plan: The migration complexity of Oracle > PostgreSQL is extremely high, and there are many differences in functionality and performance between Oracle and PostgreSQL, involving a large amount of business transformation and performance tuning, and the difficulty of migration and docking is extremely high. To reduce the risk of failure in the migration and docking from Oracle to PostgreSQL, businesses usually need to prepare a rollback plan for failure. NineData provides CDC incremental replication capabilities based on PostgreSQL wal log, supporting real-time collection, parsing, and synchronization of increments to Oracle. Before the business switches from Oracle to PostgreSQL, a replication task that flows back incremental data from PostgreSQL to Oracle in real-time can be set up in NineData. Based on this replication task, the new data generated by the business switch to PostgreSQL can be synchronized to Oracle, and Oracle stores the complete business data. In case of business operation problems caused by the functionality or performance of PostgreSQL, the business can be switched back to Oracle at any time, effectively avoiding business migration failures.
  • Comprehensive Data Quality Assurance Plan: NineData provides a variety of data consistency verification schemes, including full precision verification, quick verification, and incremental verification, which can strongly verify the data consistency of Oracle > PostgreSQL migration data. At the same time, when data inconsistency occurs, it can provide one-click repair capabilities. Based on NineData's data consistency verification capabilities, business migration failures caused by data quality can be effectively avoided.

Based on the above capabilities, NineData can easily solve the migration issues from Oracle to PostgreSQL. Let's take a look at how to operate.

Step One: Enter Source and Target Data Sources​

  1. Log in to the NineData console, click on Data Source Management > Data Sources, and then click on Create Data Source on the page to select the data source you need to enter.

    image-20240321112625958

  2. Configure according to the page prompts, and then click Create Data Source to complete the creation.

    image-20240319150009255

  1. Log in to the NineData console, click on Data Replication > Data Replication, and then click on Create Replication.

    image-20240319150313727

  2. Configure the replication task according to the page prompts. Since we need non-disruptive migration, we need to check Incremental Replication in the Replication Type.

    image-20240321180547340

  3. After the configuration is completed, start the task. For all the migration objects you have configured, NineData will first perform a full migration of all the existing data, followed by real-time synchronization of the incremental data newly added in Oracle. All newly written data will be synchronized to PostgreSQL without omission. Whenever the incremental data on the target side catches up with the source side, the task panel will display Delay 0 seconds, as shown in the figure below.

    image-20240320100557992

Step Three (Optional): Verify the Integrity of Synchronized Data on the Target Side​

In addition to the synchronization function, NineData also provides a comparison function for the source and target synchronized data after synchronization to ensure the integrity of the data on the target side.

  1. Log in to the NineData console, click on Data Replication > Data Replication, and then click on the replication task ID created in Step Two.

    image-20240321180811204

  2. Click on the Data Comparison tab, and click on Start Data Comparison (if Start Data Consistency Comparison is checked in the task configuration of Step Two, the comparison results are directly displayed here).

    image-20240321181451423

    tip

    After starting, the system will automatically compare the synchronization objects of the source and target sides and give the comparison results.

    image-20240321181600028

  3. You can click on Re-compare on the page after a while to verify the results of the latest incremental data.

    image-20240321181629396

Step Four (Optional): Configure Task Exception Alerts​

Since it is an incremental migration task, you may need the system to monitor the task status in real-time and notify you immediately when there is an exception.

  1. Log in to the NineData console, click on Data Replication > Data Replication, and then click on the replication task ID created in Step Two.

    image-20240321180811204

  2. Click on Configure Alerts in the upper right corner.

    image-20240321181730559

  3. Enter the Policy Name and click on Save Configuration. You can directly use the built-in default rules to send SMS reminders when the task fails or the replication delay is greater than or equal to 10 minutes. You can also create custom rules to notify according to your needs.

    image-20240320105025913

Finally​

After NineData migrates all data to PostgreSQL (with a delay of 0), the last step is to switch the business traffic to PostgreSQL during a low peak period to complete the entire migration from Oracle to PostgreSQL. This switching process is very critical and needs to be carefully planned and executed.

Last updated on