Practical Notes on Migrating an On-Premises Oracle Database to PostgreSQL on Google Cloud

Migrating a large on-premises database to the cloud is often described in marketing documentation as a straightforward sequence of export, transfer, and import steps. In reality, heterogeneous migrations—such as moving from Oracle to PostgreSQL—are complex engineering projects where differences in database engines, data types, and tooling behavior quickly emerge.

In this article, I want to share practical observations and troubleshooting lessons from working on an enterprise migration involving over 680 tables using Google Cloud Database Migration Service (DMS).

1. The Reality of Heterogeneous Database Migrations

When migrating homogenous workloads (e.g. PostgreSQL to Cloud SQL for PostgreSQL), the engine semantics remain identical. With heterogeneous migrations from Oracle to PostgreSQL:

Data Types Differ: Oracle types such as NUMBER, VARCHAR2, CLOB, and DATE do not map 1:1 without careful semantic verification. For instance, Oracle’s DATE includes time components (hours, minutes, seconds), which in PostgreSQL maps more accurately to TIMESTAMP.
Constraint Semantics: Handling of constraints, default values, sequence behaviors, and trigger execution differs significantly between the two systems.
Scale Matters: When dealing with 680+ tables, manually verifying every column definition is impossible without automated tooling and systematic validation.

2. Isolating the Source of Migration Failures

One of the most valuable engineering lessons during this project was learning how to systematically isolate where a failure originated.

When an error occurs during a DMS migration job, the root cause could be in one of three places:

┌─────────────────┐       ┌──────────────────────┐       ┌──────────────────────┐
│  1. Source DB   │ ────► │  2. Conversion / DMS │ ────► │  3. Target DB        │
│  (Oracle Data/  │       │  (Platform behavior, │       │  (PostgreSQL syntax, │
│   Privileges)   │       │   driver, network)   │       │   data type errors)  │
└─────────────────┘       └──────────────────────┘       └──────────────────────┘

Source Database Issues: Missing supplemental logging, insufficient database user privileges, or unsupported proprietary Oracle objects.
PostgreSQL Compatibility Issues: Incompatible schema definitions, missing extensions, or type conversion mismatches.
Migration Platform / Tooling Issues: Unexpected behavior, timeouts, or backend limitations within Google Cloud Database Migration Service itself.

Jumping to conclusions without evidence wastes critical time. By methodically checking raw error logs, query execution traces, and reproduction cases on isolated tables, we were able to separate actual schema incompatibilities from potential issues within the managed migration service.

3. Collaborating with Cloud Support

When you suspect that an error is not caused by your schema or configuration but rather by the underlying managed service, documenting the issue clearly is critical.

When opening support cases with Google Cloud Support:

Isolate the Minimal Reproduction: Identify the exact table, column definition, or data type triggering the condition.
Provide Verifiable Evidence: Include the exact DMS job configuration, job logs, and corresponding database engine logs.
Document Expected vs Actual Behavior: Clearly explain what the documentation specifies versus what the service actually produced.

This collaboration helped investigate suspected backend platform behaviors and validated our diagnostic approach.

Key Takeaways

Never treat database migration as a simple lift-and-shift. Schema compatibility between different database engines requires careful planning and testing.
Develop a disciplined diagnostic routine. Separate source issues, target schema issues, and migration tooling issues systematically.
Technical credibility comes from thorough investigation. Understanding why a failure occurred under the hood is what transforms a frustrating blocker into solid engineering knowledge.