Database migration workflows

For a relational-to-relational migration, I’d treat schema conversion, data movement, validation, and cutover as separate problems. The right toolchain depends heavily on whether the source and target use the same engine.

Recommended migration stack

Need	Good tools/processes	When to use
Inventory & assessment	AWS DMS Fleet Advisor, Azure Migrate/DMS assessment, vendor assessment tools	Before designing the migration
Schema conversion	AWS DMS Schema Conversion/SCT, Google Database Migration Service conversion, SQL Server Migration Assistant, native dump/restore	Especially for Oracle → PostgreSQL, SQL Server → PostgreSQL, etc.
Initial data load	Native dump/restore, bulk loaders, cloud migration services, COPY/LOAD DATA/BCP-type utilities	Large initial snapshot
Ongoing changes / low downtime	AWS DMS, Azure DMS, Google DMS, Debezium/Kafka, native replication	Keep target synchronized while users continue writing
Schema version control	Flyway, Liquibase, database projects / migration scripts	Make target schema reproducible and auditable
Validation	Row counts, checksums/hashes, aggregates, referential-integrity checks, application-level reconciliation	Prove that migration is correct
Cutover	Application quiesce + final CDC catch-up + DNS/config switch	Production transition
Rollback	Keep source authoritative until validation/cutover succeeds; rehearse reverse migration or application rollback	Recovery plan

1. First decide: homogeneous or heterogeneous

Homogeneous means the engine stays the same—for example PostgreSQL → PostgreSQL. This is comparatively straightforward because schema semantics and data types largely carry over. Google's migration service, for example, explicitly distinguishes homogeneous migrations and aims for high fidelity of schema, data, and metadata.docs.cloud.google.com

Heterogeneous means changing engines—for example Oracle → PostgreSQL or SQL Server → PostgreSQL. This is where most of the engineering work lies: data types, SQL dialects, indexes, sequences/identity columns, stored procedures, functions, triggers, permissions, and transaction semantics can all differ.docs.cloud.google.com

For heterogeneous migrations, don't try to solve everything with a data-copy tool. Use a schema/code conversion tool first, then a separate data migration mechanism.

2. Assess the source before migrating

Create an inventory containing at least:

• Tables and approximate row counts
• Table sizes and growth rates
• Primary/foreign keys
• Indexes
• Unique constraints
• Views/materialized views
• Sequences/identity columns
• Triggers
• Stored procedures/functions/packages
• User-defined types
• Extensions
• Permissions/roles
• Partitioning
• Generated/computed columns
• Large objects/BLOBs
• Application SQL and ORM queries
• Jobs/scheduled database processes

The important addition is application code. A schema can migrate perfectly while the application breaks because it relies on vendor-specific SQL. AWS specifically notes that heterogeneous migration may require changing application SQL as well as database objects.docs.aws.amazon.com

For AWS migrations, DMS Schema Conversion can produce an assessment showing which objects can be automatically converted and which require manual work.docs.aws.amazon.com+1

3. Convert the schema before moving the data

For a platform change, I'd generally do:

Source schema → automated conversion → human review → version-controlled SQL → target

For example:

Oracle → PostgreSQL

Oracle metadata
      ↓
Schema conversion tool
      ↓
Conversion report
      ↓
Fix incompatible objects
      ↓
Reviewed SQL
      ↓
PostgreSQL target

AWS DMS Schema Conversion can convert tables and many other objects, including views, procedures and functions, while identifying objects that need manual work.docs.aws.amazon.com+1

Google DMS similarly has conversion workspaces for heterogeneous migrations, including Oracle/SQL Server → PostgreSQL, with tooling for resolving conversion issues.docs.cloud.google.com

Don't blindly apply generated DDL to production. Put the resulting schema into Git and make it something you can recreate from scratch.

For an ongoing development environment, I'd strongly consider Flyway or Liquibase so subsequent schema changes are migrations rather than ad-hoc edits.

