Migrating and Modernizing Oracle Databases to PostgreSQL on AWS – Part 3: Planning and Execution

Introduction

In this blog post, we outline the target architectures and wave-based strategies needed to build functional PostgreSQL environments on Amazon Web Services (AWS), using Zulon City Insurance (ZCINS), a fictional carrier managing 42 Oracle databases, as a model for a successful transition. Once you have assessed your Oracle estate, as laid out in part 2 of this post, the challenge shifts to navigating the move to AWS while maintaining business continuity.

ZCINS viewed their migration not just as a change in infrastructure and database platform, but as an opportunity for strategic modernization. For example, to address legacy performance bottlenecks, they chose to denormalize their reporting workloads; their analysis showed that introducing data redundancy would reduce report generation time. To support their modernization efforts, ZCINS also had to evaluate their target infrastructure against strict business and technical criteria, specifically weighing the performance and scalability trade-offs between Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition.

Using the ZCINS decision-making process as a guide, this post demonstrates how to plan and design scalable environments and navigate the selection between Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL-Compatible Edition. Finally, we explore how ZCINS replaced legacy Oracle features with purpose-built AWS services, using a structured, wave-based implementation to facilitate a test-driven migration.

Target architecture design

Target architecture design implements a cloud-native framework by decoupling services, optimizing database schemas and data placement, and selecting appropriate infrastructure. This approach balances modernization goals with risk tolerance, resulting in better performance and the ability to scale storage automatically as data volume grows.

ZCINS modernized its legacy environment by moving 2.3TB of large objects, such as PDFs and reports, from Oracle to Amazon S3. This shift significantly reduced database overhead and costs while enabling automated lifecycle management. Simultaneously, the company adopted a hybrid database strategy by using Amazon Aurora PostgreSQL-Compatible Edition for high-scale services and Amazon RDS for PostgreSQL for legacy-compatible workloads. To establish clean service boundaries and improve agility, ZCINS replaced Oracle-embedded features with a decoupled architecture built on purpose-built AWS services. This new event-driven architecture uses Amazon Simple Notification Service (Amazon SNS) and Amazon Simple Queue Service (Amazon SQS) for messaging, AWS Step Functions for orchestration, and Amazon EventBridge with AWS Lambda for seamless integration. See Figure 1.

Figure 1: ZCINS target architecture

With the target architecture vision established, the next critical step is defining the overall migration approach that will guide execution across workloads.

Modernization strategy and architecture

A structured modernization lifecycle transforms monolithic legacy systems into agile, cloud-native environments by resolving technical debt and bottlenecks through phased refactoring. This alignment of infrastructure to specific workload requirements allows the architecture to handle increased transaction volumes without manual intervention.

ZCINS demonstrated this by adopting a multi-phased modernization strategy to address their specific requirements. This approach balanced the speed of migration with the necessity of decoupling legacy dependencies for cloud-native performance. See Figure 2.

Figure 2: ZCINS modernization architecture

The essential steps in this architecture are:

• Migration lifecycle: The process progressed from initial full data loads to continuous Change Data Capture (CDC), followed by rigorous validation and controlled cutovers with 15–60 minute rollback windows.
• Application remediation: To confirm compatibility, ZCINS updated 100+ applications, replacing Oracle JDBC drivers and implementing PgBouncer, which reduced database connection overhead by 92% (from 600 to 50).
• Logic refactoring: A strategic assessment of PL/SQL resulted in conversion to PL/pgSQL and migration to Java microservices on ECS Fargate.
• Wave-based execution: Migration was sequenced into four waves over 36 weeks, progressing from low-risk internal tools to mission-critical systems like Claims Processing.
• Multi-dimensional validation: Success was verified through a testing framework covering structural integrity, quantitative accuracy (validating 2.8 billion rows), qualitative correctness (15,000 test cases), and operational performance.
• Understanding the overall migration strategy provides context for examining the specific workloads that will undergo this transformation.

