How I Single Handedly Migrated a Large Oracle Ecosystem to PostgreSQL in 13 Working Days — with Near-Zero Downtime

The migration therefore had to preserve data correctness, database behavior, application behavior, and external-data availability—not merely produce PostgreSQL-compatible syntax.

---

Challenge 1 — Moving ~1 TB Without Losing Late Changes

Risk

A traditional one-time export/import would create a consistency gap. Because production Oracle remained active, rows could be inserted or updated after the initial copy and before cutover.

The migration needed both bulk throughput and a way to converge the target database toward the latest source state.

Engineering decision — Build O2P

I built O2P (Oracle to PostgreSQL), a dedicated migration project that connects to Oracle and PostgreSQL simultaneously and automates table-level migration.

O2P was designed to:

• translate Oracle table definitions into PostgreSQL-compatible schemas;
• migrate table data in a controlled, repeatable process;
• track migration at table level;
• validate migrated records;
• identify source data changed after the initial migration; and
• incrementally synchronize those changes before cutover.

Cutover model

Instead of forcing ~1 TB through a single downtime window, the heavy transfer happened ahead of cutover. The final window was primarily about synchronizing the remaining delta and validating readiness.

Engineering impact: the data migration became repeatable and convergent, reducing the risk of missing records changed between initial migration and production cutover.

Read More about the project: https://developerhridu.github.io/projects/dataflow-database-migration-tool

---

Challenge 2 — Converting 1,700+ Oracle Procedures and Database Objects

Risk

The database contained more than 1,700 stored procedures plus other Oracle-specific objects. Manual line-by-line conversion within 13 working days was not realistic, while naive AI translation would create a different risk: syntactically plausible but behaviorally incorrect code.

Engineering decision — AI inside an execution loop

I built an agentic migration workflow using Claude, MCP-based tooling, custom migration skills, structured migration instructions, database context, and access to the target PostgreSQL environment.

The important design choice was that generation was never the definition of success.

The agents could inspect Oracle logic and dependencies, generate PostgreSQL equivalents, execute the migrated objects, observe compilation/execution failures, revise the implementation, and retest.

This changed the role of AI from code generator to a component inside a closed engineering feedback loop.

Engineering impact: the workflow made large-scale conversion feasible under the compressed timeline while requiring target-environment execution before an object was treated as successfully migrated.

---

Challenge 3 — Migrating 23 Applications With No ORM Abstraction

Risk

The application layer was directly coupled to Oracle through ADO.NET components such as:

OracleConnection, OracleCommand, OracleDataReader, and OracleParameter.

Migrating to PostgreSQL therefore meant more than changing a connection string. The codebase had to move to Npgsql, while accounting for differences in parameter handling, SQL syntax, stored-procedure invocation, return values, data types, transaction behavior, and result processing.

Engineering decision — Convert and prove behavior

I applied the same agentic approach at repository level, providing the agents with codebase context and migration rules to systematically replace Oracle-specific data access with PostgreSQL/Npgsql implementations.

But code conversion alone did not answer the most important question:

Does the migrated repository behave correctly against PostgreSQL?

I created 4,000+ repository-level integration test cases based on application scenarios and database behavior.

This converted migration verification from a primarily visual review process into an executable compatibility test.

Engineering impact: all 23 application projects were migrated from Oracle-specific ADO.NET to PostgreSQL/Npgsql, with 4,000+ integration tests helping expose compatibility issues before QA.

---

Challenge 4 — Removing Oracle DB Link Dependencies

Risk

Parts of the database logic directly queried tables in remote Oracle databases through Oracle DB Links.

After the primary database moved to PostgreSQL, preserving that access pattern would either require another cross-database mechanism or extensive rewrites across dependent procedures and applications.

Engineering decision — Decouple remote reads with Airflow

Rather than make PostgreSQL business logic depend on synchronous remote Oracle queries, I introduced an Apache Airflow synchronization layer.

Airflow DAGs extract required data from remote Oracle databases and synchronize it into local PostgreSQL tables. The workflows were designed around scheduled execution, incremental extraction, retries, failure logging, monitoring, controlled execution, and recovery.

This changed the dependency from:

runtime remote query → local read backed by managed synchronization

Engineering impact: Oracle DB Link dependencies were removed from the migrated workload while required external Oracle data remained available through a controlled, monitorable, and recoverable synchronization path.

---

End-to-End Migration Strategy

The project was executed as a system migration, not a database conversion.

Each layer had its own validation mechanism, and the migration moved toward production only after the target behavior had been exercised.

---

What I Actually Engineered

The most important part of this project was not the amount of SQL I converted. It was the migration system around the conversion work.

I designed and implemented:

• O2P, an Oracle-to-PostgreSQL schema/data migration and incremental synchronization tool;
• an agentic database-object conversion workflow with target execution and repair loops;
• an application migration workflow for Oracle ADO.NET → Npgsql;
• 4,000+ repository-level integration tests to validate migrated application behavior;
• an Apache Airflow synchronization architecture to replace Oracle DB Links; and
• the end-to-end migration and cutover workflow across database, application, data, and integration layers.

Core engineering principle: AI-generated code was never considered migrated because it looked correct. It had to survive execution, testing, failure analysis, correction, and retesting.

---

Results

Engineering outcome	Result
Production migration	Oracle → PostgreSQL completed in 13 working days
Data	Approximately 1 TB migrated
Database estate	550+ tables and 1,700+ stored procedures/database objects migrated
Applications	23 projects migrated to PostgreSQL/Npgsql
Validation	4,000+ repository-level integration tests created
Integration architecture	Oracle DB Links replaced with Airflow-managed synchronization
QA	0 migration-related bugs reported
Production	0 migration-related bugs reported after deployment

---

Final Takeaway

The achievement was not simply converting Oracle to PostgreSQL in 13 working days. It was engineering a migration platform and validation loop that allowed one engineer to operate across a production estate of ~1 TB, 550+ tables, 1,700+ database objects, and 23 applications—while keeping migration-related regressions from being reported in QA or production.

The project reinforced a principle I now apply broadly to AI-assisted engineering:

AI + context + automation + execution + testing + human engineering judgment is far more powerful than code generation alone.

Proof of Work Documents

O2P Project github repository: https://github.com/developerhridu/O2P