AWS DMS迁移本地Postgres text列至Aurora Serverless失败求助

问题描述 #

使用dms.t3.micro实例将本地PostgreSQL数据库中10-15KB的text类型列迁移至Aurora Serverless时，text列未被正确传输，数据验证提示Mismatched records，全量加载完成后存在显著延迟。

关键日志信息 #

LOB加载日志 #

2023-04-07T06:57:08:287106 [TARGET_LOAD ]T: Insert row to table 'table_1' with LOB Lookup ('3' LOBs) (odbc_util.c:3874)

延迟日志 #

2023-04-07T07:23:30:466649 [VALIDATOR       ]D:  iSourceLatency : 0, iTargetLatency : 1226882  iMigrationLatency : 2453764  (replicationtask.c:3626)

当前DMS配置 #

{
    "Logging": {
        "EnableLogging": true,
        "EnableLogContext": false,
        "LogComponents": [
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "TRANSFORMATION"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "SOURCE_UNLOAD"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "IO"
            },
            {
                "Severity": "LOGGER_SEVERITY_DETAILED_DEBUG",
                "Id": "TARGET_LOAD"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "PERFORMANCE"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "SOURCE_CAPTURE"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "SORTER"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "REST_SERVER"
            },
            {
                "Severity": "LOGGER_SEVERITY_DETAILED_DEBUG",
                "Id": "VALIDATOR_EXT"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "TARGET_APPLY"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "TASK_MANAGER"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "TABLES_MANAGER"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "METADATA_MANAGER"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "FILE_FACTORY"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "COMMON"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "ADDONS"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "DATA_STRUCTURE"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "COMMUNICATION"
            },
            {
                "Severity": "LOGGER_SEVERITY_DEFAULT",
                "Id": "FILE_TRANSFER"
            }
        ]
    },
    "StreamBufferSettings": {
        "StreamBufferCount": 3,
        "StreamBufferSizeInMB": 8,
        "CtrlStreamBufferSizeInMB": 5
    },
    "ErrorBehavior": {
        "FailOnNoTablesCaptured": true,
        "ApplyErrorUpdatePolicy": "LOG_ERROR",
        "FailOnTransactionConsistencyBreached": false,
        "RecoverableErrorThrottlingMax": 1800,
        "DataErrorEscalationPolicy": "SUSPEND_TABLE",
        "ApplyErrorEscalationCount": 0,
        "RecoverableErrorStopRetryAfterThrottlingMax": true,
        "RecoverableErrorThrottling": true,
        "ApplyErrorFailOnTruncationDdl": false,
        "DataTruncationErrorPolicy": "LOG_ERROR",
        "ApplyErrorInsertPolicy": "LOG_ERROR",
        "EventErrorPolicy": "IGNORE",
        "ApplyErrorEscalationPolicy": "LOG_ERROR",
        "RecoverableErrorCount": -1,
        "DataErrorEscalationCount": 0,
        "TableErrorEscalationPolicy": "STOP_TASK",
        "RecoverableErrorInterval": 5,
        "ApplyErrorDeletePolicy": "IGNORE_RECORD",
        "TableErrorEscalationCount": 0,
        "FullLoadIgnoreConflicts": true,
        "DataErrorPolicy": "LOG_ERROR",
        "TableErrorPolicy": "SUSPEND_TABLE"
    },
    "ValidationSettings": {
        "EnableValidation": false
    },
    "TTSettings": null,
    "FullLoadSettings": {
        "CreatePkAfterFullLoad": false,
        "StopTaskCachedChangesApplied": false,
        "StopTaskCachedChangesNotApplied": false,
        "MaxFullLoadSubTasks": 2,
        "TransactionConsistencyTimeout": 600,
        "CommitRate": 100,
        "TargetTablePrepMode": "DROP_AND_CREATE"
    },
    "TargetMetadata": {
        "TargetSchema": "",
        "SupportLobs": true,
        "FullLobMode": true,
        "LobChunkSize": 64,
        "LimitedSizeLobMode": false,
        "InlineLobMaxSize": 4,
        "LoadMaxFileSize": 0,
        "BatchApplyEnabled": false,
        "ParallelLoadThreads": 0,
        "ParallelLoadBufferSize": 0,
        "ParallelLoadQueuesPerThread": 0,
        "ParallelApplyThreads": 0,
        "ParallelApplyBufferSize": 0,
        "ParallelApplyQueuesPerThread": 0,
        "TaskRecoveryTableEnabled": false
    },
    "ControlTablesSettings": {
        "historyTimeslotInMinutes": 5,
        "HistoryTimeslotInMinutes": 5,
        "HistoryTableEnabled": true,
        "SuspendedTablesTableEnabled": true,
        "StatusTableEnabled": true,
        "FullLoadExceptionTableEnabled": false
    },
    "ChangeProcessingTuning": {
        "BatchApplyPreserveTransaction": true,
        "BatchApplyTimeoutMin": 1,
        "BatchApplyTimeoutMax": 30,
        "BatchApplyMemoryLimit": 500,
        "BatchSplitSize": 0,
        "MinTransactionSize": 1000,
        "CommitTimeout": 1,
        "MemoryLimitTotal": 1024,
        "MemoryKeepTime": 60,
        "StatementCacheSize": 50
    },
    "ChangeProcessingDdlHandlingPolicy": {
        "HandleSourceTableDropped": true,
        "HandleSourceTableTruncated": true,
        "HandleSourceTableAltered": true
    },
    "PostProcessingRules": null,
    "BeforeImageSettings": null,
    "LoopbackPreventionSettings": null,
    "CharacterSetSettings": null,
    "FailTaskWhenCleanTaskResourceFailed": false
}

