How to Migrate from Oracle to PostgreSQL

How do I migrate from Oracle to PostgreSQL?

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, NUMBER(5) -> integer [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; no special handling needed [src4]
BLOB / RAW bytea Or use Large Objects (lo) for files > 1 GB [src3]
SYSDATE CURRENT_TIMESTAMP or clock_timestamp() CURRENT_TIMESTAMP returns transaction start time; clock_timestamp() returns actual current time [src4]
NVL(a, b) COALESCE(a, b) COALESCE is SQL standard and supports multiple arguments [src4]
DECODE(x,a,b,c,d,e) CASE WHEN x=a THEN b WHEN x=c THEN d ELSE e END Ora2Pg converts this automatically [src1]
ROWNUM LIMIT / ROW_NUMBER() ROWNUM in WHERE -> LIMIT N; ROWNUM in subquery -> ROW_NUMBER() OVER(...) [src4]
CONNECT BY / START WITH WITH RECURSIVE CTE Recursive CTEs are more flexible but syntax differs significantly [src4]
SEQUENCES (.NEXTVAL / .CURRVAL) nextval('seq_name') / currval('seq_name') Or use GENERATED ALWAYS AS IDENTITY for auto-increment columns [src3]
PACKAGES Schemas + functions No direct equivalent; split package into schema with individual functions [src1, src3]
SYNONYMS SET search_path or views Use search_path for schema-level synonyms, views for table-level [src4]
DBMS_OUTPUT.PUT_LINE RAISE NOTICE Direct replacement in PL/pgSQL [src4]
DUAL table Omit FROM DUAL PostgreSQL allows SELECT 1; without FROM clause [src4]
(+) outer join syntax LEFT JOIN / RIGHT JOIN ANSI join syntax is required in PostgreSQL [src4]
TIMESTAMP WITH TIME ZONE timestamptz Semantics differ: PostgreSQL timestamptz stores UTC and maps to Oracle's TIMESTAMP WITH LOCAL TIME ZONE, not Oracle's WITH TIME ZONE — test conversions for off-by-offset bugs [src9]
NUMBER foreign keys bigint / integer numeric joins/indexes are measurably slower than integer types — prefer bigint for surrogate keys and FKs [src9]

Decision Tree

START
├── What is the database size?
│   ├── < 100 GB → Ora2Pg export + psql import (simplest approach) [src1]
│   └── > 100 GB ↓
├── Is near-zero downtime required?
│   ├── YES → AWS DMS or logical replication for CDC during cutover [src2, src5]
│   └── NO → Ora2Pg bulk export + PgLoader parallel import [src1]
├── How much PL/SQL code exists?
│   ├── Minimal (< 50 procedures) → Ora2Pg auto-converts most code [src1]
│   ├── Moderate (50-500) → Ora2Pg + manual review of complex packages [src1, src3]
│   └── Heavy (> 500 or complex packages) → Consider EDB Advanced Server or IvorySQL for Oracle compatibility layer [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 all code to native PostgreSQL (recommended long-term) [src3]
└── DEFAULT → Start with Ora2Pg assessment report, plan phase by phase

Decision Logic

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

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. This tells you exactly how much manual work is needed before touching any code. Ora2Pg 25.x adds parallel partition export, SCRIPT action for sqlplus scripts, and multiple assessment report formats. [src1, src7]

# Install Ora2Pg (Debian/Ubuntu)
sudo apt-get install ora2pg

# Or from source (for Ora2Pg 25.x)
git clone https://github.com/darold/ora2pg.git
cd ora2pg && perl Makefile.PL && make && sudo make install

# Create config file
ora2pg --init_project myproject
cd myproject

# Edit ora2pg.conf — set Oracle connection
# ORACLE_DSN    dbi:Oracle:host=orahost;sid=ORCL;port=1521
# ORACLE_USER   migration_user
# ORACLE_PWD    secret

# Run assessment report (Ora2Pg 25.x supports multiple output formats at once)
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. Total migration cost estimate in person-days.

2. Export and convert the schema

Use Ora2Pg to export Oracle schema objects and auto-convert to PostgreSQL DDL. Review and fix any objects flagged as unconvertible. [src1, src3]

# Export all schema objects
ora2pg -c ora2pg.conf -t TABLE -o tables.sql
ora2pg -c ora2pg.conf -t SEQUENCE -o sequences.sql
ora2pg -c ora2pg.conf -t VIEW -o views.sql
ora2pg -c ora2pg.conf -t FUNCTION -o functions.sql
ora2pg -c ora2pg.conf -t PROCEDURE -o procedures.sql
ora2pg -c ora2pg.conf -t TRIGGER -o triggers.sql
ora2pg -c ora2pg.conf -t PACKAGE -o packages.sql
ora2pg -c ora2pg.conf -t TYPE -o types.sql

# Or export everything at once
ora2pg -c ora2pg.conf -t EXPORT_SCHEMA --no_header -o schema.sql

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

# Review the generated SQL for conversion warnings
grep -n "TODO" schema.sql
grep -n "FIXME" schema.sql

Verify: Load schema into a test PostgreSQL instance: psql -f schema.sql testdb -> zero errors.

3. Convert PL/SQL to PL/pgSQL

Ora2Pg handles most conversions automatically, but review these common manual fixes. Ora2Pg 25.x adds improved Oracle-to-PostgreSQL exception mapping. [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: \df update_salary in psql shows the function. Call it: CALL update_salary(101, 5000); -> no errors.

4. Migrate the data

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

# METHOD 1: Ora2Pg direct data export (small-medium databases < 100 GB)
ora2pg -c ora2pg.conf -t COPY -o data.sql
# Or for parallel export (faster):
ora2pg -c ora2pg.conf -t COPY -j 8 -o data.sql

# Import into PostgreSQL
psql -d targetdb -f data.sql

# METHOD 2: PgLoader (fast parallel load with type casting)
pgloader oracle://user:pass@orahost/ORCL \
         postgresql://user:pass@pghost/targetdb

# METHOD 3: AWS DMS (continuous replication for near-zero downtime)
# Configure via AWS Console:
# Source endpoint: Oracle (DBI:Oracle:host=...;sid=...)
# Target endpoint: PostgreSQL (RDS or Aurora)
# Replication task: Full load + CDC (Change Data Capture)
# Note: Set TrimSpaceInChar=true if CHAR columns have trailing spaces
# In 2026, schema conversion is bundled: use DMS Schema Conversion (managed,
# GenAI-assisted) inside the DMS console instead of the standalone SCT client.

# METHOD 4: Open-source CDC (Debezium / BryteFlow) reading Oracle redo logs
# Streams live changes into PostgreSQL for vendor-neutral zero-downtime cutover

Verify: Compare row counts: SELECT table_name, num_rows FROM all_tables WHERE owner='SCHEMA' (Oracle) vs SELECT relname, n_live_tup FROM pg_stat_user_tables (PostgreSQL).

5. Handle Oracle-specific NULL and empty string behavior

Oracle treats empty string ('') as NULL. PostgreSQL does not. This causes subtle bugs in migrated code. [src4]

-- Oracle: these are equivalent
SELECT * FROM users WHERE name IS NULL;     -- finds empty strings TOO
SELECT * FROM users WHERE name = '';        -- matches NULL in Oracle

-- PostgreSQL: these are different!
SELECT * FROM users WHERE name IS NULL;     -- only finds actual NULLs
SELECT * FROM users WHERE name = '';        -- only finds empty strings

-- Fix: Update application code and queries to handle both
SELECT * FROM users WHERE name IS NULL OR name = '';
-- Or normalize data during migration:
UPDATE users SET name = NULL WHERE name = '';

Verify: SELECT COUNT(*) FROM users WHERE name = '' should return 0 after normalization.

6. Rebuild indexes and optimize performance

Oracle and PostgreSQL use different index types and optimizer strategies. Recreate indexes optimized for PostgreSQL. [src3]

-- Oracle bitmap indexes -> PostgreSQL GIN or partial indexes
-- Oracle: CREATE BITMAP INDEX idx_status ON orders(status);
-- PostgreSQL equivalent:
CREATE INDEX idx_status ON orders USING btree(status);
-- For low-cardinality columns, partial indexes work better:
CREATE INDEX idx_active_orders ON orders(created_at) WHERE status = 'active';

-- Oracle function-based indexes -> PostgreSQL expression indexes
-- Oracle: CREATE INDEX idx_upper_name ON users(UPPER(name));
-- PostgreSQL:
CREATE INDEX idx_upper_name ON users(UPPER(name));  -- same syntax works!

-- Analyze all tables after data load
ANALYZE;

-- Check for missing indexes
SELECT schemaname, relname, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 1000 AND idx_scan < 50
ORDER BY seq_scan DESC;

Verify: EXPLAIN ANALYZE SELECT ... on critical queries shows index scans, not sequential scans.

Code Examples

Python: Automated Oracle to PostgreSQL data migration with validation

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

# Input:  Oracle connection + PostgreSQL connection + list of tables
# Output: Migrated data with row count validation per table
import oracledb  # cx_Oracle is now oracledb (v2.0+)
import psycopg  # psycopg 3.x (async-capable)
from typing import Dict, Tuple
# ... (see full script)

Bash: Complete Ora2Pg migration script with validation

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

#!/bin/bash
# Input:  ora2pg.conf configured with Oracle and PostgreSQL connections
# Output: Fully migrated PostgreSQL database with validation report
set -euo pipefail
CONFIG="ora2pg.conf"
# ... (see full script)

Anti-Patterns

Wrong: Treating empty strings and NULLs as interchangeable

-- ❌ BAD — Oracle code that relies on '' = NULL
-- Oracle implicitly converts empty strings to NULL
INSERT INTO users (name) VALUES ('');
SELECT * FROM users WHERE name IS NULL;  -- Returns the row in Oracle!
-- In PostgreSQL, this query will NOT find the row with ''

Correct: Explicitly handle NULL vs empty string

-- ✅ GOOD — Code that works in both databases or is PostgreSQL-native
-- During migration, normalize empty strings to NULL:
UPDATE users SET name = NULL WHERE name = '';
-- Or use COALESCE/NULLIF in queries:
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
-- Oracle packages contain state (variables), multiple procedures, and types
-- Putting it all in one PL/pgSQL function creates unmaintainable code
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 $$ ... $$;

CREATE OR REPLACE FUNCTION pkg_orders.cancel_order(p_order_id bigint)
  RETURNS void LANGUAGE plpgsql AS $$ ... $$;

-- Package variables -> table or session variables
CREATE TABLE pkg_orders.config (key text PRIMARY KEY, value text);
-- Or use: SET LOCAL myapp.setting = 'value';

Wrong: Using Ora2Pg without reviewing output

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

Correct: Review, test, iterate

# ✅ GOOD — Review generated SQL, test in staging, iterate [src1]
ora2pg -c ora2pg.conf -t EXPORT_SCHEMA -o schema.sql
# 1. Review for TODO/FIXME comments
grep -n "TODO\|FIXME\|WARNING" schema.sql
# 2. Load in test database
psql -d test_migration -f schema.sql
# 3. Run application test suite against PostgreSQL
# 4. Fix issues, re-export, repeat

Wrong: Forgetting to reset sequences after DMS replication

-- ❌ BAD — Assuming AWS DMS migrates sequence state during CDC
-- After cutover, new INSERTs fail with duplicate key errors
-- because NEXTVAL is still at 1 on the target
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 with Ora2Pg
ora2pg -c ora2pg.conf -t SHOW_REPORT

# Count objects by type in Oracle
sqlplus -s user/pass@ORCL <<< "SELECT object_type, COUNT(*) FROM user_objects GROUP BY object_type ORDER BY 2 DESC;"

# Compare row counts between Oracle and PostgreSQL
# Oracle:
sqlplus -s user/pass@ORCL <<< "SELECT table_name, num_rows FROM user_tables ORDER BY num_rows DESC;"
# PostgreSQL:
psql -d targetdb -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;"

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

# Check for missing indexes after migration
psql -d targetdb -c "SELECT schemaname, relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE seq_scan > 100 AND (idx_scan = 0 OR idx_scan IS NULL) ORDER BY seq_scan DESC LIMIT 20;"

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

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

Version History & Compatibility

Tool/Version Status Key Features Notes
Ora2Pg 25.x Current (2025) Parallel partition export, SCRIPT action, multiple assessment formats, enhanced exception mapping Recommended for new migrations [src1, src7]
Ora2Pg 24.x Stable Full PL/SQL conversion, parallel export, cost estimation Proven in production [src1]
AWS DMS Schema Conversion Current (2026) Fully managed, in-console, GenAI-assisted conversion (9 added regions Mar 2026) Preferred over standalone SCT for new AWS migrations [src9]
AWS SCT 1.x (standalone client) Legacy GUI-based, extension packs, embedded SQL conversion Superseded by managed DMS Schema Conversion [src5, src9]
AWS DMS 3.5+ Current CDC, full load + ongoing replication, TrimSpaceInChar setting Use with DMS Schema Conversion for schema [src2]
PgLoader 3.6+ Current Fast parallel data loading with type casting Data-only, no schema conversion
Orafce 4.x Current 100+ Oracle compatibility functions Install with CREATE EXTENSION orafce [src6]
IvorySQL 4.x Current (2025) Full Oracle PL/SQL compatibility, compatible_db toggle, PL/iSQL Open-source Oracle-compatible PostgreSQL [src8]
EDB Advanced Server 17 Current Full Oracle PL/SQL compatibility layer, up to 80% code reduction Commercial; best for heavy PL/SQL codebases [src3]

When to Use / When Not to Use

Use When Don't Use When Use Instead
Oracle licensing costs are unsustainable Application uses Oracle RAC extensively Consider cloud-managed Oracle (OCI)
Moving to cloud-native architecture Heavy dependency on Oracle Spatial 3D features Keep Oracle or use PostGIS for 2D
Team has PostgreSQL expertise Migration timeline is < 3 months for large DB Plan longer timeline or use EDB
Database is < 1 TB with moderate PL/SQL 100% Oracle Forms/APEX application Rewrite frontend first
Compliance requires open-source stack Oracle Advanced Security (TDE, VPD) is critical Evaluate PostgreSQL pgcrypto + RLS first
Need Oracle compatibility in open-source Complex PL/SQL but no budget for EDB Use IvorySQL (free Oracle-compat PG) [src8]

Important Caveats