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.
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 in licensing alone — before a single query executes. On top of that, Oracle charges 22% of the license fee annually for Software Update License & Support, which means that 8-core deployment costs $83,600 every single year in mandatory support fees just to receive patches and stay in compliance. Cancel support, and Oracle revokes your right to run the software on new hardware or apply security patches. You are locked in, paying rent on software you already bought, forever.
The per-core pricing is only the beginning. Oracle Real Application Clusters (RAC), required for high availability, adds $23,000 per core on top of the base license — bringing your 8-core HA deployment to $564,000 in licensing plus $124,080 per year in support. Oracle Partitioning, necessary for any table over a few hundred million rows, costs $11,500 per core. Oracle Advanced Security, which provides Transparent Data Encryption and network encryption that PostgreSQL includes for free, costs $15,000 per core. Oracle Diagnostics and Tuning packs, required to use AWR and SQL Tuning Advisor, cost $7,500 and $5,000 per core respectively. A fully-featured Oracle Enterprise deployment on 16 cores easily exceeds $1.2 million in upfront licensing with $250,000+ in annual support. This is not an edge case. This is what Oracle's own price list produces for a company that needs enterprise database features.
The hidden cost is Oracle's audit program. Oracle employs one of the most aggressive software license audit teams in the enterprise technology industry. Companies routinely discover during an Oracle audit that they owe six or seven figures in back-licensing fees because a DBA enabled a feature that was installed by default, because VMware vMotion moved a database to a host with more cores, or because a developer connected to an Enterprise Edition instance from a test environment. The Business Software Alliance estimates that 39% of Oracle customers have faced an unplanned compliance cost due to licensing complexity. These are not theoretical risks — they are line items that appear on your balance sheet with Oracle's legal team behind them.
Meanwhile, PostgreSQL costs zero in licensing. Zero in annual support fees to the vendor. Zero per core. Zero for partitioning. Zero for encryption. Zero for replication. The total cost of PostgreSQL ownership is the infrastructure it runs on and the engineers who manage it — costs you are already paying with Oracle, just buried under licensing fees. The question is not whether to migrate. The question is how much longer you can justify paying Oracle $100K–$500K+ per year for features that PostgreSQL delivers for free.
Oracle Enterprise Edition: $47,500 per core licensing + 22% annual support ($10,450/core/year in perpetuity)
Oracle RAC for high availability: additional $23,000 per core, making HA deployments $70,500/core before support
Oracle Partitioning ($11,500/core), Advanced Security ($15,000/core), Diagnostics Pack ($7,500/core) — all charged separately for features PostgreSQL includes free
16-core fully-featured Oracle deployment: $1.2M+ upfront, $250K+/year ongoing support — versus $0 for PostgreSQL
AWS RDS for Oracle: $3.60–$9.82/hr for db.r6i.2xlarge (Enterprise) vs. $1.04–$1.39/hr for equivalent RDS PostgreSQL — 3–7x cloud markup
Oracle license audits average $200K–$2M in unplanned compliance costs per finding, with 39% of Oracle customers affected
Vendor lock-in: PL/SQL, Oracle-specific SQL syntax (CONNECT BY, DECODE, NVL), and proprietary features trap you in a single-vendor ecosystem with no negotiating leverage on renewals
Our engineers have built this exact solution for other businesses. Let's discuss your requirements.
Oracle to PostgreSQL migration is not a simple export-import. Oracle and PostgreSQL are both mature relational databases, but they diverge in data types, procedural language syntax, SQL dialect, optimizer behavior, transaction semantics, and administrative tooling. A migration that handles the schema and data but ignores stored procedure logic, application query patterns, and performance characteristics will produce a database that technically runs but performs 10x worse than the Oracle original. FreedomDev approaches Oracle-to-PostgreSQL migration as a six-phase engineering project: assessment and licensing audit, schema conversion and data type mapping, PL/SQL to PL/pgSQL stored procedure translation, application SQL remediation, data migration and validation, and parallel-run cutover with rollback capability.
We use Ora2Pg for automated schema conversion as a starting point — it handles roughly 60–70% of straightforward schema objects (tables, indexes, sequences, basic views). The remaining 30–40% requires manual engineering: complex Oracle packages that need to be decomposed into PostgreSQL schemas and standalone functions, Oracle-specific data types that have no direct equivalent (INTERVAL YEAR TO MONTH, BFILE, XMLTYPE), materialized view refresh strategies that differ fundamentally between Oracle and PostgreSQL, and Oracle Advanced Queuing implementations that need replacement with PostgreSQL's LISTEN/NOTIFY, pg_partman, or an external message broker like RabbitMQ. The automated tools get you started. The engineering expertise gets you to production.
The stored procedure layer is where most migrations stall. Oracle PL/SQL and PostgreSQL PL/pgSQL are superficially similar — both are procedural extensions to SQL with block structure, exception handling, cursors, and control flow. But the differences in syntax, built-in packages, data type handling, and transaction behavior are pervasive. Oracle's DBMS_OUTPUT, DBMS_LOB, DBMS_SQL, UTL_FILE, UTL_HTTP, and dozens of other built-in packages have no direct PostgreSQL equivalent. Oracle packages (which group related procedures, functions, types, and variables into a single namespace) do not exist in PostgreSQL and must be decomposed into schemas with individual functions. Oracle's autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION) require a fundamentally different approach in PostgreSQL, typically using dblink or pg_background. A database with 200–500 stored procedures requires 4–12 weeks of dedicated PL/pgSQL engineering, depending on complexity and how heavily the procedures depend on Oracle-specific packages.
Every Oracle data type has a PostgreSQL equivalent, but the mapping is not always one-to-one. Oracle NUMBER (the universal numeric type) maps to PostgreSQL integer, bigint, numeric, or double precision depending on precision and scale — getting this wrong causes silent data truncation or application errors. VARCHAR2 maps to PostgreSQL text or varchar. Oracle CLOB and NCLOB map to text. BLOB maps to bytea. Oracle DATE (which includes time) maps to timestamp, not date — one of the most common migration bugs. Oracle TIMESTAMP WITH LOCAL TIME ZONE maps to timestamptz. RAW and LONG RAW map to bytea. XMLTYPE requires conversion to text or xml with XPath query rewriting. INTERVAL YEAR TO MONTH maps to PostgreSQL interval with application-layer validation. We document the complete type mapping for every table before writing a single line of migration code.
PL/SQL to PL/pgSQL conversion covers syntax changes (Oracle's IS/AS to PostgreSQL's AS $$ ... $$ LANGUAGE plpgsql), exception handling differences (Oracle EXCEPTION WHEN NO_DATA_FOUND translates directly, but WHEN OTHERS requires different error variable names — SQLERRM becomes the SQLERRM function), cursor syntax (Oracle explicit cursors use a different declaration and loop pattern), and built-in package replacement. Oracle DBMS_OUTPUT.PUT_LINE becomes RAISE NOTICE. UTL_FILE for file I/O has no built-in PostgreSQL equivalent and requires either pg_read_file/pg_write_file extensions or an application-layer approach. Oracle bulk operations (FORALL, BULK COLLECT) translate to PostgreSQL's native set-based operations which are often faster. We translate every procedure, validate against test data, and benchmark against Oracle execution times.
Oracle SQL uses syntax patterns that do not exist in standard SQL or PostgreSQL. CONNECT BY PRIOR for hierarchical queries converts to PostgreSQL recursive CTEs (WITH RECURSIVE). Oracle's DECODE function converts to CASE expressions. NVL becomes COALESCE. NVL2 becomes CASE WHEN ... IS NOT NULL. ROWNUM for row limiting converts to LIMIT/OFFSET or ROW_NUMBER() window functions. Oracle's outer join syntax (+) converts to explicit LEFT/RIGHT JOIN. MERGE (upsert) converts to INSERT ... ON CONFLICT. MINUS converts to EXCEPT. Oracle's date arithmetic (SYSDATE + 1 adds one day) requires PostgreSQL interval syntax (CURRENT_TIMESTAMP + INTERVAL '1 day'). Sequences use nextval('sequence_name') instead of sequence_name.NEXTVAL. We scan every application query and stored procedure for Oracle-specific syntax and convert it systematically.
Oracle packages — PL/SQL constructs that group procedures, functions, cursors, types, and package-level variables into a single namespace — have no equivalent in PostgreSQL. A single Oracle package with 30 procedures, 10 functions, 5 custom types, and package-level state variables must be decomposed into a PostgreSQL schema containing individual functions and types, with package-level state handled through either session variables (SET/current_setting), temporary tables, or application-layer caching. Package initialization blocks (the anonymous block at the bottom of the package body) require conversion to explicit initialization functions. Overloaded procedures within a package must be renamed or use PostgreSQL's function overloading based on argument types. We maintain a mapping document that traces every Oracle package member to its PostgreSQL equivalent so your development team can update application code references without guessing.
A migration that passes correctness tests but runs 5x slower than Oracle is a failed migration. Oracle's Cost-Based Optimizer and PostgreSQL's query planner make different decisions given the same schema and data distribution. Queries that perform well on Oracle may choose catastrophically bad plans on PostgreSQL if statistics are stale, if indexes that Oracle used do not exist on the PostgreSQL side, or if PostgreSQL's planner underestimates row counts due to different histogram granularity. We benchmark the top 50 queries by execution time from Oracle's AWR (Automatic Workload Repository) against their PostgreSQL equivalents. We tune PostgreSQL configuration (shared_buffers, effective_cache_size, work_mem, random_page_cost), add missing indexes, rewrite queries that the PostgreSQL planner handles differently, and use pg_hint_plan when the planner needs explicit guidance. Target is performance parity or better on 95%+ of queries.
Production cutover is the highest-risk phase of any migration. FreedomDev runs both Oracle and PostgreSQL in parallel during a validation period of 1–4 weeks. Application writes go to Oracle. A change data capture pipeline (using Oracle GoldenGate, Debezium, or custom log-based replication) streams changes to PostgreSQL in near-real-time. Reads are directed to both databases and results are compared. Row counts, checksums, and business-critical query outputs are validated continuously. Only when the parallel run shows zero discrepancies over the validation period do we execute the production cutover. The cutover itself uses a blue-green deployment pattern: application connections flip from Oracle to PostgreSQL via connection string change or DNS update, with Oracle kept online in read-only mode for 48–72 hours as a rollback safety net.
We were paying Oracle $412,000 per year in licensing and support for a database that powered four internal applications. FreedomDev migrated 340 stored procedures and 1.8 billion rows to PostgreSQL in 14 weeks. Our annual database cost dropped to $18,000 for managed hosting. The migration paid for itself in 11 months.
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.
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.
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.
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.
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.
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.
| Metric | With FreedomDev | Without |
|---|---|---|
| License Cost (8 cores) | PostgreSQL: $0 | Oracle Enterprise: $380,000 + $83,600/yr support |
| High Availability (8 cores) | Patroni + streaming replication: $0 licensing | Oracle RAC: $184,000 + $40,480/yr support |
| Partitioning (16 cores) | Built-in declarative partitioning: $0 | Oracle Partitioning option: $184,000 + $40,480/yr |
| Encryption (TDE + network) | pgcrypto + SSL: $0 | Oracle Advanced Security: $15,000/core ($240,000 for 16 cores) |
| Performance Monitoring | pg_stat_statements + pgBadger: $0 | Oracle Diagnostics Pack: $7,500/core ($120,000 for 16 cores) |
| AWS RDS (db.r6i.2xlarge) | $1.04–$1.39/hr ($9,100–$12,200/yr) | Oracle EE on RDS: $3.60–$9.82/hr ($31,500–$86,000/yr) |
| Audit Risk | Open source — no vendor audits, no compliance traps | Oracle audits average $200K–$2M in unplanned costs per finding |
| Vendor Lock-in | Standard SQL, runs anywhere, no exit fees | PL/SQL, proprietary syntax, re-migration cost if you ever leave |
Schedule a direct technical consultation with our senior architects.
Make your software work for you. Let's build a sensible solution.