Oracle to PostgreSQL Migration: A Guide to Code, Data, and Validation

What does an Oracle to PostgreSQL Migration Involve?

An Oracle to PostgreSQL migration moves a database estate’s schema, PL/SQL code, data, and every piece of application code that talks to Oracle onto PostgreSQL, and proves the result behaves the same. Converter tools automate most schema translation and the data copy, and leave stored code, application SQL, and silent engine differences to manual work.

AWS’s Oracle to PostgreSQL walkthrough states that its Database Migration Service does not migrate secondary indexes, sequences, default values, stored procedures, triggers, synonyms, or views [1]. Those objects go through a separate schema conversion tool.

This guide is for engineering leaders, data platform leads, and architects scoping an Oracle exit, and for the CIOs and CTOs deciding whether to fund one.

The conversion work is the same whether PostgreSQL runs on-premises or as a managed service in any cloud. An Oracle exit is a database modernization program on any target, and hosting is a separate decision.

Teams leaving Oracle at the runtime layer as well often pair the database move with an Oracle Java to OpenJDK migration.

PostgreSQL as an Oracle Replacement: Go/No-Go Decision by Workload

PostgreSQL can replace Oracle for a wide range of transactional and reporting workloads. Make the go/no-go decision per workload, since one blocker in one application can change the economics of a portfolio-wide move.

Three blockers override licensing math in go/no-go calls.

• Vendor certification. A packaged application supported only on Oracle stays on Oracle, whatever the conversion cost. Moving it voids vendor support and puts every future upgrade on the internal team.
  
• RAC-dependent availability. Applications designed around Oracle RAC’s shared-disk clustering need a new availability design on PostgreSQL, usually streaming replication with automated failover. Scope the replacement as a separate architecture workstream.
  
• Deep package state and Oracle-only features. Heavy use of package variables, Advanced Queuing, or APEX requires redesign of the affected modules. Oracle Spatial maps to PostGIS with function-level rewrites.

The business case nets licensing savings against conversion effort, test effort, and the cost of running two engines during the transition.

What Has to Move in an Oracle to Postgres Migration

An Oracle to Postgres migration touches six layers. Tool coverage is high for schema and data, partial for PL/SQL, and low for every layer that sits outside the database.

The application layer is where Oracle migrations overrun. Oracle dialect sits in Java strings, ORM configuration, report definitions, and shell scripts spread across repositories the database team does not own.

Little of it appears in a schema conversion report, so it has to be inventoried separately before any estimate is credible.

Oracle to Postgres Migration Challenges, Ranked by Risk

Oracle to Postgres migration challenges are easier to plan for when ranked by how they fail. Silent differences rank first, because converted code runs and returns wrong results. Conversion hotspots rank second, because they fail loudly and absorb the manual effort. Performance ranks third, because it surfaces late, under production load.

Silent Behavioral Differences Between Oracle and PostgreSQL

Each difference below passes compilation and routine functional tests and still changes query results. The table gives the business impact, and the SQL under each item shows the difference on both engines.

Empty strings are values in PostgreSQL. Required-field checks and missing-value reports stop matching after cutover. Oracle treats a zero-length string as NULL [2], and PostgreSQL stores it as an empty string.

-- tax_code is NOT NULL on both engines
INSERT INTO invoice (id, tax_code) VALUES (1, '');
-- Oracle:     rejected (ORA-01400)
-- PostgreSQL: accepted

-- After cutover, the app writes '' for "no middle name"
SELECT COUNT(*) FROM customer
WHERE middle_name IS NULL;
-- Oracle:     counts those rows
-- PostgreSQL: skips them

The fix is one rule for the target, applied to data, constraints, and code together. Either convert empty strings to NULL at write time, with NULLIF in code or a trigger, or keep them and rewrite every IS NULL check to cover both cases.

NULL concatenation returns NULL. A full name or address built from parts returns NULL for every row with a missing part. Oracle’s concatenation operator ignores NULL operands [2], and PostgreSQL’s || returns NULL when any operand is NULL.

-- Oracle
SELECT 'Order ' || NULL || ' shipped' FROM dual;
-- Result: 'Order  shipped'

-- PostgreSQL
SELECT 'Order ' || NULL || ' shipped';
-- Result: NULL

