Database migration: from Oracle, MSSQL or MySQL to Postgres or the cloud
A database migration is rarely a purely technical exercise. It touches licence costs, application architecture, downtime windows, data quality and, if things go 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 reduce downtime to minutes or even zero.
Discuss your migration case Explore migration pathsDatabase migration without downtime: what separates success from pain
Most failed database migrations don't get stuck on copying rows. They get stuck on forgotten stored procedures, on sequence IDs being reused, on encoding differences between WIN1252 and UTF8, or on a cutover window that doesn't fit the business. A good migration looks at the whole picture first (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 data 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: one night, one hour, or zero? Only with those answers can we choose between a big-bang dump and restore, parallel writes or a full change data capture (CDC) flow.
What often surfaces at this stage is that the real complexity lies not in the tables but in the surrounding systems. An Oracle database that has been running for twenty years will have PL/SQLpackages, materialised views and database links to other instances. A legacy MSSQL estate has linked servers, SSIS packages and dozens of SQL Agentjobs. This kind of hidden complexity often determines success far 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 can no longer be solved by adding more hardware.
Licence inflation with Oracle and MSSQL
Oracle Enterprise Edition, partitioning, RAC and Data Guard add up quickly to six- or seven-figure annual amounts. Microsoft SQL Server Enterprise, priced per core, follows the same curve. Moving to Postgres or a managed cloud variant removes that cost and frees budget for new features. pg_partman and pgvector now cover more 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 (a different cloud, a different 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 alongside containerised applications on Kubernetes. Migrating to AWS Aurora PostgreSQL, Google Cloud SQL or Azure Database for PostgreSQL brings automated backups, point-in-time recovery, read replicas and geographic redundancy without in-house DBA effort.
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 introduce JSONB where appropriate, add surrogate keys, repair foreign keys and set up partitioning by time or by tenant.
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) are typically where the gains are greatest.
Consolidating data silos
Three MySQL databases, two MSSQL instances and a MongoDB cluster all storing the same customer object: we see this regularly. The migration then becomes a consolidation project, bringing data together on a Postgres platform with JSONB where flexibility is needed, and relational integrity where it matters.
Six migration paths we carry out regularly
Not every migration is a straight 1-to-1 port. Sometimes consolidating is smarter, and sometimes a hybrid target makes sense. These are the paths that come up most often in our projects.
| Path | Typical trigger | Toolchain |
|---|---|---|
| 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 high availability | AWS Schema Conversion Tool + DMS, GoldenGate for zero-downtime |
| MongoDB consolidation into 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, splitting 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 won't do for you is decide what happens to data types, sequences, views and stored procedures. That is hands-on work, and it is exactly where independent database migrations tend to stall.
Mapping data types, not copying them
Oracle's NUMBER without precision is often wrongly translated to NUMERIC in Postgres when BIGINT was intended. VARCHAR2 versus TEXT affects storage and indexes. DATE in Oracle contains time components — in Postgres that 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, reset the current last_value and make sure applications don't create gaps in the IDs during cutover.
Views and materialised views
Oracle views with CONNECT BY, hierarchical queries and analytic functions do not translate 1-to-1. We rewrite them to Postgres' recursive WITHclauses and window functions. Materialised views get a refresh strategy — incremental where possible via logical replication or an application-driven refresh.
Stored procedures: application layer or plpgsql
Hundreds of PL/SQLpackages 1-to-1 into plpgsql is technically possible via ora2pg, but rarely the best choice. We analyse what is business logic (movable to the application or a service layer) and what is purely data logic (stays in the database as plpgsql). This improves testability and makes future migrations far simpler.
Migration strategies: from big-bang to zero-downtime CDC
The chosen strategy depends on the downtime window, data volume, transaction density and tolerance for risk. Four strategies we apply in projects.
Big-bang dump & 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, switching the target side and repointing applications. Downtime: a few hours to one night. Suitable for staging databases, internal tools and historical archives that are rarely queried.
Parallel-write and dual-read
The application temporarily writes to both the old and the new database. We read from the old one first, validate against the new one, and only switch over once the checksums are consistent. Suited to 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 via Apache Kafka to the target side. Initial load via pg_dump or bulk load, followed by real-time sync. Cutover downtime drops to minutes. Suitable for 24x7 transactional systems.
AWS DMS, GoldenGate or Striim
For cloud migrations without an own Kafka cluster, we deploy 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 outlined 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/SQLrewrites, pgloader for fast MySQL/MSSQL-to-Postgres bulk loads, and the 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 function to make discrepancies between source and target visible.
Testing and validation: not just row counts
A migration where the row counts match can still break business logic. We run three layers of validation in parallel, not just on cutover night.
Row count and checksum validation
For each table we compare COUNT(*) and aggregate checksums (md5 or xxhash over the relevant columns). This is automated and repeatable, so we can run the validation several times during parallel writes without manual effort. Discrepancies point to a table name plus a row range, not just "the migration doesn't add up".
Checking ETL versus ELT flows
Many migrations are in effect ETL projects: extract from the source, transform along the way, load into the target. For JSONBconsolidation or schema redesigns, ELT (loading raw data and transforming within the target database) is often more practical. We verify that the transformations are idempotent and that repeated runs produce no duplicates or discrepancies.
Business-logic equivalence tests
The most demanding test: run critical application queries against the old and new databases and compare the results one-to-one. For financial reporting, invoice totals and compliance queries this is non-negotiable. We build test suites with pytest or similar, connected to a staging environment, so that regressions become visible early in the process.
Cutover planning: the hour when you really don't want to do something stupid
Cutover is the moment you switch applications from source to target. However well the migration is prepared, this 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 tested rollback plan.
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 environments, a DNS or 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 tangible result. Each phase ends with something demonstrable: an assessment, a proof of concept, a dry run, or a production cutover.
Assessment
Inventory of the source schema, data volume, transaction density, integrations, downtime window and compliance requirements. Outcome: migration path, toolchain choice and risk register.
Schema conversion and proof of concept
Schema translation, sequence mapping and view rewrites. First end-to-end run on a staging target with representative data. Validation through row counts and checksums.
CDC and parallel run
CDC pipeline (Debezium or DMS) set up, initial load executed and replication lag monitored. The application optionally writes in parallel. Equivalence tests run daily.
Cutover and optimisation
Cutover according to the runbook, smoke tests, monitoring tightened. Afterwards: index tuning, VACUUMstrategy, autovacuumsettings and, where applicable, pg_partmanpartitioning.
Frequently asked questions about database migration
PL/SQLpackages, materialised views and critical integrations. The path is almost always: assessment, proof of concept, schema conversion, parallel run and cutover. We do not do a big bang overnight for a production database unless volume and complexity genuinely allow it.plpgsql. ELT is more efficient with large volumes and when you use JSONB as a transitional state. ETL is better suited to substantial schema redesigns or when the target database has no room for staging tables.JSONB better: you get schema flexibility where needed and SQL joins where useful. For specific use cases (write-heavy logs, eventually consistent caches, geo-replicated key-value stores), a NoSQL destination is appropriate.plpgsql. For complex analytical functions, we rewrite them using PostgreSQL's window functions or materialised views.WE8MSWIN1252 or AL32UTF8, MSSQL uses SQL_Latin1_General_CP1_CI_AS as the default, MySQL was for a long time latin1 , and it is often migrated to utf8mb4. PostgreSQL runs on UTF8by default. We detect mojibake (incorrect double encoding), normalise to UTF8 and validate with spot checks on text fields where accents and special characters occur.oude max + 1.000.000), and at the moment of 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.Would you like us to carry out a database migration?
Schedule a no-obligation conversation. We will assess your migration path, downtime requirements and risk profile, and deliver a concrete proposal covering phasing, toolchain and cutover strategy.
Book a migration assessment