Lessons from Migrating Oracle Databases to Amazon Aurora PostgreSQL

Database modernization is no longer a question of if but when — and how. As organizations continue to pursue cloud-first strategies, the migration from traditional Oracle databases to Amazon Aurora PostgreSQL has emerged as a powerful step toward performance efficiency, scalability, and cost optimization.

In this comprehensive presentation, “Lessons from Migrating Oracle Databases to Amazon Aurora PostgreSQL,” Shailesh Rangani, Director and Global Practice Leader of PostgreSQL Services at Datavail, shares real-world insights and best practices gathered from years of successful enterprise database migration projects.

The session dives deep into the business and technical drivers behind moving off commercial Oracle databases, providing a roadmap that blends strategic foresight with hands-on guidance. Whether you’re a database administrator, cloud architect, or IT decision-maker, this session will help you understand what it truly takes to transition from Oracle to Aurora PostgreSQL with confidence.

What You’ll Learn:

The Business Case for Migration: Understand why enterprises are modernizing from Oracle to Amazon Aurora PostgreSQL to reduce licensing costs, enhance flexibility, and achieve cloud-native scalability.

Common Migration Paths: Explore proven strategies and patterns for database modernization — from replatforming to rearchitecting — based on real client scenarios.

An Introduction to Amazon Aurora PostgreSQL: Learn how Aurora combines the performance and availability of enterprise databases with the simplicity and cost-effectiveness of open-source PostgreSQL.

Best Practices for Oracle-to-Postgres Migration: Gain practical insights into schema conversion, data migration, performance tuning, and compatibility assessment.

Production Rollout Success Stories: See how Datavail’s customers have completed complex migrations, avoided pitfalls, and accelerated time-to-value.

This presentation bridges the gap between theory and practice — offering a step-by-step look at how organizations can migrate mission-critical workloads without compromising performance, reliability, or business continuity.

Whether your goal is cost savings, vendor independence, or cloud optimization, this session equips you with actionable knowledge to make your database modernization journey a success.

Presented by:
Shailesh Rangani
Director & Global Practice Leader – PostgreSQL Services, Datavail

Watch, learn, and lead your database modernization initiative with confidence. https://www.datavail.com/resources/lessons-migrating-oracle-databases-amazon-aurora-postgresql/

1. Lessons from
Migrating Oracle
Databases to
Amazon Aurora
PostgreSQL

2. www.datavail.com 2
Shailesh Rangani is Practice Lead for PostgreSQL
with 18+ years’ experience in database domain.
He holds various certifications on cloud
platforms like AWS, Azure and OCI along with
database platforms like PostgreSQL, MongoDB,
Oracle and DB2 LUW.
He is an expert in the design, deployment,
administration, and management of data-
intensive applications that enable organizations
to effectively analyze and process large volumes
of structured and unstructured data.
Shailesh specializes in Cloud platforms and
DBMS technologies. He has successfully
delivered the data architecture strategy for
projects and large-scale platforms.
www.datavail.com 2
S
Shailesh
Rangani
Director & Global Practice
Lead -PostgreSQL,
Datavail

3. www.datavail.com 3
Datavail at
a Glance
Delivering a superior
approach to leveraging
data through the
application of a tech-
enabled global delivery
model & deep
specialization in databases,
data management, and
application services.
$25
M
Invested
in IP that improves the
service experience and
drives efficiency
13
+
Years
building and operating
mission critical data and
application systems
1,000
+
Employees
staffed 24x7, resolving over
2,000,000 incidents per year
2022
www.datavail.com 3

4. www.datavail.com 4
Agenda
Why break free?
The business case for moving to AWS databases
Common migration paths to modernization
Introduction to Amazon Aurora
Database migration
Best practices

5. Flexible Ways to
Break Free

6. www.datavail.com 6
A Typical Database Migration Lifecycle
Data
migration
Application code
Schema
objects
Cutover
Analysis
Testing

7. www.datavail.com 7
We Offer Flexible Ways to Help You Migrate
AWS Database
Freedom Program
AWS ProServeand
Migration Partners
AWS Migration
Tools

8. www.datavail.com 8
Flexible, Powerful Migration Tooling
Most sources and targets, higher conversion automation
Source DB or DW
AWS SCT
Destination DB or DW
Source DB or DW Destination DB or DWAWS DMS
Copy or convert
Data
schema
Step 2: Move your data
Step 1: Convert or copy your schema

9. www.datavail.com 9
Case Study 1 –Midsized Service Firm
Single monolithic DB
Lot of historical data
Oracle licensing cost
Encryption (TDE)
Scalability
Credential rotation
Quick testing environment
Native utilities for static data
migration
AWS Database Migration
Service (DMS) & AWS
Schema Conversion Tool
Databases running Aurora
PostgreSQL
Encryption via KMS
Auto Scaling (CPU based)
Native partitions
Database clone for quick test
environment
Challenges Accelerated Migration Outcome
Accelerated Amazon
Aurora Migration
AWS Schema
Conversion Tool
AWS Database
Migration Service
Oracle
instance
Aurora
PostgreSQL

