How to Migrate from Oracle to PostgreSQL

TL;DR

• Bottom line: Use Ora2Pg 25.x for schema/code conversion and PgLoader or AWS DMS for data migration — expect 70-80% automatic conversion with manual work required for PL/SQL packages, Oracle-specific functions, and shared database sequences.
• Key tool/command: ora2pg -c ora2pg.conf -t SHOW_REPORT to assess migration complexity before starting.
• Watch out for: Oracle's implicit NULL = '' (empty string) behavior — PostgreSQL treats them differently, causing subtle bugs in WHERE clauses and concatenation.
• Works with: Oracle 11g-23ai to PostgreSQL 14-18 (18.4 current as of 2026-05); tools: Ora2Pg 25.x, AWS DMS Schema Conversion (now fully managed, replacing standalone AWS SCT downloads), AWS DMS 3.5+, pgLoader 3.6+, IvorySQL 4.x.

Constraints

• Always map Oracle DATE to PostgreSQL timestamp — Oracle DATE includes time; PostgreSQL date does not. Using date silently drops time data. [src4, src7]
• Never run Ora2Pg-generated SQL directly in production — always review generated DDL for TODO/FIXME comments and test in a staging database first. [src1]
• Oracle treats empty string ('') as NULL — audit all WHERE col IS NULL and string concatenation logic before cutover; this is the #1 source of post-migration bugs. [src4]
• Do not use PostgreSQL ctid as a substitute for Oracle ROWID — ctid changes after VACUUM; add a real primary key column instead. [src4]
• Sequences must be manually reset after AWS DMS CDC replication — DMS does not migrate NEXTVAL state during ongoing replication. [src2, src5]
• When using TrimSpaceInChar endpoint setting in AWS DMS, verify CHAR/NCHAR column data is not silently truncated — this setting trims trailing spaces which may be semantically significant. [src2]
• PostgreSQL forbids COMMIT/ROLLBACK inside a PL/pgSQL function — only procedures invoked via CALL can control transactions, and even then not inside an outer transaction block. Oracle code that commits mid-function must move its transaction boundaries to the calling application layer. [src9]

Quick Reference

Decision Tree

START
├── What is the database size?
│   ├── < 100 GB → Ora2Pg export + psql import (simplest) [src1]
│   └── > 100 GB ↓
├── Is near-zero downtime required?
│   ├── YES → AWS DMS or logical replication for CDC [src2, src5]
│   └── NO → Ora2Pg bulk export + PgLoader parallel import [src1]
├── How much PL/SQL code exists?
│   ├── Minimal (< 50 procedures) → Ora2Pg auto-converts most [src1]
│   ├── Moderate (50–500) → Ora2Pg + manual package review [src1, src3]
│   └── Heavy (> 500) → Consider EDB Advanced Server or IvorySQL [src3, src8]
├── Are you migrating to AWS?
│   ├── YES → Use AWS SCT + DMS pipeline [src2, src5]
│   └── NO ↓
├── Do you need ongoing Oracle compatibility?
│   ├── YES → Install Orafce extension or use IvorySQL [src6, src8]
│   └── NO → Convert to native PostgreSQL (recommended) [src3]
└── DEFAULT → Start with Ora2Pg assessment report

Decision Logic

Structured if/then rules an agent can apply directly once it has the user's inputs.

If the database is small (<100 GB) and a maintenance window is available

--> Use Ora2Pg for both schema and data: ora2pg -t EXPORT_SCHEMA then ora2pg -t COPY -j 8, import with psql. Simplest, fewest moving parts. [src1]

If near-zero downtime is required (>100 GB or 24/7 system)

--> Run Ora2Pg (or DMS Schema Conversion) for the schema, then AWS DMS full-load + CDC — or open-source CDC such as Debezium reading Oracle redo logs — so cutover takes only seconds. Reset all sequences immediately after cutover. [src2, src9]

If migrating into AWS and you want a managed, console-driven path

--> Use AWS DMS Schema Conversion (now fully managed, with GenAI-assisted conversion since March 2026) instead of downloading the standalone AWS SCT client, then DMS for data. [src5, src9]

If the codebase has heavy or complex PL/SQL (>500 procedures or stateful packages)

--> Either budget for manual PL/pgSQL refactoring or adopt an Oracle-compatible engine — IvorySQL (open source) or EDB Advanced Server (commercial) — to avoid rewriting business logic. [src3, src8]

If existing code commits or rolls back inside Oracle functions

--> Refactor: PostgreSQL forbids transaction control inside PL/pgSQL functions. Convert to a PROCEDURE (callable via CALL) or move the transaction boundary to the application layer. [src9]

