SaaS Platform Modernization and Database Migration to PostgreSQL on Amazon RDS

Executive Summary

Challenge: To modernize its Project and Portfolio Management (PPM) SaaS platform,  OpenText (formerly Micro Focus) made the call to shift its products from on-premises Oracle servers to PostgreSQL on Amazon RDS. They faced various hurdles due to inconsistencies within the database and the legacy architecture of their app. They also requested a reusable custom algorithm for forthcoming migrations.

Solution: The ABCloudz team successfully migrated the OpenText database from Oracle to Amazon RDS cloud. The ABCloudz team leveraged DMS to move most of the customer’s data to PostgreSQL on Amazon RDS. We innovated the process by creating a unique data migration script that operated seamlessly with AWS DMS, enabling the smooth transfer of altered data to the target database.

Benefits: The delivered solution reduced system, licensing, and database maintenance expenses by over 20%. The many advantages of our database migration solution allowed the customer to meet their business needs and transfer their data assets from Oracle and MS SQL to PostgreSQL in a brief time frame.

Customer Challenge

To keep their database architecture up-to-date and manageable, OpenText wanted to migrate application data from on-premises Oracle servers to PostgreSQL on Amazon RDS. The primary challenge stemmed from the significant inconsistencies between on-premises Oracle and cloud-based PostgreSQL. In addition, AWS DMS presented additional complexities as the customer’s data was stored in on-premises data centers. Using a cloud-based technology like DMS would require time-consuming data transfer from the on-premises data centers to the cloud and back. Ultimately, the customer required a reusable solution with customized data validation scripts. Without it, they would need to spend unacceptably long times and too many resources for migration.

ABCloudz Solution

The ABCloudz DB team automated the conversion of the customer’s Oracle database to PostgreSQL using AWS SCT. We employed custom tools to identify around 200–300 conversion issues that the SCT could not accurately convert from Oracle. For a successful migration, it was crucial to fix the identified problems manually before applying the target database to the PostgreSQL engine. Next, the customer’s team converted the application code to be compatible with PostgreSQL. Once the database schema and application code were converted, we conducted integration and regression testing on the complete application migration.

After converting the database, we migrated all of OpenText’s data from Oracle to PostgreSQL on Amazon RDS. Although most of the data was moved using AWS DMS, it was unsuitable for some of our clients as their data was located in on-premises data centers.

To address the inconsistencies between an on-premises source database and a cloud-based target database, we created a custom migration process that converts data from Oracle to PostgreSQL through transitional CSV files. This includes a custom script that generates and launches several SQL scripts. The first script extracts all data from Oracle tables and transforms it into CSV files, and the second script transfers data from CSV files and fills in the tables in PostgreSQL.

Once the database was converted and the data was migrated, we developed a custom program to validate the data and objects converted from Oracle to PostgreSQL. The algorithm consists of views that compare the structure of tables in the source Oracle database and the target PostgreSQL database. It meticulously analyzes the match of all columns and datatypes in the tables. The second part of the algorithm includes a custom Python script that reads the SQL hashing of each item and each column in Oracle and PostgreSQL databases. These hashes are stored in a single database that compares them and detects hash inconsistencies.

Results and Benefits

OpenText achieved the following results:

• Oracle database schema and application code were converted to PostgreSQL, using AWS SCT for most of the conversion and manually converting the components that SCT couldn’t handle.
• To address the limitations of AWS DMS, we built a custom solution on top of DMS, allowing the data infrastructure to be accurately and efficiently migrated from Oracle to PostgreSQL on AWS RDS.
• OpenText achieved a 20% reduction in database management and maintenance costs by migrating to a more cost-effective database.
• We built custom tools to homogenize future Oracle to PostgreSQL migrations and custom tools for data and object validation.

About customer

OpenText™ (formerly Micro Focus)  is a multinational information technology corporation and an Independent Software Vendor (ISV) focused on smarter information management. To enhance their cost-efficiency, they elected to modernize one of their products, a PPM SaaS platform. OpenText migrated application data from on-premises Oracle data centers to the “walled garden of AWS cloud servers.” In addition to reducing costs, this would enable the customer to embrace greater flexibility, security, and numerous AWS-managed services.