Oracle to PostgreSQL Migration Guide

Migrating from Oracle to PostgreSQL is rarely blocked by data volume. It is blocked by everything that accumulated around the data: PL/SQL code, proprietary SQL, operational habits, and organizational inertia. This guide maps the real differences, compares the three practical routes, lists the tooling, and lays out a five-phase process with a rollback plan.

Four kinds of migration resistance

Assessments consistently find the same four sources of friction. Naming them up front keeps the project honest about where the effort goes.

1. Proprietary syntax and PL/SQL. CONNECT BY, (+) outer joins, ROWNUM, NVL, DECODE, DUAL, and PL/SQL blocks embedded in application code. Each is individually small; the volume is the problem.
2. Stored procedures and packages. Business logic that lives in the database instead of the application. Oracle packages group procedures, functions, and package-level state into one compilation unit — community PostgreSQL has no direct equivalent, so this is where manual rewriting concentrates.
3. Ecosystem coupling. Tools and features that only exist in the Oracle world: AWR reports, Enterprise Manager, Data Guard, RMAN conventions, Oracle-specific monitoring, and drivers or frameworks configured with Oracle-only behavior. Each integration is a separate migration task even when the SQL ports cleanly.
4. Organizational inertia. Team skills, change-freeze policies, vendor contracts, and the perception of risk. This determines the schedule more than any technical factor, and no tool fixes it.

The Oracle ↔ PostgreSQL compatibility map

The differences below are the ones that actually break migrations. Everything here is documented behavior of both systems.

Data types

SQL dialect

Empty string vs NULL

Oracle treats an empty string as NULL; PostgreSQL distinguishes '' from NULL. This is the single most dangerous semantic difference because it silently changes query results, unique-constraint behavior, and concatenation ('a' || NULL is 'a' in Oracle, NULL in PostgreSQL). Every application path that writes or compares strings needs a review, not just the DDL.

Sequences and identity columns

Both databases have sequences, but the calling convention differs, and migrated data does not advance the sequence counter:

-- Oracle
INSERT INTO orders (id) VALUES (orders_seq.NEXTVAL);

-- PostgreSQL
INSERT INTO orders (id) VALUES (nextval('orders_seq'));

-- Or use a standard identity column instead of an explicit sequence
CREATE TABLE orders (id bigint GENERATED BY DEFAULT AS IDENTITY, ...);

-- After any bulk data load, reset the sequence past the imported max
SELECT setval('orders_seq', (SELECT max(id) FROM orders));

Forgetting the setval step after cutover causes primary-key collisions days or weeks later. Make it part of the cutover runbook.

Synonyms, packages, and schema organization

PostgreSQL has no synonym object. The standard replacements are search_path configuration for name resolution, or views that expose objects from another schema — Ora2Pg exports synonyms as views for this reason.

Oracle packages have no community-PG equivalent either. The common mapping is one schema per package, with package procedures and functions becoming schema-scoped functions. Package-level variables have no clean equivalent; they are usually moved to application state or session-level configuration, which is one reason package-heavy systems are the expensive part of a migration.

PL/SQL → PL/pgSQL: the main rewrite points

PL/pgSQL is structurally similar to PL/SQL, so much procedural code ports with mechanical edits. The recurring rewrite points:

• No PRAGMA directives; exception handling uses BEGIN ... EXCEPTION WHEN ... THEN, and WHEN OTHERS requires care because PG error handling aborts the statement's work within a subtransaction.
• No DBMS_OUTPUT.PUT_LINE — use RAISE NOTICE.
• No package state (above); no autonomous transactions (below).
• %TYPE and %ROWTYPE exist in PL/pgSQL and port directly.
• CREATE OR REPLACE in PostgreSQL cannot change a function's return type; some redeploy workflows need DROP FUNCTION first.

Autonomous transactions

Oracle's PRAGMA AUTONOMOUS_TRANSACTION lets a procedure commit independently of the caller's transaction — commonly used for audit logging that must survive a rollback. PostgreSQL has no autonomous transactions. Workarounds (dblink to self, background workers, moving the logging out of the database) all change semantics. Code that depends on this feature must be redesigned, not translated.

Hints

Oracle's /*+ ... */ optimizer hints are comments to PostgreSQL — silently ignored. PostgreSQL steers the planner through statistics, configuration parameters, and query structure; the pg_hint_plan extension exists for teams that need hint-like control, but the better first move is fixing statistics with ANALYZE and reviewing the query. Any hint that was load-bearing in Oracle needs an explicit plan review after migration.

Three routes

Route 1: Direct migration to community PostgreSQL

Rewrite the schema, port the PL/SQL to PL/pgSQL, and run on standard PostgreSQL. Highest upfront effort, lowest long-term dependency: you end up on the mainline project with the full extension ecosystem, no licensing cost, and no compatibility layer to track across upgrades. This route fits systems with modest amounts of stored code, or organizations that want the Oracle dependency gone entirely rather than emulated.

Route 2: Commercial compatibility — EDB Postgres Advanced Server

EDB Postgres Advanced Server is a commercial PostgreSQL distribution with an Oracle compatibility mode that adds Oracle-compatible data types, keywords, built-in functions, Oracle-style catalog views, and extended MERGE compatibility, plus EDB's own tooling and support. It reduces the rewrite volume for PL/SQL-heavy estates at the cost of a commercial license and a dependency on EDB's release cadence. This route fits large packaged-application estates where rewriting is not economically viable.

Route 3: Open-source Oracle-compatible branch — IvorySQL

IvorySQL (GitHub, Apache 2.0) is a PostgreSQL-based fork that tracks upstream PostgreSQL releases and adds Oracle compatibility on top. Verified against the project's documentation and release notes as of 2026-08:

• Dual-mode initialization: initdb -m pg produces a cluster that behaves as native PostgreSQL; initdb -m oracle (the default) enables the Oracle-compatible mode. The ivorysql.compatible_mode GUC switches between the two at runtime.
• Dual parser / dual port: port 5432 serves native PostgreSQL compatibility; Oracle mode defaults to port 1521 with an independent Oracle parser, so the two syntax sets do not interfere with each other.
• PL/iSQL: a procedural language that accepts Oracle PL/SQL syntax, including Oracle-style packages; the ivorysql_ora extension supplies Oracle built-in functions.
• IvorySQL 5.0 is built on PostgreSQL 18.

This route fits teams that want Oracle syntax compatibility without a commercial license. The trade-offs are a smaller vendor ecosystem and the need to test both modes — Oracle-mode behavior is not identical to upstream PostgreSQL behavior, and that difference is exactly what must be covered in migration testing. See PostgreSQL forks and compatible databases for how IvorySQL sits in the broader compatibility landscape.

Toolchain

Ora2Pg — assessment and export

Ora2Pg is the standard open-source starting point. Two capabilities matter most:

• Assessment report: run with SHOW_REPORT and --estimate_cost, it scans the Oracle database and produces a report (text, HTML, CSV, or JSON) listing every object type, its conversion status, and a migration cost estimate in person-days. This is the factual basis for scoping the project before any code moves.
• Schema and data export: per-object-type exports (tables, views, sequences, functions, procedures, packages, triggers, partitions, synonyms) with automatic PL/SQL-to-PL/pgSQL conversion that must still be reviewed by hand. Data exports as COPY or INSERT, with parallel extraction and direct import into PostgreSQL.

ora_migrator — FDW-based migration

ora_migrator (CYBERTEC) is a PostgreSQL extension built on oracle_fdw and db_migrator. Instead of exporting files, it creates foreign tables against the Oracle database and migrates schema and data through SQL functions, including a migration_cost_estimate view for assessment and data-validation functions that flag problem rows (e.g., zero bytes or bad encoding in string columns) before migration. It also provides trigger-based catch-up replication from Oracle to PostgreSQL for near-zero-downtime cutovers.

AWS DMS Schema Conversion

DMS Schema Conversion is a managed AWS feature (built on the AWS SCT engine) that converts an Oracle schema to Aurora PostgreSQL or RDS for PostgreSQL. It produces a conversion assessment report showing what converts automatically versus what needs manual work, and applies the converted code to the target or exports it as SQL. It converts schema only — data movement is a separate AWS DMS task. Relevant when the target is AWS-managed PostgreSQL; less so for self-managed deployments.

Near-zero downtime with CDC

Bulk export/import implies downtime proportional to data size. CDC removes most of it: load the initial snapshot, then stream changes until cutover.

• Debezium Oracle connector: reads Oracle redo logs via the LogMiner adapter (XStream and OpenLogReplicator adapters also exist), takes an initial snapshot, then streams row-level changes into Kafka. A sink consumes them into PostgreSQL.
• AWS DMS ongoing replication (CDC): the managed equivalent for targets on AWS.
• ora_migrator replication (above): the trigger-based, extension-only option with no extra infrastructure.

Whichever CDC path you choose, cutover ends with two steps that are easy to forget: verify the change stream has drained to zero lag, and reset every sequence with setval against the migrated data (see the sequence section above). Replication-related operational detail is covered in Replication and upgrades.

A five-phase migration process

1. Assess. Run the Ora2Pg assessment report (or ora_migrator's cost estimate, or the DMS assessment) against the production schema. The output drives the route decision: object counts, conversion coverage, and the person-day estimate tell you whether direct migration, a compatibility layer, or a managed conversion is realistic. Inventory application-side Oracle coupling (driver settings, SQL in code, reporting tools) at the same time — the database report does not see it.
2. Pilot. Pick one bounded schema or service, migrate it end to end, and run it in production-like conditions. The pilot calibrates the assessment numbers against reality and surfaces the semantic traps — empty strings, dates, sequences, hints — at small scale.
3. Dual-run / canary. With CDC running, direct a slice of real traffic (or a full shadow workload) to PostgreSQL and compare results against Oracle: query outputs, transaction outcomes, and performance under real load. This is where you find out what the pilot missed.
4. Cut over. Drain CDC lag to zero, stop writes on Oracle, reset sequences, run the final validation, then point the application at PostgreSQL. Keep the Oracle instance read-only and intact — it is your rollback target. Apply production-hygiene rules from safe migrations to every DDL step of the cutover itself.
5. Rollback plan and validation checklist. Define the rollback trigger and the data-flow direction (if writes have occurred on PostgreSQL since cutover, rollback needs reverse replication or a reconciliation procedure — decide this before cutover, not during an incident). The minimum validation checklist:
   • Row counts per table (Ora2Pg's TEST / TEST_COUNT actions automate the diff).
   • Constraints: primary keys, foreign keys, unique, check, NOT NULL — count and enabled state on both sides.
   • Indexes: count, definitions, and validity.
   • Sequences: setval applied and nextval returns values above the migrated maximum.
   • Key transactions: a fixed set of critical business queries and procedures run on both systems with compared results.
   • Data spot checks on type-sensitive columns: dates, numerics with scale, empty strings, CLOB/BLOB content.

The organizational layer

Tools decide how fast the conversion goes; engineering discipline decides whether the migration succeeds. Budget for the rewrite and validation work, keep the rollback path real, and treat "de-O" as a program with phases, owners, and exit criteria — not a tool purchase.

Related pages: PostgreSQL forks and compatible databases, PostgreSQL vs MySQL, comparison FAQ.