Slash Costs and Boost Performance: Master Your Oracle to Aurora PostgreSQL Migration Journey

Here at ClearScale, we’ve seen a growing number of enterprises that want to migrate to open-source databases such as PostgreSQL from commercial database engines like Oracle to save on license costs and take advantage of Amazon Aurora and cloud-native capabilities.

Challenges with Oracle Database

There are many reasons companies are looking to make the Oracle to PostgreSQL database migration including:

• High licensing costs: Oracle’s licensing costs can be very high, especially for larger databases.
• Vendor lock-in: Oracle’s proprietary technology can result in vendor lock-in, making it difficult to switch to other platforms.
• Complexity: Oracle can be complex to manage and administer, requiring specialized skills and knowledge.
• Limited scalability: Oracle can be limited in terms of scalability, requiring expensive hardware upgrades to accommodate growing data volumes.
• Vendor support: Oracle’s vendor support can be expensive and require additional contracts.
• Performance: Oracle’s performance can be affected by its complexity and resource-intensive nature.

Why Amazon Aurora

There are also many reasons why Aurora PostgreSQL may be recommended over Oracle, depending on the specific use case and requirements. Here are some of the main reasons:

Cost

Aurora PostgreSQL is generally less expensive than Oracle, both in terms of licensing costs and operational costs. With Aurora PostgreSQL, you only pay for the resources that you use, whereas with Oracle, you typically have to pay for a certain number of licenses upfront, regardless of how much you actually use. Additionally, there is an infrastructure cost associated with Oracle.

Scalability

Aurora PostgreSQL is designed to be highly scalable and can easily handle large workloads. It can automatically scale up or down based on demand, so you don’t have to worry about provisioning additional resources manually. Oracle can also scale, but it typically requires more manual effort and expertise.

Ease of use

Aurora PostgreSQL is generally considered easier to use than Oracle, especially for developers who are familiar with open-source databases like PostgreSQL. The interface and tools are often more intuitive and user-friendly, and there is a large community of developers who can provide support and guidance.

Performance

Aurora PostgreSQL is designed to be high-performance, with fast read and write speeds and low latency. It also supports multi-master replication, which can improve availability and reduce the risk of downtime.

Oracle also performs well, but it may require more fine-tuning to achieve optimal performance.

Aurora by default sets 75% of the server RAM for engine buffer and thus the read operations occur from the buffer only. Additionally, Aurora being a log-based storage, only stores the logs in the disk. The Aurora Storage Daemon is smart enough to understand where the data is stored in the storage layer and retrieve it faster. Aurora PostgreSQL stores tables and indexes in the Aurora storage subsystem. Aurora PostgreSQL uses separate temporary storage for non-persistent temporary files. This includes files that are used for such purposes as sorting large data sets during query processing or for index build operations.

The cluster cache management (CCM) feature improves the performance of the new primary/writer instance after failover occurs. The replica preemptively reads frequently accessed buffers cached from the primary/writer instance. With CCM, you can designate a specific Aurora PostgreSQL replica as the failover target. CCM ensures that data in the designated replica’s cache is synchronized with the data in the primary DB instance’s cache.

Aurora Natively Includes High Availability Features That Apply to the Data in Your DB Cluster

These high-availability features include:

Aurora is Fault Tolerant by Design (In-Region HA/DR)

• Multi-Availability Zone
• Cluster volume spans three Availability Zones in a single AWS Region
• Each Availability Zone contains two copies of the data
• Survivable Cache Warming
• Storage auto-repair, auto-scale

Automatic, Continuous, Incremental Backups and Point-in-Time Restore (In-Region HA/DR)

• Stored in S3 (11 9’s of Durability)
• Instant Crash Recovery – parallel, distributed, asynchronous, improving restore times
• Fast Database Cloning
• Snapshot Export to S3 in Apache Parquet

Autoscaling, low-latency Read Replicas (In-Region HA/DR)

• Scale-out read workload with up to 15 read replicas – fast local reads
• Supports autoscaling in response to changes in performance metrics
• Automatic promotion to primary in case of instance issues

Global Database (Cross-Region HA/DR)

• Global read scale-out – up to five global regions
• Ideal for sub-second data access globally and cross-region disaster recovery use cases
• Storage-based replication enables RPO of 1 second and RTO of <1 minute

Migration Path for Moving to Aurora PostgreSQL

Database migration is a complex, multiphase process, which usually includes assessment, database schema conversion (if you are changing engines), script conversion, data migration, functional testing, performance tuning, and many other steps. Tools like the AWS Database Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT), native engine tools, and others can help to automate some phases of the database migration process.

The key to success, though, is to make your database migration project predictable. You don’t want any surprises when you are 10 terabytes deep in the data migration phase.

Every successful database migration has three major steps:

1. Assessment
2. Schema Conversion
3. Migration

Assessment

