Near-Zero-Downtime Database Cutover with AWS DMS and CDC

A near-zero-downtime database migration with AWS DMS uses a single task that runs a full load and then keeps the target in sync with change data capture (CDC). You cut over only when CDC latency is near zero, which shrinks the write-freeze window to the seconds it takes to stop application writes, confirm the target has drained the change stream, and repoint connections. The hard part is not the copy. It is the source prerequisites, the primary-key and LOB edge cases, and knowing which signal tells you it is safe to switch.

This walks through what to check before you touch anything, how the full-load-plus-CDC mechanism actually behaves, and the trade-offs that bite you weeks later.

What to check before you create a single endpoint

Do not start with the replication instance. Start with whether your source can even feed a clean change stream.

Every table needs a primary key or you lose CDC updates

This is the one that quietly corrupts a migration. On PostgreSQL sources, AWS DMS supports CDC for tables with primary keys, and if a table has no primary key the write-ahead log does not include a before image of the row, so DMS cannot apply updates or deletes to that table on the target. There is a workaround using REPLICA IDENTITY FULL, but it generates extra WAL volume, and AWS recommends testing it carefully before relying on it. REPLICA IDENTITY FULL works with a logical decoding plugin but is not supported with the pglogical plugin.

Inventory your tables for missing primary keys first. A table that full-loads correctly but silently misses every subsequent update is the worst failure mode here, because validation may pass at load time and drift afterward.

Turn on logical replication and understand the slot

For a full-load-and-CDC or CDC-only PostgreSQL task, DMS uses logical replication slots to retain WAL until the changes are decoded. On Amazon RDS for PostgreSQL that means setting rds.logical_replication and letting the instance set wal_level to logical. DMS uses either the test_decoding or pglogical plugin; if pglogical is available it is used by default, and you can force test_decoding with the PluginName extra connection attribute.

The slot is also where you can hurt yourself. On a restart, as opposed to a resume, of a full-load-and-CDC or CDC-only task, the replication slot is recreated. And if max_slot_wal_keep_size is set to a non-default value and the slot's restart_lsn falls behind the current LSN by more than that size, the task fails because the required WAL files have been removed. In plain terms: a slot that falls too far behind on a busy source can lose the WAL it needs, and the task dies. Watch slot lag on the source, not just DMS metrics.

Run the premigration assessment

Before running the task, run a premigration assessment. A premigration assessment evaluates components of the task to identify problems that would prevent it from running as expected, such as unsupported source data types or primary-key formats, and it lets you fix them before a task failure wastes a cutover window. The assessment run is the recommended option over the legacy data-type report and can write results to an S3 bucket. Treat a clean assessment as a gate, not a formality.

How full load plus CDC actually behaves

The task copies existing rows during the full load, buffers changes that happen during that load, and then applies them and switches into continuous CDC. A few mechanics decide whether your cutover is boring.

Latency is a number you watch, not a feeling

Two CloudWatch metrics tell you where the change stream stands. CDCLatencySource is the delay in seconds between the commit time of the last event captured from the source and the replication instance's current time; a high value means capture is falling behind at the source. CDCLatencyTarget is the delay between the commit on the source of the first event waiting to be applied and the current time, so it includes any read delay and is always greater than or equal to CDCLatencySource. AWS gives a worked example: an insert committed at 10:00, consumed at 10:02, and written to the target at 10:05 produces a CDCLatencyTarget of 300 seconds.

Your cutover trigger is both metrics trending to near zero and staying there. If CDCLatencyTarget stays high while CDCLatencySource is low, the target cannot ingest fast enough, and no amount of waiting fixes it. That is a target-sizing or index problem to solve before cutover, not during it.

Native start points let you control where CDC begins and ends

With CDC native start points you can begin replication from a known position in the source log rather than "now." On PostgreSQL, you set the slotName extra connection attribute to an existing logical replication slot so replication can resume from a previous point in time. DMS also caches checkpoint information so you can recover a failed task or start a second task to another target from the same point. On the stop side, DMS can stop a task at a chosen commit time or server time that you set in UTC, which is useful for a controlled, scripted cutover.

If you use DMS Serverless rather than a provisioned replication instance, budget for cold start: AWS notes it can take up to 40 minutes to initialize resources the first time you start a new Serverless replication. Do not discover that during the maintenance window.

Validate the copy, and know what validation will not check

DMS data validation compares source and target row by row and reports per-table state such as pending, mismatched, or no primary key. It is genuinely useful, but it has sharp edges you must design around.

