Oracle to PostgreSQL: the practitioner guide.
Oracle estates carry the most procedural logic and the most proprietary features — so they need the most disciplined migration. This guide covers the business case, PL/SQL conversion, data movement, and the cutover runbook, in order.
Why teams exit Oracle
Oracle migrations are almost always economics-led. The license and support bill is the business case; everything else is upside.
License & support
Processor-based licensing plus annual support routinely dominates the database budget. PostgreSQL eliminates both, and the savings typically fund the migration within the first renewal cycle.
Compliance exposure
License audits are a business risk, not just an IT annoyance. Moving to open source removes an entire category of vendor-driven financial surprise.
Cloud-native freedom
RAC and Data Guard architectures don't map to cloud economics. PostgreSQL with streaming replication and managed services fits elastic, containerized platforms.
When it makes sense — and when it doesn't
Strong candidates
- Custom applications where you own the SQL and the PL/SQL
- License/support costs that dwarf the migration investment
- RAC estates where active-active is a habit, not a measured requirement
- Data warehouse workloads better served by a lakehouse or columnar store
Weak candidates
- Packaged applications (ERP, EHR) that certify only Oracle as a backend
- Heavy use of Oracle-only features: VPD, Label Security, Spatial with no PostGIS path
- Estates where the application vendor contractually mandates Oracle
- Timelines driven by a support-renewal cliff with no assessment runway
The migration architecture
Same proven shape as our SQL Server migrations: assess, convert offline, replicate continuously, flip once, keep the rollback warm.
Assess first: PL/SQL is the critical path
Oracle assessments spend disproportionate time on procedural code and proprietary features — that's where the estimate lives or dies.
| Oracle feature | PostgreSQL path | Typical effort |
|---|---|---|
| Tables, constraints, indexes | Direct conversion via ora2pg / SCT | Low — automated |
| PL/SQL packages & procedures | PL/pgSQL with hand-rewritten idioms | High — the critical path |
| Sequences, triggers | Native sequences; trigger rewrite | Low–Medium |
| DBMS_* package calls | Extensions or application-side logic | Medium–High |
| Autonomous transactions | dblink / background workers / redesign | High — semantic review |
| CONNECT BY hierarchical queries | Recursive CTEs | Medium |
| Materialized views (fast refresh) | Materialized views + refresh scheduling | Medium |
| RAC | Streaming replication + Patroni | Medium — ops design |
| Data Guard | Streaming replicas + WAL archiving | Low–Medium |
| VPD / Label Security | Row-level security policies | Medium–High |
| Oracle Scheduler jobs | pgAgent / cron / orchestrator | Low–Medium |
| Database links | postgres_fdw / dblink | Medium |
What the assessment delivers
- Per-object compatibility scores with the hand-conversion backlog sized in engineer-weeks
- A PL/SQL triage: auto-convertible, rewrite-required, and retire-candidate units
- HA/DR re-architecture: failover SLAs re-proven, not assumed
- Sequenced database waves with a fixed-scope build proposal
Converting PL/SQL without breaking semantics
ora2pg handles structure; engineers handle meaning. The patterns below are where Oracle idioms need real rewrites.
- Exception handling: Oracle's EXCEPTION WHEN maps closely to PL/pgSQL, but transaction behavior inside exception blocks differs — each handler is reviewed for partial-commit semantics.
- Autonomous transactions: no direct equivalent; redesigned as separate connections via dblink, background workers, or application-side outbox patterns — with the ordering guarantees made explicit.
- Bulk collect / FORALL: rewritten with set-based SQL or array processing in PL/pgSQL; the performance characteristics change, so hot paths are benchmarked.
- DBMS_OUTPUT / DBMS_SQL: logging moves to proper log tables or application logging; dynamic SQL becomes EXECUTE … USING with injection review.
- Packages: package state (global variables) is the subtle killer — redesigned as session state or tables with clear lifecycle, never silently dropped.
Discipline: every converted unit gets tests with production-derived fixtures; the top decile by frequency × criticality gets human review regardless of tool confidence; acceptance is behavioral parity, not "it compiles".
Move the data, then prove it
Data movement
- Initial load via ora2pg's data export or DMS full load, parallelized by table; LOBs and partitioned tables get dedicated streams.
- CDC replication with DMS or Debezium keeps PostgreSQL within seconds of Oracle; validation jobs run row counts and checksums continuously.
- Number/date semantics: NUMBER precision and DATE-with-time behavior are verified column by column — silent truncation is the enemy.
Test gates
- Parity: captured Oracle workloads replayed against PostgreSQL; diffs triaged and signed off.
- Performance: top queries benchmarked at production volumes; connection pooling (PgBouncer) sized; autovacuum tuned to write patterns.
- HA/DR: primary kill, replica promotion, and backup-restore drills executed against the Patroni topology — the failover SLA is measured, not documented.
Cutover day, and the quarter after
Cutover
- Freeze DDL; confirm CDC lag near zero; stop writes; drain; final checksums.
- Flip connection strings; smoke-test critical paths; war room for the agreed window.
- Oracle stays warm with reverse-replication capability for the rollback period.
Rollback
- Trigger criteria defined before cutover — which metrics, for how long.
- Writes landed in PostgreSQL during the incident window are reconciled back via the tested procedure.
- The rollback is rehearsed as part of cutover rehearsal #2.
Post-migration optimization
- 30/60/90-day tuning: slow-query review, index rationalization, vacuum and checkpoint tuning.
- License true-down and Oracle decommission planning — including the support-contract exit date.
- Team enablement: PostgreSQL operations runbook, monitoring, and on-call training.
The Oracle migration checklist
- Estate inventory with PL/SQL unit counts and proprietary-feature usage mapped
- Per-object compatibility scores; hand-conversion backlog sized in engineer-weeks
- Package-state and autonomous-transaction redesigns approved
- Converted schema under version control; unit tests green on production fixtures
- HA/DR re-architecture built: streaming replication + Patroni, failover SLA measured
- CDC replication running with lag alerts and zero-drift validation
- Parity testing complete; diffs triaged and signed off
- Performance validation at production volumes; connection pooling load-tested
- Cutover runbook rehearsed twice; rollback triggers, procedure, and rehearsal done
- Monitoring, alerting, backups verified on PostgreSQL
- Oracle kept warm for rollback; decommission and license-exit plan dated
- 30/60/90-day optimization and team enablement scheduled
Oracle migration questions, answered straight
Is Oracle to PostgreSQL harder than SQL Server to PostgreSQL?
Usually, yes — Oracle estates tend to carry more procedural logic (PL/SQL packages), proprietary features (RAC, Data Guard, VPD), and third-party application dependencies. The conversion tooling (ora2pg, AWS SCT) is mature, but expect more hand conversion and a longer testing phase than a comparable SQL Server estate.
What happens to Oracle RAC and Data Guard?
They are re-architected, not migrated. RAC's active-active clustering becomes PostgreSQL streaming replication with a manager like Patroni (active-standby with automated failover); Data Guard becomes streaming replicas plus WAL archiving. Capacity and failover SLAs are re-proven under load before cutover.
How do you convert PL/SQL packages?
ora2pg and AWS SCT convert package structure, then engineers rewrite the idioms that don't transliterate: exception handling, autonomous transactions, DBMS_* package calls, and bulk-collect patterns. Every converted unit gets tests with production-derived fixtures.
Can we keep the Oracle database running as a fallback?
Yes — Oracle stays warm through the stabilization window with reverse replication or export-based reconciliation for the rollback path. The decommission date is set after the new system proves itself under real load, not on cutover day.
How long does a typical Oracle migration take?
Moderate estates run 4–8 months from assessment to cutover; large PL/SQL-heavy estates run longer. The assessment's per-object compatibility scoring is the only estimate worth trusting — everything before it is a guess.
Migrating from SQL Server instead? Read the SQL Server → PostgreSQL migration guide.
Run the Oracle → PostgreSQL migration with working instruments, not guesswork.
Discovery questionnaires, compatibility matrices, inventory workbooks, cutover and rollback checklists — free for practitioners.
Scope the PL/SQL before it scopes you.
A structured assessment: compatibility scoring, hand-conversion backlog, HA re-architecture, and a fixed-scope build proposal.