Assessment is an important step before migration because it helps to ensure a smooth and successful transition to the new environment. It helps to gain a thorough understanding of the existing environment, including the hardware, software, and applications being used. The assessment also helps to identify and mitigate potential risks associated with the migration, such as data loss, system downtime, and security breaches.  This information is essential to ensure that the migration is carried out in a safe and secure manner.

It also helps to estimate the cost and time required for the migration process. This is essential to budget and plan for the migration effectively, and to avoid any unexpected expenses or delays.

AWS provides various evaluation tools like DB-CSI and DMS Fleet Advisor. These are free tools that automate migration planning and help you migrate Oracle databases to the cloud at scale with minimal effort. To accelerate migrations, these tools inventory and assesses your on-premises Oracle database server fleet and identify potential migration paths. Target recommendations can help to quickly identify the best migration option, based on estimated costs and limitations for each migration path.

Schema Conversion

Schema Conversion Tool (SCT) assesses schema compatibility of source databases, attempts to convert all schema and code objects, as well as extracts and migrates data warehouses to AWS Redshift. In an Oracle to Aurora PostgreSQL migration, AWS SCT generates a report that lists action codes, which indicates some manual conversion is required, or that a manual verification is recommended.

Migration

AWS DMS is a service for migrating an on-premises database, either in Amazon RDS or a database on Amazon EC2, to an Amazon RDS or Amazon Aurora database. AWS DMS can handle homogenous migrations, such as Oracle-to-Oracle, and also heterogeneous migrations, such as Oracle to MySQL or PostgreSQL in the AWS Cloud.

Setting up DMS is as simple as an administrator accessing AWS DMS from the AWS Management Console. The administrator then defines a database source from which data will be transferred to the database target for receiving the data.

Using AWS DMS, you can start replication tasks in minutes. AWS DMS monitors the data replication process, providing the administrator with performance data in real-time. If the service detects a network or host failure during replication in a multi-AZ instance setup, it automatically provisions a replacement host. During the database migration, AWS DMS keeps the source database operational. The source remains operational even if a transfer is interrupted, thereby minimizing application downtime.

Although DMS setup is relatively simple, before you begin, we recommend that you understand the working of two important DMS processes, the full load and change data capture (CDC) migration methods

During a full load migration, AWS DMS loads data from tables on the source data store to tables on the target data store. Any changes made to the tables being loaded are cached on the replication server during that process. If the CDC method is chosen, then after the full load is done, AWS DMS moves to the ongoing replication phase, applying changes as transactions.

Migrating from an Oracle database to an Amazon Aurora PostgreSQL database using AWS services involves several steps, which include:

Set up an Amazon Aurora PostgreSQL Cluster

Create an Amazon Aurora PostgreSQL cluster in the AWS Management Console. Create a DB instance, a DB cluster, and a parameter group that matches the Oracle database. Ensure that the security group settings are correct.

Set up AWS DMS

Create an AWS DMS instance and configure the source and target endpoints. For the source, choose the Oracle database, and for the target, choose the Aurora PostgreSQL database.

Data Analysis

Analyze the Oracle database to identify the database schema and its components. You can use tools like Oracle SQL Developer or Oracle Data Modeler to view the schema and its components.

Set up SCT

To automatically migrate DB schema, download and install the Schema Conversion Tool (SCT). Use SCT to convert the Oracle database schema to an Aurora PostgreSQL schema. Any objects that can’t be converted automatically will be marked in the SCT report and shall be converted manually.

Migrate the Schema

Use SCT to migrate the schema to Aurora PostgreSQL. SCT can generate the SQL scripts to create the tables, indexes, and constraints in Aurora PostgreSQL.

Migrate the Data

Use AWS DMS to migrate the data from Oracle to Aurora PostgreSQL. Configure the replication instance and create a task to migrate the data from the Oracle database to the Aurora PostgreSQL database.

Code Migration

Migrate database code like stored procedures, triggers, and functions from Oracle to Aurora PostgreSQL using the appropriate syntax for Aurora PostgreSQL. You may need to rewrite the code for Aurora PostgreSQL as it uses different syntax and functions compared to Oracle.

Testing and Validation

Test and validate the migrated database to ensure that the data is complete and accurate and that the application can function as expected. Use tools like psql and pgAdmin to run SQL queries against the Aurora PostgreSQL database and ensure that the data is correct.

AWS provides several services to help you migrate your database from Oracle to Aurora PostgreSQL, which can simplify the process and reduce the risk of errors. However, it is important to perform thorough testing and validation to ensure that the migration is successful. It is also important to ensure that you have a backup of the original data before performing any migration.

ClearScale Database Migration Services

As an AWS Premier Tier Services Partner with the Migration Competency, ClearScale has extensive database migration experience. You can read the case study of one of our Oracle to Aurora PostgreSQL migrations here.

Learn more in the Oracle to Aurora PostgreSQL Migration playbook and in the eBook.

Get in touch today to speak with a cloud expert and discuss how we can help:

Call us at 1-800-591-0442
Send us an email at sales@clearscale.com
Fill out a Contact Form
Read our Customer Case Studies