Oracle → PostgreSQL Migration Without Downtime

# Migrating from Oracle to PostgreSQL Without Downtime: A Technical Breakdown

Migrating from Oracle to PostgreSQL without stopping the service is a challenging but achievable task. In this article, we break down a step-by-step migration strategy based on a real project with Spring Boot microservices, where zero impact on users was a key requirement. Using CDC via Debezium enabled real-time data synchronization, avoiding downtime and minimizing risks.

Preparation: Analyzing the Structure and Data

The first stage involves a deep audit of the current database. You need to understand the data volume, field type distribution, presence of outdated records, and Oracle-specific constructs without direct equivalents in PostgreSQL. Cleaning up irrelevant data reduces the migration volume and speeds up the process.

A key aspect is data type mapping. For example:

• NUMBER(1,0) → BOOLEAN
• VARCHAR2 → TEXT or VARCHAR
• DATE → TIMESTAMP WITHOUT TIME ZONE
• CLOB → TEXT

For analysis, we used this SQL query:

select distinct DATA_TYPE
from ALL_TAB_COLUMNS
where owner = 'schemat'
order by DATA_TYPE

Based on the results, we created a data type mapping table that formed the basis for all subsequent transformations. It's also crucial to account for differences in handling NULL and empty strings: in Oracle, an empty string is treated as NULL, but not in PostgreSQL. This requires adjustments to business logic and queries.

Schema Adaptation and Liquibase

Simply swapping the database driver will cause errors. Migration scripts must be adapted for PostgreSQL. To do this:

• DDL and DML operations are separated via Liquibase contexts — DML is temporarily disabled.
• Scripts are iteratively tested: after each change, the changelog is run, followed by cleanup and smoke testing.
• Preconditions are verified for correctness, and schema consistency with Oracle is ensured.

Special attention goes to sequences. If they're not synchronized, new IDs could conflict with historical data. The solution:

• On application startup, check the current sequence value in PostgreSQL.
• If it's lower than in Oracle, shift it forward with some buffer.
• The logic is idempotent: repeated runs don't cause failures.

As a result, the target PostgreSQL schema was assembled in a single pull request, ready for data population.

Application Code Adjustments

After successfully launching the application against an empty PostgreSQL, Java-level code errors surface. Main changes:

• Replace @Lob annotation with @Column(columnDefinition = "text").
• Handle NULL in native SQL queries, especially in IN (...) constructs.
• Switch from numeric flags (0/1) to boolean.
• Adjust string comparison logic accounting for Oracle/PostgreSQL differences.
• Rewrite native queries for the PostgreSQL dialect.

Testing was done at the unit test and integration scenario levels. Special focus on edge cases: empty strings, NULL values, large text fields.

Implementing CDC via Debezium and Kafka Connect

We chose the classic stack: Debezium + Kafka Connect. Architecture:

• Source connector (Debezium Oracle) reads the redo log and generates CDC events.
• Events are sent to Kafka (one topic per table).
• Sink connector (JDBC Sink) applies changes to PostgreSQL in the same order.

Setup Stages:

• Initial Snapshot — full data copy at startup time.
• Streaming Changes — continuous replication of INSERT/UPDATE/DELETE from the redo log.
• Order Preservation — change sequence guarantee via LogMiner.

Images used:

• quay.io/debezium/kafka:3.0
• quay.io/debezium/zookeeper:3.0
• quay.io/debezium/connect:3.0
• provectuslabs/kafka-ui:0.7.2 (for monitoring)

Setup is done via REST API using curl. The Debezium UI was unstable during migration, so configurations were passed manually in JSON format.

Example Source Connector Configuration:

{
  "name": "oracle-connector",
  "config": {
    "connector.class": "io.debezium.connector.oracle.OracleConnector",
    "database.hostname": "oracle-host",
    "database.port": "1521",
    "database.user": "user",
    "database.password": "password",
    "database.dbname": "ORCLCDB",
    "database.server.name": "server1",
    "table.include.list": "schema.table1,schema.table2",
    "database.history.kafka.bootstrap.servers": "kafka:9092",
    "database.history.kafka.topic": "schema-changes"
  }
}

Example Sink Connector Configuration:

{
  "name": "postgres-sink",
  "config": {
    "connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector",
    "connection.url": "jdbc:postgresql://postgres:5432/db",
    "connection.user": "user",
    "connection.password": "password",
    "topics": "server1.schema.table1,server1.schema.table2",
    "auto.create": "false",
    "auto.evolve": "false",
    "insert.mode": "upsert",
    "pk.mode": "record_key",
    "pk.fields": "id"
  }
}

Some tables (e.g., those with composite keys) require separate configurations. Each connector is tested in isolation.

Final Cutover and Quality Control

After completing the initial snapshot and stabilizing streaming:

• Test instances of services connected to PostgreSQL are launched.
• End-to-end testing is performed: API, UI, background processes.
• Load testing is conducted on production-like data.
• At the moment of minimal load, switch DNS or ingress routes to the new instances.
• Monitor logs, metrics, and alerts for 24–48 hours.

Rollback is only possible up to the switchover point. After that, the system runs exclusively on PostgreSQL. Oracle backups are retained for emergency data recovery.

Key Takeaways

• No one-size-fits-all solution — every project needs custom adaptation.
• CDC without double writes — an acceptable compromise with a few seconds of lag.
• Sequence synchronization — essential to prevent ID conflicts.
• Testing at every stage — the key to a stable migration.
• Debezium + Kafka Connect — a flexible and reliable stack for online migrations.

The migration took 18 hours including preparation, but actual user downtime was 0 seconds. The project has been running successfully on PostgreSQL for over a year without regressions.

— Editorial Team