Workload distribution and access patterns

ZCINS used a unified Amazon API Gateway for IAM-based authentication and cross-user rate limiting. Traffic is routed via an Application Load Balancer (ALB) to support blue-green deployments across multiple Availability Zones (AZs).

To optimize performance and cost, ZCINS moved document storage to Amazon S3, retaining only metadata keys in PostgreSQL. This strategic placement significantly decreased database I/O and backup times. Furthermore, they eliminated schema bloat via dedicated audit tables, enabled long-term archiving using partitioned history tables in S3 Glacier, and accelerated average report generation by 60% (from 25 down to 10 minutes) through denormalized reporting workloads.

These defined characteristics now guide the selection of the optimal AWS service for each specific workload.

Service selection strategy

Effective database modernization requires matching each workload to either Amazon Aurora or Amazon RDS for PostgreSQL based on its specific I/O patterns, failover requirements, and total cost of ownership. The decision tree (Figure 3) guides service selection: Amazon Aurora is optimal for mission-critical workloads requiring high concurrency, sub-30-second failover, or unpredictable scaling. Conversely, Amazon RDS for PostgreSQL is better suited for cost-sensitive applications with predictable traffic, offering granular storage control via gp3 or io2 volumes for workloads that can tolerate standard 60–120 second failover windows. See Figure 3.

Figure 3: Service selection decision tree

ZCINS used this tree to place its workloads with diverse requirements. See Figure 4.

Figure 4: ZCINS data placement strategy

Once the appropriate service has been selected for each workload, the next phase is orchestrating the actual data migration with zero production impact.

Application layer remediation

Application remediation confirms compatibility with PostgreSQL’s architecture while optimizing performance. Transitioning from Oracle requires systematic updates of connection drivers, syntax, and logic handling to address fundamental differences in memory management and SQL dialect.

ZCINS executed remediation across 100+ applications, focusing on architectural decoupling and performance optimization:

• Driver & connection management: ZCINS replaced Oracle JDBC drivers with PostgreSQL drivers and implemented PgBouncer for connection pooling. This achieved a 12:1 connection multiplexing ratio (from 600 to 50), reducing database memory consumption by 92% (from 6GB to 500MB).
• SQL syntax standardization: ZCINS modified a total of 847 queries to PostgreSQL syntax. Key changes included removing DUAL table references, replacing NVL with COALESCE, and converting hierarchical CONNECT BY queries into recursive Common Table Expressions (CTEs).
• Strategic stored procedure refactoring: ZCINS assessed 78,000 lines of PL/SQL code. 30% was converted to PL/pgSQL for performance-critical operations requiring sub-10ms execution, while 50% was migrated to Java microservices on Amazon Elastic Container Service (Amazon ECS) Fargate to enhance deployment flexibility. The remaining 20% of obsolete code was decommissioned.
• Error handling & locking logic: ZCINS updated applications to use SQLSTATE codes instead of Oracle’s ORA- prefixes. Transaction isolation was adjusted by converting FOR UPDATE locking to PostgreSQL’s FOR UPDATE with NOWAIT or SKIP LOCKED clauses to maintain consistency without deadlocks.
• API layer decoupling: ZCINS implemented Amazon API Gateway as an abstraction layer. Applications now invoke RESTful APIs with IAM authentication rather than direct database connections, insulating the application layer from backend database changes.

Data migration strategy

A robust data migration strategy maintains integrity and continuity through a bit-for-bit accurate transition. A two-phased approach, using an initial Full Load followed by Change Data Capture (CDC), allows the source database to remain operational until final validation. See Figure 5.

Figure 5: Full load + CDC migration strategy

Alternatively, a standalone Full Load is sufficient for big-bang migrations, where data transfer and cutover are consolidated into a single operation during a scheduled maintenance window. See Figure 6.

Figure 6: Full load only migration strategy

With data migration orchestrated, the next phase addresses replacing proprietary Oracle features with cloud-native AWS services.

