Stacknesia

Last year, I was handed one of the more stressful migration projects of my career: move a 250GB Oracle database to PostgreSQL for a banking client, with minimal downtime and zero data loss. The database had been running Oracle for over a decade, with hundreds of stored procedures, custom data types, and Oracle-specific functions baked deep into the application layer.

This is the article I wish I had before starting. No sanitized success story — just what actually happened, what broke, and how we fixed it.

Why they were migrating

The client's Oracle license was up for renewal, and the cost was significant. PostgreSQL was the natural alternative — mature, enterprise-ready, and free. But cost wasn't the only driver. The team also wanted more control over their infrastructure and the ability to run on cloud-managed PostgreSQL (Aurora, Cloud SQL) without Oracle's licensing restrictions.

Key constraint

The database had to stay available during business hours. Maximum planned downtime for the final cutover: 4 hours, on a weekend.

Pre-migration assessment

Before touching anything, we spent two weeks in assessment mode. This is the part most people skip — and where most migrations go wrong.

Here's what we catalogued:

→847 tables, ranging from a few KB to 40GB+
→124 stored procedures — many using Oracle-specific PL/SQL syntax
→38 database functions with no direct PostgreSQL equivalent
→Custom Oracle sequences used for ID generation
→Oracle-specific data types: VARCHAR2, NUMBER, CLOB, BLOB
→12 database links to external Oracle instances

The database links were the biggest surprise. Nobody had documented them. We only found them by querying DBA_DB_LINKS directly. Each one required a separate conversation with the application team about whether it was still in use.

The toolchain: why we chose Pentaho

We evaluated three tools before settling on Pentaho Data Integration (PDI) for the data migration:

ora2pg

✓ Free, open source, schema conversion built-in

✗ Slow on large datasets, limited transformation logic

AWS DMS

✓ Managed, good for ongoing replication

✗ Expensive, less control over transformation

Pentaho PDI

✓ Flexible, handles complex transformations, good for 250GB+

✗ Steeper learning curve, requires setup

We ended up using ora2pg for schema conversion and Pentaho for data movement. They complement each other well — ora2pg handles the DDL translation, Pentaho handles the actual data transfer with transformation logic in between.

Schema conversion with ora2pg

ora2pg produced a first-pass schema conversion, but it was never going to be production-ready out of the box. Here's a typical example of what it generated vs. what we actually needed:

-- Oracle original

CREATE TABLE accounts (
  account_id    NUMBER(10) NOT NULL,
  account_name  VARCHAR2(100),
  balance       NUMBER(15,2),
  created_at    DATE,
  notes         CLOB,
  CONSTRAINT pk_accounts PRIMARY KEY (account_id)
);

-- ora2pg output (needs fixes)

CREATE TABLE accounts (
  account_id    NUMERIC(10) NOT NULL,
  account_name  VARCHAR(100),
  balance       NUMERIC(15,2),
  created_at    TIMESTAMP,
  notes         TEXT,
  PRIMARY KEY (account_id)
);

-- Final PostgreSQL (what we actually used)

CREATE TABLE accounts (
  account_id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_name  VARCHAR(100),
  balance       NUMERIC(15,2),
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  notes         TEXT
);

The 3 biggest problems we hit

Problem 1NVL vs COALESCE in stored procedures

Oracle's NVL() function has no direct equivalent in PostgreSQL. We had 847 occurrences across 124 stored procedures. We wrote a sed script to batch-replace them, then manually reviewed every procedure that touched financial calculations.

-- Oracle
NVL(balance, 0)

-- PostgreSQL
COALESCE(balance, 0)

Problem 2Sequence gaps causing primary key conflicts

Oracle sequences and PostgreSQL sequences behave differently under load. During our dry run, we hit primary key conflicts because the sequence start values weren't set correctly after data migration. Always query the max ID after migration and set your sequence accordingly.

Problem 3CLOB data with embedded newlines

Several CLOB columns contained JSON-like data with embedded newlines. Pentaho's default CSV export broke on these. We had to switch to a binary transfer mode and handle the encoding explicitly during transformation.

The cutover weekend

We ran 3 dry runs before the actual cutover. Each one surfaced new issues. By the third run, we had the process down to 3h 42min — within our 4-hour window.

Cutover timeline