Case RedeDor

Migration from an Oracle RDS to an Aurora PostgreSQL RDS.

Objective

This document describes the migration performed from an Oracle RDS to an Aurora PostgreSQL RDS using the DMS and SCT tools to convert and synchronize data between databases causing less downtime for the application.

Environment

The infrastructure below was used to carry out all stages of the migration process:

  • RDS Oracle 12c in Enterprise Edition Edition
  • DMS (Data Migration Service) with CDC (Change Data Capture) enabled
  • SCT (Schema Conversion Tool)
  • RDS Aurora PostgreSQL 12

Customer need

The client RedeDor came to us to study the replacement of the Oracle database for PostgreSQL because RDS Oracle has a very high cost.

Initially, the client’s project team considered the use of RDS PostgreSQL due to meeting the technical needs and budget, but due to the evolution of the application and the increase in the database there was a considerable increase in writings and readings in Oracle and this software did not meet the performance and budget needs, we presented the Aurora PostgreSQL solution because our experience and skills showed us that due to the excellent I/O throughput, high performance and native scalability of the product, it would meet RedeDor’s need. After several meetings on Aurora PostgreSQL, it was decided to use it.

We had great support from AWS through Claudio Medeiros and Cassiano Tesseroli in one of the meetings on Aurora PostgreSQL.

Starting the project in approval

After defining the use of Aurora PostgreSQL, the process of total duplication of the environment (EC2 machines, Test Robots and RDS’s) was started to fully simulate the production environment in the customer’s development account.

Types of instances used (EC2, Robots and RDS’s):

  • EC2: 8 c5.2xlarge machines
  • Robots: 2 c5.4xlarge machines
  • RDS: Oracle (db.r5.8xlarge) and Aurora Postgres (db.r5.16xlarge)

Note. All production instance classes were used in the development account for reliable performance and business testing.

After duplicating the production environment for development, Aurora PostgreSQL was created using the db.r5.16xlarge class without using read replica.

With the RDS Oracle duplicated and the Aurora PostgreSQL instance created, the DMS configuration process was started.

The application provider sent a DDL script with all the PostgreSQL tables and objects and the complete structure was uploaded to Aurora PostgreSQL.

After creating the objects, the sequences generated by Oracle and converted to Aurora PostgreSQL were extracted.

The sequences were extracted using the SCT tool (Schema Conversion Tool) generating a file with all the sequences and their numerical positioning. After generating the file, it was necessary to convert the limits of the sequences to Aurora Postgres (Oracle has a higher limit for sequential generation of numbers).

In addition to the conversion, adjustments were made to the sequence creation commands to be supported by Aurora Postgres.

After making the adjustments, the final file was successfully imported into Aurora Postgres, thus adjusting the sequence between Oracle and Aurora Postgres.

The structure in the schema in Aurora PostgreSQL was ready, the process of configuring the DMS was started.

Before configuring the DMS, several points were raised about the migration process with the application supplier. They were:

  • Mapping of tables that would be converted;
  • DMS machine size definition;
  • Use of online or offline replication;
  • Monitoring and logging the replication process;

During  several meetings with the client and the supplier, the following points were defined:

  1. Mapping of tables that would be converted – All tables would be replicated;
  2. DMS machine size definition – Initially the machine used for the DMS in the configuration phase was used dms.r5.2xlarge. For the migration tests along with the performance robots, it was updated to dms.r5.8xlarge;
  3. Use of online or offline replication – The type of replication chosen was online due to the fact that we had 20 hours of conversion between Oracle and Aurora PostgreSQL. There would be no window available for the computed tim
  4. Replication process monitoring and logs – In the DMS configuration process, CloudWatch was chosen to monitor the entire conversion and CDC process.

After defining the above items, the DMS configuration process was started according to the evidence below:

The configuration, we started the first synchronization between RDS Oracle and Aurora PostgreSQL. The same was successfully performed as evidenced below:

The delivery of the replication performed by the DMS, the client started the validation process of the application configured for Aurora PostgreSQL.

The tests performed were:

  • Scheduling of exams, alteration and deletion;
  • Issuance of management reports;
  • Administrative routines of the sectors involved;
  • Performance validation at critical points of application use;

It was necessary to make adjustments to the application for proper use in Aurora PostgreSQL, generating test and re-tested builds, these adjustments were made by the client in the application.

Preparing the environment for production deployment

Finishing all the functional and performance tests, the simulations of RDS Oracle implementation for Aurora PostgreSQL with CDC (Change Data Capture) started.

During the process, adjustments were made to RDS Oracle and Aurora PostgreSQL to implement the use of CDC and after all the configuration performed, several tests were carried out to validate its efficiency.

DML tests (insertion/update/removal) were performed and all operations performed were validated in Aurora PostgreSQL.

The build of the new version of the application was generated by the client for full compatibility with Aurora PostgreSQL.

Adjustments were made to RDS Oracle in production, Aurora PostgreSQL was created with the same configuration as the development environment and the DMS was created with the same configuration as the development environment.

Implementation in production

After the adjustments described above, the date for activating the DMS and monitoring the synchronization was programmed.

A weekend window was chosen for the entire migration to be carried out and starting the CDC on the weekend itself.

A war room was mobilized where everyone involved would be following the application and synchronization.

After 3 days of monitoring, the final switch was made to Aurora PostgreSQL where the application was stopped, and the CDC was monitored to perform the last transaction recorded in RDS Oracle, it was stopped to prevent any type of access.

The latest version made available by the client was built and the pointing configuration for Aurora PostgreSQL was changed.

After 7 days of using Aurora PostgreSQL, RDS Oracle and DMS were removed from the production environment.

At the request of the customer, after several days of using Aurora PostgreSQL, the read copy was activated for the consumption of reports, thus reducing the read load on the database.

Conclusion and successful outcome

At the customer’s request, a study was carried out on market tools capable of performing data conversion between Oracle and Aurora PostgreSQL and keeping the synchronization active until the switch to Aurora PostgreSQL.

It was identified that the best solution would be to use the DMS together with the SCT to perform the conversion and synchronization of the databases using native AWS tools.

After 3 months of a lot of planning, meetings to define environments and tools, daily status of migration activities, functional and performance tests (use of robots) and production turnaround tests, the conversion to Aurora PostgreSQL was carried out without errors and performance problems (on the operational side of the client running Oracle) and the synchronization of data in real time.

The downtime for the switch to Aurora PostgreSQL was only 45 minutes, where 30 minutes were spent to apply the deploy of the new version and 15 minutes to wait for the last application of the Oracle redo log, the conversion and sending of the data to the DMS and writing that data to Aurora PostgreSQL.

As a result, the customer was able to save around R$1,500,000.00 in Oracle licensing in the Enterprise edition. With this difference, it was able to improve the hardware resources for Aurora PostgreSQL and enable a read replica for report consumption. Reports were running on the primary Oracle instance causing times of high CPU and I/O usage.

Below is the Oracle and Aurora PostgreSQL cost estimate for the project

Despite the cost of Aurora PostgreSQL being higher than RDS Oracle (monthly), there is no licensing cost for PostgreSQL as it is an open source database. In the monthly cost of RDS Oracle, the licensing cost is not included, as the customer absorbed it internally by paying for the licensing.

With the annual licensing savings, the customer was able to improve the application and database infrastructure (Aurora Postgres currently has a Read Replica instance for reporting that it previously could not have on Oracle for the cost of yet another RDS).