AWS Oracle to PostgreSQL Migration: SCT, DMS and What They Leave to You

The short answer

An AWS Oracle to PostgreSQL migration has two halves. Schema and code go through DMS Schema Conversion, the managed feature inside AWS Database Migration Service, or the AWS Schema Conversion Tool (SCT), the standalone application it builds on. Data goes through AWS DMS, with a full load followed by change data capture from Oracle's redo logs until you cut over. The target can be Aurora PostgreSQL, RDS for PostgreSQL, PostgreSQL you run on EC2, or PostgreSQL outside AWS altogether.

What the tools leave to you: converted code that still needs review, secondary indexes and foreign keys that DMS does not create, application SQL, sequences, Oracle features with no direct equivalent, and a rollback plan. Babelfish does not apply here. It gives Aurora PostgreSQL a SQL Server wire protocol and T-SQL dialect, and nothing comparable exists for Oracle's protocol or PL/SQL. If your source is SQL Server, read SQL Server to Aurora PostgreSQL: Babelfish vs full conversion instead. For the tool-neutral sequence, see the Oracle to PostgreSQL migration guide, and if you are still weighing whether to leave Oracle at all, Oracle support alternatives compares staying, third-party support and moving.

Step 1: Choose the target before the tool

The target decides which conversion tool you can use and what your team runs afterwards.

DMS Schema Conversion only converts to Aurora, RDS and Redshift targets. AWS states that SCT supports more sources and targets, including PostgreSQL itself and databases on EC2. For the trade-offs between the three PostgreSQL options, see PostgreSQL vs Aurora vs RDS. Version calendars matter too. A managed version that reaches the end of standard support moves into paid RDS Extended Support, covered in RDS Extended Support for PostgreSQL.

Step 2: Assess the Oracle schema

Both AWS tools produce an assessment report that splits objects into those converted automatically and those that need manual work. DMS Schema Conversion supports Oracle 10.2 and later, 11g to 12.2, 18c and 19c as sources, while SCT lists Oracle 10.1 and higher. In DMS Schema Conversion you create three resources first:

• an instance profile with the network and encryption settings used to reach both databases,
• a data provider for Oracle and one for PostgreSQL (or a virtual target, if the target does not exist yet),
• a migration project that ties them together, with the database credentials held in AWS Secrets Manager.

Read the report per schema, not as a total. An object count says little about effort when one package carries the business rules. Many teams also run Ora2Pg's SHOW_REPORT on the same schema for a second, independent view; the Ora2Pg tutorial shows how.

Step 3: Convert the schema and code

DMS Schema Conversion converts tables, views, stored procedures, functions and other objects with a rules-based engine. Transformation rules rename objects and change data types. For Oracle to Aurora PostgreSQL or RDS for PostgreSQL, AWS also offers generative AI conversion, in some Regions only, for objects the rules cannot fully convert. Treat that output like any other converted code: it needs a reviewer who knows both dialects. You then apply the converted code to the target, or export it as SQL scripts to S3 and run them through your own change process.

Both tools may install an extension pack, a schema called aws_oracle_ext that emulates Oracle system functions and views the target lacks. SCT maps V$VERSION to aws_oracle_ext.v$version, for example, and applies the extension pack before any other object. Converted code that calls it depends on that schema being present, which matters if the database later moves to another PostgreSQL platform. Search the converted code for aws_oracle_ext and replace calls with native PostgreSQL where it is practical.

For SCT with a self-managed target, the target login needs CREATE ON DATABASE, and AWS recommends a search path that includes the converted public synonyms:

GRANT CREATE ON DATABASE hr TO sct_user;
ALTER DATABASE hr SET SEARCH_PATH = "$user", public_synonyms, public;

On RDS for PostgreSQL, SCT needs rds_superuser. SCT can also convert Oracle SQL*Plus scripts to psql and convert SQL embedded in application code, which helps with the work Step 7 lists.

Load the tables with their primary keys first. Hold back foreign keys, secondary indexes and triggers until the data is in, as Step 5 explains.

Step 4: Prepare Oracle for change data capture

A full-load-only task needs no extra Oracle setup. A low-downtime migration does. DMS reads changes through Oracle LogMiner by default, or through its own Binary Reader. AWS recommends LogMiner in general, and Binary Reader for cases such as several migration tasks on one source or a high volume of redo. For a self-managed Oracle source:

-- The database must run in ARCHIVELOG mode
SELECT log_mode FROM v$database;

-- Minimal supplemental logging at database level
SELECT supplemental_log_data_min FROM v$database;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Primary key logging per table (DMS adds this itself if it has ALTER on the table)
ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Check identifier lengths before you trust the CDC phase. AWS documents that DMS skips CDC events for objects whose identifiers exceed 30 bytes, silently and without stopping the task, which can leave data missing on the target. The limit is in bytes, so multibyte names reach it sooner. LENGTHB shows the byte length of a name, and the DMS premigration assessment can check every object in scope.

Step 5: Run the DMS task

Create a replication instance, a source endpoint for Oracle and a target endpoint for PostgreSQL, then a task with the full-load-and-cdc migration type. The schema already exists, so tell DMS to truncate rather than drop and recreate, and to stop before applying cached changes so you can build indexes first:

aws dms create-replication-task \
  --replication-task-identifier hr-oracle-to-pg \
  --source-endpoint-arn "$SOURCE_ENDPOINT_ARN" \
  --target-endpoint-arn "$TARGET_ENDPOINT_ARN" \
  --replication-instance-arn "$REPLICATION_INSTANCE_ARN" \
  --migration-type full-load-and-cdc \
  --table-mappings file://table-mappings.json \
  --replication-task-settings file://task-settings.json

