Oracle to PostgreSQL
Oracle to PostgreSQL migration
Last updated:
Datalobe assesses your Oracle Database source, converts PL/SQL procedures, functions, packages and triggers to PL/pgSQL, recreates views, compiles every object on PostgreSQL before deploy, copies data with change data capture (CDC) until cut-over, and validates every table with 17 checks.
Source
Oracle Database
- Example run
- Oracle Database 19c
- Code
- PL/SQL
Target
PostgreSQL
- Version
- 16.4
- Code
- PL/pgSQL
Example run: ERP-Oracle to ERP-PG, 1,036 tables, 2.14 billion rows, 15 h 40 min
Code
What converts automatically, and what needs review?
From the example run: 540 code objects from Oracle Database 19c.
Tables, types and keys use fixed rules.
AI converts stored code (procedures, functions, triggers, complex views). Every AI conversion is checked, and anything that can't be proven goes to a named person. Nothing AI-written is deployed silently.
540 code objects
540 of 540 compiled before deploy- 212 procedures
- 96 functions
- 24 packages
- 88 triggers
- 120 views
540 of 540 converted and deployed. Anything that could not be proven went to a named engineer.
Data types
Which Oracle types need a decision?
Each Oracle type maps to a PostgreSQL type by a rule, and where meaning could change the mapping is a decision.
| Oracle | PostgreSQL | Note | Method |
|---|---|---|---|
| VARCHAR2(n) | VARCHAR(n) | Length kept. | Rule |
| NUMBER | NUMERIC(38,10) | NUMBER without precision. 1,284 columns in the example run. | Decision |
| NUMBER(p,s) | NUMERIC(p,s) | Precision and scale kept. | Rule |
| DATE | TIMESTAMP(0) | An Oracle DATE holds a time of day as well as a date. | Rule |
| CHAR(1) 'Y'/'N' | BOOLEAN | 2,906 columns in the example run. | Decision |
| CLOB | TEXT | Rule |
6 of 35 data types shown
See all 35 data typesCompile check
Compiled on PostgreSQL before it ships
Every converted statement is compile-checked on PostgreSQL before deploy: Datalobe replays it inside a transaction that is rolled back.
CREATE OR REPLACE VIEW inv.v_stock_position ASSELECT h.location_id, h.depth, s.item_id, s.qty_on_hand FROM (SELECT location_id, LEVEL AS depth FROM inv.locations START WITH parent_id IS NULL CONNECT BY PRIOR location_id = parent_id) h JOIN inv.stock s ON s.location_id = h.location_id;CREATE OR REPLACE VIEW inv.v_stock_position ASWITH RECURSIVE h AS ( SELECT location_id, 1 AS depth FROM inv.locations WHERE parent_id IS NULL UNION ALL SELECT l.location_id, h.depth + 1 FROM inv.locations l JOIN h ON l.parent_id = h.location_id)SELECT h.location_id, h.depth, s.item_id, s.qty_on_hand FROM h JOIN inv.stock s ON s.location_id = h.location_id;Compiling on PostgreSQL 16.4Compiled in a rolled-back transaction. Ready to deploy.
Example run
ERP-Oracle to ERP-PG in numbers
Oracle Database 19c (19.21) to PostgreSQL 16.4, in the example run.
- Tables
- 1,036 in 5 schemas
- Rows
- 2,141,876,402
- Code objects
- 540 of 540 compiled before deploy
- Assessment
- 214 findings
- Data copy
- 13 h 48 min
- Validation
- 42 min · 17,612 check runs · 0 failed
- Lag at cut-over
- 4 s
- Downtime
- 18 min
Validation
How is the migration validated?
17 checks on every table in six groups, and functions run on both databases and compared.
- Checks
- 17,612
- Failed
- 0
- Accepted
- 7
- Completeness2,141,876,402 rows on both sidesPass
- Accuracy7 FIN rounding differences, accepted by Sarah Mitchell7 accepted
- Structure71,940 columns mapped as plannedPass
- Integrity7,969 constraints holdPass
- Code objects61 functions equal on bothPass
Questions
Frequently asked questions
Can Oracle packages be migrated to PostgreSQL?
Yes, but not one to one, because PostgreSQL has no packages. A package becomes a schema with functions, and package state moves to a session settings table, as FIN.PKG_GL_POSTING did in the example run.
What happens to CONNECT BY queries?
They convert to WITH RECURSIVE. In the example run that covered INV.V_STOCK_POSITION and 17 views.
How do you migrate Oracle DBMS_JOB jobs?
The assessment flags each one (DFLUX-311). In the example run it flagged HR.PRC_PAYROLL_CLOSE, and its schedule moved to pg_cron.
How much downtime does an Oracle to PostgreSQL migration need?
Downtime is limited to the cut-over window. CDC keeps PostgreSQL in sync while Oracle Database stays in use. In the example run, application downtime was 18 min.
Sources (38)
- Oracle Database 19c SQL Language Reference: Data Types, checked
- Oracle Database 19c Reference: Datatype Limits, checked
- Oracle Database 19c PL/SQL Language Reference: BOOLEAN Data Type, checked
- Oracle Database 19c PL/SQL Language Reference: PLS_INTEGER and BINARY_INTEGER Data Types, checked
- PostgreSQL 16 Documentation: 8.3 Character Types, checked
- PostgreSQL 16 Documentation: 8.1 Numeric Types, checked
- PostgreSQL 16 Documentation: 8.5 Date/Time Types, checked
- PostgreSQL 16 Documentation: 8.4 Binary Data Types, checked
- PostgreSQL 16 Documentation: 8.6 Boolean Type, checked
- PostgreSQL 16 Documentation: 8.12 UUID Type, checked
- PostgreSQL 16 Documentation: 8.13 XML Type, checked
- PostgreSQL 16 Documentation: 8.14 JSON Types, checked
- PostgreSQL 16 Documentation: 8.15 Arrays, checked
- PostgreSQL 16 Documentation: Appendix K. PostgreSQL Limits, checked
- PostgreSQL 16 Documentation: 73.2 TOAST, checked
- PostgreSQL 16 Documentation: 5.5 System Columns, checked
- Oracle Database 19c Globalization Support Guide: Datetime Data Types and Time Zone Support, checked
- Oracle Database 19c SQL Language Reference: SYS_GUID, checked
- Oracle Database 19c SQL Language Reference: ROWID Pseudocolumn, checked
- Oracle Database 19c JSON Developer's Guide: JSON in Oracle Database, checked
- Oracle Database 19c Object-Relational Developer's Guide: Collection Data Types, checked
- PostgreSQL 15.0 Release Notes, checked
- PostgreSQL 16 Documentation: 24.3 Character Set Support, checked
- PostgreSQL 16 Documentation: 35.1 Introduction (Large Objects), checked
- PostgreSQL 16 Documentation: Chapter 8 Data Types, checked
- Oracle Database 19c SQL Language Reference: NVL, checked
- Oracle Database 19c SQL Language Reference: DECODE, checked
- Oracle Database 19c SQL Language Reference: Nulls, checked
- Oracle Database 19c SQL Language Reference: LAST_DAY, checked
- Oracle Database 19c SQL Language Reference: Hierarchical Queries, checked
- Oracle Database 19c SQL Language Reference: SYS_CONNECT_BY_PATH, checked
- PostgreSQL 16 Documentation: 9.18 Conditional Expressions, checked
- PostgreSQL 16 Documentation: 9.2 Comparison Functions and Operators, checked
- PostgreSQL 16 Documentation: 9.9 Date/Time Functions and Operators, checked
- PostgreSQL 16 Documentation: 7.8 WITH Queries (Common Table Expressions), checked
- PostgreSQL 16 Documentation: BEGIN, checked
- PostgreSQL 16 Documentation: ROLLBACK, checked
- PostgreSQL wiki: Transactional DDL in PostgreSQL: A Competitive Analysis, checked
Next step
Assess your Oracle database
Send a read-only connection or a schema export and get an Assessment Report.