Migrating Database Between Providers

The Schema Mapping Trap and Silent Data Corruption

We built this skill because migrating databases is where engineering judgment goes to die. You aren't just moving bytes; you're translating dialects. A VARCHAR(255) in MySQL might need to become TEXT in PostgreSQL, or a DECIMAL(10,2) could silently truncate if your ETL tool defaults to floating-point arithmetic. We've seen engineers burn weekends trying to manually map types, only to find out during UAT that their ENUM columns vanished or their timestamps shifted by a timezone offset. Migrating between providers isn't a copy-paste job; it's a structural translation that demands precision ^[8]. If you're still relying on ad-hoc scripts and hope, you're risking silent data corruption that your tests won't catch. The real pain comes from the hidden edge cases: collation mismatches that break search queries, JSONB vs JSON type conflicts, and the nightmare of migrating stored procedures that don't have a direct equivalent in the target engine. You spend days reverse-engineering migration scripts, only to realize you missed a single index or a view dependency that breaks the entire application.

What a Botched Migration Actually Costs

The cost of a migration error isn't just the hours you spend fixing it. It's the downtime. It's the SLA breach. It's the engineering team losing sleep because the CDC stream lagged by 40 minutes during cutover. Every hour you spend debugging a broken ALTER TABLE directive is an hour your product roadmap stalls. If your migration script lacks proper validation, you don't get a rollback; you get a corrupted table and a frantic war room. Teams that skip structured planning often end up with data loss or severe performance degradation post-migration ^[3]. Without a validation layer, you're flying blind during the most critical phase of the migration ^[6]. A single syntax error in your replication task config can waste thousands in cloud compute, and a failed cutover can destroy user trust in your platform. Consider the downstream impact: if your migration script has a typo in the S3 endpoint settings, your CDC stream fails silently. You don't know until hours later that transactions are missing, leading to reconciliation failures that take days to fix. The cost isn't just engineering time; it's the opportunity cost of delayed features and the reputational damage of a failed release.

Why "Just Dump and Load" Fails at Scale

Imagine a team managing a 4TB MySQL cluster that needs to move to a managed PostgreSQL service. They decide to run a full dump during off-peak hours. They don't account for the locking strategy, and the primary table locks for 45 minutes, killing checkout traffic. Or picture a fintech with 200 endpoints trying to replicate changes in real-time. They spin up a replication task but forget to validate the S3 endpoint settings, causing the CDC stream to fail silently. A 2024 analysis of zero-downtime strategies ^[1] highlights that assessment and planning are non-negotiable; teams that rush into extraction without a dual-write or CDC strategy end up scrambling. LaunchDarkly's zero-downtime practices ^[2] show that scrambling during cutover is avoidable with the right tools and pre-migration checks. DeployHQ's guide ^[4] emphasizes that stateful applications require SQL examples and checklists, not just hope. Even with tools like pgloader, you still need to handle schema transformations, index naming, and view-based materialization manually if you don't have a framework. We've seen teams migrate large tables in stages just to avoid locking, only to lose track of which chunks were replicated ^[5]. You need a repeatable process, not a one-off script. The complexity multiplies when you have to maintain data integrity across multiple schemas, handle circular dependencies, and ensure that the target database has the same performance characteristics as the source.

What Changes Once the Framework Is Installed

When you install this skill, you stop guessing and start executing. You get a production-grade orchestration layer that handles the heavy lifting. The dms-task.json template ensures your AWS DMS replication tasks are configured with the correct DataMigrationType enums, CDC settings, and table mappings. The pgloader.mig script automates schema transformations, index handling, and view materialization, so you don't have to write SQL dialects by hand. The schema-mapping.yaml gives you a metadata-driven way to translate types across platforms, eliminating guesswork. When you run validate-dms.sh, it checks your configuration against a strict JSON Schema, catching missing fields and invalid enums before you waste a single compute hour. You can integrate this with your existing implementing database migrations workflow to ensure version-controlled schema changes are paired with safe data movement. The result is a migration you can trust, validate, and repeat without a war room. You also get a migration playbook pack integration that ensures your assessment and planning phases are covered, and you can pair this with an implementing data export pipeline to ensure your extraction doesn't lock the source database. The skill provides a canonical knowledge base that covers enterprise best practices, so you're not just running scripts; you're following a proven framework.

What's in the Migration Pack

• skill.md — Orchestrator skill defining the migration framework, workflow phases, and cross-references to all templates, validators, references, and examples.
• templates/dms-task.json — Production-grade AWS DMS Replication Task configuration template with CDC, S3 endpoint settings, and table mappings.
• templates/pgloader.mig — Production-grade pgloader migration script with schema transformations, index handling, and view-based materialization.
• templates/schema-mapping.yaml — Metadata-driven schema mapping template for cross-platform type translation and normalization.
• references/migration-framework.md — Canonical knowledge base covering zero-downtime strategies, CDC, data assessment, integrity checks, and enterprise best practices.
• references/aws-dms-reference.md — Authoritative extraction of AWS DMS API structures, DataMigrationType enums, S3 endpoint settings, and statistics models.
• references/pgloader-reference.md — Authoritative extraction of pgloader syntax, ALTER TABLE transformations, drop schema behavior, and index naming strategies.
• validators/dms-schema.json — JSON Schema definition for validating DMS task configurations against required fields and valid enum values.
• scripts/validate-dms.sh — Executable validation script that checks DMS task JSON against schema, verifies CDC/S3 settings, and exits non-zero on failure.
• scripts/validate-pgloader.sh — Executable syntax and configuration checker for pgloader migration scripts, ensuring valid directives and paths.
• examples/worked-mysql-to-postgres.md — Step-by-step worked example demonstrating a zero-downtime migration from MySQL to PostgreSQL using pgloader and DMS CDC.

Install and Ship

Stop risking data integrity on unvalidated scripts. Upgrade to Pro to install the skill and ship with confidence.