From Oracle to PostgreSQL: 12TB Data Move in 12 hours

In this article, we will explore the process of migrating from Oracle to PostgreSQL, using a specific project as an example. By analyzing the main challenges and providing a step-by-step breakdown, we will illustrate how we optimized the migration of 24 databases in just 8 months and eventually transferred over 12 terabytes of data in 12 hours.

This comprehensive guide aims to provide valuable insights and practical strategies for anyone undertaking a similar migration. Ready, steady, go!

Major stages of a migration project

Let’s briefly go through the main stages of the migration project and look at what is the key to a successful transition to a new technology.

• Planning. The basis of the basics, the foundation of any project. At this point, the migration team receives the necessary access to the software, discusses the project with the customer and creates a detailed migration plan, taking into account all the requirements and features of the project.
• Migration. The main stage, which includes migrating the database schema, transferring business logic to the application level (at the request of the client, as was the case in the project we are going to talk about), changing the application.
• Testing. This stage is normally divided into two parts – functional and load testing.
• Cutover. The final step of switching from the initial to a new database.

Now, let’s move on to our project and look in detail at how we organized the Oracle to PostgreSQL migration.

Migration in Deep Water: the Details

In order to make our story more consistent, and not to confuse you in the cobweb of this intricate project, we’ll begin with some introductory information.

Our client, a major telecommunications company, offers a broad spectrum of services. Like any forward-thinking organization, they needed a modern database that meets the latest security standards, offers flexible data management, and crucially, cuts maintenance costs. Considering the project scale – 24 databases and 12 TB of data – the move to a more cost-effective solution is a prudent decision, with the potential for significant savings in the long run.

The client chose an open-source PostgreSQL database hosted in the cloud, setting strict project requirements:

• Migration of all databases should proceed in parallel with ongoing database development. Database updates are released every two months.
• Maximum allowed downtime – 12 hours.
• Testing must be conducted in collaboration with the client’s QA team.

The Ispirer team was clear on their mission: migrate 24 databases to PostgreSQL in the cloud, maintain the two-monthly release schedule, and adhere to the 12-hour downtime limit.

With all crucial details settled, Ispirer embarked on the most critical phase – planning the migration project. After all, planning is the cornerstone of migration, right?

Oracle to PostgreSQL: Planning is the Key

Migration planning should always be based on existing data about the source database. Of course, it is possible to analyze 24 databases with 12 TB of data manually, but is it necessary?

The answer is yes, but instead of doing it manually it should be automated in order not to turn gray at the information-gathering stage. Using a free Assessment Wizard, we gathered comprehensive information about the database’s functionality. The scope of the project included 24 databases, 24 similar database schemas and more than 12 TB of data in total. The data was spread across 24 databases with the largest database being 3 TB. It was essential to migrate 300 packages containing extensive business logic, totaling 500,000 lines of code. A Java application which interacted with the database also needed modification to work with PostgreSQL.

The main challenge was the customer’s strict requirements. The client continued constant development of their databases with new releases every 2 months. Continuous development on the databases posed a significant obstacle, as ongoing changes would render initial conversion efforts obsolete. It was necessary to find a solution that would help solve the problem of the database being out of date.

In addition, 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.

The client agreed on making a 2-month break in constant development to allow us to convert all the 24 databases, migrate 12 TB of data, fix all the errors and fully switch from Oracle to PostgreSQL within this period of time.

During the planning stage, the Ispirer team took all these project peculiarities, scope and other customer requirements into account. On this basis, a project plan to optimize the process and high-quality automatic migration had been developed. Here is a detailed project roadmap we ended up with:

Oracle to PostgreSQL: Migration Stage

Given the size, complexity and customer requirements, such a project could take years if done manually. In order to streamline the migration project, we automated the conversion process using Ispirer Toolkit.

Despite the fact that automation streamlines database migration when compared with manual modernization, Ispirer Toolkit further enhances migration speed with the help of customization. Here’s how the process works: we begin the migration with Ispirer Toolkit, identify major issues, add new conversion rules to the tool, restart the migration, and achieve a conversion with those major issues resolved. Customization of the tool for this project alone made it possible to reduce conversion time by 2 months.