Replacing Oracle features with AWS services

Modernizing a database-centric architecture requires replacing vendor-specific, embedded features with purpose-built cloud services. Decoupling application logic from the database layer eliminates expensive licensing costs, removes scaling bottlenecks, and establishes clean service boundaries.

To optimize cost and performance, ZCINS modernized its storage and messaging infrastructure. They reduced database I/O, schema bloat, and backup times by offloading documents to Amazon S3 (retaining only metadata keys in PostgreSQL) and archiving history tables to S3 Glacier.

Simultaneously, ZCINS decoupled its infrastructure by migrating 23 queues from Oracle Advanced Queue (AQ) to Amazon SNS/SQS. The Claims Application now publishes events to an SNS Topic that fans out to independent SQS queues for the Billing, Notification, and Analytics Services, handling 50,000 daily messages with sub-second latency. To compensate for the lack of native database-transactional messaging in SNS/SQS, they implemented the transactional outbox pattern to guarantee exactly once delivery tied to database commits. See Figure 7.

Figure 7: Messaging architecture with Amazon SNS and Amazon SQS

ZCINS also replaced 156 Oracle DBMS_SCHEDULER jobs with Amazon EventBridge and AWS Lambda, eliminating idle database connections for routine automation. For instance, daily policy renewal reminders run via an EventBridge rule at 6:00 AM, triggering a Lambda function that queries expiring policies from Amazon Aurora PostgreSQL. This function publishes to an SNS notification topic, delivering 15,000 daily reminders with a 200ms average processing time. For complex jobs exceeding Lambda’s 15-minute execution limit, ZCINS utilized AWS Step Functions. See Figure 8.

Figure 8: Scheduling architecture with Amazon EventBridge and AWS Lambda

ZCINS moved 2.3TB of documents from Oracle UTL_FILE to Amazon S3. By storing only S3 object keys as metadata in PostgreSQL, they reduced database I/O, improved backup speeds, and used S3 Intelligent-Tiering for automatic cost optimization. The Claims Application uploads and downloads via Amazon S3 Bucket using Presigned URLs with Lifecycle Policy transitioning to S3 Glacier for infrequent access, while storing metadata and retrieving keys through Amazon Aurora PostgreSQL-Compatible Edition Object Keys. See Figure 9.

Figure 9: File storage architecture with Amazon S3

To eliminate direct database exposure, ZCINS replaced 12 Oracle Database Links with Amazon API Gateway and PostgreSQL Foreign Data Wrappers (postgres_fdw). API Gateway replaced links previously used for cross-system business logic; the Policy and Billing systems now connect securely via HTTPS/REST APIs with IAM authentication to trigger AWS Lambda business logic. Conversely, postgres_fdw replaced links used for direct, cross-instance SQL access, providing legacy batch processes with read-only entry to Amazon Aurora PostgreSQL-Compatible Edition policy data. See Figure 10.

Figure 10: Connectivity architecture with Amazon API Gateway/FDW

ZCINS used AWS Step Functions to coordinate multi-step business logic previously embedded in the database, further insulating applications from database-tier dependencies.

Migration wave planning

To reduce risks during massive database migrations, use strategic wave planning to group workloads logically. By starting with simple systems and graduating to mission-critical ones, the team builds institutional knowledge and refines technical playbooks in a low-stakes environment. This iterative cycle provides early wins that harden the processes used for complex cutovers, maintaining momentum while protecting business continuity.

To implement a successful Oracle to PostgreSQL migration, follow this structured, four-wave methodology:

• Phase 1: Foundation and pilot validation: Begin with low-complexity databases where the AWS Database Migration Service Schema Conversion (AWS DMS SC) achieves high automatic conversion rates. Use this phase to validate AWS Database Migration Service (AWS DMS) replication stability and establish operational baseline. Success criteria are high AWS DMS SC conversion percentages, minimal replication lag, and completed PostgreSQL training for all database administrators.
• Phase 2: Moderate complexity and data consistency: Migrate mid-tier workloads using a blue-green deployment strategy to run legacy and target environments in parallel. This approach is mandatory for verifying data integrity before a full cutover. Success criteria are zero discrepancies in business calculations, successful implementation of PgBouncer for connection pooling, and validated performance baselines.
• Phase 3: High complexity and architectural refactoring: Target core systems requiring extensive PL/SQL refactoring due to low automatic conversion rates. Decompose monolithic legacy logic into modern application-tier services and perform exhaustive regression testing of all business rules. Success criteria are complete validation of refactored logic, successful decomposition of stored procedures, and verified stability of the new microservices architecture.
• Phase 4: Mission-critical and performance optimization: Finalize the migration by transitioning high-demand workloads to high-performance tiers like Amazon Aurora I/O-Optimized. Execute these migrations within narrow cutover windows, supported by stress testing for peak concurrency and rapid failover. Success criteria are adherence to strict SLAs (including sub-second response times), validated failover recovery speeds, and total production readiness.

Phase 1 establishes operational confidence with low-risk systems, creating validated runbooks. Phase 2 introduces complexity gradually while maintaining safety nets. Phase 3 tackles architectural transformation when teams have proven competency. Phase 4 executes mission-critical migrations with full organizational readiness.

ZCINS executed its migration over a 36-week timeline, following this wave planning paradigm to balance technical risk with organizational readiness. The 36 weeks represents sequential execution; parallel work streams may reduce actual calendar time. See Figure 11.

Figure 11: ZCINS wave planning timeline

Testing and validation framework

A multi-dimensional validation framework serves as the final safeguard for data and logic integrity. This process begins with structural and quantitative reconciliation, where you systematically compare the source and target environments to verify that no degradation occurred during the transfer. ZCINS validated the conversion of table definitions and indexes, for example, recreating 23 Oracle bitmap indexes as PostgreSQL B-tree indexes. ZCINS validated 100% of 2.8 billion rows against a strict 0.001% discrepancy threshold to eliminate the risk of data loss.

Qualitative and functional validation proves that the target environment correctly enforces mission-critical business rules by executing a comprehensive test suite that mirrors the end-to-end lifecycle of your operations. ZCINS executed 15,000 test cases covering everything from policy creation to claims and billing, ensuring that the logic remained intact despite the underlying platform change. ZCINS tested 800 concurrent users, ensuring the new architecture could sustain high throughput without regression.

ZCINS simulated AZ failures and executed planned failovers, intentionally triggering these failure scenarios to validate their backup and recovery runbooks. This confirms that the migration is not only technically accurate but operationally ready for the demands of a production environment.

Even with comprehensive testing, production migrations require clearly defined rollback procedures to maintain business continuity.

Rollback strategy and risk mitigation

A multi-layered rollback strategy provides the final safety net of a migration, maintaining business continuity by providing clear, pre-defined exit ramps if the target environment fails to meet performance or integrity baselines. For mission-critical workloads, the ability to revert to the source database with minimal downtime and zero data loss is a fundamental requirement for risk management.

ZCINS implemented a tiered rollback approach, with decision criteria based on customer-impacting incidents, data integrity issues, or performance degradation exceeding 20% of the baseline:

Blue-green deployment (mission critical):

For revenue-critical systems like Claims Processing, ZCINS maintained parallel environments with real-time replication. This allows instant rollback by switching traffic back to Oracle with sub-30-second recovery and zero data loss. See Figure 12.

Figure 12: Blue-green deployment

While blue-green deployment doubles infrastructure costs, ZCINS maintained parallel environments for only 72 hours post-cutover, limiting incremental costs to <3% of annual database spend while eliminating multi-million dollar rollback risk for Claims Processing.

Replication-based rollback (moderate criticality):

For systems like Commission Calculation, bidirectional DMS replication was maintained for 60 days. This provided a 15-minute recovery time using PostgreSQL-to-Oracle replication and automated reconciliation scripts. See Figure 13

