Cloud Database Migration

Production engineering guide for cloud database migration covering patterns, implementation strategies, and operational best practices.

Cloud database migration is the process of moving a database workload—schema, data, and configuration—from one environment to another, typically from on-premises or legacy systems to a cloud-based platform. It matters when performance, scalability, or cost efficiency cannot be achieved in the current setup, or when an organisation adopts a cloud-first strategy. The migration is not a single command but a sequence of interdependent actions: planning, data extraction, schema transformation, data loading, validation, cutover, and post-migration tuning.

Pre-Migration Assessment

Before any data moves, assess the source and target environments. Identify schema differences, data volume, and operational constraints.

Use pg_dump with custom format to extract a full schema and data dump from a PostgreSQL source:

pg_dump -h old-db.example.com -U app_user -F c -v -f /backups/2024-05-10-full.dump myapp

The -F c flag produces a custom-format archive, which allows selective restoration and is efficient for large databases. The -v flag enables verbose output, critical for diagnosing failures during migration.

For MySQL, use mysqldump with --single-transaction and --routines to ensure consistency and preserve stored procedures:

mysqldump -h old-mysql.example.com -u app_user -p --single-transaction --routines --triggers --events --result-file=/backups/2024-05-10-full.sql myapp

The --single-transaction flag guarantees that all tables are dumped as of a single point in time, which is essential when data changes during the dump.

Validate the migration plan by comparing schema differences using pg_dump with --schema-only and --no-privileges:

pg_dump -h old-db.example.com -U app_user -s -N public -f /schemas/old-schema.sql myapp
pg_dump -h new-db.example.com -U app_user -s -N public -f /schemas/new-schema.sql myapp
diff /schemas/old-schema.sql /schemas/new-schema.sql

A diff output reveals missing indexes, altered column types, or added constraints. A common failure mode is forgetting to set LC_COLLATE and LC_CTYPE during pg_dump, resulting in case-sensitive or accent-sensitive mismatches in the target.

Schema Transformation

Transform the schema to align with the target database’s capabilities, constraints, and idioms.

Use a script in Python or shell to automate column type mapping. For example, map TIMESTAMP WITH TIME ZONE in PostgreSQL to DATETIMEOFFSET in SQL Server:

import re

def transform_schema(sql: str) -> str:
    # Replace PostgreSQL-specific types
    sql = re.sub(r'\bTIMESTAMP\ WITH\ TIME\ ZONE\b', 'DATETIMEOFFSET', sql)
    sql = re.sub(r'\bTEXT\b', 'NVARCHAR(MAX)', sql)
    sql = re.sub(r'\bUUID\b', 'UNIQUEIDENTIFIER', sql)
    # Add default value for auto-incrementing primary keys
    sql = re.sub(r'(CREATE\ TABLE.*?\()', r'\1\n    ID BIGINT IDENTITY(1,1) PRIMARY KEY,', sql)
    return sql

Apply this script to the CREATE TABLE statements extracted from the schema dump.

Common silent failures:

Data Extraction and Transfer

Extract data from the source system using a tool that supports streaming and resumable operations.

For large databases, use pg_dump with --jobs to parallelize the dump:

pg_dump -h old-db.example.com -U app_user -F c -v -j 8 -f /backups/2024-05-10-dump.j8.dump myapp

The -j 8 flag enables 8 parallel jobs, which is especially effective when the database has many large tables.

Transfer the dump file to the cloud using rsync with checksum-based updates:

rsync -avz --checksum --partial --progress /backups/2024-05-10-dump.j8.dump user@cloud-instance:/mnt/data/migrations/

The --checksum flag ensures that only changed blocks are transferred, reducing bandwidth usage. The --partial flag allows resuming interrupted transfers.

For incremental migrations, extract only changed data using pg_dump with --data-only and --table:

pg_dump -h old-db.example.com -U app_user -d myapp -t users -t orders -F c -v -f /backups/2024-05-10-incremental.dump

Apply the incremental dump in the target database using pg_restore:

pg_restore -h new-db.example.com -U app_user -d myapp -v -j 4 /backups/2024-05-10-incremental.dump

The -j 4 flag enables parallel restoration, which significantly reduces load time.

A critical failure mode is data consistency during the incremental phase: a transaction that modifies users and orders simultaneously may be captured in one pg_dump but not fully applied in the target. To address this, use pg_dump with --table and --schema in a script that runs during the migration window.

Data Loading and Validation

Load data into the target database using the appropriate tool or SQL commands.

For PostgreSQL, use psql to apply schema and data:

psql -h new-db.example.com -U app_user -d myapp -f /schemas/transformed-schema.sql
psql -h new-db.example.com -U app_user -d myapp -f /data/transformed-data.sql

