Pattern 05 · Modernization
Oracle → PostgreSQL migration pattern
A production pattern for moving off Oracle licensing onto PostgreSQL — assess honestly, convert PL/SQL deliberately, replicate continuously, and cut over only when validation proves parity.
01 · PROBLEM
Oracle licensing is a growing tax on growth
Oracle licensing — processor-based, plus annual support — scales with your hardware and penalizes modern architectures (more cores, more replicas, more environments). Meanwhile PostgreSQL has closed the enterprise gap: it now handles the OLTP, mixed, and analytical workloads most Oracle estates actually run, with extensions (PostGIS, pgvector, TimescaleDB, Citus) and operational tooling (Patroni, pgBackRest) that make it a production platform, not a compromise.
This pattern describes how to migrate production Oracle estates to PostgreSQL without heroic cutovers — continuous replication, dual-run validation, and rollback plans that have been rehearsed, not just written.
02 · WHEN TO USE
Fit and anti-fit
- License and support renewals are forcing the conversation, or growth plans multiply processor costs.
- You want portability and vendor-independence — PostgreSQL runs well on every cloud and on-prem.
- Workloads are OLTP, mixed, or analytical with standard SQL and procedural logic you own.
- The organization can fund conversion engineering — especially PL/SQL rewrites — not just a "lift and shift."
Don't use it as a pure lift-and-shift: moving Oracle-isms (RAC clustering assumptions, autonomous transactions, DBMS_* package calls) to Postgres without redesign just relocates the pain. And if your estate is dominated by third-party applications certified only on Oracle, or deeply coupled to proprietary features with no appetite for change, honesty beats a doomed migration.
03 · ARCHITECTURE
Reference migration flow
The spine of the pattern: replicate early, validate continuously, cut over late. Validation isn't a phase — it's a standing process from the first replicated row to decommission.
04 · COMPONENTS
What each piece does
| Component | Responsibility | Build/buy notes |
|---|---|---|
| Assessment | Inventory databases, score compatibility per object (PL/SQL units, proprietary-feature usage), size the hand-conversion backlog honestly | Automated analysis (ora2pg, AWS SCT) plus human review of the hard 20% — packages, autonomous transactions, DBMS_* calls |
| Schema conversion | Data types, constraints, indexes, sequences, partitions — and the semantic traps: DATE vs timestamp, empty-string vs NULL, NUMBER precision | Automate the 80%; hand-convert the semantic edge cases and prove them with fixtures, not assumptions |
| Code conversion | PL/SQL → plpgsql: packages, procedures, functions, triggers; autonomous-transaction redesign; job scheduling → orchestrator | The long pole. Tools convert structure; engineers rewrite idioms — exception handling, bulk collect, package state. Budget per unit. |
| Replication (initial + CDC) | Bulk load then continuous change capture with lag monitoring | Debezium, AWS DMS, or your enterprise replicator — pick for your topology, test failover, keep Oracle warm through stabilization |
| Validation | Row counts, checksums, data sampling, application-level dual-run comparison | Dual-run is the gold standard: same inputs, compare outputs, investigate diffs — including Oracle-vs-Postgres semantic diffs |
| Cutover | Blue-green switch with rehearsed rollback | Rehearse the rollback like the cutover — an untested rollback is a hope; keep reverse-replication or reconciliation ready |
| Postgres operations | HA (streaming replication with Patroni), PITR backups, vacuum/autovacuum tuning, monitoring | RAC and Data Guard get re-architected, not migrated — design Postgres HA/DR from day one |
05 · FLOW
How the migration proceeds
06 · TRADEOFFS
Decisions with real costs
| Decision | Option A | Option B | Our default |
|---|---|---|---|
| Strategy | Big-bang — one cutover, maximum risk | Wave-based trickle — longer program, contained blast radius | Wave-based; big-bang only for trivial estates |
| Procedural code | Convert PL/SQL → plpgsql faithfully — preserves behavior, preserves complexity | Simplify/move logic to app — better long-term, more app work | Convert faithfully first; simplify where the business signs off |
| Postgres flavor | Managed (RDS, Cloud SQL, Azure) — less ops, some limits | Self-managed — full control, full responsibility | Managed default; self-manage only with a real Postgres ops team |
| Downtime | Planned outage window — simpler, business cost | Near-zero via CDC + blue-green — more engineering | Near-zero for production; windows only for dev/test |
On Oracle → PostgreSQL specifically, see our in-depth migration guide.
07 · SECURITY
Non-negotiables
- Lower environments: migrate with masked or synthetic data; production dumps in dev are how breaches start.
- Credential rotation: new secrets for the Postgres estate — don't carry Oracle service accounts across.
- Encryption parity: TDE equivalents (disk-level + pgcrypto where needed), TLS everywhere, audit logging from day one.
- Replication security: CDC channels are privileged — encrypt, authenticate, and monitor them like production access, because they are.
- Privilege redesign: Oracle roles and VPD/FGAC policies don't transliterate — rebuild least-privilege on Postgres, don't replicate the old grants.
08 · GOVERNANCE
Running it like a program, not a project
- Wave governance: each wave has entry/exit criteria; no wave cuts over until dual-run diffs are explained.
- Rollback rehearsals: tested on schedule, timed, with named owners — a rollback plan that's never been run is fiction.
- License tracking: track Oracle license and support-contract retirement per wave; the business case is realized at decommission, not at cutover.
- Knowledge transfer: your DBAs and developers learn Postgres operations during the program, not after — pair with practitioners throughout.
09 · COST
Where the money goes
- Conversion engineering is the dominant cost — PL/SQL conversion and app remediation, not tooling.
- Dual-running doubles infra temporarily; budget it per wave and decommission Oracle aggressively after each stabilization window.
- License savings are real but lagging — model the crossover point honestly, including the program cost.
- Postgres ops (managed service fees or DBA time) replaces license cost; it's smaller, but it isn't zero.
10 · IMPLEMENTATION
A phased path that de-risks
Engagement tie-in: our Modernization practice runs these migrations as engineering programs, starting with a fixed-scope Oracle → PostgreSQL assessment.
11 · RELATED
Keep exploring
Oracle → PostgreSQL Guide
The in-depth migration guide: assessment, PL/SQL conversion, and cutover detail.
Read guide →Oracle → PostgreSQL Migration Pack
Discovery questionnaire, compatibility matrix, cutover checklist, reference architecture — the working instruments.
Open pack →SQL Server → PostgreSQL migration pattern
The sibling pattern — same spine, different source-database idioms.
Read pattern →Start here
Talk to an Architect
Bring your hardest AI, data, or modernization problem. We'll tell you plainly whether we can help — and what it takes.
FAQ
Questions we hear
It depends on database count, PL/SQL volume, and proprietary-feature coupling — not data size. Moderate estates run months from assessment to cutover; estates heavy on PL/SQL packages and proprietary features run in phased quarters. The assessment phase exists to replace guessing with a measured plan.
Usually yes, using initial load plus change-data-capture with a blue-green cutover. Zero-downtime is an engineering outcome you design for — it requires dual-run validation and a rehearsed rollback, not just a replication tool.
They are re-architected, not migrated. RAC's clustering becomes PostgreSQL streaming replication with an automated failover manager like Patroni; Data Guard becomes streaming replicas plus WAL-archived backups and rehearsed point-in-time recovery. Plan this as part of conversion, not after.