Migrating from Oracle to PostgreSQL: a cutover playbook, not a fantasy

An Oracle to PostgreSQL migration sounds like a data problem. It is not. The tables, indexes and rows move in a weekend. The project lives or dies on the 10 to 20% of logic that does not auto-convert: PL/SQL packages, proprietary SQL, sequences, triggers, hierarchical queries, and the edge data types nobody documented. This is the playbook we run when the goal is a clean cutover, not a slide that says "migration complete."

Why teams move, and where they are wrong about it

The honest motivation behind an Oracle to PostgreSQL migration is almost always licensing cost: the per-core model, the audit risk, the renewal that arrives with a number attached to features you stopped using years ago. PostgreSQL is open source, runs on commodity infrastructure, and the same workload often fits on hardware you already own.

That is a legitimate reason. The mistake is treating cost as the only variable. PostgreSQL is not Oracle with a smaller bill. Some workloads should stay where they are:

• Extreme OLTP with very high concurrent write contention, where Oracle's locking and undo behaviour has been tuned for a decade.
• HA built on RAC. PostgreSQL has excellent replication and failover (Patroni, streaming replication), but it is not RAC. Shared-storage active-active is a redesign, not a port.
• Heavy use of Oracle-specific features: Advanced Queuing, Spatial, certain partitioning behaviours, or analytics that lean on optimizer hints.

The first deliverable of a serious migration is an honest answer to whether this database should move at all.

Phase 1: assessment and compatibility audit

Before anyone touches a schema, you measure the gap. This is where ora2pg earns its place: in assessment mode it produces a migration-cost report that estimates effort by object type and flags what will not convert cleanly.

What the audit needs to surface:

• Object inventory: tables, views, indexes, sequences, triggers, packages, procedures, functions, materialized views, with line counts.
• PL/SQL complexity. Rank the packages. A 200-line utility package is an afternoon; a 5,000-line package with autonomous transactions, dynamic SQL and Oracle built-ins is a project of its own.
• Proprietary SQL in the application. CONNECT BY, (+) outer joins, DECODE, ROWNUM, NVL chains, MERGE edge cases. The risky ones live in the application code, not the database.
• Data type edge cases: NUMBER with no precision, DATE that actually carries time, RAW, LONG, and BLOB/CLOB sizing.

Risk callout: the assessment number ora2pg gives you is a floor, not a ceiling. It scores syntactic convertibility, not whether the converted logic is correct. Treat it as a map of where to send your senior people.

Phase 2: schema and stored-procedure conversion

Schema first, because it is the easy 80% and it builds momentum. ora2pg (or AWS SCT, or similar database migration tools) generates PostgreSQL DDL for the bulk of your objects: datatypes map, most constraints carry over, and you get a working schema quickly.

Then you hit the parts that do not map one to one:

• Sequences. Oracle and PostgreSQL sequences behave differently around caching and gaps. If application code reads .NEXTVAL and depends on contiguity, that assumption breaks. Decide per sequence whether to use native sequences or IDENTITY columns.
• PL/SQL to PL/pgSQL. The syntax is close enough to be dangerous. Packages do not exist in PostgreSQL (you model them as schemas plus functions). Autonomous transactions need dblink or a redesign. %ROWTYPE, bulk collect and exception handling all have equivalents that behave subtly differently.
• Triggers. PostgreSQL trigger functions are separate from the trigger definition. Compound triggers and Oracle's firing order need rework.
• Hierarchical queries. CONNECT BY ... PRIOR becomes a recursive CTE (WITH RECURSIVE). The rewrite is mechanical but the results must be diffed, because ordering and cycle handling differ.

This is where PL/SQL conversion stops being a tooling exercise and becomes engineering. The tool flags the line; a human decides whether the new version means the same thing. No converter handles business logic untouched, whatever the brochure says.

Phase 3: data migration

Compared to the logic, moving the rows is straightforward, which is why teams underestimate the surrounding work.

• Initial load. For anything beyond a small database, bulk export to PostgreSQL COPY is the fast path. Parallelism matters: load by table with indexes and constraints disabled, then rebuild them after.
• Data validation. Row counts are the minimum, not the proof. Checksum critical tables, validate that NUMBER precision survived, and confirm character-set conversion (AL32UTF8 to UTF8) did not mangle anything.
• Change data capture for the gap. If the source cannot take downtime for the full load, use CDC (logical replication, GoldenGate, Debezium) to stream changes from Oracle into PostgreSQL during the load and the dual-run window.

Risk callout: the empty-string trap catches almost everyone. In Oracle, '' is NULL; in PostgreSQL, it is an empty string. Application logic and WHERE clauses that relied on Oracle's behaviour will silently return different rows. Find these in testing, not in production.

Phase 4: dual-run and parallel validation

This is the phase most projects skip, and skipping it is why migrations fail loudly. Run both databases in parallel: mirror production write traffic to PostgreSQL (via CDC or application-level dual writes), let it process the same workload, and diff the outputs. Reports, batch jobs, key API responses: run them against both and compare. Where they disagree, you have found a conversion bug while it is still cheap. Run it long enough to cover the real cycle: a month-end close, a billing run, the quarterly job that only fires four times a year. A dual-run period turns the cutover from a leap of faith into a formality, and it is the single highest-leverage phase in the playbook.

Phase 5: cutover and rollback

The cutover itself should be boring, because the risk was retired in dual-run. Pick the window around the lowest-traffic period and the safest point in the business cycle, never before month-end.

• Freeze, drain, switch. Stop writes to Oracle, let CDC drain the final changes, validate the last delta, repoint the connection strings, and bring it up.
• Have a real rollback plan. Once PostgreSQL has taken production writes, rolling back means reconciling data that only exists on the new side, so keep a documented reverse path (reverse replication) ready. A rollback plan that loses the writes taken after cutover is not one.
• Define go/no-go criteria in advance, in writing, with a named owner. At 2 a.m. you want a checklist, not a debate.

Phase 6: post-cutover performance tuning

You are live, not done. PostgreSQL needs its own tuning pass, because the defaults and the planner differ from Oracle:

• Run ANALYZE so the planner has fresh statistics, then tune autovacuum for your write patterns. Bloat from un-vacuumed tables is the classic post-migration slowdown.
• Find the regressed queries. pg_stat_statements shows the expensive ones. Expect a handful that were fine on Oracle and now need an index, a rewritten join, or adjusted statistics targets.
• Tune the obvious knobs (shared_buffers, work_mem, effective_cache_size); the shipped defaults are deliberately conservative.
• Watch connection handling. PostgreSQL spawns a process per connection, so if the application opened Oracle connections freely, put PgBouncer in front before it becomes the bottleneck.

Plan two to four weeks of active tuning after cutover. The savings are real and recurring, but they land after this work, not at go-live.

Talk to us before you commit to the date

An Oracle to PostgreSQL migration is won in the assessment and the dual-run, not in the cutover weekend. DNA Solutions runs the full path for European enterprises: compatibility audit, PL/SQL conversion, data migration services, parallel validation, and the rollback plan that lets you sleep through go-live. The right first step is an honest gap assessment of your real schema. Talk to us.