If the application relies on Oracle's empty-string-equals-NULL behavior

--> Do not skip data normalization: run UPDATE t SET col = NULL WHERE col = '' during cutover and audit every WHERE col IS NULL and string-concatenation path before go-live. [src4]

If you need ongoing Oracle SQL/function compatibility after the move

--> Install the orafce extension for the 100+ Oracle built-ins, or run IvorySQL in Oracle-compatible mode — but do not mix Oracle and PostgreSQL modes in the same IvorySQL database. [src6, src8]

Step-by-Step Guide

1. Run the Ora2Pg migration assessment

Install Ora2Pg and run a migration complexity report. Ora2Pg 25.x adds parallel partition export, SCRIPT action for sqlplus scripts, and multiple assessment report formats. [src1, src7]

# Install and create project
ora2pg --init_project myproject
cd myproject

# Configure ora2pg.conf with Oracle connection details
# Run assessment report (25.x supports multiple output formats)
ora2pg -c ora2pg.conf -t SHOW_REPORT
# Or with HTML + JSON output:
ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html --dump_as_json

Verify: Report shows migration levels A (trivial) through E (very complex) per object type.

2. Export and convert the schema

Use Ora2Pg to export Oracle schema objects and auto-convert to PostgreSQL DDL. The 25.x -O flag allows overriding config on the fly. [src1, src3]

# Export all schema objects
ora2pg -c ora2pg.conf -t TABLE -o tables.sql
ora2pg -c ora2pg.conf -t FUNCTION -o functions.sql
ora2pg -c ora2pg.conf -t PROCEDURE -o procedures.sql
ora2pg -c ora2pg.conf -t PACKAGE -o packages.sql

# Override config on the fly (Ora2Pg 25.x)
ora2pg -c ora2pg.conf -t TABLE -O "CASE_SENSITIVE=1" -o tables.sql

# Review warnings
grep -n "TODO\|FIXME" *.sql

Verify: psql -f schema.sql testdb -> zero errors.

3. Convert PL/SQL to PL/pgSQL

Ora2Pg handles most conversions automatically. Ora2Pg 25.x adds improved Oracle-to-PostgreSQL exception mapping. Review complex packages and Oracle-specific functions. [src1, src4, src7]

-- Oracle PL/SQL (BEFORE)
CREATE OR REPLACE PROCEDURE update_salary(
  p_emp_id IN NUMBER, p_raise IN NUMBER
) IS
  v_current_sal NUMBER;
BEGIN
  SELECT salary INTO v_current_sal FROM employees WHERE emp_id = p_emp_id;
  IF v_current_sal IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
    RETURN;
  END IF;
  UPDATE employees SET salary = salary + p_raise WHERE emp_id = p_emp_id;
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee ' || p_emp_id || ' not found');
END;
/

-- PostgreSQL PL/pgSQL (AFTER)
CREATE OR REPLACE PROCEDURE update_salary(
  p_emp_id bigint, p_raise numeric
) LANGUAGE plpgsql AS $$
DECLARE
  v_current_sal numeric;
BEGIN
  SELECT salary INTO v_current_sal FROM employees WHERE emp_id = p_emp_id;
  IF NOT FOUND THEN
    RAISE NOTICE 'Employee % not found', p_emp_id;
    RETURN;
  END IF;
  UPDATE employees SET salary = salary + p_raise WHERE emp_id = p_emp_id;
  -- No explicit COMMIT in procedures (auto-commit outside transaction)
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE NOTICE 'Employee % not found', p_emp_id;
END; $$;

Verify: CALL update_salary(101, 5000); -> no errors.

4. Migrate the data

Choose migration method based on size and downtime tolerance. Ora2Pg 25.x adds parallel partition export for faster data extraction from partitioned tables. [src1, src2, src7]

# Small/medium: Ora2Pg parallel export
ora2pg -c ora2pg.conf -t COPY -j 8 -o data.sql
psql -d targetdb -f data.sql

# Large + zero downtime: AWS DMS with CDC
# Configure source (Oracle) → target (PostgreSQL) replication
# Set TrimSpaceInChar=true if CHAR columns have trailing spaces

Verify: Compare row counts between Oracle and PostgreSQL for every table.

5. Handle NULL vs empty string differences

Oracle treats '' as NULL. PostgreSQL does not. Normalize data during migration. [src4]

-- Normalize empty strings to NULL
UPDATE users SET name = NULL WHERE name = '';
-- Or use NULLIF in queries
SELECT * FROM users WHERE NULLIF(name, '') IS NULL;

6. Rebuild indexes and optimize

