Oracle to PostgreSQL migration: the complete 2026 guide

If you are reading this, you are probably staring down an Oracle migration that someone has decided is happening. The license renewal is up, the budget pressure is real, and the engineering team has been told to make it work. Now you need a plan that does not melt down in month seven.

This is the working guide we wish we had the first time we ran one of these. It covers the whole pipeline (assessment, schema conversion, PL/SQL translation, dependency-ordered deployment, data transfer, validation, and cutover) with a focus on what actually breaks and how to handle it. Code samples are real. Cost numbers are sourced. Tool trade-offs are honest.

Skim it for what you need, or read it end to end before you commit to a plan. Then go run a free trial on a representative subset of your real schema, because no guide replaces seeing the conversion output for your own PL/SQL.

Why migrate from Oracle to PostgreSQL
The seven phases of an Oracle migration
Tool landscape (Ora2Pg, AWS SCT, EDB Migration Portal, Trekport, others)
Oracle to PostgreSQL data type mappings
PL/SQL to PL/pgSQL: the constructs that break naive tools
Dependency-ordered deployment
Data transfer at scale
Validation and cutover
Realistic timelines and costs
The 100 percent deployment standard
FAQs

1. Why migrate from Oracle to PostgreSQL

The reasons fall into three buckets.

Cost. Oracle Database Enterprise Edition lists at $47,500 per processor core. Annual support runs 22 percent of license, so a 16-core deployment costs roughly $928,000 in year one and $167,200 every year after. The same workload on PostgreSQL costs $0 to license and $20,000 to $40,000 per year in commercial support (EnterpriseDB, Crunchy Data, Cybertec). Year-one savings on a typical mid-market deployment are around $890,000.

Freedom. PostgreSQL is open source under a permissive BSD-style license. You are not locked into a single vendor's release cadence, support contract, or audit practices. Your DBA can read the source code. Your security team can sign off on it without an NDA. Your platform team can run it on commodity hardware, in any cloud, on bare metal, or air-gapped.

Pace. PostgreSQL ships features faster than Oracle Enterprise Edition in most categories. Logical replication, declarative partitioning, JSON operators, native MERGE, parallel query, hash partitioning, foreign data wrappers, full-text search, PostGIS, and extensions like TimescaleDB, Citus, and pgvector all originated in or matured first in PostgreSQL.

The economic argument is well documented. The harder question is whether the migration itself is feasible at acceptable cost and risk for your specific estate. The rest of this guide addresses that.

2. The seven phases of an Oracle migration

Every Oracle to PostgreSQL migration we have seen, successful or not, goes through seven phases. Skipping any of them produces problems later.

Phase 1: Assessment

Before you commit to a migration, you need to know what you are migrating. The assessment phase produces a complete inventory of your Oracle estate:

Schemas, tables, sequences, indexes, materialized views
Functions, procedures, packages, triggers
Constraints (primary keys, foreign keys, check, unique)
Synonyms, grants, roles
Database links, scheduled jobs (DBMS_SCHEDULER), partitions
Custom Oracle types (object types, collections, nested tables)
Oracle-specific constructs that need attention (BULK COLLECT, FORALL, PRAGMA AUTONOMOUS_TRANSACTION, hierarchical queries, MODEL, MERGE)

The output is a coverage report: how many objects of each kind, how many lines of PL/SQL, which constructs appear, which packages reference which others. This drives the time-and-cost estimate for the project.

Tools that produce this report: Ora2Pg --type SHOW_REPORT, AWS SCT assessment report, EDB Migration Portal assessment, Trekport's catalog extractor + report generator.

Phase 2: Schema conversion

The schema is the easier half. DDL conversion translates Oracle CREATE statements to PostgreSQL CREATE statements: tables, indexes, sequences, views, materialized views, types, synonyms.

The hard parts are:

Data type mappings (especially NUMBER, DATE, TIMESTAMP WITH LOCAL TIME ZONE, XMLTYPE, BFILE, SDO_GEOMETRY)
Empty string vs NULL semantics (Oracle treats '' as NULL; PostgreSQL does not)
Default expressions that reference Oracle-specific functions
Index types Oracle has that PostgreSQL approximates differently (function-based indexes work, but bitmap indexes do not)
Storage clauses, tablespaces, and partitioning syntax

Most tools handle 90 to 100 percent of schema conversion automatically. Trekport ships at 100 percent for the constructs above.

Phase 3: PL/SQL conversion