4. Move the initial data

You have two primary patterns.

A. Offline dump → transform → load

Source
  ↓
Consistent dump
  ↓
Transform
  ↓
Target
  ↓
Validate

Best when:

• Database is relatively small
• Downtime is acceptable
• You can schedule a maintenance window
• You want a simple migration

For example, PostgreSQL's pg_dump can produce schema-only or data-containing dumps; Microsoft's migration guidance also uses native database tooling for particular scenarios.learn.microsoft.com

B. Bulk load + CDC

For a production system where downtime matters, this is usually the better architecture:

             ┌───────────────┐
             │ Source DB     │
             └───────┬───────┘
                     │
              Initial snapshot
                     │
                     ▼
             ┌───────────────┐
             │ Target DB     │
             └───────────────┘
                     ▲
                     │
                 CDC stream
                     │
             ┌───────┴───────┐
             │ Source changes│
             └───────────────┘

The target initially receives a snapshot, then CDC continuously applies changes that occurred afterward.

AWS DMS supports both homogeneous and heterogeneous migrations and can keep the source operational during migration. docs.aws.amazon.comdocs.cloud.google.com Google DMS likewise supports continuous CDC-based migration for low-downtime heterogeneous migrations.docs.cloud.google.com+1

Debezium + Kafka is another strong option when you want a more infrastructure-independent CDC architecture, particularly for repeatable migrations or ongoing replication pipelines.

5. Pay particular attention to data-type mappings

This is one of the biggest failure points in cross-platform migrations.

Build an explicit mapping table such as:

Don't merely check whether a value can be inserted. Check whether its meaning remains identical.

6. Validate continuously, not just at the end

I recommend three levels of validation.

Level 1 — Structural

Compare:

• Tables
• Columns
• Types
• Nullability
• PKs
• FKs
• Unique constraints
• Indexes
• Views
• Procedures/functions
• Triggers

Level 2 — Data

At minimum:

row count(source.table) == row count(target.table)

But row counts alone aren't sufficient.

For important tables, compare:

COUNT(*)
MIN(primary_key)
MAX(primary_key)
SUM(numeric columns)
COUNT(DISTINCT key)
NULL counts

For stronger validation, calculate deterministic hashes/checksums over sorted records.

For example conceptually:

hash(
    ORDER BY primary_key
    concat(column1, '|', column2, '|', column3 ...)
)

Compare those between source and target.

Level 3 — Application behavior

Run the actual application against the target and test:

• Reads
• Inserts
• Updates
• Deletes
• Transactions
• Reporting
• Search
• Authentication/authorization
• Background jobs
• Stored-procedure-dependent functionality

This catches problems that database-level validation won't.

7. Do a rehearsal migration

This is probably the most valuable process improvement.

Do at least one full dress rehearsal:

Production-like source
        ↓
Migration
        ↓
Target
        ↓
Validation
        ↓
Application tests
        ↓
Cutover simulation
        ↓
Measure elapsed time

Record:

• Initial load duration
• CDC lag
• Conversion failures
• Data discrepancies
• Target performance
• Cutover duration
• Rollback duration

Then fix those problems before production.

8. Cut over only after CDC catches up

A typical low-downtime cutover looks like:

1. Stop/restrict application writes.
2. Allow CDC to process remaining changes.
3. Confirm CDC lag = zero or within an agreed threshold.
4. Run final reconciliation.
5. Enable application connections to target.
6. Run smoke tests.
7. Monitor heavily.
8. Keep source intact for the agreed rollback window.

Cloud migration services explicitly support this general snapshot + ongoing replication model.docs.aws.amazon.com+1

The critical concept is don't make the target authoritative merely because the migration tool says "100% loaded." You want independent validation.

Which tools I'd choose

PostgreSQL → PostgreSQL

Start with:

• pg_dump / pg_restore for smaller or offline migrations
• Native PostgreSQL replication for appropriate same-engine migrations
• AWS DMS / Azure DMS / Google DMS for cloud migrations where managed CDC is useful
• pg_dump --schema-only or database-project tooling for schema
• Flyway/Liquibase for ongoing schema management

SQL Server → PostgreSQL

I'd investigate:

• AWS DMS Schema Conversion/SCT + AWS DMS if moving toward AWS
• Google Database Migration Service if moving toward Google Cloud
• Azure tooling if PostgreSQL is the destination but Azure is the migration environment
• Native/custom ETL where the migration involves substantial business-rule transformations

AWS explicitly separates schema conversion from data migration: Schema Conversion handles the former while DMS handles the latter.docs.aws.amazon.com

Oracle → PostgreSQL

Use a schema/code conversion tool plus CDC-based data migration. This is a classic heterogeneous migration where automated conversion can save substantial effort but should not be treated as fully automatic. Google DMS, for example, supports Oracle → Cloud SQL/AlloyDB for PostgreSQL and uses CDC for ongoing changes.learn.microsoft.com+1

MySQL → MySQL / PostgreSQL → PostgreSQL

Prefer native engine tooling first, especially if you're not changing database semantics. Managed DMS products become attractive when you need cross-network/cloud movement, CDC, orchestration, or minimal downtime.

A good production architecture

For a serious migration, I'd aim for:

                 ┌────────────────────┐
                 │ Source DB           │
                 │                    │
                 │ Production writes  │
                 └─────────┬──────────┘
                           │
                    ┌──────┴──────┐
                    │             │
                Snapshot         CDC
                    │             │
                    ▼             ▼
              ┌────────────────────────┐
              │ Target DB               │
              │                         │
              │ Converted schema        │
              │ + migrated data         │
              │ + replicated changes    │
              └───────────┬────────────┘
                          │
                    Validation
                          │
                ┌─────────▼─────────┐
                │ Application tests │
                └─────────┬─────────┘
                          │
                       Cutover
                          │
                          ▼
                 Target becomes
                  authoritative

And manage the whole thing as code:

migration/
├── assessment/
├── schema/
│   ├── tables/
│   ├── indexes/
│   ├── constraints/
│   ├── views/
│   └── procedures/
├── transformations/
├── validation/
├── cutover/
└── rollback/

My rule of thumb

Same engine + tolerable downtime: native dump/restore.

Same engine + little downtime: native replication or managed CDC migration.

Different engines + little downtime: schema-conversion tool + bulk initial load + CDC + independent validation.

Large/mission-critical migration: rehearse it repeatedly, automate the assessment/validation/cutover, and don't decommission the source until the rollback window has expired.

For reference, AWS, Azure, and Google all now provide managed migration services with varying source/target coverage; the exact choice should follow the source → target pair, not the cloud provider you happen to use.learn.microsoft.com

AWS Database Migration Service
AWS Schema Conversion Tool / DMS Schema Conversion
Google Cloud Database Migration Service
Azure Database Migration Service

If you give me the source DB, target DB, approximate size, and acceptable downtime (e.g. Oracle 19c → PostgreSQL 17, 4 TB, <5 minutes), I can turn this into a concrete tool choice and step-by-step migration architecture.

Migrating both data and schema between two different relational database engines (e.g., PostgreSQL to MySQL, or Oracle to PostgreSQL) is a complex engineering challenge. Because data types, constraints, indexes, and SQL dialects differ, it requires a structured, multi-phase approach rather than a simple dump-and-restore.

Step-by-Step Migration Process

1. Schema Assessment and Mapping
   • Audit your source schema (tables, views, stored procedures, triggers, and data types).
   • Map source types to target equivalents (e.g., PostgreSQL SERIAL or UUID vs. MySQL AUTO_INCREMENT or VARCHAR(36)).
   • Refactor or rewrite database-specific procedural code (PL/pgSQL to T-SQL, etc.), as stored procedures rarely translate cleanly.
