Seamless Oracle RDS to PostgreSQL RDS Migration on AWS: A CTO’s Guide Using DMS and Terraform

In this blog, You will get a practical approach of automation in database migration. For example we have used Oracle RDS as source database and PostgreSQL RDS as target in AWS, We will be leveraging AWS Database Migration Service (DMS) and Terraform for automation. This blog is designed for anyone interested in modernizing their data infrastructure—no jargon, just clear steps and essential insights.

Architecture at a Glance

Component	Role
Oracle RDS	Source database, holding your current data
PostgreSQL RDS	Target database, where data will be migrated
AWS DMS	Handles schema and data migration
EC2 Jump Host	Secure, controlled access point for database administration
Security Groups	Control network access and security

All resources are deployed within your existing AWS VPC for compliance and security.

Tools and Services

Terraform: Automates infrastructure provisioning of all AWS resources for consistency and repeatability.
AWS DMS: Migrates schema and data, with support for ongoing replication.
AWS SCT (Schema Conversion Tool): Converts Oracle schema to PostgreSQL syntax.
Amazon EC2: Jump host for secure database connectivity.
AWS Security Groups: Restrict access to only what’s necessary.

Folder Structure: Why Modularize?

Organizing Terraform code into modules (e.g., rds_oracle, rds_postgresql, dms, jump_host, security_groups) promotes reuse, easier maintenance, and clearer separation of responsibilities.

├── main.tf

├── variables.tf

├── terraform.tfvars

├── README.md

├── modules/

│ ├── rds_oracle/

│ ├── rds_postgresql/

│ ├── dms/

│ ├── jump_host/

│ └── security_groups/

This modular approach keeps your code organized and reusable.

Prerequisites & Planning

AWS CLI & Terraform: Ensure both are installed and configured.
VPC & Subnets: Confirm you have the right network setup.
Database Sizing: Estimate storage and performance needs for both source and target.
Schema Assessment: Use AWS SCT to generate an assessment report, identifying objects that require manual conversion or are incompatible.
Version Compatibility: Confirm supported Oracle and PostgreSQL versions for DMS.

Step-by-Step Migration Process

1. Assessment and Planning

Use AWS SCT to analyze the Oracle schema.
Identify objects needing manual conversion (complex procedures, triggers, proprietary features).
Plan for any necessary application code changes.

2. Schema Conversion

Convert schema with AWS SCT and review for manual adjustments.
Apply the converted schema to PostgreSQL RDS.

3. Infrastructure Provisioning

Edit terraform.tfvars with your environment details.
Initialize Terraform:

bash

terraform init

Review and apply the infrastructure plan:

bash

terraform plan -var-file=”terraform.tfvars”

terraform apply -var-file=”terraform.tfvars”

4. Data Migration with AWS DMS

Configure DMS endpoints for Oracle and PostgreSQL.
Start a full-load migration and enable change data capture (CDC) for ongoing changes.
Monitor migration tasks for errors and performance.

5. Validation and Testing

Connect to both databases via the jump host.
Compare row counts and schema between Oracle and PostgreSQL.
Run application-level queries to ensure data integrity and performance.

6. Cutover

Schedule a brief downtime window.
Stop writes to Oracle, let DMS synchronize final changes.
Point applications to PostgreSQL RDS.

7. Cleanup

Use terraform destroy -var-file=”terraform.tfvars” to remove temporary resources.

Best Practices and Insights

Data Type Mapping: Review type conversions carefully to avoid data loss or performance issues.
Manual Review: Some schema elements require manual intervention—plan for this early.
Testing: Validate thoroughly before switching over production workloads.
Monitoring: Use AWS CloudWatch and DMS logs throughout the process.
Security: Apply least-privilege principles with security groups and IAM roles.

Final Thoughts

Migrating from Oracle RDS to PostgreSQL RDS using AWS DMS and Terraform is a proven approach for organizations seeking cost savings, flexibility, and cloud-native capabilities. By following these steps and best practices, you can achieve a smooth, automated, and secure migration, unlocking the full potential of your data in the cloud.

Ready to modernize? Contact us and start your migration journey today!