When it's a good fit
- Applications that use standard SQL with moderate PL/SQL.
- Licence and support costs drive the change.
- The team is ready to own PostgreSQL operations (or use a managed service).
Harder cases: heavy use of Oracle-specific packages (DBMS_*, UTL_*), Oracle Forms/APEX, RAC-dependent designs, or third-party apps certified only on Oracle.
1. Install and connect Ora2Pg
Ora2Pg is a Perl tool that needs the Oracle client (Instant Client) and DBD::Oracle. Create a project:
ora2pg --project_base /migr --init_project sales
cd /migr/sales
# edit config/ora2pg.conf:
# ORACLE_DSN dbi:Oracle:host=ora01;service_name=SALES;port=1521
# ORACLE_USER ora2pg_reader (needs SELECT_CATALOG_ROLE / SELECT ANY DICTIONARY)
# SCHEMA SALES
# PG_VERSION 16
2. Assess
ora2pg -c config/ora2pg.conf -t SHOW_REPORT --estimate_cost --dump_as_html > reports/assessment.html
The report counts objects by type and gives a migration-effort estimate with a complexity level. Use it to scope, not as a promise. Review the PL/SQL units it marks as complex.
3. Convert the schema
./export_schema.sh # generated by --init_project: exports TABLE, VIEW, SEQUENCE, FUNCTION, PROCEDURE, PACKAGE, TRIGGER...
Review the generated SQL in schema/. Common changes:
| Oracle | PostgreSQL |
|---|---|
NUMBER(p,s) | numeric(p,s); NUMBER(10) → bigint/integer (check PG_INTEGER_TYPE) |
VARCHAR2 | varchar / text |
DATE (with time) | timestamp(0) |
| Empty string = NULL | Empty string is not NULL. Fix code that relies on this |
SYSDATE | now() / clock_timestamp(), or the orafce function |
| Packages | Schemas with functions; package variables need redesign |
| Sequences + triggers for IDs | GENERATED ... AS IDENTITY or sequence defaults |
ROWNUM | LIMIT / row_number() |
CONNECT BY | WITH RECURSIVE |
| Autonomous transactions | dblink / pg_background workarounds, or redesign |
The orafce extension provides many Oracle-compatible functions (check that your managed service supports it).
4. Load data
For a test load straight from Oracle:
ora2pg -c config/ora2pg.conf -t COPY -j 4 -J 2 -b data/ --pg_dsn "dbi:Pg:dbname=sales;host=pg01" --pg_user migrator
-j sets parallel processes for loading into PostgreSQL. -J opens several Oracle connections to extract one large table in parallel, which needs a numeric key. Check ora2pg --help for your version. For production cut-over with minimal downtime, use a CDC tool for the delta (AWS DMS, Debezium, or a commercial replicator) after an initial load.
5. Validate
- Row counts per table and checksums on key columns.
- Ora2Pg's
TESTandTEST_DATAactions compare object counts and data between source and target. - Run the application test suite against PostgreSQL, focusing on date handling, NULL vs empty string, sorting/collation and numeric precision.
- Performance: run the top 50 queries from Oracle AWR on PostgreSQL with
EXPLAIN ANALYZE; add indexes, fix implicit casts.
6. Cut-over
- Freeze schema changes on Oracle.
- Initial load + CDC running; lag near zero.
- Stop application writes; let CDC drain; final counts.
- Reset sequences on PostgreSQL to max IDs:
SELECT setval('orders_id_seq', (SELECT max(id) FROM orders)); - Switch connection strings; smoke tests.
- Keep Oracle read-only for the agreed rollback window.
Warning
Collation and case sensitivity differ: Oracle compares
VARCHAR2with binary semantics by default, while PostgreSQL uses the database collation for sorting, and=is case-sensitive in both. TestORDER BYresults and unique constraints on text columns.
Checklist
- Assessment report reviewed; complex PL/SQL owners assigned
- Schema converted and reviewed by developers
- Empty-string/NULL and date semantics tested
- Data load + CDC rehearsed with timings
- Performance tested on top queries
- Sequences reset and rollback plan agreed