10. www.datavail.com 10
Case Study 2 –Midsized Financial Company
Oracle licensing cost
Multiple Sources
Heavy read intensive
workload over weekend
150,000 lines of code to
convert
3,000 + objects to convert
AWS Database Migration
Service (DMS) & AWS
Schema Conversion Tool
Expert PostgreSQL
developers/AWS SA
Code conversion 2 months
Regressing Testing
Databases running Aurora
PostgreSQL
Faster reports
Lower licensing costs
IAM authentication at DB level
DB code moved to app layer
Auto Scaling (schedule based)
Challenges Accelerated Migration Outcome
Accelerated Amazon
Aurora Migration
AWS Schema
Conversion Tool
AWS Database
Migration Service
Oracle
instance
Aurora
PostgreSQL

11. Introduction to
Amazon Aurora

12. www.datavail.com 12
Benefits of Amazon Aurora
Amazon Aurora
Speed andavailability of high-end commercial databases
Simplicityand cost-effectivenessof open-source databases
Drop-in compatibilitywith MySQL and PostgreSQL
Simple payasyougopricing
Amazon Aurora-enterprise database at open-source price, delivered as a
managed service.

13. www.datavail.com 13
Purpose-built log-
structured distributed
storage
Storage volume is
striped across hundreds
of storage nodes
Storage nodes with
locally attached SSDs
Continuous backup to
Amazon S3
Scale-out, Distributed, Multi-tenant Storage
Architecture
AZ 1 AZ 2 AZ 3
SHARED CLUSTER STORAGE VOLUME
Writer
Transactions
Caching
SQL
Cluster Endpoint
Amazon S3
Reader
Transactions
Caching
SQL
Reader
Transactions
Caching
SQL
Reader Endpoint

14. www.datavail.com 14
Leverages the AWS Cloud Ecosystem
AWS Lambda Amazon S3 AWS IAM
Amazon CloudWatch
Invoke AWS Lambda events
from stored
procedures/triggers
Load from, save to Amazon S3,
store snapshots and backups in
S3
Use AWS Identity & Access
Management (IAM) roles to
manage database access control
Upload systems metrics and audit logs to Amazon
CloudWatch
Amazon SagemakerAmazon Comprehend
Make inferences directly from your database
using SQL calls

15. www.datavail.com 15
Automates Administrative Tasks
Schema design
Query construction
Query optimization
Automatic fail-over
Backup & recovery
Isolation & security
Industry compliance
Push-button scaling
Automated patching
Advanced monitoring
Routine maintenance
Takes care of your time-
consuming database
management tasks, freeing you
to focus on your applications
and business
YOU
AWS

16. www.datavail.com 16
Who is Adopting Amazon Aurora?
Higher performance
Better availability and
durability
Easy migration; no
application change
One-tenth of the cost;
no licenses
Comparable
performance and
availability
Migration tooling and
services
Integration with
other
AWS services
Cloud-native
capabilities and
access mechanisms
Scalability,
availability, managed
service
Customers migrating from
open-source PostgreSQL
Customers using
commercial DB engines
Customers building new
applications

17. Schema Conversion

18. www.datavail.com 18
AWS Schema Conversion Tool
Tab with the assessment reportManual conversion tips
Side by side code view
Assessment report
Project interface
Code browser
Automates many conversion tasks
Packages
Stored procedures
Functions
Triggers
User defined types
Schemas
Tables
IndexesViews
Sequences
Synonyms

19. www.datavail.com 19
AWS Schema Conversion Tool Tips
Save as an SQL file
•Allows you to apply
only Table DDL &
PK
•Save the secondary
DDL for after
migration
Memory management
•Global settings
•JVM settings

20. www.datavail.com 20
SCT Migration Assessment Report
Connect SCT to
“source” & “target”
Run “assessment
report”
Read “executive
summary” & follow
detailed
instructions
More: https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_AssessmentReport.Create.html

21. www.datavail.com 21
SQL Scripts
Packages
Stored procedures
Functions
Triggers
User defined types
Schemas
Tables
Indexes
Views
Sequences
Synonyms
SCT does a great job of converting
your schema and code objects
Users, roles, grants
https://aws.amazon.com/blogs/database/use-sql-to-map-users-roles-and-grants-from-oracle-to-postgresql/

22. www.datavail.com 22
Oracle to
Amazon
Aurora
PostgreSQL
Migration playbook –
example
More:
https://d1.awsstatic.com/whitepapers/Mi
gration/oracle-database-amazon-aurora-
postgresql-migration-playbook.pdf

23. Data Migration

24. www.datavail.com 24
Move Data Using Data Migration Service
Oracle Databases AWS Database
Migration Service
Amazon Aurora
AWS DMS Replication
Instances
AWS DMS
Replication Tasks
Read Write

