Oracle to PostgreSQL
Oracle to PostgreSQL migration: convert the code, then prove the data.
The data is the easy part. The PL/SQL, the Oracle-specific SQL and the behavior your application quietly relies on are the project.
The short answer
An Oracle to PostgreSQL migration converts the schema, PL/SQL code and data to PostgreSQL. Tools such as ora2pg, AWS Schema Conversion Tool and AWS DMS do the mechanical conversion and data load. Ace8 engineers rewrite what the tools cannot, such as packages and Oracle-specific behavior, and prove results match the source before cutover.
Tooling
What do ora2pg, SCT and DMS each do?
ora2pg is an open-source tool that reads an Oracle schema and generates PostgreSQL DDL. It also converts a good part of PL/SQL to PL/pgSQL and produces an assessment report with a rough migration-difficulty score. AWS Schema Conversion Tool (SCT) does a similar conversion with an assessment report that lists objects it could not convert. AWS Database Migration Service (DMS) moves data: a full load, then ongoing change capture.
Conversion tools are strongest on tables, indexes, constraints and simple procedures. They are weakest on packages, complex cursors and anything that leans on Oracle built-in packages. Plan for the remainder to be hand work.
The core of the project
What changes between Oracle and PostgreSQL?
This is the list we work through for each application. It is not complete, and each row can hide many objects.
| Oracle feature | PostgreSQL approach | What needs care |
|---|---|---|
| PL/SQL packages | Schemas holding PL/pgSQL functions | Package state and initialization have no direct equivalent |
| NUMBER | numeric, integer or bigint, chosen per column | Wrong choice changes rounding or performance |
| VARCHAR2 and empty strings | varchar or text | Oracle treats an empty string as NULL. PostgreSQL does not |
| DATE (includes time) | timestamp | Mapping to date silently drops the time |
| Sequences and triggers for keys | Identity columns or sequences | Gaps and ordering assumptions |
| ROWNUM and CONNECT BY | LIMIT and recursive CTEs | Ordering semantics differ |
| Outer join (+) syntax | ANSI joins | Mechanical, but numerous |
| DBMS_ and UTL_ packages | Extensions such as orafce, or application code | Scheduler, file and mail packages need redesign |
| Autonomous transactions | Separate connection or dblink pattern | Redesign rather than translation |
| Materialized views, partitioning | Native equivalents with different refresh rules | Refresh timing and partition maintenance |
Applications
What changes in the application?
Applications call the database through drivers, ORMs and embedded SQL. JDBC URLs and drivers change. Hibernate dialect settings change. Any SQL written against Oracle syntax in application code must be found and converted, including SQL built from strings. That search is part of the inventory.
Where the application is Java on an old runtime, the database move often travels with a framework move. See Java modernization for the sequencing.
Data
How is the data moved without a long outage?
- 1
Initial load
Bulk copy history to PostgreSQL while Oracle stays live.
- 2
Change capture
Stream ongoing changes, with DMS or logical replication tooling, so the target stays within seconds of the source.
- 3
Compare
Run row counts, column checksums and report-level comparisons on a schedule.
- 4
Rehearse cutover
Run the whole sequence on a copy, time it, and write the rollback.
- 5
Cut over
Stop writes, drain changes, verify, switch applications, keep Oracle read-only for a defined period.
Judgement
When is Oracle to PostgreSQL the wrong move?
Some workloads rely on Oracle features that PostgreSQL does not replace: heavy use of Real Application Clusters behavior, large amounts of Oracle-specific packages, or vendor applications that only support Oracle. In those cases the cost of conversion can exceed the licence saving. The assessment puts both numbers side by side, and sometimes the answer is to stay.
After go-live
Who supports PostgreSQL afterward?
Open-source PostgreSQL has no vendor to call. OSSeva provides support for PostgreSQL, including help with versions reaching end of life, so the migration does not trade a licence for an unsupported database. See supported after migration. This work sits inside database modernization.
After go-live
Keep it running after cutover.
Tables and data are straightforward. The effort is PL/SQL packages, Oracle-specific SQL and application code. ora2pg's assessment report gives a first estimate of difficulty.
An open-source tool that converts an Oracle schema and much of its PL/SQL to PostgreSQL, and produces an assessment report of what it could not convert.
DMS moves the data, with a full load and change capture. It does not convert code. Pair it with SCT or ora2pg for the schema and procedures.
Yes, usually. Change capture keeps PostgreSQL current while Oracle runs, so the cutover window is short. We rehearse it first.
Empty strings treated as NULL in Oracle, DATE columns that hold time, NUMBER precision choices, package state and hidden SQL in application code.
For most application workloads, yes, after tuning. Queries written for the Oracle optimizer may need new indexes or rewrites. We test on production-shaped data before cutover.
Know the cost before anything moves.
A fixed-price assessment gives you the inventory, the risk score and a phased plan. Scope and cost are fixed before the migration starts.
Book a Fixed-Price AssessmentGet in touch
Talk to a modernization engineer
Tell us what you run today. An engineer, not a sales script, replies within one business day.