Replace these expressions with concat() or concat_ws(), which skip NULL arguments, or wrap each operand in COALESCE.

Oracle DATE loses its time of day. Order, event, and audit timestamps lose their time with no error. Oracle DATE stores the date and the time to the second, and PostgreSQL DATE stores the date only. Converters default to a timestamp type, so the risk comes from hand-written or overridden DATE-to-DATE mappings.

-- Oracle
SELECT TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI:SS')
FROM orders WHERE id = 42;
-- Result: 2026-09-24 14:35:10

-- PostgreSQL, column mapped to DATE
SELECT order_date FROM orders WHERE id = 42;
-- Result: 2026-09-24

Map Oracle DATE to timestamp(0) unless profiling confirms a column never carries a time, and check custom type maps before the first load. Date arithmetic changes too, since Oracle date subtraction returns a number of days and PostgreSQL timestamp subtraction returns an interval.

PL/SQL Constructs That Need Manual Conversion

Converters translate routine PL/SQL well. Manual effort clusters in five constructs, and a count of each in the inventory is a stronger effort signal than line count alone.

• Packages and package variables. PostgreSQL has no packages. Its documentation recommends schemas to group functions and notes that there are no package-level variables either [3]. Session state held in package variables has to move to temporary tables, custom configuration settings, or the application tier.
  
• Autonomous transactions. PostgreSQL has no PRAGMA AUTONOMOUS_TRANSACTION. Audit and error-logging routines that commit independently need dblink, pg_background, or a redesign. Both extensions run the work in a separate session, which adds connection or worker overhead.
  
• Dynamic SQL built in strings. SQL assembled at runtime is invisible to static converters. It converts only when someone reads the string-building logic. PostgreSQL’s EXECUTE takes values through USING, and identifiers need quote_ident or format() [3].
  
• Hierarchical and paging queries. CONNECT BY rewrites to WITH RECURSIVE. Oracle assigns ROWNUM before ORDER BY, so a mechanical swap to LIMIT can change which rows a page returns.
  
• Built-in packages. UTL_FILE and DBMS_SCHEDULER have no core equivalents, and file handling and scheduling logic usually move out of the database. DBMS_OUTPUT and most DBMS_LOB calls map to RAISE NOTICE and native string and bytea functions, and the Orafce extension covers part of the rest.

Oracle to PostgreSQL Performance Differences

Three causes recur behind post-cutover performance regressions.

• Planner differences. PostgreSQL’s planner makes different join and index choices from Oracle’s optimizer on the same query. Statistics targets, work_mem, and index design need tuning per workload.
  
• Oracle hints stop working. Inline /*+ INDEX */ hints are comments to PostgreSQL, and the project has stated it will not implement hints the way other databases do [4]. The pg_hint_plan extension and the pg_plan_advice module arriving in PostgreSQL 19 use their own syntax, so hinted queries need rewriting or new indexes.
  
• NUMBER mapped to NUMERIC everywhere. Oracle NUMBER columns used as keys and counters perform better as integer or bigint. A blanket NUMERIC mapping slows joins, grows indexes, and forces casts in application code.

Capture a baseline of the top queries and batch windows on Oracle before conversion. Replay the same workload on PostgreSQL before cutover, when regressions can be fixed without user impact.

Start a $0 Modernization Assessment

Dependency Map

Risk Heatmap

Modernization Plan (3-5 Days)

+more

Full name

Work email

Claim my $0 Assessment

Oracle to PostgreSQL Converter Options, Including AI-Assisted Conversion

An Oracle to PostgreSQL converter handles schema translation well and PL/SQL partially. The table lists what each category converts and what it leaves for manual work.

Automation rates published by vendors count code objects converted, which is a different unit from effort or validated behavior. The PostgreSQL wiki’s tool directory lists Cortex’s claim of an average of 80% of code objects converted automatically [5]. Package state, dynamic SQL, and built-in packages concentrate in the remaining objects, so they carry a larger share of the manual hours.

Ora2Pg is the community default. It is free and maintained, converts schema and data reliably, and is a sound starting point before any commercial tool is evaluated.

How to Migrate Data From Oracle to Postgres

The method used to migrate data from Oracle to Postgres depends on the downtime the business will accept at cutover.

