Why Migration Automation Matters

Why Migration Automation Matters

Migrating from RDS Postgres to Aurora Postgres is not a simple schema copy. Aurora introduces architectural differences—shared storage, read replicas with different replication lag characteristics, and different failover semantics—that demand careful orchestration. Manual migration risks data loss, extended downtime, and inconsistent state across application layers. Automation ensures repeatability, validates data integrity at each stage, and minimizes the window where your system operates in a hybrid state.

The Core Challenge: Consistency During Live Migration

The fundamental problem: you cannot simply snapshot RDS and restore to Aurora while applications write continuously. You need a strategy that:

• Captures baseline state from RDS without blocking writes
• Streams incremental changes to Aurora in near-real-time
• Validates consistency between source and target before cutover
• Handles the cutover window where writes pause and final sync completes
• Rolls back safely if validation fails

Architecture: The Three-Phase Approach

Phase 1: Full Snapshot with Logical Replication Setup

Start by creating a baseline copy of your RDS database. Use pg_dump with the --no-privileges flag to avoid permission issues during restore, but critically, use --snapshot-isolation semantics (via explicit transactions) to ensure consistency:

-- On RDS source
BEGIN ISOLATION LEVEL SERIALIZABLE;
CREATE PUBLICATION migration_pub FOR ALL TABLES;
COMMIT;

This publication will be the source of truth for incremental changes. Meanwhile, in parallel, dump the schema and initial data:

pg_dump --host=rds-endpoint --username=postgres \
  --schema-only --no-privileges \
  --dbname=mydb > schema.sql

pg_dump --host=rds-endpoint --username=postgres \
  --data-only --no-privileges \
  --dbname=mydb > data.sql

Restore these into Aurora:

psql --host=aurora-endpoint --username=postgres \
  --dbname=mydb < schema.sql

psql --host=aurora-endpoint --username=postgres \
  --dbname=mydb < data.sql

Why this approach? Logical replication via publications decouples the snapshot from the replication stream. You can take your time restoring the baseline without blocking the source, then catch up on changes incrementally.

Phase 2: Incremental Sync via Logical Replication

Once the baseline is in Aurora, set up a subscription on the Aurora side to consume changes from the RDS publication:

-- On Aurora target
CREATE SUBSCRIPTION migration_sub 
  CONNECTION 'host=rds-endpoint dbname=mydb user=postgres password=xxx' 
  PUBLICATION migration_pub 
  WITH (copy_data = false, synchronous_commit = off);

The copy_data = false is critical—you already have the baseline, so don't re-copy. The subscription will now apply WAL (Write-Ahead Log) records from RDS to Aurora asynchronously.

Key insight: Logical replication in Postgres works at the SQL statement level, not the byte level. This means it's database-agnostic and can handle schema differences gracefully (within limits). However, it requires a replica identity on the source tables—typically the primary key or a unique index.

Phase 3: Validation and Cutover

Before switching traffic, you must verify that Aurora has caught up and data is consistent. This is where automation becomes critical:

-- Check replication lag
SELECT slot_name, restart_lsn, confirmed_flush_lsn 
FROM pg_replication_slots 
WHERE slot_type = 'logical';

-- On Aurora, verify row counts match
SELECT schemaname, tablename, n_live_tup 
FROM pg_stat_user_tables 
ORDER BY schemaname, tablename;

Write a validation script that compares checksums of critical tables:

import psycopg2
import hashlib

def get_table_checksum(conn, schema, table):
    """Compute MD5 checksum of table data for validation."""
    cursor = conn.cursor()
    cursor.execute(f"""
        SELECT md5(string_agg(row_data::text, '' ORDER BY row_data))
        FROM (
            SELECT row_to_json(t.*) as row_data 
            FROM {schema}.{table} t 
            ORDER BY {get_primary_key(conn, schema, table)}
        ) sub;
    """)
    return cursor.fetchone()[0]

def validate_migration(rds_conn, aurora_conn, tables):
    """Compare checksums across all tables."""
    mismatches = []
    for schema, table in tables:
        rds_hash = get_table_checksum(rds_conn, schema, table)
        aurora_hash = get_table_checksum(aurora_conn, schema, table)
        if rds_hash != aurora_hash:
            mismatches.append((schema, table, rds_hash, aurora_hash))
    return mismatches

