Database Migration Services

The approach that avoids extended downtime is replication-based migration rather than dump-and-restore. We set up ongoing replication from the source database to the destination, using AWS DMS, Azure Database Migration Service, or native database replication depending on the source and target engines. The destination database receives a continuous stream of changes from the source as replication runs. When the replication lag drops to seconds, we execute the cutover: write traffic switches from source to destination, replication stops, and the source becomes read-only for a rollback window. The cutover itself, the window where writes are unavailable, is measured in seconds to low minutes, not hours. This approach is only possible when the migration has been running in replication mode for long enough that the destination is fully caught up. Dump-and-restore is appropriate for smaller databases where a maintenance window is acceptable and the restore time is predictable.

Change Data Capture (CDC) replication captures every insert, update, and delete from the source database's transaction log and replays those changes on the destination database in near real time. For a migration, this means the destination is kept continuously in sync with the source as replication runs, days or weeks before the cutover window. When the cutover window arrives, the replication lag is typically under a second. The application is pointed at the destination, the source is set to read-only, and any remaining replication lag is applied. The downtime window is the time between making the source read-only and the destination being fully caught up, usually seconds. AWS DMS supports CDC for most common database engines. Native database replication (PostgreSQL logical replication, MySQL binlog replication) is used where DMS introduces limitations. CDC replication is the standard approach for production database migrations where extended downtime is not acceptable.

Cross-engine migrations (Oracle to PostgreSQL, MSSQL to Aurora PostgreSQL) involve schema conversion, not just data movement. Oracle and MSSQL have proprietary data types, functions, and procedural language features that do not have direct PostgreSQL equivalents. AWS Schema Conversion Tool (SCT) or similar automated tools identify incompatibilities and produce a conversion report with the items that require manual resolution. Common issues: Oracle NUMBER types mapped incorrectly to PostgreSQL NUMERIC, Oracle stored procedures using PL/SQL syntax that must be rewritten in PL/pgSQL, Oracle sequences replaced with PostgreSQL SERIAL or generated columns, and Oracle-specific date arithmetic functions replaced with PostgreSQL equivalents. We work through the SCT report manually, rewrite the incompatible objects, and test the converted schema against a data sample from the source database before the migration runs. Application queries that use engine-specific SQL are also reviewed and updated as part of the schema conversion engagement.

Database migration cost depends on three factors: database size, the number of databases being migrated, and whether schema conversion between engines is required. Like-for-like migrations (PostgreSQL on-prem to PostgreSQL on RDS) for a single database of moderate size typically run $15,000 to $30,000. Multi-database migrations or databases over 1TB typically run $30,000 to $60,000. Cross-engine migrations that require schema conversion (Oracle to PostgreSQL, MSSQL to Aurora) add schema conversion and testing effort and typically run $40,000 to $80,000 depending on stored procedure and schema complexity. We scope every migration based on a discovery engagement, database inventory, size assessment, schema analysis, and a review of application query patterns that may be affected by the migration.