Oracle to PostgreSQL Migration: A Step-by-Step Guide

The short answer

Migrate from Oracle to PostgreSQL in eight steps: assess what the Oracle schemas contain, choose tooling for each part of the job, convert the schema, convert the PL/SQL code, move and validate the data, test the application against PostgreSQL, run the cutover, and keep a rollback path open until the new system has carried real traffic. The open source workhorse is ora2pg, which inventories an Oracle database, estimates the conversion effort, generates PostgreSQL DDL, converts part of the PL/SQL and exports the data. On AWS, the Schema Conversion Tool and Database Migration Service cover the same ground with ongoing replication for the cutover.

No tool converts everything. Schema and data move mostly by machine. Packages, procedures, triggers and the SQL embedded in your application are where the hours go, and the tools tell you where those hours are rather than removing them. The companion posts cover the technical traps and PL/SQL to PL/pgSQL conversion patterns in detail.

Step 1: Assess what you have

Start with an inventory per schema: tables, indexes, constraints, sequences, views, materialized views, synonyms, database links, types, packages, procedures, functions and triggers, plus the data volume and the rate of change. Then find the SQL that lives outside the database: queries built in application code, reports, ETL jobs and anything that connects with an Oracle driver.

ora2pg does the database half. Its SHOW_REPORT export type inspects every object and lists what it contains and what cannot be exported automatically. Adding --estimate_cost scores each object in migration units and totals them in person-days. Its documentation says each unit represents about five minutes for a PostgreSQL expert, and recommends raising that value with COST_UNIT_VALUE if this is your first migration. The report also gives a migration level: A for a migration that might run automatically, B for one that needs code rewriting within a person-day limit, C for one above it, each with a technical difficulty from 1 (no stored code) to 5 (stored functions or triggers that need rewriting).

ora2pg -t SHOW_REPORT --estimate_cost --dump_as_html -c ora2pg.conf > report.html

For an estate with many instances, the ora2pg_scanner script reads a CSV list of connections and writes one assessment line per schema plus an HTML report for each database, which is the quickest way to rank which schemas to move first. AWS SCT produces its own assessment report for Oracle 10.1 and later when the target is Aurora PostgreSQL or PostgreSQL. Treat both as an estimate of conversion work only. Neither counts application changes, testing, data movement or the cutover itself, which our post on Oracle migration cost factors covers.

Step 2: Choose tooling for each job

A common self-managed toolset is ora2pg for the schema and code, orafce to cut down the number of rewrites, and either ora2pg or a change-data-capture tool for the data. Teams moving to AWS at the same time can pair SCT with DMS instead. The choice of target matters too: Oracle database alternatives compares community PostgreSQL, EDB's Oracle-compatible distribution and the managed services.

Step 3: Convert the schema

Most schema conversion is type mapping and naming:

• Strings. VARCHAR2 becomes varchar or text, as the PostgreSQL porting guide recommends.
• Numbers. NUMBER(p,s) becomes numeric(p,s). NUMBER(p) and key columns usually become integer or bigint, which ora2pg does when PG_INTEGER_TYPE is on. Check every bare NUMBER column before letting it become bigint, because Oracle lets it hold decimals.
• Dates. Oracle DATE holds a time down to the second. PostgreSQL date holds no time of day, so map it to timestamp(0) unless the column truly holds dates only.
• Sequences and identity. Oracle sequences map to PostgreSQL sequences, and seq.NEXTVAL becomes nextval('seq'). Oracle identity columns map to PostgreSQL identity columns, which ora2pg generates with PG_SUPPORTS_IDENTITY.
• Packages. PostgreSQL has none. ora2pg emulates each package as a schema by default, so pkg.proc() keeps its shape.
• Synonyms. PostgreSQL has none. ora2pg exports them as views, and its report notes the other workaround: set search_path so names resolve across schemas.
• Names. Oracle folds unquoted identifiers to upper case and PostgreSQL folds them to lower case. Keep names unquoted on both sides and nothing changes. Quoted mixed-case names need a decision before you generate any DDL.

Step 4: Convert the code

PL/pgSQL is close to PL/SQL in structure, which is why so much converts mechanically, and different in enough details that every converted routine needs a human read. ora2pg rewrites function, procedure and package headers and parameters, simple (+) outer joins, and autonomous transactions into dblink or pg_background wrappers. It leaves a lot of semantics to you: exception handling, cursor loops, dynamic SQL, package state, CONNECT BY queries and the empty-string rule. Work through the conversion patterns one routine at a time, starting with the code your most important transactions call.

