From Oracle Database to PostgreSQL

Oracle Database is among the most capable, and most expensive, pieces of software in the enterprise. Per-core licensing, mandatory support uplifts, hard-partitioning rules that limit virtualization savings, and the ever-present risk of an audit make it a perennial target for cost reduction. PostgreSQL is the most common open-source destination: a mature, standards-leaning relational database with strong SQL support, extensions for almost everything, and no license fee. But “no license fee” is not the same as “free migration,” and this guide is about the gap between those two.

The economics

Oracle’s cost is driven by the vCPUs of the database server. Oracle applies a core factor and, critically, expects you to license all the cores a workload could run on, so virtualizing Oracle on a shared cluster often means licensing the whole cluster unless you use approved hard partitioning or Oracle-engineered systems. PostgreSQL removes the license entirely; your ongoing cost becomes infrastructure plus optional commercial support (EDB, Percona, Crunchy Data, etc.). For most workloads the three-year TCO drops sharply, the question is migration effort, not whether it saves money.

What ora2pg automates, and what it doesn’t

ora2pg is the workhorse. It connects to Oracle, assesses migration complexity and estimates effort, exports schema (tables, indexes, constraints, sequences), converts much of your PL/SQL to PL/pgSQL, and migrates data in parallel. Start with the assessment report:

ora2pg -t SHOW_REPORT --estimate_cost -c ora2pg.conf > assessment.txt

It will not magically convert everything. The parts that need human attention:

• PL/SQL packages and complex procedures. Simple logic converts cleanly; packages, autonomous transactions, and heavy use of Oracle built-ins need rework.
• Datatype and semantics differences. NUMBER without precision, DATE (which carries time in Oracle), empty-string-equals-NULL behavior, and sequence/ROWNUM patterns all need review.
• Oracle-specific features. RAC, partitioning syntax, materialized-view refresh semantics, hierarchical CONNECT BY, and certain analytic functions map differently (or to extensions).
• Application SQL. Hints, (+) outer joins, DUAL, and PL/SQL called from the app must be updated. This is often the largest hidden cost.

The behaviors that fail silently

A few Oracle behaviors deserve their own attention because they cause silent, hard-to-find bugs rather than clean compile errors:

• DATE carries time in Oracle. PostgreSQL splits date (no time) from timestamp. Map Oracle DATE to timestamp unless you are certain the column is date-only, or you will truncate the time component and quietly change results.
• NUMBER without precision. An unqualified Oracle NUMBER is arbitrary precision. Map it to numeric rather than an integer type to avoid rounding or overflow, then tighten the type only where you have confirmed the domain.
• Empty string is not NULL. Oracle treats '' as NULL; PostgreSQL stores a zero-length string. Constraints and IS NULL predicates behave differently, so normalize on load.
• ROWNUM and CONNECT BY. ROWNUM filtering becomes LIMIT/row_number(), and hierarchical CONNECT BY becomes a WITH RECURSIVE CTE. Neither is a mechanical find-and-replace.
• Sequences and triggers. Oracle sequence-plus-trigger patterns usually become identity columns or nextval() defaults; reset the high-water mark after the bulk load or you will hit key collisions on the first insert.

Application SQL changes

The database conversion is visible and testable; the application SQL is the part that hides. Inventory every place SQL is written: embedded queries, ORM mappings, reporting tools, ETL jobs, and stored logic the app calls directly. Oracle idioms like SELECT ... FROM DUAL, the (+) outer-join operator, optimizer hints, NVL, DECODE, and SYSDATE all have PostgreSQL equivalents (now(), COALESCE, CASE), but each has to be found and changed. ORMs shield you from some of this, yet native queries and any hand-tuned SQL do not benefit from that abstraction. Grep the codebase for the tell-tale Oracle constructs early so the scope is known rather than discovered during the cutover rehearsal.

A migration flow that holds up

1. Assess with ora2pg; triage objects by conversion difficulty.
2. Provision the target, a primary plus replicas, sized on vCPU, with backups and parameter tuning.
3. Convert schema, review the action items, and load it into PostgreSQL.
4. Migrate data, a bulk load for the cutover rehearsal, then change-data-capture (CDC) replication to keep the target current with low downtime.
5. Convert procedures and application SQL; this proceeds in parallel with data work.
6. Validate with row counts, checksums/data-diffs, an application regression suite, and query-plan/performance comparisons against the Oracle baseline.
7. Cut over during a window: stop writes (or set Oracle read-only), let CDC drain to zero lag, run a final diff, repoint connection strings, smoke-test, and keep Oracle recoverable through hypercare.

Keeping T-SQL-style compatibility (the SQL Server cousin)

If you’re migrating SQL Server rather than Oracle and the application rewrite is the blocker, Babelfish for Aurora PostgreSQL can accept T-SQL/TDS directly, dramatically reducing app changes. There’s no equivalent for Oracle’s PL/SQL, but EDB Postgres Advanced Server offers a high degree of Oracle compatibility if you want to minimize conversion effort at the cost of a commercial subscription.

Testing is the project

The single biggest predictor of a smooth Oracle→PostgreSQL cutover is the depth of the parallel-run and validation phase. Run both databases in parallel, replay representative workloads, compare results and query plans, and only promote when the target meets documented acceptance criteria (correctness, performance, and failover). Budget real time here, it’s cheaper than discovering a behavioral difference in production.

Where the effort really goes

Oracle→PostgreSQL reliably lowers licensing cost; the work is schema/PL-SQL conversion, application SQL changes, and rigorous validation, not the data movement itself. ora2pg handles the mechanical 70–90%; plan engineering time for the rest, run CDC to minimize downtime, and validate exhaustively before cutover. Use the calculator above to model your vCPU-based savings, and treat the figures as illustrative until a distributor or support vendor quotes your environment.