From Oracle to PostgreSQL: migrating 12 TB of data in 12 hours

Ispirer experts made it possible to migrate 24 databases without interrupting the ongoing software development.

Results

Background

Our client stands as a reliable leader in emergency response solutions, delivering cutting-edge software systems and services to the global public safety sector. With a legacy spanning over four decades in facilitating emergency communications, they provide technologically advanced solutions designed for rapid, confident, and clear responses. Their mission is to bridge the gap between individuals in crisis and the experts best equipped to assist them. The company excels in helping communities prepare for, respond to, and recover from critical incidents.

Challenge

An impressive migration scope, 24 databases with more than 12 TB of data and 500 000 lines of SQL code is a critical part of the client’s infrastructure. Such a volume of outdated systems makes a company fork out on maintenance. That was exactly the case of our client. Our client chose migration to PostgreSQL in the cloud to reduce maintenance costs, get access to advanced PostgreSQL extensions without additional cost, and avoid vendor lock-in.

Having compared several companies on the market that provide migration services, the client chose Ispirer. In addition to the deep expertise of Ispirer specialists, the most important factor in choosing our company was the automation of the migration process. Our approach turned out to be the most optimal for the client, taking into account the ongoing development of the existing infrastructure.

At the discussion stage of the project, the customer provided strict requirements:

• Migration should be conducted in parallel with the ongoing software development. As database updates are released every two months, it should be taken into account by the migration team.
• 12-hour downtime window allowed.
• Testing of the migrated databases should be performed in collaboration with the client’s team.

Strict requirements of the customer were the most crucial challenges of the migration project. Ongoing software development made migration difficult to implement as every two months a new database version was released. It means that ongoing changes would render initial conversion efforts obsolete.

The customer agreed to make a 2-month break in the development process to let the experts migrate all the 24 databases, 12 TB of data, fix conversion errors, and switch from Oracle to PostgreSQL, all within such a short period of time.

Moreover, the customer was able to allocate only 12 hours for switching. As it is impossible to migrate 12 TB of data in such a short window, Ispirer had to come up with a non-trivial solution.

Solution

During the planning stage, the Ispirer team took all these project peculiarities, scope and other customer requirements into account. But before embarking on the migration itself, the Ispirer team had to come up with a plan to optimize migration and combine it with ongoing development. On this basis, a project roadmap was the following:

• 6 months - converting schema and testing
• 1 month - databases and applications updates after 3 releases
• 1 month - production migration of all databases

One more challenge concerned data migration and a short downtime window. To ensure a high-quality migration of all data within 12 hours, the Ispirer team conducted preliminary migrations. One of the databases contained 3 TB of data. Such a volume cannot be migrated in 12 hours by any tool, so we performed preliminary conversions. Some 60-70% of the cold data was migrated from the larger databases before the production migration, still at the planning stage.

Just before transitioning to the new database, the client agreed to a two-month development break. Considering the production migration would take a month, the Ispirer team had roughly one month to implement all changes into the final PostgreSQL database and resolve any errors to ensure proper system functionality.

A significant portion of the project was successfully migrated using the Ispirer Toolkit for automated conversion. Nonetheless, fully automating the entire database migration proved unfeasible. Therefore, we proceeded with manual adjustments to refine the automated conversion results. Subsequently, we performed initial testing to verify the accuracy of the conversion. This method enabled a single developer to achieve a conversion rate of 5,000 lines of code per day.

Migration from Oracle to PostgreSQL included a number of complexities that the migration team encountered. The main ones related to:

• Native packages
• Collections
• Dynamic SQL
• Working with Geography
• Hierarchical queries
• Transactions + exceptions

The ideal migration strategy involved dividing the process: migrating one portion of the SQL code to the new database while transferring the other portion to the application level. Through close collaboration with the client, we pinpointed code segments suitable for relocation from the database to the application. Additionally, the Ispirer team modified the application, updating connections, database APIs, and embedded SQL accordingly.

To meet the project deadline, the testing process was conducted concurrently with the migration. Testing was conducted in partnership with the client's QA specialists, who developed test cases and executed the testing process. High-quality database testing can only be achieved by experts familiar with the system, making this approach both time- and cost-effective. The deep knowledge and testing experience of the client's QA team were crucial for an optimal testing phase.

Initially, testing experts set up test environments and prepared test cases. By this point, most of the SQL code had already been converted, allowing the testing process to begin. Once the migration was completed, the Ispirer team provided support by fixing errors and bugs, setting up test environments, and ensuring the accuracy, integrity, and performance of the migrated databases.

Outcome

As a result, the Ispirer experts made it possible to migrate all the 24 databases within 8 months. Here are the main outcomes of this project:

• Efficient database migration. The migration scope was successfully migrated within 8 months. This swift turnaround was achieved through meticulous planning and execution.
• Automated SQL conversion. The majority of SQL was converted automatically using Ispirer Toolkit. This significantly reduced the manual effort and sped up the overall migration process.
• Business logic transfer. Part of the business logic was moved from the database to the application level. This approach not only simplified the database migration but also enhanced the application's performance and maintainability.
• Testing and collaboration. The presence of the client's QA team greatly accelerated the testing process. Their in-depth knowledge and experience with the database allowed for more thorough and effective testing than would have been possible with external testers. This collaboration ensured that any issues were quickly identified and resolved.