原因分析 #

1. DMS实例资源不足：dms.t3.micro仅1GB内存，处理10-15KB的LOB数据时，8MB的StreamBufferSizeInMB和1GB的MemoryLimitTotal无法满足LOB缓存与传输需求，导致数据截断或丢失，同时引发高延迟。

2. LOB配置不匹配：InlineLobMaxSize设置为4KB，远小于text列的10-15KB大小，所有text列都会触发FullLobMode传输，增加了内存开销与传输复杂度，容易因资源不足导致传输失败。

3. 验证机制未开启：ValidationSettings.EnableValidation关闭，无法在迁移过程中实时校验数据一致性，仅在全量加载完成后才发现数据不匹配，错过早期排查时机。

4. 错误处理策略宽松：DataErrorPolicy设置为LOG_ERROR，LOB传输错误仅被记录日志而未终止任务或暂停表，导致错误数据持续累积，最终出现大量不匹配记录。

解决方案 #

• 升级DMS实例类型：将dms.t3.micro替换为dms.t3.small或更高配置，提升内存与CPU资源，解决LOB处理的资源瓶颈。

• 调整LOB相关配置：

  • 增大StreamBufferSizeInMB至16或32，提升LOB数据的缓存能力；
  • 将InlineLobMaxSize调整为16KB（覆盖text列的最大大小），让小尺寸LOB可以inline传输，降低FullLobMode的开销；
  • 保持FullLobMode: true，确保超过inline大小的LOB能完整传输。

• 优化全量加载设置：

  • 适当提高CommitRate至500，减少事务提交次数，提升加载效率；
  • 增大MaxFullLoadSubTasks至4（需匹配实例资源），并行处理更多加载任务。

• 开启数据验证：设置ValidationSettings.EnableValidation: true，让DMS在迁移过程中实时校验源端与目标端数据，及时发现并报错。

• 调整错误处理策略：将DataErrorPolicy改为SUSPEND_TABLE，当出现LOB传输错误时自动暂停表迁移，避免错误数据扩散，便于定位问题。

内容的提问来源于stack exchange，提问作者Dragos Roban