Recreate indexes optimized for PostgreSQL's planner. [src3]

-- Oracle bitmap indexes → PostgreSQL partial indexes
CREATE INDEX idx_active_orders ON orders(created_at) WHERE status = 'active';

-- Analyze all tables after data load
ANALYZE;

-- Find tables needing indexes
SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables
WHERE seq_scan > 1000 AND idx_scan < 50;

Code Examples

Python: Automated data migration with validation

Full script: python-automated-oracle-to-postgresql-data-migrati.py (63 lines)

# Input:  Oracle + PostgreSQL connections, list of tables
# Output: Migrated data with row count validation

import oracledb  # cx_Oracle is now oracledb (v2.0+)
import psycopg   # psycopg 3.x (async-capable)

def migrate_table(ora_conn, pg_conn, table_name, batch_size=10000):
    ora_cur = ora_conn.cursor()
    pg_cur = pg_conn.cursor()
    ora_cur.execute(f"SELECT COUNT(*) FROM {table_name}")
    ora_count = ora_cur.fetchone()[0]

    ora_cur.execute(f"SELECT * FROM {table_name}")
    columns = [desc[0].lower() for desc in ora_cur.description]
    placeholders = ", ".join(["%s"] * len(columns))
    insert_sql = f'INSERT INTO {table_name.lower()} ({", ".join(columns)}) VALUES ({placeholders})'

    inserted = 0
    while True:
        rows = ora_cur.fetchmany(batch_size)
        if not rows:
            break
        cleaned = [tuple(None if v == "" else v for v in row) for row in rows]
        pg_cur.executemany(insert_sql, cleaned)
        inserted += len(rows)
    pg_conn.commit()
    return ora_count, inserted

Bash: Complete Ora2Pg migration script

Full script: bash-complete-ora2pg-migration-script-with-validat.sh (37 lines)

#!/bin/bash
set -euo pipefail
CONFIG="ora2pg.conf"
PG_DB="target_db"

# Assessment
ora2pg -c "$CONFIG" -t SHOW_REPORT

# Schema export
for type in TABLE SEQUENCE VIEW FUNCTION PROCEDURE TRIGGER PACKAGE; do
    ora2pg -c "$CONFIG" -t "$type" -o "${type,,}.sql"
done

# Data migration (parallel)
ora2pg -c "$CONFIG" -t COPY -j 8 -o data.sql
psql -d "$PG_DB" -f data.sql

# Post-migration
psql -d "$PG_DB" -c "ANALYZE;"

Anti-Patterns

Wrong: Treating empty strings and NULLs as interchangeable

-- ❌ BAD — Oracle code that relies on '' = NULL
INSERT INTO users (name) VALUES ('');
SELECT * FROM users WHERE name IS NULL;  -- Returns the row in Oracle, NOT in PostgreSQL!

Correct: Explicitly handle NULL vs empty string

-- ✅ GOOD — Normalize during migration
UPDATE users SET name = NULL WHERE name = '';
SELECT * FROM users WHERE NULLIF(name, '') IS NULL;

Wrong: Converting Oracle PACKAGES as single functions

-- ❌ BAD — Cramming an entire Oracle package into one giant function
CREATE OR REPLACE FUNCTION pkg_orders_everything(action text, ...) ...

Correct: Split packages into schema + individual functions

-- ✅ GOOD — Use a PostgreSQL schema to group related functions [src1, src3]
CREATE SCHEMA pkg_orders;
CREATE OR REPLACE FUNCTION pkg_orders.create_order(p_customer_id bigint, ...)
  RETURNS bigint LANGUAGE plpgsql AS $$ ... $$;

Wrong: Using Ora2Pg without reviewing output

# ❌ BAD — Blindly running generated SQL in production
ora2pg -c ora2pg.conf -t EXPORT_SCHEMA -o schema.sql
psql -d production -f schema.sql

Correct: Review, test, iterate

# ✅ GOOD — Review generated SQL, test in staging [src1]
grep -n "TODO\|FIXME\|WARNING" schema.sql
psql -d test_migration -f schema.sql
# Run application tests, fix issues, repeat

Wrong: Forgetting to reset sequences after DMS replication

-- ❌ BAD — Assuming AWS DMS migrates sequence state during CDC
-- After cutover, INSERTs fail with duplicate key errors
INSERT INTO orders (id, ...) VALUES (DEFAULT, ...);
-- ERROR: duplicate key value violates unique constraint

Correct: Manually reset sequences after DMS cutover

