Database migration: from Oracle, MSSQL or MySQL to Postgres or the cloud

A database migration is rarely a purely technical exercise. It affects licence costs, application architecture, downtime windows, data quality and, if it goes wrong, direct revenue. We guide migration projects from legacy Oracle, Microsoft SQL Server, MySQL or MongoDB to Postgres, AWS Aurora, Cloud SQL or Azure Database, using change data capture and parallel-write strategies that limit downtime to minutes or even zero.

Oracle to Postgres MSSQL to Postgres MySQL to Postgres Cloud migration CDC with Debezium AWS DMS Zero-downtime cutover
Discuss your migration case View migration paths
LEGACY POSTGRES CDC

Database migration without downtime: what separates success from pain

Most failed database migrations don't founder on copying rows. They founder on forgotten stored procedures, on sequence IDs that get reused, on encoding differences between WIN1252 and UTF8, or on a cutover window that doesn't fit the business. A good migration first looks at the whole picture (application, schema, data, integrations, monitoring) and only then chooses a strategy.

We start every migration project with a sober inventory. What volume are we talking about: 50 GB of OLTP or 12 TB of history? How many reads and writes per second at peak? Which applications talk directly to the database, and which go through an API? How many stored procedures, triggers, views and jobs run on the source? What downtime window does the business accept: a night, an hour, or none? Only with those answers do we choose between a big-bang dump-restore, parallel-write or a full change data capture (CDC) flow.

What often comes to light is that the real complexity is not in the tables but in the surrounding systems. An Oracle database that has been running for twenty years has PL/SQLpackages, materialised views and database links to other instances. A legacy MSSQL has Linked Servers, SSIS packages and dozens of SQL Agentjobs. That hidden complexity often determines success more than the Postgres target itself.

Typical reasons to move away from a legacy database

Database migrations rarely start out of curiosity. There is almost always a concrete trigger: a licence invoice, a cloud strategy or a scaling problem that more hardware can no longer solve.

💰

Oracle and MSSQL licence inflation

Oracle Enterprise Edition, partitioning, RAC and Data Guard quickly add up to six- or seven-figure annual amounts. Microsoft SQL Server Enterprise per core follows the same curve. Moving to Postgres or a managed cloud variant removes that cost and frees up budget for new features. pg_partman and pgvector cover a growing number of use cases that historically only worked on Oracle.

🔓

Breaking vendor lock-in

PL/SQLpackages, T-SQL stored procedures and proprietary types make it hard to move away. We rewrite business logic into the application layer or into plpgsql, so you can choose again in the future, whether that means another cloud, another database product or a new runtime, without having to redesign every schema.

☁️

Cloud-native deployment

On-premise Oracle or MSSQL on a physical server does not scale with containerised applications on Kubernetes. A migration to AWS Aurora PostgreSQL, Google Cloud SQL or Azure Database for PostgreSQL gives you automatic backups, point-in-time recovery, read replicas and geographic redundancy without in-house DBA work.

📐

Schema modernisation

Tables designed in 2003 for a VARCHAR2(4000) world often no longer fit REST APIs and event-driven architectures. A migration is the natural moment to use JSONB where appropriate, add surrogate keys, repair foreign keys and set up partitioning along a time or tenant axis.

⚡

Performance on modern workloads

Postgres with JIT, parallel query, logical replication and modern index types (BRIN, GIN, GiST) outperforms a legacy MySQL 5.7 or an under-specified Oracle Standard on many OLTP and analytical workloads. Analytical queries on time series and geometry (PostGIS) in particular are where the gains typically lie.

🧩

Consolidating data silos

Three MySQL databases, two MSSQL instances and a MongoDB cluster that all store the same customer object: we see it regularly. A migration project then becomes a consolidation project: bringing data together in a Postgres platform with JSONB where flexibility is needed, and relational integrity where it is required.

Six migration paths we carry out regularly

Not every migration is a 1-to-1 port. Sometimes consolidating is smarter, sometimes a hybrid destination makes sense. These are the paths that come up most often in our projects.

PathTypical triggerToolchain
Oracle → PostgreSQL Licence costs, vendor lock-in, modernisation of PL/SQL ora2pg, AWS DMS, custom plpgsqlrewrite
MSSQL → PostgreSQL Per-core licences, Linux-only deployment, cloud strategy AWS DMS, pgloader, T-SQL → plpgsql conversion
MySQL → PostgreSQL Need for transactional integrity, JSONB, better index types pgloader, Debezium MySQL connector, logical replication
Oracle → AWS Aurora / Cloud SQL Full cloud migration, managed service, multi-AZ HA AWS Schema Conversion Tool + DMS, GoldenGate for zero downtime
MongoDB consolidation to Postgres + JSONB Schema drift, lack of joins, analytics pain Custom ETL, Debezium MongoDB connector, dual-write transition
NoSQL ↔ SQL or cross-platform Deliberate polyglot strategy, separating OLTP and OLAP Striim, Kafka Connect, change data capture pipelines

