I have spent twenty years watching organizations pay Oracle licensing invoices that made their CFOs physically uncomfortable. The conversation is always the same: someone runs the numbers on what they could save by migrating off Oracle, the number is large enough to fund several engineering teams, and then the project stalls because someone says “but what about the stored procedures.” Fourteen thousand lines of PL/SQL, accumulated since 2003, written by consultants who are no longer findable.
In 2026, I am seeing more Oracle migrations actually complete than at any point in my career. The tools have matured, PostgreSQL has become genuinely enterprise-grade, and managed services like Aurora PostgreSQL and AlloyDB have removed most of the operational burden that used to make self-managed PostgreSQL a hard sell in enterprises. This guide covers what actually trips teams up and how to get through it without a production outage that costs more than the Oracle license you were trying to escape.
Why the Oracle Exit Is Accelerating Right Now
The timing is not random. Oracle’s licensing model, particularly around Processor and Named User Plus licenses, has become increasingly aggressive with audits. The shift to cloud has made the cost differential starker: a comparable Oracle Database Enterprise Edition workload running on-premises versus a managed Aurora PostgreSQL or Cloud SQL for PostgreSQL instance is not even close on total cost of ownership once you factor in licensing, hardware, and DBA time.
According to the 2025 Stack Overflow Developer Survey, PostgreSQL reached 55.6% usage among professional developers, making it the most-used database for the third consecutive year, which means your next hire almost certainly already knows it. That was not true when I started my career. The ecosystem around PostgreSQL, including extensions like PostGIS, TimescaleDB, and pgvector, has also made it capable of workloads that used to require Oracle Spatial, Oracle Advanced Analytics, or Oracle Text.
The risk calculus has shifted. Staying on Oracle is no longer the safe, conservative choice. It is now the choice that accumulates technical debt and licensing exposure simultaneously.
Starting With an Honest Assessment
The first mistake I see teams make is diving into tooling before understanding the scope of what they are migrating. Oracle databases that have been in service for a decade or more carry decades of accumulated complexity: PL/SQL packages, database triggers, materialized view logs, database links to other Oracle instances, and application code that reaches directly into Oracle-specific data dictionary views.
Ora2Pg, the open-source Oracle-to-PostgreSQL migration tool, generates an assessment report before it does anything else. Run it in report mode before you commit to any timeline. The report counts every object type in your source database: tables, indexes, views, materialized views, sequences, synonyms, database links, triggers, functions, procedures, and packages. It assigns a migration difficulty score to each object type and produces an overall complexity estimate.
That score is not a timeline. I have seen databases with high complexity scores migrate faster than expected because the PL/SQL was clean and well-structured. I have seen moderate-complexity databases take three times as long because the application code was doing Oracle-specific things that the assessment could not detect: relying on ROWNUM-based pagination in the application layer, using Oracle-specific date arithmetic, or calling DBMS packages directly from the application.
The assessment tool tells you about the database. You also need to audit the application. Look for any SQL that uses Oracle-specific syntax: CONNECT BY for hierarchical queries, the FROM DUAL pattern for scalar selects, DECODE instead of CASE, Oracle outer join syntax with the plus-sign notation, and the MERGE statement, which behaves differently enough between Oracle and PostgreSQL to cause subtle bugs.
The Data Type Translation Problem
Oracle’s type system differs from PostgreSQL’s in ways that seem minor until they are not.
VARCHAR2 and CHAR: PostgreSQL’s VARCHAR and CHAR work similarly, but Oracle’s VARCHAR2 with a byte-length qualifier (VARCHAR2(100 BYTE)) maps differently than a character-length version (VARCHAR2(100 CHAR)). If your Oracle database was created with a multi-byte character set like AL32UTF8, your actual byte limits may differ from what your application expects. Test character limits explicitly with multi-byte content.
NUMBER: Oracle’s NUMBER(p, s) type covers the range of both integers and decimals. PostgreSQL maps this to NUMERIC(p, s), which is correct but slower than native integer types. Where the application is storing plain integers in NUMBER columns (a common Oracle pattern), replacing those with PostgreSQL INTEGER or BIGINT at migration time yields meaningful performance gains. The assessment report often catches these automatically.
DATE: This is where people get burned. Oracle’s DATE type stores date and time, with seconds precision. PostgreSQL’s DATE type stores only the date component. In PostgreSQL, you want TIMESTAMP or TIMESTAMPTZ for what Oracle stores as DATE. Ora2Pg handles this by default, mapping Oracle DATE to PostgreSQL TIMESTAMP, but you need to verify that application code handling date arithmetic works correctly after the conversion.
CLOB and BLOB: Oracle’s large object types map to PostgreSQL’s TEXT and BYTEA respectively for moderate sizes. PostgreSQL handles very large text natively in TEXT columns without the Oracle CLOB interface, which is actually simpler for application code. Large binary content can also use PostgreSQL’s large object facility (pg_lo) if you need streaming access.
ROWID: Oracle’s ROWID pseudo-column, used in some applications as a fast row locator, has no direct equivalent in PostgreSQL. Applications using ROWID for row-level locking patterns or pagination need to be rewritten to use primary keys or ctid with awareness that ctid is not stable across vacuums.
PL/SQL to PL/pgSQL: The Real Work
Schema conversion is mostly mechanical. Code conversion is where the project slows down. PL/SQL and PL/pgSQL are different enough that automated tools get you perhaps 60-70% of the way there, and the remaining conversion requires someone who understands both languages.
Oracle Packages: PostgreSQL has no concept of a package. Ora2Pg maps packages to schemas, placing all package procedures and functions inside a schema named after the package. This preserves the PACKAGE.PROCEDURE calling convention by using SCHEMA.FUNCTION notation. What it cannot preserve is package-level state: package variables that persist across procedure calls within a session.
Package-level state is one of the most common Oracle patterns and one of the hardest to translate. Options in PostgreSQL include temporary tables for transient state, custom configuration parameters (SET and current_setting()) for session-scoped values, or refactoring the logic to pass state explicitly. The right choice depends on whether the state is session-scoped or transaction-scoped and how it is accessed.
Sequences and Identity Columns: Oracle sequences map cleanly to PostgreSQL sequences. However, Oracle applications often use sequences explicitly (SELECT seq.NEXTVAL FROM DUAL before an INSERT), while PostgreSQL idioms prefer SERIAL or GENERATED AS IDENTITY columns. Both approaches work, but if you are migrating application code, check that the sequence consumption pattern is preserved. Oracle allows multiple NEXTVAL calls in a single query; PostgreSQL evaluates the sequence function once per row in many contexts.
Exception Handling: Oracle’s EXCEPTION block works similarly to PL/pgSQL’s EXCEPTION clause, but the exception names differ. Oracle’s NO_DATA_FOUND becomes PostgreSQL’s NO_DATA_FOUND (same name, fortunately), but Oracle’s TOO_MANY_ROWS, VALUE_ERROR, and INVALID_NUMBER need specific mapping. Custom exceptions in Oracle, defined with PRAGMA EXCEPTION_INIT, translate to PostgreSQL’s RAISE statement with custom SQLSTATE codes.
Cursor FOR Loops: Oracle’s implicit cursor FOR loop is a common pattern. PostgreSQL supports it too, but the syntax for declaring the loop variable differs. Ora2Pg handles most cases, but nested cursor patterns, REF CURSOR types as OUT parameters, and dynamic cursor queries often need manual review.
CONNECT BY: Oracle’s hierarchical query syntax with CONNECT BY and PRIOR has no direct PL/SQL equivalent in PostgreSQL. PostgreSQL uses recursive CTEs (WITH RECURSIVE) for the same purpose. The translation is not always automated; it requires understanding the tree traversal logic. This is one area where I always budget extra time.
Autonomous Transactions: Oracle’s PRAGMA AUTONOMOUS_TRANSACTION allows a procedure to commit independently of the caller’s transaction. PostgreSQL does not have this. The common workaround is dblink to a loopback connection, which is ugly but functional, or redesigning the pattern to use deferred constraint checking or a separate logging table written outside the main transaction. This is rare enough that I treat each instance individually.
Tool Selection: Ora2Pg, AWS DMS, and GenAI-Assisted Conversion
Ora2Pg remains the most capable open-source option for schema and data migration. It handles the widest range of Oracle objects, including synonyms (converted to views or search_path adjustments), database links (converted to postgres_fdw foreign tables), and materialized view logs (with appropriate notes about what does not translate). For teams doing the migration themselves without cloud-provider tooling, ora2pg plus a CDC pipeline is the standard stack.
AWS DMS Schema Conversion (formerly AWS SCT) is the right choice when you are migrating to Aurora PostgreSQL or RDS for PostgreSQL. As of early 2026, AWS added GenAI-assisted code conversion using Amazon Bedrock, which significantly improves the quality of PL/SQL-to-PL/pgSQL translation for complex packages. AWS DMS handles the ongoing data migration (both full load and CDC replication) and integrates with the schema conversion output. The tradeoff is that it works best within the AWS ecosystem.
Google Database Migration Service is the equivalent for migrations to Cloud SQL for PostgreSQL or AlloyDB. It supports Oracle as a source and handles both schema conversion and continuous replication. The continuous replication path reads Oracle Redo Logs directly, which is the same logical replication technique as Debezium-based approaches.
For the code that automated tools cannot convert, AWS DMS with GenAI assistance is genuinely useful for drafting translations of complex PL/SQL. I treat it as a first-draft generator, not a finished product. A senior DBA or developer still needs to review the output and test it, but the starting point is much better than a blank file.
For a general view of change data capture patterns, which underpin the continuous replication that makes near-zero-downtime migrations possible, the article on Debezium and CDC patterns covers the underlying mechanics in detail.
Near-Zero-Downtime Migration Architecture
The worst Oracle migrations I have seen used a big-bang approach: export the data, take the application offline for a maintenance window, import into PostgreSQL, fix the errors, and bring it back up. In an enterprise context with a transactional database used across time zones, that window is measured in hours and the risk is enormous.
The right architecture for a near-zero-downtime migration has several phases.
Phase 1: Schema and initial data load. Convert the schema to PostgreSQL format and load a point-in-time snapshot of all tables. This can take hours or days for large databases, and it runs without touching production.
Phase 2: Continuous replication. While the initial load is in progress or after it completes, establish a CDC pipeline from Oracle to PostgreSQL that reads Oracle’s Redo Log stream and applies changes to the PostgreSQL target. The pipeline keeps PostgreSQL current as the Oracle source continues taking writes. This is where ora2pg alone is not enough: you need DMS, Google DMS, Debezium with an Oracle connector (which requires Oracle LogMiner), or a commercial tool like Attunity or HVR (now Fivetran HVR).
The Oracle CDC path requires that Oracle Supplemental Logging be enabled at the table level or database level. This adds a small overhead to Oracle’s log writing and increases Redo Log volume, so you want to validate the storage impact before enabling it on a production system.
Phase 3: Application validation. Run your application against the PostgreSQL replica while Oracle remains the production source. This is not just functional testing; it is performance testing. PostgreSQL’s query planner behaves differently from Oracle’s, and queries that are fast on Oracle may be slow on PostgreSQL and vice versa. This phase often uncovers missing indexes and queries that relied on Oracle optimizer hints.
Phase 4: Cutover. When validation passes and you are confident in performance, you stop writes to Oracle, let the CDC pipeline drain, verify that both databases are identical, update the application’s database connection string, and restart the application pointing at PostgreSQL. This window can be as short as a few minutes for a well-prepared migration.
For cutover-specific patterns, the zero-downtime database migration guide covers expand-contract patterns and application-level techniques that apply here, particularly for schema changes you want to make in PostgreSQL that differ from the Oracle source schema.
Performance: Where Oracle and PostgreSQL Differ
PostgreSQL does not have optimizer hints in the Oracle sense. Oracle applications that use hints like /*+ INDEX(t idx_name) */ or /*+ LEADING(a b c) */ will have those hints ignored by PostgreSQL (they become comment text). This is usually fine, but occasionally an Oracle query was hinted because the optimizer was making a wrong choice, and without the hint PostgreSQL may make the same wrong choice or a different wrong choice.
The tools for influencing PostgreSQL’s planner are different: pg_hint_plan is a PostgreSQL extension that adds hint support, SET enable_seqscan = off and similar parameters disable specific plan types per-session, and in many cases the right answer is creating the right index rather than hinting.
Oracle’s BITMAP indexes, commonly used on low-cardinality columns in data warehouse tables, have no direct equivalent in PostgreSQL. PostgreSQL uses B-tree indexes with partial index and multicolumn index strategies to cover similar use cases. For analytical workloads, PostgreSQL’s indexing strategies including BRIN indexes for sequential data and GIN indexes for array and full-text columns can cover most of what bitmap indexes were doing.
Function-based indexes: Oracle allows indexes on expressions (function-based indexes). PostgreSQL supports these with expression indexes and they work the same way. Ora2Pg converts them correctly.
Partitioning: Oracle’s partition pruning strategies and partition-wise joins translate well to PostgreSQL’s native declarative partitioning (introduced in PostgreSQL 10, substantially improved in later versions). The partition key, partition type (range, list, hash), and partition constraints translate directly. Subpartitions require more work.
Statistics and autovacuum: Oracle has DBMS_STATS for collecting statistics; PostgreSQL has autovacuum and ANALYZE. After a large data load, run ANALYZE explicitly on all tables before testing query performance. Dead tuples from the initial load can accumulate; run VACUUM ANALYZE rather than just ANALYZE if you see bloat.
One pattern I have seen cause performance regressions in nearly every Oracle migration: Oracle’s date range queries on unindexed columns that benefited from Oracle’s full table scan parallelism. PostgreSQL’s parallel query is capable but behaves differently, and the parallel degree is controlled by max_parallel_workers_per_gather rather than Oracle’s PARALLEL hint. Check your most expensive query patterns explicitly.
Post-Migration: Connection Pooling and the Path Forward
Oracle applications typically use a single connection per application server thread or use Oracle’s connection pool via JDBC or OCI. PostgreSQL connection handling is process-based: each connection spawns a backend process. At high concurrency this becomes a bottleneck. PgBouncer is the standard solution.
The PgBouncer and connection pooling guide covers the transaction vs. session pooling tradeoff in detail. The short version: if your migrated PL/SQL code uses session-level state (SET, temporary tables, prepared statements that persist across transactions), you need session mode, which limits the pooling efficiency. If you refactored away session-level state during the migration, transaction mode gives you much better connection multiplexing.
For teams choosing a managed PostgreSQL destination, the managed PostgreSQL comparison covers Aurora PostgreSQL, AlloyDB, and Azure Flexible Server in detail. AlloyDB’s columnar engine is worth evaluating if you have analytical workloads that you were running on Oracle with partitioned tables and parallel query.
Once you are on PostgreSQL, the PostgreSQL extensions ecosystem opens up capabilities that Oracle required separate products to deliver: time-series storage with TimescaleDB, horizontal sharding with Citus, geospatial queries with PostGIS, and vector similarity search with pgvector.
For teams who want to run PostgreSQL on Kubernetes rather than a managed service, PostgreSQL HA with Patroni covers the self-managed path with automatic failover.
The Schema-as-Code Transition
After the migration, your PostgreSQL schema needs to be managed going forward. Oracle teams often rely on Oracle Enterprise Manager and ad-hoc DDL scripts for schema management. PostgreSQL teams that want to maintain the same operational discipline they had on Oracle typically reach for Atlas or Flyway for declarative schema management.
The database schema-as-code guide covers how to take schema management from ad-hoc DDL to a version-controlled, CI-reviewed, automatically applied process. This is worth establishing immediately after migration while the schema is clean and before the same ad-hoc patterns that accumulated on Oracle begin accumulating on PostgreSQL.
What Actually Goes Wrong
In my experience, the biggest surprises in Oracle migrations fall into three categories.
Undocumented application behavior: Applications that worked on Oracle for years often rely on subtle Oracle-specific behaviors that nobody documented because nobody had to. NULL comparison behavior (Oracle treats empty string and NULL identically in many contexts; PostgreSQL does not), case-sensitive table name handling when identifiers are quoted, and character encoding edge cases all surface during the validation phase.
PL/SQL volume underestimation: The ora2pg assessment counts objects. It does not count lines. A package with 5 procedures might have 5 lines per procedure or 500. The business logic embedded in Oracle packages at enterprises that built on Oracle in the 2000s is often extensive, and the conversion effort is proportional to the volume and complexity of that code, not the object count.
CDC pipeline stability: Getting CDC to start is easier than keeping it running. Oracle Redo Log management, supplemental logging overhead, LogMiner session handling, and network connectivity between the Oracle source and the migration target all need to be stable for the weeks or months the pipeline runs before cutover. Plan for pipeline monitoring, alerting, and a runbook for restarting it after Oracle maintenance windows or log archive gaps.
The Migration You Keep Putting Off
The Oracle migration is the one that keeps appearing in the roadmap and getting deprioritized. I have seen teams punt it for five consecutive years because something more urgent was always on the queue. In 2026, the urgency calculus is different: Oracle licensing audits are more frequent, cloud-native managed PostgreSQL services have removed most of the operational risk, and the tooling for near-zero-downtime migration has matured to the point where the migration itself is less risky than it used to be.
The teams that are completing migrations now are doing it with a parallel-run CDC architecture, not a big-bang weekend cutover. They are running their applications against PostgreSQL for weeks before the actual cutover, finding the performance regressions, fixing the application behavior differences, and switching over with confidence.
The PL/SQL conversion is still real work. Plan for it honestly, do not assume automated tools will handle everything, and allocate enough time for the validation phase. But the migration is achievable in a way it genuinely was not ten years ago.
The Oracle invoice that arrives next quarter is a better motivator than I can write. Get the assessment running before it does.
Get Cloud Architecture Insights
Practical deep dives on infrastructure, security, and scaling. No spam, no fluff.
By subscribing, you agree to receive emails. Unsubscribe anytime.