25. www.datavail.com 25
Support for the Following Conversions
Source* DatabaseTarget* Database on AWS
Oracle databaseAmazon Aurora, MySQL, PostgreSQL, Oracle
Oracle data warehouseAmazon Redshift
Azure SQLAmazon Aurora, MySQL, PostgreSQL
Microsoft SQL ServerAmazon Aurora, Amazon Redshift, MySQL PostgreSQL
TeradataAmazon Redshift
IBM NetezzaAmazon Redshift
GreenplumAmazon Redshift
HPE VerticaAmazon Redshift
MySQL and Maria DBPostgreSQL
PostgreSQLAmazon Aurora, MySQL
Amazon AuroraPostgreSQL
IBM DB2 LUWAmazon Aurora, MySQL, PostgreSQL
Apache CassandraAmazon DynamoDB
Source: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.html Target: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.html

26. www.datavail.com 26
AWS Database Migration Service
Working with DMS components
Replication Instance
C-compute R -memory T-burstable
Endpoint
Connect source / target from replication instance
Choose advanced override settings
Task
Full Load
Change Data Capture

27. www.datavail.com 27
AWS Database Migration Service
Best practices for choosing a replication instance
How to choose between different instance types.
Things to know about EBS storage on the replication
instance
Important Amazon CloudWatchmetrics to watch on
the replication instance

28. www.datavail.com 28
Choosing a “migration type”
•Full Load
•Change Data Capture (CDC)
Choosing a target table prep mode
•DMS can create tables, however, better
pre-create table with SCT
•Flexibility to drop or truncate tables
Include LOB columns in replication
•Why limited LOB (Large Object) mode
is a better choice?
Selection & transformation rules,
logging, exceptions, and others
•Flexible in selection but limited
transformations
•How to work around transformations
AWS Database Migration Service
Components of a DMS task

29. www.datavail.com 29
Task –Migration Type
Choose migration
type
Existing data
Existing data and
replicate changes
Replicate changes
only
Creates files or tables in the target database
Populates the tables with data from the source
Migrate existing dataoption in the AWS console and Full Loadin the API
Captures changes on the source during migration
Once initial migration completes, changes are applied to the target as units of
completed transactions
Migrate existing data and replicate ongoing changesoption in the AWS console and
full-load-and-cdcin the API.
Reads the recovery file on the source database
Groups together transactions and applies them to the target. Buffering as needed
Replicate data changes onlyoption in the AWS console

30. www.datavail.com 30
Task –Target Preparation
Target preparation
Do nothing
Drop tables on
target
Truncate
In Do nothing mode, AWS DMS assumes target tables are pre-created.
In full load or full load plus CDC, ensure that the target tables are empty before starting
the migration.
InDrop tables on targetmode, AWS DMS drops the target tables and recreates them
before starting the migration. This ensures that the target tables are empty when the
migration starts.
InTruncatemode, AWS DMS truncates all target tables before the migration starts.

31. www.datavail.com 31
Task –Include LOBs
Include LOBs
Don't include LOBs
Full LOB
mode
Limited LOB
mode*
LOB columns are excluded from the migration.
Migrate complete LOBs regardless of size. AWS DMS migrates LOBs piecewise in chunks
controlled by theMax LOB sizeparameter. This mode is slower than using Limited LOB
mode.
Truncate LOBs to the value of theMax LOB sizeparameter. This mode is faster than
using Full LOB mode.

32. www.datavail.com 32
Task –Selection and Transformation Rules

33. www.datavail.com 33
AWS Database Migration Service
AWS Cloud
VPC
Customer On-premises
VPN
Oracle Aurora PostgreSQL
DMS Replication
Instance
Application users
End Point Connection
Full Load
Change Data
Capture
•Start a replication instance
•Connect the source and target endpoints
•DMS FULL LOAD the data from Source to Target
•DMS Change Data Capture to replicate ongoing changes
•At steady state –take an outage, validate & redirect
connection

34. Best Practices for
Production Rollout

35. www.datavail.com 35
Understand Basic Database Engine
Differences
PostgreSQL is a lowercase data
dictionary
Use “exception handlers” when
needed, not by default
PostgreSQL has six different index
types.
Store your BLOBs in Amazon S3
instead of the database
search_path replaces
PUBLIC SYNONYM
PostgreSQL
has 64 datatypes

36. Overview of
Migration Steps

37. www.datavail.com 37
Start Full Load
Source TargetReplication
Instance

38. www.datavail.com 38
While Loading Data Also Capture Changes
Source TargetReplication
Instance
App

39. www.datavail.com 39
Load Complete –Apply Captured Changes
Source TargetReplication
Instance
App

40. www.datavail.com 40
Changes Reach Steady State
Source TargetReplication
Instance
App

41. www.datavail.com 41
Shutdown Apps and Apply Remaining Changes
Source TargetReplication
Instance
App

42. www.datavail.com 42
Change Your Application Endpoint
Source TargetReplication
Instance
App

44. Complete
Session
Evaluation
For a Chance
to WIN
Virtual Reality Headset
w/Controller &
Headphones

45. www.datavail.com 45
Thank You
Shailesh Rangani
Director & Global Practice Lead : PostgreSQL Services
[email protected]
[phone]