Schema translation: where it gets stuck in practice

A migration tool can copy tables. What a tool will not do for you is decide what happens to data types, sequences, views and stored procedures. That is manual work, and it is exactly where database migrations without independent expertise get stuck.

Mapping data types, not copying them

Oracle's NUMBER without precision is often translated incorrectly to NUMERIC in Postgres, whereas BIGINT was intended. VARCHAR2 versus TEXT affects storage and indexes. DATE in Oracle includes a time component — in Postgres this is called TIMESTAMP. MSSQL's DATETIME2, UNIQUEIDENTIFIER and HIERARCHYID each need a targeted approach.

Sequences and identity columns

Oracle SEQUENCES with NEXTVAL behave differently from Postgres sequences. MSSQL IDENTITY and MySQL AUTO_INCREMENT map to Postgres' GENERATED BY DEFAULT AS IDENTITY or classic sequences. We rename them, reset the current last_value and make sure applications don't create gaps in the IDs during cutover.

Views and materialized views

Oracle views with CONNECT BY, hierarchical queries and analytic functions don't translate one-to-one. We rewrite them to Postgres' recursive WITHclauses and window functions. Materialized views get a refresh strategy — incremental where possible via logical replication, or an application-driven refresh.

Stored procedures: application layer or plpgsql

Porting hundreds of PL/SQLpackages one-to-one to plpgsql is technically possible via ora2pg, but rarely the best choice. We analyse what is business logic (which can move to the application or a service layer) and what is purely data logic (which stays in the database as plpgsql). This improves testability and makes future migrations much simpler.

Migration strategies: from big-bang to zero-downtime CDC

The right strategy depends on the downtime window, data volume, transaction density and risk tolerance. These are the four strategies we use in projects.

Big-bang dump and restore

The fastest route for small databases (up to a few tens of GB) or non-critical systems. Stop the source, pg_dump or expdp, restore on the target side, switch the applications over. Downtime: a few hours to a night. Suited to staging databases, internal tools and historical archives that are rarely consulted.

Parallel write and dual read

The application temporarily writes to both the old and the new database. First we read from the old one, validate against the new one, and only switch over once the checksums are consistent. A good fit for applications where we can adapt the data-access layer and where a few weeks of parallel infrastructure is acceptable.

Change Data Capture (CDC) with Debezium

Debezium reads the transaction log (Oracle redo logs, MySQL binlog, MSSQL CDC, Postgres WAL) and streams every change to the target via Apache Kafka. Initial load via pg_dump or bulk load, followed by real-time sync. Cutover downtime drops to minutes. Suited to 24x7 transactional systems.

AWS DMS, GoldenGate or Striim

For cloud migrations without your own Kafka cluster, we use AWS Database Migration Service. For an Oracle source with strict zero-downtime requirements, Oracle GoldenGate works well. For cross-platform and heterogeneous flows, Striim is a strong option. The choice of tool depends on the source, the destination, the data volume and how long the source and target need to run side by side.

The toolchain we use

No migration without the right tools. The choice depends on the source, the destination and the strategy above. We have hands-on experience with the tools below and choose pragmatically — not based on whatever the latest blog post recommends.

For schema and data: ora2pg for Oracle-to-Postgres schema conversion, including PL/SQLrewrite, pgloader for fast MySQL/MSSQL-to-Postgres bulk loads, AWS Schema Conversion Tool for automated assessment of AWS target databases, pg_dump and pg_restore for Postgres-to-Postgres with logical replication as a follow-up.

For change data capture: Debezium connectors for Oracle (via LogMiner or XStream), MySQL (binlog), MSSQL (CDC) and MongoDB, connected to Apache Kafka. AWS DMS for managed CDC without a self-hosted broker. Oracle GoldenGate where the licence is already in place and zero downtime is critical.

For schema versioning: Liquibase or Flyway to roll out DDL changes deterministically and reproducibly, including during parallel-write phases. Schema diff tools such as migra or Liquibase's diff feature to make deviations between source and target visible.

PostgreSQL 15+ ora2pg pgloader AWS DMS Debezium Apache Kafka Liquibase Flyway pg_partman pgvector PostGIS JSONB logical replication COPY GoldenGate Striim

Testing and validation: more than counting rows

A migration where the row counts match can still break business logic. We use three validation layers that run in parallel, not just on the night of the cutover.

Row-count and checksum validation

For each table we compare COUNT(*) and aggregate checksums (md5 or xxhash over relevant columns). This is automated and repeatable, so we can run the validation several times during parallel writes without manual work. Discrepancies return a table name plus a row range, not just "the migration is wrong".

Checking ETL versus ELT flows

Many migrations are effectively ETL projects: extract from the source, transform along the way, load into the target. For JSONBconsolidation or schema redesigns, ELT (load raw, transform in the target database) is often more practical. We validate that the transformations are idempotent and that repeated runs produce no duplicates or discrepancies.

Business-logic equivalence tests

