Migrate from Oracle Database
to PostgreSQL
Migrating from Oracle Database to PostgreSQL replaces a proprietary RDBMS with the most advanced open-source relational database, eliminating license costs that often represent the largest single IT expenditure. The migration requires systematic conversion of PL/SQL to PL/pgSQL, careful data type mapping from Oracle-specific types, and replacement of Oracle-specific features like synonyms, database links, and Real Application Clusters with PostgreSQL equivalents.
When Oracle Database stops working
Oracle becomes a constraint when licensing costs consume a disproportionate share of the database budget, particularly under Oracle's core-based licensing model where virtualization and cloud deployment multiply license requirements. The audit risk associated with Oracle's License Management Services creates ongoing compliance overhead. Organizations find Oracle's pricing model increasingly punitive when scaling horizontally or deploying to public clouds, where Oracle charges premium rates for cloud instances. When the cost of Oracle licenses exceeds the total engineering cost of migration, the economic argument becomes definitive.
What PostgreSQL unlocks
PostgreSQL eliminates per-core and per-processor licensing entirely, typically reducing database TCO by 70-90%. Its extensibility through extensions like PostGIS, pg_trgm, and hstore provides specialized capabilities without additional licensing. PostgreSQL's active open-source community delivers frequent releases with performance improvements, and its compatibility with cloud-managed offerings like Amazon RDS, Google Cloud SQL, and Azure Database for PostgreSQL provides operational flexibility. The JSONB data type enables document-store patterns within the relational model, and logical replication allows zero-downtime upgrades between major versions.
Who should not migrate
Organizations with deep investments in Oracle-specific technologies like Oracle Advanced Queuing, Oracle Spatial with network data models, Oracle Text for full-text indexing with specialized linguistic features, or Oracle Application Express (APEX) applications should carefully evaluate the rewrite cost. Systems relying on Oracle Real Application Clusters for horizontal read-write scaling have no direct PostgreSQL equivalent, though Citus provides distributed PostgreSQL for specific workload patterns. If your application uses hundreds of Oracle packages with complex PL/SQL interdependencies, object types, and pipelined table functions, the conversion effort may span years for large codebases.
What usually goes wrong
The most underestimated challenge is PL/SQL to PL/pgSQL conversion for complex stored procedure codebases. Oracle's PL/SQL supports packages with specification and body separation, autonomous transactions, bulk collect with FORALL, and pipelined table functions that have no direct PL/pgSQL equivalent. Implicit data type conversions differ between the two databases: Oracle treats empty strings as NULL while PostgreSQL does not, causing application logic failures that are difficult to detect in testing. Transaction isolation behavior differences, particularly around Oracle's read-committed snapshot isolation versus PostgreSQL's MVCC implementation, can cause subtle concurrency bugs. Teams frequently underestimate the effort required to migrate Oracle sequences with CACHE and ORDER options, materialized views with query rewrite, and partitioned tables with complex sub-partitioning schemes.
Risk Matrix: Oracle Database to PostgreSQL
Oracle PL/SQL features like autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION), package-level global variables, BULK COLLECT with LIMIT and FORALL, implicit cursor attributes (%FOUND, %ROWCOUNT), and exception handling with WHEN OTHERS THEN have different semantics or require restructuring in PL/pgSQL. Automated conversion tools handle syntax but miss semantic differences.
Use Ora2Pg for initial automated conversion, then conduct manual review of every procedure classified as medium or high complexity. Build a comprehensive test harness that exercises each procedure with production-representative data. Implement Oracle-compatible wrapper functions in PostgreSQL for commonly used Oracle built-in functions like NVL, NVL2, DECODE, and TO_DATE with Oracle format masks.
Oracle NUMBER without precision maps to PostgreSQL numeric, but application code may assume specific precision. Oracle VARCHAR2 byte semantics versus PostgreSQL varchar character semantics can cause data truncation for multi-byte character sets. Oracle DATE includes time components while PostgreSQL date does not, requiring migration to timestamp. CLOB and BLOB handling differs fundamentally between the two platforms.
Create a detailed data type mapping document covering every column in the schema. Run data profiling on Oracle to determine actual precision, scale, and length usage for NUMBER and VARCHAR2 columns. Migrate Oracle DATE columns to PostgreSQL timestamp to preserve time components. Validate data integrity post-migration by comparing row-level checksums between source and target.
Application code contains Oracle-specific syntax like (+) for outer joins, CONNECT BY for hierarchical queries, ROWNUM for pagination, MERGE statements with complex conditions, and Oracle-specific date arithmetic. ORM-generated SQL may use Oracle dialect features transparently.
Audit all application SQL using Oracle's AWR or ASH reports to identify every distinct query pattern. Replace CONNECT BY with PostgreSQL recursive CTEs. Replace ROWNUM with ROW_NUMBER() window functions or LIMIT/OFFSET. Convert (+) syntax to ANSI JOIN syntax. Replace MERGE with PostgreSQL INSERT ON CONFLICT (UPSERT) or dedicated merge logic.
Oracle RAC provides active-active multi-node clustering that PostgreSQL cannot directly replicate. Data Guard provides synchronous and asynchronous standby databases with automatic failover. Replacing this with Patroni for failover, streaming replication for standbys, and optionally Citus for distributed queries requires different operational expertise and monitoring.
Design the PostgreSQL HA architecture before beginning data migration. Deploy Patroni with etcd for leader election and automatic failover. Configure synchronous replication for zero data loss requirements. Conduct failure injection testing (kill primary, network partition, disk failure) to validate failover behavior meets RTO and RPO targets.
Oracle has been known to initiate License Management Services audits when customers announce migration plans or reduce license renewals. Audit findings during an active migration can create legal and financial complications that delay the project.
Engage Oracle licensing specialists before announcing migration timelines. Ensure current Oracle deployments are fully compliant before beginning migration. Maintain Oracle licenses through the parallel-run period. Document the decommission timeline clearly and retain proof of Oracle instance termination for compliance purposes.
What Must Not Change During This Migration
Every row in every migrated table must be verified through checksum comparison between Oracle and PostgreSQL before cutover
All stored procedures must produce identical outputs for the same inputs on both platforms, validated through automated regression testing
Transaction isolation behavior must be explicitly tested for concurrent workloads to ensure application correctness under PostgreSQL MVCC
Character encoding must be validated end-to-end, ensuring Oracle AL32UTF8 or WE8ISO8859P1 data migrates correctly to PostgreSQL UTF-8
Sequence values in PostgreSQL must be initialized ahead of Oracle's current values to prevent primary key collisions during dual-write periods
Migration Process: Oracle Database to PostgreSQL
Schema assessment
Run Ora2Pg's assessment mode to generate a migration complexity report scoring each schema object. Inventory all tables, indexes, views, materialized views, sequences, synonyms, database links, types, packages, procedures, functions, and triggers. Classify each PL/SQL unit by conversion complexity (simple, medium, complex) based on feature usage. Document Oracle-specific features that require architectural redesign: partitioning schemes, materialized view refresh groups, virtual private database policies, and fine-grained auditing.
Query translation
Convert DDL using Ora2Pg with customized type mappings. Convert PL/SQL packages to PostgreSQL schemas with standalone functions, breaking package state into session variables or application-level caching. Replace Oracle-specific functions with PostgreSQL equivalents or custom compatibility functions (e.g., create NVL as a SQL function, implement DECODE using CASE). Convert hierarchical queries from CONNECT BY PRIOR to recursive CTEs with cycle detection.
Data migration
Use Ora2Pg for initial bulk data export from Oracle to PostgreSQL-compatible COPY format. For tables exceeding 100 million rows, parallelize extraction by partitioning on primary key ranges. Disable indexes, constraints, and triggers on PostgreSQL target tables during bulk load, then rebuild them afterward. For ongoing synchronization during the parallel-run phase, configure Oracle GoldenGate or Debezium with Kafka to stream changes from Oracle to PostgreSQL in near real-time.
Parallel validation
Execute the full application test suite against PostgreSQL. Run critical business queries on both databases and compare results using automated diff tools. Validate that materialized view refresh produces identical results. Test stored procedure outputs with production-scale data volumes. Perform load testing to verify PostgreSQL handles peak concurrency with acceptable latency. Verify that application connection pooling (PgBouncer or pgpool-II) handles the connection patterns previously managed by Oracle's shared server architecture.
Traffic cutover
Switch application database connections from Oracle to PostgreSQL using connection string updates or DNS CNAME changes. For zero-downtime cutover, implement a brief write-pause window where Oracle changes are drained to PostgreSQL via CDC, then redirect all traffic. Update all application configuration, ORM dialect settings, JDBC/ODBC driver configurations, and connection pool parameters. Verify that all batch jobs, cron-scheduled procedures, and reporting queries are pointing to PostgreSQL.
Verification and decommission
Monitor PostgreSQL performance using pg_stat_statements, auto_explain, and pgBadger for the first 30 days. Compare query performance profiles against Oracle AWR baselines. Tune PostgreSQL configuration parameters (shared_buffers, work_mem, effective_cache_size, random_page_cost) based on observed workload patterns. After confirming stability, archive Oracle data exports for regulatory retention, terminate Oracle instances, and initiate the license termination process with Oracle.
How This Migration Changes at Scale
PL/SQL codebase exceeds 100,000 lines
Stored procedure conversion becomes the dominant project timeline driver. Requires dedicated PL/SQL-to-PL/pgSQL conversion specialists and an iterative migration approach where procedures are converted and validated in dependency order. Expect 30-50% of complex procedures to require manual rewriting rather than automated conversion.
Database size exceeds 10 TB
Bulk data migration requires careful orchestration to meet cutover windows. Parallel extraction from Oracle and parallel COPY loading into PostgreSQL are essential. Initial data migration may take days, requiring CDC-based synchronization to capture changes during the transfer. Consider using pg_bulkload for faster loading performance.
Active Oracle RAC deployment with more than 4 nodes
The HA architecture redesign becomes a significant workstream. PostgreSQL streaming replication provides active-passive failover but not active-active read-write scaling. Workloads that relied on RAC for write scalability may need application-level sharding, Citus distributed tables, or architectural changes to reduce write hotspots.
Related Migration Paths
Oracle Database migrations often coincide with moving away from Oracle E-Business Suite or other Oracle applications that depend on the Oracle database, creating opportunities to modernize the entire application stack.
Organizations standardizing on PostgreSQL frequently migrate both Oracle and SQL Server databases, sharing tooling, expertise, and PostgreSQL operational knowledge across both migration tracks.
Oracle Database migration is often part of a broader legacy modernization initiative that includes refactoring monolithic applications, adopting microservices, and moving to cloud-native infrastructure.
Related Analysis
Oracle Database vs PostgreSQL
For organizations evaluating whether to leave Oracle's ecosystem, PostgreSQL offers a mature open-source alternative with zero licensing costs.
Read analysisWhen Oracle Database Becomes Too Expensive
6 warning signs that Oracle Database has outgrown its limits.
Read analysisIf you're evaluating a migration from Oracle Database to PostgreSQL, the first step is validating risk, scope, and invariants before any build work begins.