Hello, I am migrating from an RDS instance to an RDS instance (both Postgres based) and I am facing an issue with the table errors, namely, only a handful of tables (8) out of the entire database (~280) fail on primary key constraint check (which is odd). What's interesting is that the list of the failing tables is always the same (so it's not a random/race condition issue). I am doing a full load with CDC (tried both truncate and delete on create ; I have also tried dropping all tables manually, etc.). At this stage I am more-or-less clueless what to do.
I saw an option to set CreatePkAfterFullLoad on the CLI, but not on the Console itself. I wonder why is this option not on the Web, as it would solve my issue.
The error log from DMS:
]E: RetCode: SQL_ERROR SqlState: 23505 NativeError: 1 Message: ERROR: duplicate key value violates unique constraint "audit_pkey"; Error while executing the query [1022510] (ar_odbc_stmt.c:2874)
and
2024-01-30 18:52:18 UTC:10.1.3.122(38114):postgres@data_warehouse_prod:[22145]:DETAIL: Key (audit_id)=() already exists.`
from RDS itself. No value?
Also, unrelated, I have tried to use the serverless option, the evaluator which provisions the DMS node is very ... unopinionated which node it selects. It seems to me to always select the biggest node regardless of the fact that both the source (4 core, 16 GB RAM) and target (2 core, 2 GB of RAM) are too "small", database is ~120 GB.
Regards,
Peter.
AWS OFFICIALUpdated 8 months ago
Yes of course, I would like to know more. I spent entire day looking into it. If I knew there was a major issue with it, I wouldn't have used it. I have looked into the log and there is nothing suspicious but the PK violations.