// table-mappings.json
{
  "rules": [
    {
      "rule-type": "selection",
      "rule-id": "1",
      "rule-name": "hr-all-tables",
      "object-locator": { "schema-name": "HR", "table-name": "%" },
      "rule-action": "include",
      "filters": []
    }
  ]
}

// task-settings.json (only the keys changed here)
{
  "FullLoadSettings": {
    "TargetTablePrepMode": "TRUNCATE_BEFORE_LOAD",
    "StopTaskCachedChangesNotApplied": true
  },
  "ValidationSettings": { "EnableValidation": true }
}

The order follows AWS's own best practices. During the full load, foreign keys and triggers on the target cause errors because tables load in groups, and indexes slow the load. Before the CDC phase, though, secondary indexes that support updates and deletes must exist, or each replicated change can turn into a full table scan. So the task pauses after the full load. You create secondary indexes and foreign keys, then resume. Triggers stay off until just before cutover.

Validation compares source and target row by row after the full load and keeps comparing changes during CDC. It needs a primary key or unique index on each table and adds query load on both databases, so watch the source during business hours.

Step 6: Finish what DMS does not create

AWS states that DMS creates only the objects it needs to move data: tables, primary keys and, in some cases, unique indexes. It does not create secondary indexes, non-primary-key constraints or column defaults, so those come from your converted schema. Then:

• Sequences. Set every sequence and identity column above the highest key loaded, with setval, before the application writes anything.
• Grants and roles. Recreate application logins and privileges on the target.
• Scheduled jobs. Oracle scheduler jobs need a new home, such as cron or a job scheduler.
• Statistics. Run ANALYZE after the load so the planner has real numbers before the first performance test.

What the AWS tools leave to you

The behaviour differences are covered in Oracle to PostgreSQL migration challenges, and the code patterns in PL/SQL to PL/pgSQL conversion.

Step 7: Cut over and keep a way back

1. Freeze DDL on Oracle. Schema changes during CDC are a common cause of failed tasks.
2. Confirm validation shows no mismatches and the CDC latency is near zero.
3. Stop application writes to Oracle and wait for DMS to apply the last changes.
4. Enable triggers on PostgreSQL, reset sequences and run your reconciliation queries.
5. Switch connection strings and smoke-test the critical paths.

For rollback, DMS can run the other way. PostgreSQL is a supported DMS source and Oracle a supported target, so a second task can replicate writes made on PostgreSQL back to Oracle after cutover. It needs logical replication on the PostgreSQL side: wal_level = logical on a self-managed server, or the rds.logical_replication parameter on RDS and Aurora. Set it up and test it before cutover day, and agree a date after which Oracle is retired.

PostgreSQL on EC2 or off AWS instead of Aurora

Using AWS tools does not commit you to Aurora. SCT converts to PostgreSQL on EC2, and DMS replicates into self-managed PostgreSQL on EC2 or on premises. Some teams use DMS for its CDC and still land on a PostgreSQL they run themselves, because they want superuser access, a free choice of extensions, or a version calendar they control, or they plan to run the database outside AWS later. Others use Ora2Pg for schema and code and DMS only for the data. Either way, avoid building a dependency on aws_oracle_ext if the target may move.

If you would rather have AWS operate the engine, Aurora or RDS is the better fit and the AWS path above is the most direct one. If you want to run PostgreSQL yourself, our cloud database repatriation page explains what changes when the database comes back under your control.

Where OSSeva fits

OSSeva for PostgreSQL supports self-managed community PostgreSQL wherever it runs, including EC2 and on-premises servers, from the day of cutover. OSSeva Assure includes Oracle-to-Postgres migration design, covering schema conversion, query compatibility analysis and the application-layer strategy, whichever conversion tools you use. Signed PostgreSQL builds include patched releases for versions past community end of life, so a version chosen early in a long programme stays covered. On OSSeva Operate you get 24/7 replication and failover monitoring with a 15-minute P1 response. Pricing is per cluster; book a discovery call for a quote. See Oracle to PostgreSQL with OSSeva and migration services.

Frequently asked questions

How does an AWS Oracle to PostgreSQL migration work?

DMS Schema Conversion or AWS SCT converts the schema and code and flags what needs manual work. AWS DMS then copies the data with a full load and replicates ongoing changes from Oracle's redo logs until you switch the application over.

What is the difference between AWS SCT and DMS Schema Conversion?

DMS Schema Conversion is a managed, web-based feature of AWS DMS that builds on the SCT conversion engine and targets Aurora, RDS and Redshift. SCT is a standalone application and command-line tool that supports more sources and targets, including PostgreSQL on EC2.

Can you migrate Oracle to Aurora PostgreSQL with zero downtime?

With a full-load-and-cdc task, downtime shrinks to the time needed to stop writes, let DMS apply the last changes, reset sequences and switch connections. Some outage is still needed for that switch.

Does the AWS Schema Conversion Tool convert PL/SQL packages?

It converts a large share of PL/SQL and marks the rest as action items in its report. Converted code can depend on the aws_oracle_ext extension pack, and all of it needs review and testing.

Can Babelfish be used for Oracle?

No. Babelfish for Aurora PostgreSQL understands SQL Server's TDS protocol and T-SQL. It does nothing for Oracle clients or PL/SQL.

Does AWS DMS migrate indexes, foreign keys and sequences?

DMS creates tables, primary keys and sometimes unique indexes. Secondary indexes, other constraints and defaults come from the converted schema, and you reset sequences yourself after the load.

Can AWS DMS migrate Oracle to PostgreSQL outside AWS?

Yes. DMS lists on-premises and EC2 PostgreSQL as targets. Use AWS SCT or Ora2Pg for the schema, because DMS Schema Conversion targets only AWS managed databases.