How to Migrate from Oracle to PostgreSQL

Type: Software Reference Confidence: 0.92 Sources: 8 Verified: 2026-02-23 Freshness: quarterly

TL;DR

Constraints

Quick Reference

Oracle Feature PostgreSQL Equivalent Migration Notes
VARCHAR2(N) varchar(N) or text PostgreSQL text has no performance penalty vs varchar [src4]
NUMBER numeric, bigint, integer Map by precision: NUMBER -> numeric, NUMBER(10) -> bigint [src4]
NUMBER(p,s) numeric(p,s) Direct mapping; use real/double precision for approximate math [src4]
DATE timestamp Oracle DATE includes time component; PostgreSQL date does not [src4]
CLOB / NCLOB text PostgreSQL text can hold up to 1 GB [src4]
BLOB / RAW bytea Or use Large Objects (lo) for files > 1 GB [src3]
SYSDATE CURRENT_TIMESTAMP CURRENT_TIMESTAMP = transaction start; clock_timestamp() = wall clock [src4]
NVL(a, b) COALESCE(a, b) COALESCE is SQL standard, supports multiple arguments [src4]
DECODE CASE WHEN ... THEN ... END Ora2Pg converts this automatically [src1]
ROWNUM LIMIT / ROW_NUMBER() ROWNUM in WHERE -> LIMIT N; in subquery -> ROW_NUMBER() OVER(...) [src4]
CONNECT BY WITH RECURSIVE CTE More flexible but syntax differs significantly [src4]
SEQUENCES nextval('seq') / IDENTITY Or use GENERATED ALWAYS AS IDENTITY [src3]
PACKAGES Schemas + functions Split package into schema with individual functions [src1, src3]
SYNONYMS SET search_path or views Use search_path for schema-level, views for table-level [src4]
DBMS_OUTPUT RAISE NOTICE Direct replacement in PL/pgSQL [src4]
DUAL table Omit FROM DUAL PostgreSQL allows SELECT 1; without FROM [src4]
(+) outer join LEFT JOIN / RIGHT JOIN ANSI join syntax required in PostgreSQL [src4]

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

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

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

Tool/Version Status Key Features Notes
Ora2Pg 25.x Current (2025) Parallel partition export, SCRIPT action, multiple assessment formats Recommended for new migrations [src1, src7]
Ora2Pg 24.x Stable Full PL/SQL conversion, parallel export Proven in production [src1]
AWS SCT 1.x Current GUI-based, extension packs Best for AWS targets [src5]
AWS DMS 3.5+ Current CDC, full load + replication, TrimSpaceInChar Use with SCT for schema [src2]
PgLoader 3.6+ Current Fast parallel data loading Data-only, no schema conversion
Orafce 4.x Current 100+ Oracle compatibility functions CREATE EXTENSION orafce [src6]
IvorySQL 4.x Current (2025) Full Oracle PL/SQL compatibility, compatible_db toggle Open-source Oracle-compatible PG [src8]
EDB Advanced Server 17 Current Full Oracle PL/SQL compatibility Commercial [src3]

When to Use / When Not to Use

Use When Don't Use When Use Instead
Oracle licensing costs unsustainable Heavy Oracle RAC dependency Cloud-managed Oracle (OCI)
Moving to cloud-native architecture Extensive Oracle Spatial 3D Keep Oracle or use PostGIS for 2D
Team has PostgreSQL expertise Timeline < 3 months for large DB Plan longer or use EDB
Compliance requires open-source 100% Oracle Forms/APEX app Rewrite frontend first
Need Oracle compat in open-source Complex PL/SQL, no budget for EDB Use IvorySQL (free Oracle-compat PG) [src8]

Important Caveats

Related Units

SQL Server to PostgreSQL Migration MySQL to PostgreSQL Migration MongoDB to PostgreSQL Migration PostgreSQL Slow Query Optimization