-- ✅ GOOD — Reset all sequences to MAX(id)+1 after CDC cutover [src2]
DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT sequencename FROM pg_sequences WHERE schemaname = 'public'
  LOOP
    EXECUTE format(
      'SELECT setval(%L, COALESCE((SELECT MAX(id) FROM %I), 1))',
      r.sequencename, replace(r.sequencename, '_id_seq', '')
    );
  END LOOP;
END $$;

Common Pitfalls

• Oracle (+) outer join syntax: PostgreSQL only supports ANSI SQL joins. Fix: rewrite using LEFT JOIN ... ON. [src4]
• CONNECT BY hierarchical queries: No direct equivalent. Fix: rewrite using WITH RECURSIVE CTEs. [src4]
• Transaction behavior differences: Oracle auto-commits DDL; PostgreSQL wraps DDL in transactions. Fix: add explicit COMMIT or leverage transactional DDL. [src3]
• Case sensitivity: Oracle folds to uppercase; PostgreSQL folds to lowercase. Fix: use lowercase unquoted names or Ora2Pg's CASE_SENSITIVE option. [src4]
• ROWID pseudo-column: PostgreSQL's ctid changes after VACUUM. Fix: add a real primary key. [src4]
• Global temporary tables: Oracle's persist across sessions. Fix: use PostgreSQL CREATE TEMPORARY TABLE or UNLOGGED TABLE. [src3]
• Materialized view refresh: DBMS_MVIEW.REFRESH -> REFRESH MATERIALIZED VIEW CONCURRENTLY. Fix: use pg_cron for scheduling. [src4]
• NULL character in VARCHAR columns: AWS DMS converts Oracle NULL character (0x00) to a space when targeting PostgreSQL. Fix: verify data integrity after migration. [src2]
• Transaction control inside functions: Oracle PL/SQL routinely COMMITs mid-function; PostgreSQL throws an error if a PL/pgSQL function tries to COMMIT/ROLLBACK. Fix: convert such routines to a PROCEDURE (callable via CALL) or hoist the transaction boundary into the calling application. [src9]
• numeric foreign keys slow joins: Ora2Pg maps unsized Oracle NUMBER to numeric, which is slower in joins and indexes than integer types. Fix: explicitly map surrogate keys and FKs to bigint/integer. [src9]

Diagnostic Commands

# Assess migration complexity
ora2pg -c ora2pg.conf -t SHOW_REPORT

# Process sqlplus scripts as a whole (Ora2Pg 25.x)
ora2pg -c ora2pg.conf -t SCRIPT -i input_script.sql -o converted_script.sql

# Compare row counts
# Oracle: SELECT table_name, num_rows FROM user_tables;
# PostgreSQL:
psql -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;"

# Find unconverted Oracle-isms
grep -rn "SYSDATE\|NVL\|DECODE\|ROWNUM\|CONNECT BY\|DBMS_" --include='*.sql' migrated/

# Check for missing indexes
psql -c "SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE seq_scan > 100 AND idx_scan < 10;"

# Verify extension availability
psql -c "SELECT * FROM pg_available_extensions WHERE name IN ('orafce', 'oracle_fdw', 'pgcrypto');"

Version History & Compatibility

When to Use / When Not to Use

Important Caveats

• Oracle DATE includes time; PostgreSQL date does not. Always map to timestamp.
• Oracle treats empty strings as NULL — audit all WHERE col IS NULL and concatenation operations.
• Oracle's CBO and PostgreSQL's planner behave differently — queries may need index tuning.
• PL/SQL WHEN OTHERS THEN catch-all blocks should be reviewed — error codes differ. Ora2Pg 25.x has improved exception mapping but manual review is still required. [src7]
• CONNECT BY LEVEL for series generation -> use generate_series() in PostgreSQL.
• AWS DMS converts Oracle NULL characters (0x00) to spaces when PostgreSQL is the target. [src2]
• IvorySQL's compatible_db toggle allows Oracle mode, but mixing Oracle and PostgreSQL modes in the same database is not supported. [src8]
• PostgreSQL 18 is the current major (18.4 released 2026-05-14); 14-18 are all supported targets. Validate Ora2Pg's PG_VERSION/PG_SUPPORTS_* settings against your exact target so generated DDL uses features available in that release. [src7, src9]
• As of 2026, AWS recommends managed DMS Schema Conversion (with optional GenAI assistance) over the downloadable AWS SCT client for AWS-target migrations — the standalone SCT still works but is no longer the primary path. [src9]
• Transaction control differs fundamentally: PostgreSQL cannot COMMIT/ROLLBACK inside a PL/pgSQL function, and even procedures cannot manage transactions when called inside an outer transaction block. Audit every Oracle routine that commits mid-body. [src9]