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.
Discuss your migration case View migration pathsDatabase 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.
| 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 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.
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
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.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.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.plpgsql. For complex analytical functions, we rewrite them using Postgres window functions or materialised views.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.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