Skip to content

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.

Oracle Database to PostgreSQL

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 to PostgreSQL type mappings
OraclePostgreSQLNoteMethod
VARCHAR2(n)VARCHAR(n)Length kept.Rule
NUMBERNUMERIC(38,10)NUMBER without precision. 1,284 columns in the example run.Decision
NUMBER(p,s)NUMERIC(p,s)Precision and scale kept.Rule
DATETIMESTAMP(0)An Oracle DATE holds a time of day as well as a date.Rule
CHAR(1) 'Y'/'N'BOOLEAN2,906 columns in the example run.Decision
CLOBTEXTRule

6 of 35 data types shown

See all 35 data types

Compile 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.

INV.V_STOCK_POSITION · viewDFLUX-301 · CONNECT BY hierarchical query
SOURCE · Oracle PL/SQL
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;
TARGET · PostgreSQL PL/pgSQL
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;

Compiled 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.

Example run · Validation Report / ERP-Oracle to ERP-PGPassed with warnings
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)

Next step

Assess your Oracle database

Send a read-only connection or a schema export and get an Assessment Report.