The major part of the project scope was migrated with the help of Ispirer Toolkit automatically. However, it is almost impossible to automate the entire database migration. So next we got on to making manual corrections to the automatically generated results. Finally, we conducted initial testing to ensure the accuracy of the conversion. Using this approach, a single developer has achieved a conversion speed of 5,000 lines of code per day.

As a matter of fact, migration from Oracle to PostgreSQL included a number of difficulties that we also encountered. The main ones related to:

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

The optimal solution for migration turned out to be to split it – migrate one part of the SQL code to the new database, and move the other part to the application level. By collaborating closely with the client, we identified code segments that could be moved from the database to the application. Besides, the Ispirer team made changes to the application, adjusting connections, database API and embedded SQL.

The entire migration phase took 6 months, during which the client continued database development. Right before switching to a new database, the client agreed on making a 2-month break in development. Taking into account that production migration would take a month, Ispirer’s team had about a month to push all the changes into the final PostgreSQL database and fix all the errors to ensure the system operates properly. Testing, which we will discuss in more detail below, was carried out in parallel, as well as the data migration, but first things first.

Oracle to PostgreSQL: Testing Stage

As previously mentioned, the client releases a new version of the databases every two months. The optimal migration strategy involved starting the preparation of benches and test cases from the second month of the project, concurrently with the schema conversion. Additionally, experts began preparations for data migration in parallel with the schema conversion and testing processes.

Testing was carried out in collaboration with the client’s QA specialists who wrote test cases and performed the testing. It is important to note that high-quality testing of the database can only be carried out by specialists who are familiar with the system. This approach to testing is the most time- and cost-effective. Besides, it goes without saying that in-depth knowledge and testing experience of the customer’s QA team are the basic components for an optimal testing stage. The client handled the testing completely on their side, while the Ispirer team assisted with fixing errors and bugs, setting up test benches, and ensuring the accuracy, integrity and performance of the migrated databases.

Testing had to be carried out both at the database and at the application level. Due to the regular releases, it was necessary to add a testing step after a reconversion stage.

For testing, it is important to understand the dependency tree, which is a set of dependencies between objects, as this allows you to test objects in the desired order. Ispirer’s QA engineer helped the client’s testing team with collecting dependencies, which improved the quality of testing.

Oracle to PostgreSQL: Data migration

The Ispirer team considered 4 data migration scenarios that we usually apply to database migrations and opted for the most efficient one.

What was so special about this scenario? To ensure a high-quality migration of all data within a 12-hour downtime window, the Ispirer team conducted preliminary migrations. As mentioned earlier, 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.

Now let’s proceed to the tasks we managed to do during the downtime. During the production migration, Ispirer experts uploaded the latest changes for larger databases. The smaller databases were migrated with the standard lift-and-shift approach. The speed of 90 GB per hour was sufficient to migrate the rest of the data hassle-free.

Our dedicated efforts over the course of 8 months culminated in a seamless data transfer and cutover of all 24 databases within just 12 hours of downtime. This achievement underscores our team’s commitment and efficiency, ensuring minimal disruption to operations.

Conclusion

As a result of close cooperation with the client, the Ispirer team was able to complete the Oracle to PostgreSQL migration project in the shortest possible time. The project’s key outcomes were as follows:

• Efficient database migration. All databases were 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.
• Streamlined testing with dependency tree. The use of a dependency tree was instrumental in organizing and streamlining the testing phase. This approach ensured that all dependencies were identified and tested systematically, reducing potential issues.
• Business logic transfer. A portion of the business logic was transferred from the database to the application level. This not only simplified the database migration, but also improved the performance and maintainability of the application.
• Enhanced testing through collaboration with the client’s QA. 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.

Overall, the project was a success due to the combined efforts of our team and the client’s active involvement.

If you are also looking for an Oracle to PostgreSQL migration solution, we invite you to try Ispirer Toolkit free for 30 days. This trial will allow you to evaluate the product’s capabilities and see firsthand how it can facilitate a smooth and efficient database migration.