This is the phase where projects fail. Oracle PL/SQL is a substantial programming language with hundreds of built-in packages. PostgreSQL PL/pgSQL is intentionally smaller. Some constructs map directly. Others need wholesale rewriting.

The five categories that cause 90 percent of pain:

Packages (PostgreSQL has no native equivalent, must be schemas)
Compound triggers (PostgreSQL fires statement and row triggers separately)
FORALL and BULK COLLECT (different bulk semantics)
Autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION has no clean PG equivalent)
Oracle built-in packages (DBMS_*, UTL_*) without Postgres analogs

We covered each in detail in our PL/SQL to PL/pgSQL deep dive. The short version: most tools handle the first three poorly, the fourth not at all, and the fifth via the orafce extension (with gaps).

Phase 4: Deployment

Once you have converted DDL and PL/pgSQL on disk, you have to deploy it into a target PostgreSQL database in dependency order. This is where naive tools fail.

The minimum dependency-aware ordering:

Schemas, roles, grants
Sequences (referenced by table DEFAULTs)
Custom types (referenced by table columns)
Tables (without foreign keys)
Functions and procedures (in topologically-sorted call order)
Package signatures, then bodies (handles circular references)
Views (multi-pass to handle view-on-view chains)
Materialized views
Triggers
Indexes
Synonyms
Foreign keys (last, after all referenced tables exist)

Most tools deploy in catalog order or alphabetical order, expecting you to script the dependency resolution yourself. Trekport ships a 14-phase deployment pipeline that handles all of this automatically and is idempotent: failed phases can be retried without manual cleanup.

Phase 5: Data transfer

The data transfer phase moves the actual rows from Oracle to PostgreSQL. For small databases, this is a simple table-by-table COPY. For multi- terabyte estates, it requires:

Parallel COPY (fast for non-temporal tables)
Batched INSERT (necessary for tables with temporal column types)
Backpressure-aware channel pipeline (so the source does not overwhelm the target)
Row-count checkpointing (for resumability)
Constraint deferral (FK checks at end, not per-row)

Realistic throughput on commodity hardware: 50 to 500 GB per hour depending on row width, parallelism, and target write capacity. A 1 TB estate transfers in 2 to 20 hours.

Phase 6: Validation

After deployment and data transfer, you validate that the target matches the source. Three layers of validation:

Object-level: every Oracle object has a corresponding PostgreSQL object, deployed without error.
Schema-level: row counts match per table, column types match per column, constraints are enabled and identical.
Functional: PL/pgSQL functions return identical results to PL/SQL originals on a representative input set.

The first two are mechanical. The third requires test data and effort, but is the only way to catch subtle conversion bugs (Oracle empty-string-as- NULL, Oracle DATE-with-time vs PG DATE, NUMBER overload resolution ambiguity).

Trekport's validator runs all three layers and produces an HTML and PDF report you can hand to compliance and audit teams.

Phase 7: Cutover

The cutover is the moment you redirect application traffic from Oracle to PostgreSQL. Two strategies:

Big bang: schedule a maintenance window, sync the final delta, flip the connection string, validate, done. Works for systems with predictable downtime windows.
Dual-write with logical replication: applications write to both databases for a transition period via change data capture (CDC). Cut reads over first, then writes. Works for 24x7 systems that cannot tolerate downtime.

CDC tools that pair well with Trekport: Striim, Debezium with the Oracle LogMiner connector, AWS DMS replication tasks. For one-shot migrations with a maintenance window, Trekport's parallel data transfer is usually sufficient on its own.

3. Tool landscape

The Oracle to PostgreSQL migration tool market has roughly five categories:

Open-source assessment + conversion

Ora2Pg: the standard open-source Perl tool. Free under GPL. Two-decade-old codebase. Strong on schema, weak on PL/SQL (per its own docs). See our Ora2Pg comparison.

Cloud-vendor tools

