- Newest
- Most votes
- Most comments
I was able to track down the cause of this issue - it has to do with some configuration of the tables in my source database. The table I'm trying to replicate has REPLICA IDENTITY set to FULL. It seems Postgres cannot add a table to the replication slot when this is the case. The replication identity must be set to DEFAULT.
Despite Postgres raising an error for this, the error is not surfaced in a way that an be identified by searching logs for "]E:" as suggested.
This is the error I received
2025-08-01T16:10:28 [SOURCE_CAPTURE ]D: RetCode: SQL_ERROR SqlState: 22023 NativeError: 1 Message: ERROR: table clinician cannot be added to replication set <replicasetname> DETAIL: table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEs; Error while executing the query [1022502] (ar_odbc_stmt.c:2947)
Please confirm if the table has a primary key.
Please note the below limitation [1]:
A captured table must have a primary key. If a table does not have a primary key, AWS DMS ignores DELETE and UPDATE record operations for that table. As a workaround, see Enabling change data capture (CDC) using logical replication.
We don't recommend migrating without a Primary Key/Unique Index, otherwise additional limitations apply such as "NO" Batch apply capability, Full LOB capability, Data Validation and inability to replicate to Redshift target efficiently.
Does the task logs have any errors or warnings. You can search Cloudwatch logs for "]E:" (for errors) and "]W:" (for warnings). Can you share the table DDL so that I can try to reproduce the issue?
[1] Using a PostgreSQL database as an AWS DMS source - Limitations on using a PostgreSQL database as a DMS source - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.Limitations
When AWS DMS is not capturing updates during CDC from PostgreSQL to Redshift, there are several potential causes to investigate:
- Change Streams/WAL Configuration: For PostgreSQL as a source, DMS relies on the Write-Ahead Log (WAL) to capture changes. Make sure that:
- The PostgreSQL source has logical replication enabled (wal_level set to 'logical')
- The necessary DMS artifacts are created in your PostgreSQL database, including:
- trigger awsdms_intercept_ddl
- function awsdms_intercept_ddl
- table awsdms_ddl_audit
-
Replication Slot Issues: Check if the replication slot used by DMS is active and functioning properly. If OldestReplicationSlotLag or TransactionLogsDiskUsage are growing continuously, it might indicate that DMS isn't properly consuming the logs.
-
Source Endpoint Configuration: For PostgreSQL sources with low transaction volume, add the HeartbeatEnable=true parameter to your source endpoint's extra connection attributes. Without this option, if the source database has few transactions, the WAL can consume disk space quickly without DMS properly processing changes.
-
Primary Keys: Ensure all tables being replicated have primary keys. DMS uses these to efficiently identify and apply updates.
-
Task Settings: Review your CDC-related task settings. Your current configuration looks reasonable, but you might want to adjust the ChangeProcessingTuning parameters if updates are being delayed or missed.
-
Permissions: Although you've tried using admin users, double-check that the PostgreSQL user has sufficient permissions to access the replication slot and read the WAL logs.
-
Table Mapping Rules: Your current mapping rules look straightforward, but ensure they're not inadvertently filtering out update operations.
-
CDC Start Position: If you're restarting the task, make sure it's resuming CDC from the correct position.
Try enabling more detailed logging for the SOURCE_CAPTURE and TARGET_APPLY components by setting their severity to LOGGER_SEVERITY_DETAILED or LOGGER_SEVERITY_DEBUG to get more insights into what's happening during the CDC phase.
Sources
Issue with AWS DMS Full Load + CDC Task Not Reflecting Newly Created Tables in Target DB | AWS re:Post
DMS documentdb cdc loads full, but it doesn't replicate/load deltas | AWS re:Post
DMS between two RDS instances, grown OldestReplicationSlotLag | AWS re:Post
Relevant content
- asked 3 years ago
- asked 2 years ago
- asked 6 hours ago
Thank you for the advice. I can confirm that the tables do have a primary key. If it's helpful context, this is currently running in a staging environment where there are a relatively small number of transactions.
I already have a DMS task which replicates from the same Postgres source to a different Postgres RDS instance and that task has been running smoothly for months. I believe the source database is configured properly to work with DMS (all tables, functions, triggers are set up, logical replication is enabled, etc)
I searched the CloudWatch logs for "]E:" or "]W:" and nothing was returned.
While troubleshooting I've limited the job to replicating a single table: clinician. I've updated my original question to include the structure of that table and the current table mapping.