Migrate Oracle to PostgreSQL

Ispirer offers InsightWays — a free assessment tool that connects to your Oracle database, analyzes all objects and structures, and produces a complexity and scope report before you commit to migration. The Ispirer Toolkit itself is also available for a free trial with no payment required.

Oracle's licensing model creates a substantial financial burden — per processor, per edition, with annual support costs. PostgreSQL is open source and free to use. Moving from Oracle to PostgreSQL eliminates vendor lock-in, reduces infrastructure costs, opens access to modern extensions without additional cost, and improves cloud readiness across AWS, GCP, and Azure.

Ispirer handles data migration directly — without middleware, without altering the source database, and without requiring unique columns. It supports parallel migration threads, custom data type mapping, BLOB migration at full speed, and Change Data Capture (CDC) for near-zero downtime scenarios.

The recommended approach is a live connection from Ispirer Toolkit to the source Oracle database via ODBC — this gives the tool full visibility into object dependencies and data types. Alternatively, files containing PL/SQL scripts can be converted without a live connection. Ispirer tools read from Oracle in read-only mode with no changes made to the source system.

Not inherently. Performance differences typically arise from configuration and query optimization rather than the database engine itself. Code optimized for Oracle needs to be reviewed and tuned for PostgreSQL after migration — this is a standard step in every Ispirer migration project.

Oracle Corporation does not provide tools or support for migrating to PostgreSQL. Ispirer Toolkit specifically bridges this gap with a 20,000+ rule engine that handles Oracle-specific constructs, data types, and procedural language differences.

Yes. Ispirer Data Migrator supports migration of all Oracle data types, including BLOB, CLOB, RAW, LONG, XMLType, and spatial data. The tool reads from Oracle in read-only mode with no dependency on unique columns or specific table structures.

Ora2Pg is a free open-source Oracle to Postgres migration tool that handles basic schema migration and data export. It requires significant manual effort for complex PL/SQL, Oracle packages, hierarchical queries, and dynamic SQL. For large-scale or complex migrations, automated tools like Ispirer Toolkit offer significantly higher automation rates and dedicated expert support.

Automation rates depend heavily on code complexity. For straightforward schema and simple stored procedures, automation routinely exceeds 90%. For codebases heavy in Oracle-specific features, rates vary and are assessed during pre-migration analysis. Ispirer's InsightWays tool provides a complexity index per object so you can estimate manual effort before the project begins. Customization of the toolkit can further increase automation — up to 95% as demonstrated in the Magnit Global project.

Ispirer Toolkit includes both global and local data type mapping engines. Oracle NUMBER maps to NUMERIC, VARCHAR2 to VARCHAR, DATE to TIMESTAMP, XMLTYPE to XML, RAW to BYTEA. Users can override default mappings at the object or column level where project requirements differ.

Does the automated solution support the migration of partitioned tables and advanced indexing?

Yes. Ispirer Toolkit migrates partitioned tables, translating Oracle's partitioning syntax to PostgreSQL's native declarative partitioning. Oracle index types are translated to their PostgreSQL equivalents — B-tree, GIN, GiST, BRIN, partial indexes. Oracle-specific index options with no PostgreSQL equivalent are dropped with logged warnings.

Ispirer converts Oracle global temporary tables to PostgreSQL temporary tables using ON COMMIT DELETE ROWS or ON COMMIT PRESERVE ROWS behavior depending on the Oracle table's configuration, and adjusts any code that references them accordingly.

Yes. Oracle CONNECT BY hierarchical queries are converted to PostgreSQL WITH RECURSIVE CTEs. The toolkit handles CONNECT BY LEVEL, CONNECT BY PRIOR, START WITH, and NOCYCLE clauses. A direct before/after example is available in the code samples section above.

Ispirer performs data integrity testing using snapshots of the source database. Migrated data in PostgreSQL is compared against source Oracle data using automated checks and scenario-based testing derived from the source application's actual usage patterns. Discrepancies are logged, analyzed, and corrected before production cutover.

Yes. Ispirer uses Change Data Capture (CDC) to replicate ongoing data changes from Oracle to PostgreSQL while the source remains live. Preliminary cold-data migration is performed in advance for large datasets, reducing the final cutover window significantly. The 12 TB in 12 hours case study above demonstrates this approach in production.

Oracle sequences are migrated to PostgreSQL sequences with matching start values and increment settings. Triggers are converted to PostgreSQL trigger functions plus a trigger definition that calls the function. Views are converted directly to PostgreSQL view syntax. All three object types are included in the full automated migration run.

The main areas to address are query plan differences, index strategy, connection pooling, VACUUM and autovacuum configuration for write-heavy tables, and partitioning design. Ispirer's post-migration optimization step addresses these systematically.

The toolkit parses each Oracle object's source code, builds a full syntax tree including type information and inter-object dependencies, and applies its rule engine (20,000+ rules) to produce PL/pgSQL output. It handles data type resolution, cursor behavior, exception handling, GOTO statement transformation, and built-in function substitution.

Ispirer Toolkit converts: tables and all constraints, indexes, views, sequences, functions, stored procedures, packages (specification and body), triggers, collection types (associative arrays, nested tables, varrays), object types, and Oracle supplied packages (UTL_FILE, DBMS_LOB, and others). Dynamic SQL, hierarchical queries, spatial functions, XML functions, and PRAGMA AUTONOMOUS_TRANSACTION are also handled.

The Free Trial is available at no cost with no payment required. Contact Ispirer to discuss trial scope — the team will help you configure the toolkit for your environment and run a test migration on a representative portion of your codebase.

Yes. RAW maps to BYTEA, LONG to TEXT. BFILE requires a custom approach since PostgreSQL has no native equivalent — Ispirer handles this via configuration options depending on how BFILE is used in the source code.

Yes. The free InsightWays assessment tool generates a detailed report including object counts, complexity indexes per object, and estimated automation rates. A test migration of representative code further validates these estimates against actual output.

Oracle CONNECT BY queries are rewritten as WITH RECURSIVE CTEs in PostgreSQL. The toolkit maps CONNECT BY LEVEL to recursive iteration depth, CONNECT BY PRIOR to the recursive join condition, START WITH to the base case of the CTE, and NOCYCLE to cycle detection logic.

Yes. Ispirer provides expert support during the trial to help with toolkit configuration and initial test migration. If conversion rules need to be added for your specific codebase, the engineering team can implement new rules within 3–5 business days.

Data integrity is maintained through read-only source access, row count comparison, and scenario-based testing using source application test cases. For numeric precision, type mapping preserves Oracle NUMBER precision in PostgreSQL NUMERIC equivalents. Ispirer runs a full data integrity testing phase before sign-off.

Yes. Ispirer Toolkit has 300+ configuration parameters for SQL objects and data migration behavior. Users can define custom data type mappings, adjust how specific Oracle constructs are handled, and work with Ispirer engineers to add entirely new conversion rules.