AWS Schema Conversion Tool (SCT): free desktop tool tied to AWS DMS. Up to 90 percent automated conversion per AWS. Wraps untranslated BULK COLLECT in compiling-but-empty PL/pgSQL shells (per AWS's own blog). See our AWS SCT comparison.
Azure Database Migration Service (DMS): schema-only assessment for Oracle, weak on PL/SQL conversion. Tied to Azure target.

Forked-Postgres vendors

EnterpriseDB Migration Portal: free assessment + conversion via web portal. Output targets EDB Postgres Advanced Server (paid Postgres fork with built-in Oracle compatibility), not vanilla PostgreSQL. Locks you into the EDB distribution.

CDC / replication-only tools

Striim, Quest SharePlex, Bryteflow, Oracle GoldenGate: handle the data movement and CDC piece. None of them convert schema or PL/SQL.

Conversion-focused commercial tools

Trekport: AST-based conversion, 14-phase dependency-ordered deployment, 100 percent deployment mandate, vanilla PostgreSQL target, desktop app, public pricing.
Splendid Cortex, Ispirer SQLWays, Cybertec Migrator: each take different approaches; all "contact sales" pricing.

Picking a tool

Decision tree:

Schema only, under 100 tables, no PL/SQL: Ora2Pg, free, fine.
AWS-hosted target, willing to live with manual cleanup: AWS SCT, free, expect 10 to 30 percent manual translation.
Locked into EDB Postgres Advanced Server: EDB Migration Portal, free assessment, paid runtime.
Anything else (vanilla PG target, real PL/SQL, dependency-ordered deployment, air-gapped, calendar matters more than tool license): Trekport, $49 per month, free trial up to 10 tables.

4. Oracle to PostgreSQL data type mappings

The mapping table below covers every Oracle scalar, LOB, and complex type you are likely to hit. Direct mappings are unambiguous. Mappings flagged gotcha need attention during conversion.

| Oracle type | PostgreSQL target | Notes | |---|---|---| | VARCHAR2(n) | varchar(n) or text | Oracle treats '' as NULL; PG does not. Test application code. | | NVARCHAR2(n) | varchar(n) | UTF-8 by default in PG. | | CHAR(n) | char(n) | Identical, but trailing-space comparison semantics differ. | | NUMBER (no precision) | numeric | numeric performs poorly on joins; prefer bigint if scale = 0. | | NUMBER(p) | numeric(p) or bigint | bigint is faster for join columns. | | NUMBER(p,s) | numeric(p,s) | Direct mapping. | | DATE (Oracle includes time) | timestamp(0) | Common bug source. PG date has no time portion. | | TIMESTAMP | timestamp | Direct. | | TIMESTAMP WITH LOCAL TIME ZONE | timestamptz | PG timestamptz matches LTZ semantics. | | TIMESTAMP WITH TIME ZONE | (no exact match) | Oracle stores TZ literally; PG normalizes to UTC. Decide per-column. | | INTERVAL DAY TO SECOND | interval | Native PG interval. | | CLOB | text | PG text is unbounded. | | NCLOB | text | UTF-8. | | BLOB | bytea | Bytea has 1 GB practical limit; use Large Objects for bigger. | | BFILE | text + filesystem ref | No PG equivalent for external LOBs. | | RAW(n) | bytea | Length is informational only in PG. | | XMLTYPE | xml | Native PG xml type with xpath() for path navigation. | | ROWID | ctid (volatile) or sequence-backed PK | ctid is volatile; never persist. Trekport substitutes a sequence-backed identity column. | | SDO_GEOMETRY | geometry (PostGIS) | Requires PostGIS extension at target. | | REF / OBJECT / TABLE OF | composite type / array | Mapped via PG composite type system. Method dispatch lost. | | ANYDATA | (no equivalent) | Manual rewrite required. |

5. PL/SQL to PL/pgSQL: the constructs that break naive tools

This list is not exhaustive. It is the set we have seen break the most migrations. For each, the fix is in our PL/SQL deep dive.

PACKAGES with package-level state. PostgreSQL has no packages. The workaround is schemas-as-packages plus set_config()/current_setting() for state.
COMPOUND TRIGGERS with shared local state across phases. PG fires statement and row triggers separately. Trekport synthesizes shared state via session GUCs.
FORALL and BULK COLLECT. The constructs AWS SCT silently leaves in compiling-but-empty PL/pgSQL shells. Trekport translates to set-based UPDATE patterns.
PRAGMA AUTONOMOUS_TRANSACTION. No clean PG equivalent. Translate to pg_background or refactor to deferred trigger / LISTEN-NOTIFY.
Built-in packages: DBMS_OUTPUT, DBMS_PIPE, DBMS_RANDOM, DBMS_LOB, DBMS_SQL, UTL_FILE. The orafce extension covers most.
CONNECT BY hierarchical queries. Translate to WITH RECURSIVE.
MERGE statements. PG 15+ has native MERGE; pre-15 use INSERT ... ON CONFLICT.
ROWID / ROWNUM. ctid is volatile in PG; substitute a sequence-backed PK and ROW_NUMBER() OVER (...) for ROWNUM.
PIVOT / UNPIVOT. Rewrite as CASE aggregations or use the tablefunc extension.
(+) outer-join operator. Translate to ANSI JOIN syntax.
Synonyms. PG uses search_path and views as synonym substitutes.
MODEL clause. No PG equivalent. Rewrite as window functions or external code.

6. Dependency-ordered deployment

A working migration deploys objects in the right order. The minimum order is the 14 phases below. Skipping or reordering causes loud failures (relation does not exist, function does not exist) or worse, silent ones (FK violations on data load, trigger fires before its target function exists).

| Phase | Object class | Why this order | |---|---|---| | 01 | Schemas, roles, grants | Containers first, authorization in advance. | | 02 | Sequences | Referenced by table DEFAULT clauses. | | 03 | Custom types | Referenced by table columns. | | 04 | Tables (no FKs) | Children before parents in FK order is impossible without circular handling, so create all tables first, add FKs at the end. | | 05 | Compatibility extensions | orafce and Trekport adapters. Functions in PL/pgSQL may reference these. | | 06 | Standalone functions | Topologically sorted by call graph. | | 07 | Package signatures | Header-only, so cross-package refs resolve. | | 08 | Package bodies | Implementations attached after all signatures exist. | | 09 | Views | Multi-pass to resolve view-on-view chains. | | 10 | Materialized views | After views, since MVs commonly reference them. | | 11 | Triggers | After all callable objects exist. | | 12 | Indexes | Including functional and partial indexes. | | 13 | Synonyms | Implemented as views or search_path entries. | | 14 | Foreign keys, check constraints | Final pass to enable referential integrity. |

This is the sequence Trekport runs by default. Each phase is transactional and retryable. Failures at any phase produce a precise diagnostic and do not leave the target in a partial state.

7. Data transfer at scale

For small databases, data transfer is unremarkable. For terabyte-scale enterprise estates, throughput, parallelism, and resumability all matter.

The mechanics:

COPY: PostgreSQL's bulk-load primitive. Fast for non-temporal tables. Trekport pipes Oracle row data directly into COPY over a parallel set of connections.
Parallelism: 4 to 16 parallel streams is typical. More than 16 saturates either network bandwidth or target write IOPS.
Backpressure: a buffered channel between the Oracle reader and the PostgreSQL writer. Without it, slow targets cause the source to OOM.
Temporal tables: tables with TIMESTAMP columns sometimes need batched INSERT instead of COPY due to PG COPY parsing of timestamp formats. Trekport detects these and switches modes per table.
Resumability: row-count checkpoints per table allow restart after network failures.
Constraint deferral: foreign keys are added in Phase 14, after data is loaded, so per-row FK checks do not slow the load.

Realistic throughput on commodity hardware: 50 to 500 GB per hour depending on row width, parallelism, and target write capacity. A 1 TB estate transfers in 2 to 20 hours.

8. Validation and cutover

Validation is the part most teams underinvest in. The cost of catching a conversion bug post-cutover is two orders of magnitude higher than catching it pre-cutover. Three layers:

Object-level validation

Does every Oracle object have a corresponding PostgreSQL object? Did deployment succeed for each? Trekport's deployment report tracks this automatically and produces a per-phase pass/fail summary.

Schema-level validation

Row counts match per table. Column types match per column. Constraints are enabled and identical. Indexes exist on the corresponding columns.

A simple SQL pattern works:

-- Per-table row count check
SELECT 'employees' AS table, COUNT(*) AS pg_count FROM employees;
-- Compare to Oracle source via dblink or external script

Trekport runs this automatically across all migrated tables and produces a diff report.

Functional validation

PL/pgSQL functions return identical results to PL/SQL originals on a representative input set. This is the layer that catches:

Empty-string vs NULL behavior differences
DATE-with-time vs DATE-without-time differences
Numeric overload resolution differences
Sort order differences (Oracle's NLS_SORT vs PG's locale)

The mechanism: identify the top N most-called functions in production (via Oracle AWR or application logs), capture representative inputs and expected outputs, replay them against PostgreSQL, diff results.

Cutover

Once validation passes, the cutover itself is mechanical:

Stop application writes to Oracle (or enable dual-write via CDC).
Run a final delta sync (Trekport's incremental data transfer mode, or a CDC tool).
Run validation one more time on the final state.
Update application connection strings.
Restart application services.
Watch error rates for 24 to 72 hours.
Decommission Oracle (or keep it as a read-only fallback for 30 days).

For systems with downtime tolerance, this is a 30 to 120 minute maintenance window. For 24x7 systems, the dual-write CDC pattern extends the cutover over days or weeks but eliminates downtime.

9. Realistic timelines and costs

This is the line item every estimate gets wrong. Here is the realistic range for a 30,000-object enterprise estate, by tool path:

Hand-written conversion

12 to 24 months elapsed
2 to 6 senior engineers full-time
$300,000 to $1.5M professional services contract
Total cost: $1.0M to $2.5M

Ora2Pg + manual cleanup

9 to 18 months elapsed
2 to 4 engineers full-time
$0 tool license, but $200,000 to $800,000 in labor
Total cost: $400,000 to $1.5M

AWS SCT + manual cleanup

9 to 18 months elapsed
Same labor profile as Ora2Pg
$0 tool license, AWS DMS hourly compute
Total cost: $400,000 to $1.5M

Trekport

1 to 3 months elapsed
1 to 2 engineers part-time during the migration window
$49 to $299 per seat per month
Total cost: $50,000 to $300,000

The differential is not a 2x or 3x improvement. It is an order of magnitude. And the timeline savings often matter more than the dollar savings: every month you delay cutover is another month of paying both Oracle support and the migration team. We covered the full TCO model in Oracle to PostgreSQL TCO.

10. The 100 percent deployment standard

If you take one thing from this guide, take this: do not accept "up to 90 percent automated" as a finish line. The remaining 10 percent is not the easy 10 percent. It is 100 percent of the hard work: packages, compound triggers, dependency-ordered objects, BULK COLLECT, autonomous transactions, the long tail.

The vendor pitch you will hear most often is "automated conversion plus manual review for the remainder." That sounds reasonable. In practice, the manual review is where projects die, because:

The manual review work is unscoped at the start.
The engineers who can do it are scarce.
Each manual conversion is its own correctness problem.
Validation does not catch silent failures (the BULK COLLECT empty shell pattern from AWS SCT is the textbook example).

The 100 percent deployment standard inverts this. Every object is either fully equivalent PostgreSQL, backed by a verified extension, or explicitly flagged with a documented workaround. There is no manual SQL editing on the long tail because the long tail is part of the tool's scope.

This is the standard Trekport ships against. It is the standard we think the market should adopt. And it is the only standard that makes the calendar-time and dollar-cost savings of an Oracle migration actually materialize.

11. FAQs

How long will the migration take? 1 to 3 months with Trekport for a typical enterprise estate. 12 to 24 months with hand-written conversion or manual-cleanup tools. Application-side validation often takes longer than the database conversion itself.

Can I roll back? Yes. Trekport runs against your PostgreSQL target without modifying the Oracle source. If validation fails, the Oracle database is untouched and the deployment is idempotent.

Do I need orafce? Yes, in most cases. orafce is the standard open- source Oracle compatibility layer for PostgreSQL. It provides PG implementations of NVL, DECODE, SYSDATE, DBMS_OUTPUT, and many others. Trekport's converted output uses orafce. EDB customers do not need it because EDB ships its own (paid, proprietary) compatibility layer.

What about my application code? Most application code (JDBC, SQLAlchemy, Hibernate, ActiveRecord, Pro*C) needs only connection-string changes. Stored procedure call sites preserve their signatures via Trekport's package-as-schema convention. Realistic application-side effort: 10 to 30 engineer-days for a typical enterprise application.

What if my schema has unusual constructs? Talk to us. We have probably seen it. Trekport's coverage matrix is documented in the docs, and we add explicit support for new constructs on a per- release basis.

Can I migrate to Aurora PostgreSQL or RDS for PostgreSQL? Yes. Trekport works with any PostgreSQL 14+ target, including AWS RDS, Aurora PostgreSQL, GCP Cloud SQL, Azure Database for PostgreSQL, and on-premises PostgreSQL.

Is there a free trial? Yes. Download Trekport Studio and run it against any Oracle source with up to 10 tables. Full conversion, full deployment, full validation report. No credit card required.

Get started

Download Trekport Studio. Run the free trial against a representative subset of your Oracle schema. Read the conversion report. Then come back and run it against the rest.

If you want a guided walkthrough, talk to us. We do not run a sales process. We run a conversion against your real source and walk you through the output.