2. Schema Migration (DDL)
   • Generate and apply the Data Definition Language (DDL) scripts on the target database to recreate the structure, primary keys, and foreign keys.
   • Tip: Create indexes after loading the data to significantly speed up the bulk-insertion phase.
3. Data Migration (DML) - Initial Load
   • Extract data from the source, transform it to match target schema expectations (handling nullability, date formats, or character encodings), and load it into the target.
   • For large datasets, use parallelization or chunking (e.g., by ID ranges or date partitions) to avoid memory or network bottlenecks.
4. Change Data Capture (CDC) / Catch-Up Sync
   • Because production systems keep running during migration, changes will happen on the source database after the initial load.
   • Set up a real-time CDC or replication pipeline to stream ongoing inserts, updates, and deletes from source to target.
5. Validation and Testing
   • Compare row counts, checksums, and aggregate queries between source and target databases.
   • Run your application's integration and regression test suites pointing at the new target database.
6. Cutover
   • Put the source application into read-only mode (or schedule a brief maintenance window).
   • Let the final CDC sync complete, switch application connection strings to the new database, and enable full read/write operations.

Recommended Migration Tools

Depending on your source and target engines, several industry-standard tools can automate or simplify the heavy lifting:

• General / Multi-Database Tools:
  • Apache Kafka / Kafka Connect: Excellent for real-time CDC pipelines when moving data continuously between disparate systems using connector plugins.
  • Debezium: An open-source distributed platform for change data capture that turns your database into a streaming event source, supporting MySQL, PostgreSQL, Oracle, SQL Server, and more.
  • Pentaho Data Integration (Kettle): A robust, visual ETL (Extract, Transform, Load) tool capable of handling complex data mapping and type conversions across various SQL databases.
• Engine-Specific Tools:
  • A reliable, open-source tool used specifically to migrate an Oracle database to a PostgreSQL-compatible schema and data layout.

If you'd like to narrow this down, let me know:

• What is your source database engine and your target database engine?
• Roughly how large is the dataset (gigabytes vs. terabytes)?
• Can you afford a maintenance window with downtime , or does it need to be zero-downtime?

How to migrate data and schema between relational database systems

When switching from one relational database management system (RDBMS) to another — for example, MySQL to PostgreSQL, SQL Server to Oracle, or Oracle to PostgreSQL — the safest approach is to treat the migration as two related projects: schema conversion and data migration, followed by validation and a controlled cutover.

The main challenge is that databases differ in data types, SQL syntax, transaction behavior, indexing, stored procedures, and constraint handling. Copying the data alone does not guarantee that the new system will behave like the old one.docs.aws.amazon.com+2

Recommended migration workflow

1. Assess the source database

   Inventory tables, columns, primary and foreign keys, indexes, views, triggers, stored procedures, sequences, permissions, and database-specific features. Measure database size and transaction volume, and define your acceptable downtime.

2. Convert and review the schema

   Map source data types and SQL features to target equivalents. Use automated conversion tools where appropriate, but manually review anything unsupported or semantically different.

3. Create the target schema

   Deploy tables and constraints in a controlled order. Create indexes, views, triggers, stored procedures, and permissions as needed, using version-controlled migration scripts.

4. Transfer the data

   Perform a full load using a native export/import utility or a migration tool. If the source database must remain online, use change data capture (CDC) or replication to keep the target synchronized.

5. Validate correctness and performance

   Compare row counts, key values, aggregates, relationships, and representative application queries. Test transaction behavior and performance under realistic workloads.

6. Cut over and monitor

   Freeze or coordinate writes, synchronize the final changes, verify consistency, redirect application traffic, and monitor errors and performance. Keep a tested rollback plan.

This workflow aligns with documented migration practices that separate schema conversion from data transfer and recommend pre-migration assessments and data validation.docs.aws.amazon.com+2