A maintenance window is the lower-risk default. A zero downtime migration adds design, monitoring, and testing cost that needs a contractual SLA behind it.

Three data issues surface during the load itself.

• Precision. NUMBER columns without declared precision can hold values a narrower target type rounds or rejects.
  
• Character sets. Legacy Oracle databases in single-byte character sets are usually converted to UTF-8 during the move, and invalid bytes stop the load.
  
• LOBs. CLOB and BLOB columns map to text and bytea, and their size drives load time more than row count does. Values over 1 GB need PostgreSQL large objects or external storage.

Oracle to PostgreSQL Migration Validation for Data, Code, and Business Outputs

Validation for an Oracle to PostgreSQL migration runs at three levels. Row counts and checksums cover data, and code and business-output comparisons cover behavior, since every silent difference above passes a row count.

• Data. Row counts per table and partition, then column-level hashes computed after normalizing types and timestamp precision on both sides. Empty-string handling is checked as its own test.
  
• Converted code. Each converted function, procedure, and trigger runs against the same inputs on Oracle and PostgreSQL, and the outputs are compared. The empty-string, concatenation, and DATE cases become fixed test cases.
  
• Business outputs. Reports, batch results, invoices, and exports generated from both systems for the same period are compared line by line. A report total that differs by one cent points to a rounding or type issue upstream.

Data-level checks follow the same techniques used in any data migration validation, applied table by table. Code and business-output comparison is the part specific to an engine change, and it needs the Oracle system available as a reference until sign-off.

Oracle to Postgres Migration Step by Step

The eight phases below lay out an Oracle to Postgres migration step by step. Phases 1, 5, and 7 carry the most unplanned effort when they are compressed.

1. Inventory and effort sizing. Count PL/SQL objects by type, packages with state, dynamic SQL, and built-in package calls, plus every application, script, and job that connects to Oracle. A legacy system audit covers the application side of this inventory.
   
2. Go/no-go per workload. Assign move, rewrite, retire, or stay to each application before conversion budget is committed.
   
3. Schema and type mapping. Decide the DATE, NUMBER, empty-string, and identifier-case rules once, and apply them to schema, code, and data together. Keep identifiers unquoted and lowercase, since quoted uppercase names in converted DDL break application SQL.
   
4. PL/SQL conversion. Run the converter, then work the hotspot list. Decide per package whether logic stays in PL/pgSQL or moves to the application tier.
   
5. Application conversion. Treat Java and .NET data access, embedded SQL, ORM dialect settings, reports, and scripts as a separate workstream with its own owners and tests.
   
6. Data rehearsal loads. Run full loads into a test environment at least twice, timing each one and validating at all three levels.
   
7. Performance baselining. Replay the top queries and batch windows captured on Oracle and fix regressions before cutover.
   
8. Cutover and rollback. Freeze Oracle writes, run the final load or CDC switch, validate, and keep a tested path back to Oracle until business outputs are signed off.

The phases overlap. Application conversion depends on the naming and type rules from phase 3, so starting it before those rules settle produces code that targets objects still in flux.

Gen AI for Oracle to PostgreSQL Code Conversion and Output Verification

Gen AI speeds up pattern-heavy Oracle to PostgreSQL conversion spread across many files, with verification designed in for logic that depends on runtime state.

AI conversion performs well on three kinds of work.

• Translating PL/SQL syntax and idioms, such as NVL to COALESCE, DECODE to CASE, and sequence calls.
  
• Finding and rewriting Oracle SQL embedded in Java, .NET, scripts, and report definitions.
  
• Explaining what a legacy package does, which shortens human review.

AI conversion needs human verification in four areas.

• Package state and session behavior that only appear at runtime.
  
• Concurrency and transaction semantics, including autonomous transactions and locking.
  
• SQL assembled from strings at runtime, where the final statement never appears in source.
  
• Semantic differences with no syntax change, such as empty-string handling.

Microsoft’s PostgreSQL extension for VS Code runs schema conversion first and application conversion second, compiles converted DDL against a scratch database, and raises review tasks for objects that need human judgment [6]. Its application conversion is still a preview feature.

