How to Achieve Non-Disruptive Database Migration from Oracle to PostgreSQL

In 1970, the father of databases, Edgar Frank Codd, published the paper "A Relational Model of Data for Large Shared Data Banks," which laid the foundation for the development of relational databases that followed. In 1979, Oracle 2, a database product based on relational model theory, made its debut and has since dominated the global database market for the past three to four decades.

Fast forward to 1989, when Oracle entered the Chinese market, it was almost a monopoly, with most large enterprises under its control. However, despite Oracle's powerful functionality and stable operation, its high licensing fees accounted for a significant portion of corporate expenditures, with some companies even stating that Oracle's licensing fees exceeded the total cost of a major project. Nevertheless, due to Oracle's strong stickiness, without alternative solutions, companies had to pay up for a considerable period.

Today, the situation has changed dramatically, with a plethora of alternative solutions emerging. PostgreSQL, as one of them, not only has diverse features and powerful performance but also has excellent scalability, which can better accommodate Oracle. Enterprises can also benefit from the rich resources and technical support from the long-term active open-source community. Most importantly, PostgreSQL is open-source software, eliminating the need to pay high licensing fees, saving companies a considerable amount of expenditure. Therefore, the migration from Oracle to PostgreSQL has become the first choice for many enterprises.

However, migrating from Oracle to PostgreSQL is not as simple as it sounds; there are many issues that must be addressed.

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.

   

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

   

Step Two: Configure Synchronization Links

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

   

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.

   

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.

   

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.

   

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

   

   tip

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

   

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

   

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.

   

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

   

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.

   

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.