Use psql with \copy for high-performance bulk loading from CSV files:

\copy users FROM '/data/users.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', NULL 'NULL');

The HEADER true option ensures that the first row contains column names, and DELIMITER ',' specifies comma-separated values.

For SQL Server, use bcp (bulk copy program):

bcp myapp.dbo.users in /data/users.csv -c -t, -U app_user -P password -S new-db.example.com

The -c flag specifies character format, -t, sets comma as the field terminator, and -U and -P pass the username and password.

Validate data integrity with a set of SQL queries executed against both source and target:

-- Count rows in each table
SELECT 'users' AS table, COUNT(*) AS count FROM users
UNION ALL
SELECT 'orders' AS table, COUNT(*) AS count FROM orders;

-- Check for nulls in critical columns
SELECT 'users' AS table, COUNT(*) AS missing_email FROM users WHERE email IS NULL
UNION ALL
SELECT 'orders' AS table, COUNT(*) AS missing_customer_id FROM orders WHERE customer_id IS NULL;

Run these queries in a script that compares results and logs discrepancies.

A common silent failure is not flushing the WAL (Write-Ahead Log) before taking a dump. The result is a partial or inconsistent state in the target database. Use pg_wal_lsn_diff to verify that the WAL position in the source matches the one in the target after pg_restore.

Cutover and Switchover

Migrate application traffic from source to target with minimal downtime.

Use a two-phase cutover: first, establish a replication stream from source to target, then switch write traffic to the target.

Set up logical replication in PostgreSQL using pglogical or native PUBLICATION and SUBSCRIPTION:

-- On source database
CREATE PUBLICATION myapp_publication FOR TABLE users, orders;

-- On target database
CREATE SUBSCRIPTION myapp_subscription
  CONNECTION 'host=old-db.example.com user=app_user dbname=myapp'
  PUBLICATION myapp_publication
  WITH (refresh_lsn = '0/1000000');

The refresh_lsn parameter ensures that the target starts with the correct WAL position, minimizing data loss.

When ready, stop replication and switch the application to the target:

-- On target database
ALTER SUBSCRIPTION myapp_subscription SET (refresh_lsn = '0/2000000');
-- Wait for replication to catch up
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), pg_last_wal_receive_lsn('myapp_subscription'));
-- Promote target as primary
ALTER SYSTEM SET primary = 'on';
-- Restart the database
SELECT pg_reload_conf();

A sharp edge: after promoting the target, the application may still write to the old database. To prevent this, use a read-write lock on the source database during cutover:

-- On source database
LOCK TABLE users IN EXCLUSIVE MODE;
LOCK TABLE orders IN EXCLUSIVE MODE;
-- Wait for all transactions to commit
SELECT pg_sleep(30);
-- Then, switch the application to the new database
-- Then, drop the lock
UNLOCK TABLE users, orders;

Failures during cutover include:

Post-Migration Tuning

After the migration, tune performance and monitor health.

Use EXPLAIN ANALYZE to identify slow queries:

EXPLAIN ANALYZE
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.customer_id
WHERE o.created_at >= '2024-05-01'
ORDER BY o.total DESC
LIMIT 10;

Compare the execution plans between source and target to identify missing indexes or inefficient joins.

Adjust work_mem, maintenance_work_mem, and effective_cache_size in PostgreSQL:

-- Set work_mem for complex queries
SET work_mem = '16MB';

-- Improve vacuum performance
SET maintenance_work_mem = '2GB';

-- Optimize for large datasets
SET effective_cache_size = '32GB';

Monitor database health using pg_stat_activity, pg_stat_statements, and pg_locks:

-- Show current queries and their execution times
SELECT pid, query, now() - query_start AS duration, state
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

A key failure mode is not resetting pg_stat_statements after migration, leading to skewed query statistics. Reset the statistics with:

SELECT pg_stat_statements_reset();

Also, ensure that autovacuum is enabled and tuned in the new database:

-- Enable autovacuum
ALTER SYSTEM SET autovacuum = on;

-- Set vacuum threshold and scale factor
ALTER SYSTEM SET autovacuum_vacuum_threshold = 50;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
ALTER SYSTEM SET autovacuum_analyze_threshold = 100;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.2;

Apply configuration changes with pg_reload_conf().

Failure Modes and Common Pitfalls

Cloud database migration is not a single command but a workflow of interdependent operations. Each phase must be validated, and each failure mode must be documented and rehearsed. The migration succeeds not when data is copied, but when the application runs smoothly on the new database with no unexpected behavior.

This page was rewritten on 16 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.