1. Handle schema differences explicitly

Do not assume that a schema can be copied unchanged between database engines. Build a mapping of source and target features before migrating.

For example, when migrating from SQL Server to PostgreSQL, review IDENTITY columns, T-SQL stored procedures, date/time behavior, case sensitivity, and identifier naming. Some objects will need to be rewritten rather than automatically converted.

Best practice: Generate a conversion assessment report, fix incompatible objects, and review the converted SQL before applying it. AWS provides DMS Schema Conversion for supported migration paths. It converts schemas, not the actual data, so a separate data-transfer process is required.docs.aws.amazon.com+1

2. Choose the right data migration strategy

Option A: Export and import

(Best for simpler migrations)

Export data to a database dump, CSV, or another supported format, then load it into the target.

• Suitable for smaller databases, maintenance windows, and offline migrations.
• Straightforward to repeat and troubleshoot.
• Requires a plan for writes occurring during the export and import.

Examples: pg_dump and pg_restore for PostgreSQL-to-PostgreSQL migrations; native utilities for other engines.

Option B: Full load plus change data capture

(Best for minimizing downtime)

Copy the existing data first, then continuously replicate inserts, updates, and deletes until cutover.

• Suitable for large or continuously used databases.
• Reduces the final synchronization window.
• Requires monitoring replication lag, handling schema changes, and planning the final write freeze.

Example: AWS Database Migration Service, where supported.

Option C: ETL or custom transformation pipeline

Extract data, transform it to the target model, and load it in batches.

• Useful when data types or business rules require substantial transformation.
• Supports cleansing, normalization, and legacy data fixes.
• Requires careful handling of retries, duplicate records, and transaction boundaries.

The appropriate choice depends on source and target compatibility, database size, acceptable downtime, and transformation complexity. AWS documents both full-load and ongoing-replication approaches, with engine-specific limitations.docs.aws.amazon.com+2

3. Validate the migrated data systematically

A successful import is not proof of a successful migration. Use several independent checks.

Migration validation checklist

of

For large datasets, consider checksums or partition-level comparisons, and use built-in migration validation where available. Investigate discrepancies before cutover rather than treating matching total row counts as sufficient.docs.aws.amazon.com+1

4. Avoid the most common migration failures

• Data loss or corruption: Take verified backups and test restore procedures before starting.
• Constraint failures: Load tables in an appropriate order or temporarily defer/disable certain constraints when the chosen tool and engine permit it. Re-enable and validate them afterward.
• Missing database objects: Explicitly inventory secondary indexes, triggers, views, routines, grants, and scheduled jobs. Basic migration tools may not transfer all of these automatically.docs.aws.amazon.com+1
• Broken application queries: Test database-specific SQL, ORM-generated queries, transaction isolation, and locking behavior.
• Sequence collisions: Ensure identity generators and sequences are positioned beyond existing IDs before new writes begin.
• Unsafe cutover: Define a clear write-freeze or synchronization procedure and avoid uncontrolled writes to both databases during transition.
• No rollback path: Preserve the source database until the new system has passed acceptance checks and the rollback window has expired.

5. A practical migration toolset

Treat automated tools as accelerators, not substitutes for review. Tool support varies by source-target combination.

To tailor this to your migration

The most useful next step is to identify your source and destination database engines.

Your migration profile

Source database

MySQL / MariaDB

PostgreSQL

Microsoft SQL Server

Oracle

Other

Target database

MySQL / MariaDB

PostgreSQL

Microsoft SQL Server

Oracle

Other

Approximate database size

Under 10 GB

10–100 GB

100 GB–1 TB

Over 1 TB

Downtime requirement

Offline migration is acceptable

A short maintenance window

Minimal downtime

Schema complexity

Simple tables and relationships

Views, triggers, and stored procedures

Complex business logic and database-specific features

[Build my migration plan]