Data validation requires that the table has a primary key or unique index, and it skips an entire table if a data-masking transformation exists on that table's primary or unique key column, showing the state as "No primary key." It also does not work with Amazon Aurora PostgreSQL Limitless, where tables show as "No primary key." For RDS for PostgreSQL endpoints AWS recommends SSL mode required, and on PostgreSQL 12 and 13 you must create a BIT_XOR aggregate for validation to work:

CREATE OR REPLACE AGGREGATE BIT_XOR(IN v bit) (
  SFUNC = bitxor,
  STYPE = bit
);

The practical rule: the tables most likely to hold data quietly, the ones without a stable primary key, are exactly the ones validation cannot vouch for. Reconcile those with your own count and checksum queries.

The LOB trade-off, which costs you at load time and cutover

Large objects force a choice between correctness and speed, and the setting you pick determines both how long the load takes and whether data gets truncated.

In full LOB mode DMS migrates every LOB regardless of size, one at a time, piece by piece, which can be quite slow. In limited LOB mode you set a maximum LOB size, DMS pre-allocates memory and bulk-loads, which is much faster, but LOBs larger than the maximum are truncated and a warning is written to the log. Inline LOB mode transfers small LOBs inline and falls back to full LOB mode for larger ones, which suits a table where most LOBs are small.

Two details that cause real incidents. To validate limited LOB size, you must set ValidationPartialLobSize to the same value as LobMaxSize, otherwise validation and the load disagree. And a primary key is mandatory on any table with LOB columns during CDC. The recommended LobChunkSize for full LOB mode is 64 KB; going above that can cause disconnects if it exceeds your network's maximum packet size, and can cause task failures. If you choose limited LOB mode, base the limit on the actual maximum in the data, not a guess, or you will silently truncate in production and find out during a support ticket months later.

Sizing the replication instance so it is not the bottleneck

The replication instance is transient infrastructure, but an undersized one turns a two-hour load into an all-day one. DMS storage volumes are GP2 SSDs with a base of three IOPS per GB and burst up to 3,000 IOPS on credits, so watch ReadIOPS and WriteIOPS and keep their sum under the volume's base performance to avoid exhausting burst credit mid-load. On a small instance such as dms.t2.medium, reduce the number of tables loaded in parallel; parallelism helps on larger instances but degrades performance past a point.

One availability caveat: with a single-AZ or Multi-AZ replication instance, if a failover or host replacement happens during a full load, the full-load task is expected to fail. Plan to restart the full load if that happens, and run the benchmark migration AWS recommends so you understand your real throughput before the cutover window.

If you are planning a datacentre exit or a database move where the cutover has to be boring, this is the kind of rehearsal our cloud migration engineers build into the plan, and the downstream sync into your warehouse is where our data engineering and pipelines team makes sure the migrated data still agrees with itself.

Frequently asked questions

How do I minimise downtime during an AWS DMS cutover?

Run a single full-load-plus-CDC task and let CDC catch up until CDCLatencySource and CDCLatencyTarget are both near zero and stable. Then stop application writes, confirm the target has drained the remaining change stream, run final validation, and repoint your connection string or DNS. The write-freeze window is only the last few steps, typically minutes, not the whole copy.

What breaks CDC in AWS DMS most often?

Missing primary keys and replication-slot lag. Without a primary key, PostgreSQL WAL has no before image, so DMS cannot apply updates or deletes to that table. And if the logical replication slot falls behind by more than max_slot_wal_keep_size, the source discards WAL the task still needs and the task fails, so monitor slot lag on the source directly.

Does DMS data validation guarantee the migration is correct?

No. Validation requires a primary key or unique index, skips tables with a data-masking rule on the key column, and does not cover Aurora PostgreSQL Limitless. Tables without a stable key are the ones validation cannot check, so reconcile those yourself with counts and checksums rather than trusting a green status.

Should I use limited LOB mode or full LOB mode?

Use limited LOB mode when you can prove the maximum LOB size from the data, because it is much faster, but anything over the limit is silently truncated. Use full LOB mode when sizes are unbounded and correctness matters more than speed. Inline LOB mode is a good middle ground when most LOBs are small with occasional large ones.

Do I need to run a premigration assessment?

Yes, treat it as a gate. The premigration assessment run flags unsupported data types, primary-key issues and other blockers before the task runs, so you fix them on your own schedule instead of watching a task fail inside a cutover window. It can write results to S3 for your records.