Search the application code in the same pass. Oracle hints, ROWNUM, NVL, DECODE, SYSDATE, FROM dual and outer joins written with (+) all turn up in application SQL as often as in stored code. orafce supplies nvl, decode, dual and oracle.sysdate(), which buys time, but plan to replace them with standard SQL as the code is touched.

Step 5: Move and validate the data

For a schema that can stop for the length of a copy, ora2pg exports table data with COPY or INSERT statements and can load PostgreSQL directly. For a system that cannot stop that long, use change data capture: AWS DMS does a full load and then replicates ongoing changes until you switch over, and similar tools do the same off AWS.

Validate before you trust the copy. ora2pg's TEST action compares the objects in the two databases, TEST_COUNT compares row counts per table, and TEST_DATA checks row content on both sides. Add your own checks for the columns most likely to change on the way: empty strings that Oracle stored as NULL, DATE values with times, numbers mapped to integer types, and character set conversions.

Step 6: Test the application, not just the database

• Functional tests against PostgreSQL, using the application's own test suite plus the business flows that touch converted procedures.
• Behaviour checks for the known differences: empty string versus NULL, implicit rollback inside exception blocks, case of returned column names, date arithmetic and sort order under the new collation.
• Performance tests on production-sized data. PostgreSQL ignores Oracle hints, and its community has said it is not interested in hints of the kind other databases implement. Fix slow queries with statistics, indexes and rewrites first. The pg_hint_plan extension adds comment-based hints if you need an escape hatch.
• Operational tests: backups, point-in-time recovery, failover and monitoring on the new cluster. The PostgreSQL backup, recovery and high availability runbook covers what to rehearse.

Step 7: Cut over

1. Freeze schema changes on Oracle and agree a go/no-go checklist with the application owners.
2. Stop writes, or let change data capture drain until PostgreSQL has caught up.
3. Run the final validation: row counts, checksums on key tables, and the business totals finance or operations already reconcile.
4. Reset every sequence and identity column to above the highest key loaded, using setval. A sequence left at its starting value is the classic first-hour failure.
5. Switch connection strings and run smoke tests on the critical paths.

Step 8: Keep a way back

Leave the Oracle database intact and read-only after cutover, and set a deadline for the decision to roll back. Before that deadline, decide how writes made on PostgreSQL would reach Oracle if you went back: replicate in reverse if your tooling supports it, or accept that those writes would be replayed from application logs. After the deadline, take a final backup of Oracle and retire it. Cutting the rollback window short to save on licences is a false economy if the first month-end close on PostgreSQL finds a problem.

Where OSSeva fits

OSSeva for PostgreSQL includes Oracle-to-Postgres migration design in the Assure tier, covering schema conversion, query compatibility analysis and the application-layer migration strategy. From the day of cutover, the same contract supports the PostgreSQL you landed on: patched builds for versions past community end of life, and on Operate, 24/7 replication and failover monitoring with a 15-minute P1 response. That matters because the PostgreSQL version chosen at the start of a long migration can reach end of life before the programme finishes. OSSeva runs where you run, on bare metal, VMs, any Kubernetes or any cloud account, and is priced per cluster. See Oracle to PostgreSQL with OSSeva and migration services.

Frequently asked questions

What are the Oracle to PostgreSQL migration steps?

Assessment, tooling choice, schema conversion, code conversion, data migration and validation, application testing, cutover, and a rollback window. The first and last steps are the ones teams shorten and regret.

What are the best Oracle to PostgreSQL migration tools?

ora2pg for assessment, schema, code and data on any platform; orafce to reduce rewrites; AWS SCT and DMS when the target is on AWS; Google's Database Migration Service when the target is Cloud SQL or AlloyDB. pgloader does not read from Oracle.

How do you do an Oracle to PostgreSQL migration using ora2pg?

Run SHOW_REPORT with --estimate_cost to size the work, export TABLE, SEQUENCE, VIEW and the code object types, load and fix the DDL, export the data with COPY, then run TEST, TEST_COUNT and TEST_DATA to compare the two databases.

How does an AWS Oracle to PostgreSQL migration work?

AWS SCT or DMS Schema Conversion converts the schema and flags what needs manual work. DMS then does a full load and replicates ongoing changes from Oracle to Aurora PostgreSQL or RDS for PostgreSQL until you cut over.

Can you replace Oracle with PostgreSQL for every application?

Most custom applications, yes, with effort that depends on how much logic sits in PL/SQL. Packaged applications are different: if the vendor supports only Oracle, check their supported databases before planning anything.

Is there an Oracle to PostgreSQL migration playbook we can follow?

The eight steps above are the playbook. Write down the go/no-go criteria and the rollback deadline before step 5, and get the application owners to sign them.