Figure 13: Replication-based rollback

Basic fallback (low criticality):

For non-critical internal tools, ZCINS maintained Oracle instances for 30 days post-migration. Rollback involved DNS updates with a 2–4-hour completion window. See Figure 14.

Figure 14: Basic fallback

Fix forward:

In cases of non-critical issues, ZCINS used rapid remediation, such as query optimization or application hotfixes, to address performance gaps without reverting the migration. See Figure 15.

Figure 15: Fix forward

To further mitigate risk, ZCINS established a 72-hour validation period post-cutover before decommissioning legacy Oracle environments. This period allowed continuous monitoring of transaction isolation and NULL handling semantics to confirm complete application compatibility.

With rollback strategies defined, the final phase addresses cutover execution strategies.

Cutover strategies

Cutover represents the final transition from Oracle to PostgreSQL in production. Cutover approaches include big bang (single coordinated transition during maintenance window), blue-green (zero-downtime with parallel environments allowing instant rollback), and phased (gradual transition of specific modules or data subsets).

ZCINS employed these strategies across its portfolio:

Prerequisites

This post requires no AWS infrastructure deployment. Familiarity with Oracle database concepts, the AWS Database Migration Service, and the planning deliverables from Part 2 is recommended before proceeding with the execution activities described in this post.

Walkthrough

The sections above constitute the walkthrough for this post. To apply the planning and execution framework to your own Oracle-to-PostgreSQL migration:

1. Design your target architecture using the service selection decision tree (Figure 3) to assign each workload to Aurora or RDS for PostgreSQL based on I/O patterns, failover requirements, and cost.
2. Execute application remediation across all applications interacting with Oracle, updating drivers, SQL syntax, stored procedure logic, and error handling as described in the Application layer remediation section.
3. Replace Oracle-specific features (AQ, DBMS_SCHEDULER, UTL_FILE, database links) with purpose-built AWS services (Amazon SNS/SQS, Amazon EventBridge, AWS Lambda, Amazon S3, and Amazon API Gateway).
4. Implement a wave-based migration plan following the four-phase methodology: foundation and pilot validation, moderate complexity and data consistency, high complexity and architectural refactoring, and mission-critical and performance optimization.
5. Validate completeness using the multi-dimensional framework: structural integrity, quantitative row-count accuracy, qualitative business rule correctness, and operational performance under production-equivalent load.
6. Define tiered rollback strategies for each workload, blue-green for mission-critical, replication-based for moderate criticality, and basic DNS fallback for low-criticality systems, before initiating any cutover.

Cleanup

No cleanup is required for this post as no AWS resources are provisioned.

Conclusion

In this post, we explored how to plan and execute an Oracle to PostgreSQL migration using the proven patterns from ZCINS’ successful transformation. Transitioning to a cloud-native framework does more than move data; it eliminates technical debt and allows organizations to respond to market shifts with greater agility. The cornerstone of this success is shifting from a ‘lift-and-shift’ mindset to a decoupled, scalable target architecture through strategic refactoring, rigorous validation, and phased risk mitigation.

The effectiveness of this approach was demonstrated during ZCINS’ Wave 4 cutover for their Claims Processing system. Using a blue-green deployment strategy, the team followed a precise two-hour Saturday morning runbook: 15 minutes for final DMS validation to confirm zero replication lag, 5 minutes to switch traffic to Amazon Aurora, 30 minutes for smoke testing 200 critical transaction paths, and a 70-minute monitoring window. With zero data loss and sub-30-second failover capability validated, the team successfully transitioned into their 72-hour extended validation period.

While a successful cutover is a significant milestone, treating it as the finish line misses the real value of modernization. The next and final part of this series will focus on post-cutover validation and optimization, transforming a functional PostgreSQL environment into an operationally excellent one tuned specifically for PostgreSQL’s architectural patterns rather than Oracle’s.