AI-generated PL/pgSQL and application SQL should clear three gates before merge. The code compiles, it returns the same outputs as Oracle on the same inputs, and an engineer reviews the diff. Programs that repeat these gates across teams formalize them as safety nets for Gen AI in legacy modernization, with compile, parity, and review checks enforced in the pipeline.

How Long an Oracle to PostgreSQL Migration Takes

Published estimates for an Oracle to PostgreSQL migration vary by orders of magnitude, because they measure different things. Schema and data for a small, standard database can move in days, and a PL/SQL-heavy estate with several applications on top can run for many months.

Effort follows code volume and complexity. Seven drivers explain the spread.

• PL/SQL line count, and the share held in packages with session state.
  
• Dynamic SQL and built-in package calls.
  
• Applications, scripts, and jobs that connect to Oracle.
  
• How application SQL is written, whether through an ORM, stored-procedure calls, or hand-built strings.
  
• Integrations and downstream consumers to re-point.
  
• Performance-critical queries and batch windows to re-tune.
  
• The validation depth the business requires before sign-off.

Ora2Pg’s assessment report turns the first two drivers into a number. It scores objects in cost units of five minutes by default and moves an estate from grade B to grade C once estimated rewriting passes 10 person-days [7]. Grade A means the migration can largely run automatically, and technical levels from 1 to 5 describe how much stored code and trigger rewriting is involved.

The report covers database objects only. Application SQL, scripts, and integrations need their own count, and on application-heavy estates that count can exceed the database figure.

How Legacyleap’s Gen AI Agents Run an Oracle to PostgreSQL Migration

Legacyleap is a Gen AI-powered legacy modernization platform built on multi-agent orchestration. For an Oracle exit, its five agents run the whole-estate approach this guide describes across the Assess, Comprehend, Modernize, Validate, and Deploy lifecycle.

• Assessment Agent. Inventories the PL/SQL and application estate, flags hotspots such as package state and dynamic SQL, and produces a dependency map with a migration effort and timeline estimate.
  
• Documentation Agent. Reconstructs the business logic held in packages, procedures, and triggers, along with data flows and integration maps, including where no documentation exists.
  
• Recommendation Agent. Recommends per module whether logic stays in the database or moves to a Java or .NET service tier, and produces an ordered migration plan with module-level effort and risk scores.
  
• Modernization Agent. Executes the migration plan as diff-based pull requests, including moving PL/SQL business logic into Java or .NET services and refactoring the data-access code that calls it. Roughly 70% of the modernization work is automated, and engineers review and direct the rest.
  
• QA Agent. Generates unit, integration, regression, and API tests and produces behavior parity validation reports against the Oracle baseline before cutover.

No agent merges, deploys, or executes code autonomously. Every change is a diff-based pull request that an engineer reviews.

Full-codebase grounding analyzes application SQL and the PL/SQL it calls together, so cross-layer dependencies appear in one map. All processing runs inside the organization’s own infrastructure, and source code never leaves the environment. The same agents run broader data platform modernization programs when the Oracle exit is one part of a wider data estate change.

Next Steps for Planning an Oracle to Postgres Migration

Code and behavior that depend on Oracle set the scope of an Oracle to Postgres migration. Sizing the estate by PL/SQL and application SQL, testing silent differences first, and validating business outputs before cutover give the program a realistic plan and a credible sign-off.

For teams scoping an Oracle exit, the $0 Modernization Assessment produces the estate inventory, a dependency and module map, a risk and complexity heatmap, and a modernization plan for one representative codebase. It takes 3 to 5 days and runs inside the client’s environment. More than 150 production-grade assessments have been completed to date.

A technical demo shows how the five agents take an approved plan through conversion and parity validation.

FAQ

References

[1] AWS Database Migration Service Step-by-Step Walkthroughs. Migrating an Oracle Database to PostgreSQL

[2] Oracle Database SQL Language Reference 19c. Nulls

[3] PostgreSQL 18 Documentation. Porting from Oracle PL/SQL

[4] PostgreSQL Wiki. Optimizer Hints Discussion

[5] PostgreSQL Wiki. Oracle to Postgres Conversion

[6] Microsoft Learn. Migrate Oracle to PostgreSQL with the PostgreSQL extension for Visual Studio Code

[7] GitHub (darold/ora2pg). Ora2Pg README