# Oracle to PostgreSQL Migration: Save $220K+ in Annual Licensing Oracle Database Enterprise Edition costs $47,500 per processor core. Not per server. Not per instance. Per core. A midsize company running Oracle on a dual-socket server with 8 cores pays $380,000 ... ## Oracle to PostgreSQL Migration: Save $220K+ in Annual Licensing Full-scope Oracle Database to PostgreSQL migration — PL/SQL to PL/pgSQL conversion, schema mapping, data type translation, stored procedure rewriting, and parallel-run validation — from a Zeeland, MI company with 20+ years of enterprise database engineering. We have migrated databases with 500+ stored procedures and billions of rows. Zero licensing fees. Zero vendor lock-in. --- ## Our Process 1. **Oracle Licensing Audit & Migration Assessment (1–2 Weeks)** — We start with the money. Before touching a single schema, we audit your current Oracle licensing: edition (Standard vs. Enterprise), named user plus vs. processor licensing, installed options and packs (Partitioning, RAC, Advanced Security, Diagnostics, Tuning), actual feature usage versus what you are licensed for, and annual support costs. This produces a hard-dollar annual cost figure that becomes the ROI baseline for the migration. On the technical side, we inventory the Oracle database: schema count, table count, total data volume, stored procedure count and complexity, Oracle-specific feature usage (Advanced Queuing, Spatial, XMLDB, Flashback), application connection patterns, and current performance baselines from AWR reports. Deliverable: a migration feasibility report with licensing cost savings, estimated migration effort, risk assessment, and a phased timeline. 2. **Schema Conversion & Data Type Mapping (2–3 Weeks)** — Ora2Pg generates the initial schema conversion: tables, indexes, constraints, sequences, views, and basic functions. We then manually review and correct every data type mapping, paying particular attention to Oracle NUMBER columns (which require precision/scale analysis to determine the correct PostgreSQL type), DATE columns (which must become timestamp, not date), LOB columns, and any XMLTYPE or spatial data. We redesign indexes based on PostgreSQL's optimizer behavior — Oracle's bitmap indexes convert to GIN indexes, function-based indexes may need expression indexes, and Oracle's index-organized tables convert to PostgreSQL CLUSTER or covering indexes. Partitioning strategy is redesigned using PostgreSQL's declarative partitioning. The output is a complete DDL script for the target PostgreSQL schema that has been reviewed, tested, and validated against sample data. 3. **PL/SQL to PL/pgSQL Conversion & Application SQL Remediation (3–8 Weeks)** — This is the most labor-intensive phase. Every stored procedure, function, trigger, and package is converted from PL/SQL to PL/pgSQL. Oracle packages are decomposed into PostgreSQL schemas with standalone functions. Oracle-specific built-in package calls (DBMS_OUTPUT, DBMS_LOB, UTL_FILE, UTL_HTTP, DBMS_SQL, DBMS_SCHEDULER) are replaced with PostgreSQL equivalents or application-layer implementations. Simultaneously, we scan all application code (Java, Python, .NET, PHP, Node.js — whatever your stack) for Oracle-specific SQL: CONNECT BY, DECODE, NVL/NVL2, ROWNUM, (+) outer join syntax, MERGE statements, Oracle date arithmetic, and sequence references. Every query is converted to PostgreSQL-compatible SQL and regression-tested. Duration depends on stored procedure count and complexity — 50 procedures takes 3–4 weeks, 200–500 procedures takes 6–8 weeks. 4. **Data Migration & Integrity Validation (1–3 Weeks)** — Data migration runs in two stages. Stage one is the bulk initial load: full table extracts from Oracle, data type conversion, character encoding normalization (Oracle AL32UTF8 to PostgreSQL UTF-8), and bulk loading into PostgreSQL using COPY for maximum throughput. For large databases (500GB+), we use parallel extraction and loading across multiple tables simultaneously to minimize migration window. Stage two is incremental sync: changes made in Oracle after the initial extract are captured and applied to PostgreSQL using log-based change data capture. Validation is exhaustive — row counts per table, column-level checksums, referential integrity verification, constraint validation, and business-logic spot checks on critical data (financial totals, inventory counts, customer record completeness). Any discrepancy triggers investigation and re-migration of the affected data before proceeding. 5. **Performance Tuning & Parallel Run (2–4 Weeks)** — With data loaded, we run PostgreSQL under production-equivalent workload. The top 50–100 queries by execution time from Oracle AWR are benchmarked on PostgreSQL. We tune postgresql.conf (shared_buffers at 25% of RAM, effective_cache_size at 75% of RAM, work_mem sized for sort operations, maintenance_work_mem for vacuum and index builds, WAL settings for write throughput). Queries that underperform are analyzed with EXPLAIN (ANALYZE, BUFFERS) and optimized through index additions, query rewrites, or planner parameter adjustments. The parallel run phase routes production reads to both Oracle and PostgreSQL simultaneously, comparing results and response times. This phase catches edge cases that unit testing misses: queries that perform differently on production data distributions, timezone-dependent logic, locale-sensitive sorting, and race conditions under concurrent load. 6. **Production Cutover & Oracle Decommission (1 Week + 30 Days Hypercare)** — Production cutover executes during a maintenance window scheduled with your operations team. Application connection strings switch from Oracle to PostgreSQL. Oracle remains online in read-only mode for 48–72 hours as a rollback safety net. We monitor PostgreSQL continuously during the first 72 hours: query performance, replication lag (if using streaming replication for HA), connection pool utilization, lock contention, and error rates. Hypercare support runs for 30 days post-cutover, covering performance issues, edge case bugs in converted procedures, and any application behavior differences that surface under real production usage. After the hypercare period, we provide a decommission plan for the Oracle infrastructure — terminating instances, canceling licenses, and documenting the Oracle-to-PostgreSQL mapping for future reference. --- ## Frequently Asked Questions ### How much does an Oracle to PostgreSQL migration cost? Migration cost depends on three variables: database complexity (schema objects, stored procedures, Oracle-specific features), data volume, and application layer changes. A small Oracle database with 20–50 tables, minimal stored procedures, and one application costs $25,000–$60,000. A mid-size database with 100–300 tables, 100–300 stored procedures, Oracle packages, and 2–4 connected applications runs $80,000–$200,000. A large enterprise migration with 500+ stored procedures, Oracle RAC, Advanced Queuing, partitioning, and 5+ consuming applications costs $200,000–$500,000+. Even at the high end, the migration investment pays for itself within 12–24 months through eliminated Oracle licensing. A company paying $300,000 per year in Oracle licensing that spends $250,000 on migration recovers the full cost in 10 months and saves $300,000 every year after that. ### How long does the migration take from start to production cutover? Simple migrations (under 50 stored procedures, no Oracle-specific features, single consuming application) take 8–12 weeks from kickoff to production cutover. Mid-complexity migrations (100–300 stored procedures, Oracle packages, partitioning, 2–4 applications) take 12–20 weeks. Complex enterprise migrations (500+ stored procedures, RAC, Advanced Queuing, multiple Oracle-specific features, 5+ applications) take 20–36 weeks. The primary timeline driver is stored procedure conversion — this is manual engineering work that cannot be fully automated. The second driver is application testing — every application that connects to Oracle must be tested against PostgreSQL, and in regulated industries, this includes full regression testing with documented evidence. We provide a detailed week-by-week timeline during the assessment phase so there are no surprises. ### Can PostgreSQL actually replace Oracle for enterprise workloads? Yes, and it has been doing so at scale for over a decade. PostgreSQL handles ACID transactions, row-level security, tablespace management, point-in-time recovery, streaming replication, logical replication, declarative partitioning, parallel query execution, full-text search, JSONB document storage, and advanced analytics with window functions and CTEs — all features that Oracle charges separately for. Apple runs PostgreSQL. Instagram ran on PostgreSQL before and after the Facebook acquisition. The International Space Station uses PostgreSQL. The UK National Health Service migrated from Oracle to PostgreSQL. PostgreSQL handles databases in the tens of terabytes with proper architecture. The question is not capability — it is feature parity for your specific workload, which is exactly what our assessment phase determines. In 20 years of database work, we have encountered exactly two scenarios where Oracle had a genuine technical advantage over PostgreSQL: Oracle Spatial for complex 3D geometry workloads (PostgreSQL PostGIS handles 95% of geospatial use cases but lacks some 3D operations), and Oracle Real Application Clusters for shared-storage active-active clustering (PostgreSQL uses shared-nothing architecture with Patroni, which is architecturally different but handles the same availability requirements for most workloads). ### What about our PL/SQL stored procedures? Can they be automatically converted? Partially. Ora2Pg, the leading open-source Oracle-to-PostgreSQL conversion tool, handles roughly 60–70% of PL/SQL to PL/pgSQL conversion automatically: basic syntax changes, simple cursor operations, standard exception handling, and data type mapping. The remaining 30–40% requires manual engineering. The areas that automation cannot handle include Oracle packages (which must be decomposed into schemas and standalone functions since PostgreSQL has no package construct), Oracle-specific built-in packages (DBMS_OUTPUT, DBMS_LOB, UTL_FILE, UTL_HTTP, DBMS_SQL, DBMS_SCHEDULER, DBMS_AQ, DBMS_CRYPTO — each requires a PostgreSQL-specific replacement approach), autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION requires dblink or pg_background), dynamic SQL differences (Oracle's DBMS_SQL versus PostgreSQL's EXECUTE format), and bulk operations (FORALL and BULK COLLECT translate to PostgreSQL's set-based operations but require structural refactoring, not just syntax changes). For a database with 200 stored procedures, expect approximately 130 to convert automatically with minor manual cleanup, and 70 to require substantial manual rewriting — that manual work is 60–80% of the total conversion effort. ### What are the biggest risks in Oracle to PostgreSQL migration? The top five risks, in order of frequency: First, performance regression on complex queries. Oracle's optimizer and PostgreSQL's planner make different decisions. Queries that ran in 200ms on Oracle can run in 20 seconds on PostgreSQL if the planner chooses the wrong join strategy or misestimates cardinality. Mitigation: benchmark the top 100 queries before cutover and tune until performance matches. Second, implicit behavior differences. Oracle treats empty strings as NULL. PostgreSQL does not. Oracle's DATE type includes time. PostgreSQL's date type does not. Oracle's default sort order for NULLs is NULLS LAST. PostgreSQL's is NULLS LAST for ASC and NULLS FIRST for DESC (matching the SQL standard). These subtle differences cause application bugs that unit tests miss. Mitigation: comprehensive integration testing with production data. Third, transaction semantics. Oracle's default isolation level is READ COMMITTED with statement-level read consistency. PostgreSQL's READ COMMITTED provides different snapshot behavior for long-running transactions. Applications that depend on Oracle-specific MVCC behavior may see different results. Mitigation: identify long-running transactions and test explicitly. Fourth, character set and collation differences. Oracle AL32UTF8 and PostgreSQL UTF-8 are compatible, but collation behavior (sort order for accented characters, case sensitivity) can differ. Mitigation: validate sort-dependent application logic. Fifth, stored procedure conversion defects. Complex PL/SQL that was manually converted may contain subtle bugs that only surface with specific data patterns. Mitigation: parallel-run validation comparing Oracle and PostgreSQL outputs row-by-row for 2–4 weeks before cutover. ### Should we migrate to PostgreSQL on-premises or move to cloud-managed PostgreSQL (RDS, Cloud SQL, Azure Database)? The answer depends on your current infrastructure strategy and operational capacity. Cloud-managed PostgreSQL (AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL) eliminates database administration overhead — automated backups, automated minor version upgrades, push-button replication, and built-in monitoring. For companies that do not have a dedicated DBA, managed PostgreSQL reduces operational risk significantly. AWS RDS for PostgreSQL on a db.r6i.2xlarge instance costs approximately $1.04–$1.39 per hour ($9,100–$12,200 per year) versus the equivalent Oracle RDS instance at $3.60–$9.82 per hour ($31,500–$86,000 per year). On-premises PostgreSQL makes sense if you have existing data center infrastructure with sunk costs, regulatory requirements that mandate on-premises data residency, a DBA team that can manage PostgreSQL operations, or performance requirements that benefit from bare-metal hardware. Many of our clients use a hybrid approach: production on managed cloud PostgreSQL for availability and operational simplicity, with on-premises PostgreSQL for development and testing environments. We architect the migration to be infrastructure-agnostic so you can deploy to any target without re-engineering. ### What Oracle features have no direct PostgreSQL equivalent? Four Oracle features lack a direct one-to-one PostgreSQL equivalent, though all have workable alternatives. First, Oracle Real Application Clusters (RAC) — true shared-storage, active-active clustering where multiple instances read and write to the same physical storage. PostgreSQL uses shared-nothing architecture. The alternative is Patroni with streaming replication for automatic failover, or Citus for distributed PostgreSQL if you need horizontal write scaling. For the vast majority of workloads, Patroni provides equivalent availability at zero licensing cost. Second, Oracle Packages — namespace constructs that group procedures, functions, and variables. PostgreSQL has no package concept. We decompose packages into schemas containing standalone functions and handle package state through session variables or temporary tables. Third, Oracle Advanced Queuing (AQ) — an integrated message queuing system built into the database. PostgreSQL's LISTEN/NOTIFY provides basic pub/sub, but for enterprise queuing requirements, we implement pgq (Skytools) or use an external broker like RabbitMQ or Kafka alongside PostgreSQL. Fourth, Oracle Flashback — the ability to query data as it existed at a previous point in time without restoring from backup. PostgreSQL does not have built-in flashback. The alternative is point-in-time recovery (PITR) using WAL archiving, or application-level temporal tables using system-versioned tables (available in PostgreSQL 16+ via extensions). In every case, we implement the PostgreSQL alternative during migration so there is no functionality gap at cutover. --- **Canonical URL**: https://freedomdev.com/solutions/oracle-to-postgresql-migration _Last updated: 2026-05-12_