The toughest test: run critical application queries against both the old and the new database and compare the results one-to-one. For financial reports, invoice totals and compliance queries, this is non-negotiable. We build test suites with pytest or similar, linked to a staging environment, so regressions become visible early in the project.

Cutover planning: the hour when you don't want to do anything silly

Cutover is the moment applications switch from source to target. However well the migration has been prepared, it remains the most tense hour of the project. We always deliver a detailed cutover runbook, tested in dry runs, with explicit go/no-go criteria and a rollback plan that has been tested.

A typical runbook includes: confirmation that CDC lag is under 5 seconds, a freeze on schema changes 24 hours before cutover, application health checks on both the old and new environment, a DNS/connection-string switch with a TTL that fits the downtime window, a smoke-test suite that runs within 10 minutes, and go/no-go decision points with the business. If the smoke test fails, we roll back, no heroics.

Our approach in four phases

No months of preliminary research without a concrete result. Every phase ends with something demonstrable: an assessment, a POC, a dry run, a production cutover.

Assessment

Inventory of the source schema, data volume, transaction density, integrations, downtime window and compliance requirements. Result: migration path, toolchain choice and risk map.

Schema conversion and POC

Schema translation, sequence mapping, view rewrites. First end-to-end run on a staging target with representative data. Validation via row counts and checksums.

CDC and parallel run

CDC pipeline (Debezium or DMS) set up, initial load run, replication lag monitored. The application optionally writes in parallel. Equivalence tests run daily.

Cutover and optimisation

Cutover according to the runbook, smoke tests, monitoring fine-tuned. Afterwards: index tuning, VACUUMstrategy, autovacuumsettings and any pg_partmanpartitioning.

Frequently asked questions about database migration

How long does a typical Oracle-to-Postgres migration take?
From first assessment to production cutover, it ranges from six weeks for a compact application to six to twelve months for an Oracle database of several TB with hundreds of PL/SQLpackages, materialised views and critical integrations. The path is almost always: assessment, POC, schema conversion, parallel run and cutover. We don't do a big-bang overnight migration for a production database unless volume and complexity genuinely allow it.
What is the difference between ETL and ELT in a migration?
ETL transforms data in transit — the migration tool restructures tables, splits columns or adds references before the data lands in the target database. ELT loads the data raw first and transforms it inside the target database, often with SQL or plpgsql. ELT is more efficient with large volumes and when you use JSONB as a transitional state. ETL is a better fit for major schema redesigns, or when the target database has no room for staging tables.
What do you use Debezium for versus AWS DMS?
Debezium is open source, runs on your own Kafka cluster and gives you maximum control — useful in self-hosted or multi-cloud scenarios. AWS DMS is a managed service without the Kafka overhead, ideal when the destination is AWS Aurora or RDS and you don't want to maintain your own streaming platform. For heavily loaded Oracle sources, Oracle GoldenGate is often the most stable choice, provided the licence is already available.
Do you also migrate to MongoDB or NoSQL as a target?
Yes, but in most cases we advise against moving relational data to a document store without a deliberate reason. Postgres with JSONB often works better — you get schema flexibility where you need it and SQL joins where they're useful. For specific use cases (write-heavy logs, eventually consistent caches, geo-replicated key-value stores), a NoSQL destination is a good fit.
How do you handle stored procedures that don't translate one-to-one?
First, we take stock of which procedures genuinely contain business logic and which purely enforce data consistency. Business logic preferably moves to the application layer or a service layer — there it is testable and version-controlled in the same repository as the rest of the code. Pure data logic (constraints, triggers for audit tables) stays in the database as plpgsql. For complex analytical functions, we rewrite them using Postgres window functions or materialised views.
Can downtime really be zero?
In most practical situations, CDC plus parallel-write gives you a cutover downtime of a few seconds to a few minutes — the time needed to switch the application's connection string and catch up on the last replication lag. True zero downtime (no dropped connections, no failed requests) requires extra investment in feature-flag-driven data access and careful routing. We often recommend a window of two to ten minutes at a quiet time — that is usually more than enough.
What do you do about character encoding differences?
A common pitfall. Oracle databases often run on WE8MSWIN1252 or AL32UTF8, MSSQL uses SQL_Latin1_General_CP1_CI_AS as its default, MySQL was for a long time latin1 and has often been migrated to utf8mb4. Postgres runs on UTF8by default. We detect mojibake (incorrect double encoding), normalise to UTF8 and validate with spot checks on text fields containing accents and special characters.
How do you prevent sequence IDs from getting gaps or duplicates?
During parallel-write, both databases generate their own IDs. We solve this with a sequence-rename strategy: the old sequence keeps running, the new sequence gets an offset (for example oude max + 1.000.000), and at cutover we align the values without confusing the application. For UUID-based primary keys this is trivial. For numeric IDENTITYcolumns it requires careful bookkeeping during dry runs.

Ready to migrate your database?

Schedule a no-obligation conversation. We assess your migration path, downtime requirements and risk profile — and deliver a concrete proposal with phasing, toolchain and cutover strategy.

Schedule a migration assessment

Edit content