Blog: Using PostgreSQL in cloud alongside with Oracle to enhance the database system performance

One of the leading independent software vendors in educational domain needed to expand their original Oracle database system. They wanted to add a new PostgreSQL database running on AWS RDS to their original system and upgrade the connected Java application to support both database platforms. We came up with a proof of concept and converted one of the largest customer’s database schemas to PostgreSQL, demonstrating how the upgraded system might work. To do so, we updated the application by adding the unified SQL code and changing its architecture to support 2 database platforms. As a result, the newly created system could meet the customer’s performance requirements.

Watch the following video to learn more about the technical approach that we’ve used for code unification.

Typical Code Unification Workflow

Usually, adding a new database platform to the existing system requires performing the following steps:

• converting the source database to the target environment (schemas, code);
• migrating the latest data from the source database to the target database;
• remediating the connected application to use the unified code.

Actually, you need to perform the code unification not only for the built-in application SQL code fragments but for the database code as well. Unifying the source database code with the universal SQL standards, for example, the ANSI SQL will lead to a tremendous reduction of effort required for database conversion.

In the end, the new architecture of the customer’s system looks as follows:

Let’s examine the key steps of this unification project.

Database Migration

We leveraged the AWS Schema Conversion Tool (SCT) to automate the conversion of a database schema. At this step, we discovered that the converted schema has a lot of dependent objects that we need to convert, too. Because of the tight deadlines, we defined a reasonable scope of converted objects on the proof of concept stage to proceed. Fortunately, SCT converted approximately 90% of the source database code, saving us from a lot of mundane work. Finally, we needed to convert just around 10 percent of the source code manually to finalize the migration.

Then we migrated the data from the source database to the target. To do so, we created ETL scripts to grab the initial data from Oracle database and upload it to PostgreSQL database. Later we used the same scripts running against the PostgreSQL in AWS RDS to upload the data changes and keep the databases synchronized. So, we could run these 2 databases simultaneously and connect to them with various versions of the customer’s application.

Application Remediation

We approached the application upgrade in 2 sure steps:

• extracted SQL code from the customer’s Java application;
• updated the application architecture to make it compatible with both Oracle and PostgreSQL databases.

We tried using AWS Schema Conversion Tool to extract the SQL code from the original application. Because it was built on Grails and Hibernate frameworks, the extracted code by SCT didn’t satisfy us. It included a lot of statements that we initially commented, and that really made the conversion process more complicated. So, we created a re-usable tool to extract the SQL code from this Java application. Then we analyzed the extracted code and classified it into 4 categories:

• doesn’t require modification and can be used in PostgreSQL without conversion;
• requires unification to be used both with Oracle and PostgreSQL afterwards;
• can be automatically converted using AWS Schema Conversion Tool;
• requires manual conversion and should be duplicated in the application.

Luckily enough, less than 5% of the extracted source code required manual conversion.

After performing the unification and conversion of the source code, we needed to upgrade the application itself. We changed the structure of the application and created an additional layer that stood between the application and the database. This layer included the platform-specific code. When the customer selects the desired platform on the compilation stage, we add this platform-specific file to the original code and build the application compatible either with Oracle or PostgreSQL.

Customer’s Benefits

As a result, we built an ultimate application compatible both with Oracle and PostgreSQL platforms. Also, we kept the databases on these two platforms synchronized. In the end the customer received the following benefits:

• Meeting the latest industry security standards with the new cloud database platform;
• Ability to opt for compatibility either with Oracle or PostgreSQL while building an application;
• Received the documentation including a set of best practices for developing the unified source code of the Java application to ensure compatibility with both database platforms.

Planning our next steps with our customer, we are going to convert the rest of the database schemas to ensure the full-fledged system operation and address the security policies. The thing is that open-source PostgreSQL database doesn’t provide the built-in security features relevant to the original Oracle’s features, so we will need to implement them manually. This is a very important step for the application certification.

Streamline your AWS Migration with our Jumpstart offering. Feel free to contact DB Best to fulfill your application or data management project.