Once validation passes, execute the cutover:

-- On RDS: pause writes (application-level or via connection pooling)
-- On Aurora: wait for subscription to catch up
SELECT wait_for_catchup('migration_sub', 'write', NULL);

-- Final validation
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') as current_lsn;

-- Drop subscription and publication
DROP SUBSCRIPTION migration_sub;
DROP PUBLICATION migration_pub;

-- Update application connection strings to Aurora

Handling Edge Cases

Sequences and Identity Columns

Logical replication does not replicate sequence state. You must manually sync them:

-- On RDS
SELECT sequence_name, last_value 
FROM information_schema.sequences;

-- On Aurora, set each sequence to the RDS value
SELECT setval('my_sequence', 5000);

Large Objects and Unlogged Tables

Logical replication cannot replicate large objects (BLOBs) or unlogged tables. For these, you'll need a separate strategy—either migrate them manually post-cutover or store them in S3 and reference via URLs.

Replication Slot Retention

RDS has a maximum replication slot retention period (typically 24 hours). If your migration takes longer, the slot may be dropped and you'll lose the replication stream. Monitor this:

SELECT slot_name, restart_lsn, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) as bytes_retained
FROM pg_replication_slots;

Automation Framework: Orchestration with Idempotency

A production migration automation tool should be idempotent—running it twice should be safe. Structure it as a state machine:

class MigrationState:
    PENDING = 'pending'
    BASELINE_COPIED = 'baseline_copied'
    REPLICATING = 'replicating'
    VALIDATED = 'validated'
    CUTOVER_COMPLETE = 'cutover_complete'
    ROLLED_BACK = 'rolled_back'

def migrate(rds_config, aurora_config, state_store):
    state = state_store.get_state()
    
    if state == MigrationState.PENDING:
        copy_baseline(rds_config, aurora_config)
        state_store.set_state(MigrationState.BASELINE_COPIED)
        state = MigrationState.BASELINE_COPIED
    
    if state == MigrationState.BASELINE_COPIED:
        setup_replication(rds_config, aurora_config)
        state_store.set_state(MigrationState.REPLICATING)
        state = MigrationState.REPLICATING
    
    if state == MigrationState.REPLICATING:
        if validate_consistency(rds_config, aurora_config):
            state_store.set_state(MigrationState.VALIDATED)
            state = MigrationState.VALIDATED
        else:
            rollback(rds_config, aurora_config)
            state_store.set_state(MigrationState.ROLLED_BACK)
            raise Exception('Validation failed')
    
    if state == MigrationState.VALIDATED:
        execute_cutover(rds_config, aurora_config)
        state_store.set_state(MigrationState.CUTOVER_COMPLETE)

Performance Considerations

Replication lag: Logical replication applies changes serially on the subscriber. If your RDS instance has high write throughput, Aurora may fall behind. Monitor the lag and consider increasing max_parallel_apply_workers on Aurora (available in Aurora PostgreSQL 13+).

Network bandwidth: Replication streams WAL over the network. If RDS and Aurora are in different regions, this can be a bottleneck. Use VPC endpoints or AWS DMS (Database Migration Service) for cross-region migrations.

Validation cost: Computing checksums on large tables is expensive. Consider sampling or using incremental validation (e.g., validate only tables modified in the last hour).

Rollback Strategy

If validation fails or cutover encounters issues, you need a fast rollback. Keep the RDS instance running and the publication active until you're confident in Aurora. To rollback:

-- On Aurora
DROP SUBSCRIPTION migration_sub;

-- On RDS, keep the publication active
-- Repoint application to RDS

-- Later, when ready to retry:
TRUNCATE TABLE aurora_table CASCADE;
CREATE SUBSCRIPTION migration_sub_retry ...;

The key is that logical replication is resumable—if you drop and recreate the subscription, it will pick up from where it left off (as long as the replication slot hasn't been garbage collected).

Monitoring and Observability

Instrument your migration with metrics:

• Replication lag (bytes and time): How far behind is Aurora?
• Validation checksum mismatches: Which tables are out of sync?
• Cutover duration: How long did the write pause last?
• Rollback count: How many times did validation fail?

Log these to CloudWatch or your observability platform so you can correlate migration events with application behavior.