Resolution for Fatal error when using DMS for on-going replication from RDS Postgres to S3
I attempted to configure my RDS postgres instance for CDC using Setting up an Amazon RDS PostgreSQL DB instance as a source I configured the DMS source endpoint to use a specific slotName. First using a slot that already existed in the DB and then using a slot I created using pg_create_logical_replication_slot. After successfully testing the endpoint to ensure connectivity, I started the replication task with was to load existing data followed by on-going changes. Both times, the replication failed with a fatal error such as the following and no data (even the existing data that should have loaded) gets replicated.
Last Error Stream Component Fatal error. Task error notification received from subtask 0, thread 0 [reptask/replicationtask.c:2822]  Error executing source loop; Stream component failed at subtask 0, component st_0_KVPUHIZBICJJRNGCC32HP5EFGGLIZLRF2YSRJ6Y ; Stream component 'st_0_KVPUHIZBICJJRNGCC32HP5EFGGLIZLRF2YSRJ6Y' terminated [reptask/replicationtask.c:2829]  Stop Reason FATAL_ERROR Error Level FATAL
What is the configuration I'm missing to enable CDC from RDS Postgres v11.8? I'm configuring DMS to use the RDS master user. A migration existing data only DMS task succeeds on this instance (if the slotName configuration is removed).
The relevant CloudWatch log entries appear to be these
2021-02-03T22:03:05 [SOURCE_CAPTURE ]I: Slot has plugin 'test_decoding' (postgres_test_decoding.c:233) 2021-02-03T22:03:05 [SOURCE_CAPTURE ]I: Initial positioning requested is 'now' (postgres_endpoint_capture.c:511) 2021-02-03T22:03:05 [SOURCE_CAPTURE ]E: When working with Configured Slotname, user must specify LSN  (postgres_endpoint_capture.c:517) 2021-02-03T22:03:05 [TASK_MANAGER ]I: Task - W6AUC5OI3DNFMFUQ6ZDEYYNZ3NBSABQK3HFD2WQ is in ERROR state, updating starting status to AR_NOT_APPLICABLE (repository.c:5101) 2021-02-03T22:03:05 [TASK_MANAGER ]E: Task error notification received from subtask 0, thread 0  (replicationtask.c:2822) 2021-02-03T22:03:05 [TASK_MANAGER ]E: Error executing source loop; Stream component failed at subtask 0, component st_0_KVPUHIZBICJJRNGCC32HP5EFGGLIZLRF2YSRJ6Y ; Stream component 'st_0_KVPUHIZBICJJRNGCC32HP5EFGGLIZLRF2YSRJ6Y' terminated  (replicationtask.c:2829) 2021-02-03T22:03:05 [TASK_MANAGER ]E: Task 'W6AUC5OI3DNFMFUQ6ZDEYYNZ3NBSABQK3HFD2WQ' encountered a fatal error (repository.c:5194) 2021-02-03T22:03:05 [SORTER ]I: Final saved task state. Stream position , Source id 0, next Target id 1, confirmed Target id 0, last source timestamp 0 (sorter.c:803)
Other items in the log appear to be informational to track progress.
Please check if below information can help,
rds.logical_replication is enable.
If using Existing Slot we will need to configure below for a Task. -->CLI -
aws dms create-replication-task --replication-task-identifier <<task_identifier>> --source-endpoint-arn <<SRC_ARN>> --target-endpoint-arn <<Target_ARN>> --replication-instance-arn <<RepInstance_ARN>> --migration-type cdc --table-mappings <<path_to_mapping_json>> --cdc-start-position "0/57D86A50" <<LSN need to specified as Start Position>> --replication-task-settings <<Task_Setting_File>>
-->Add Slot name in source end points connection attribute within AWS DMS
If don't wanna use existing replication slot, AWS DMS will automatically create one based on task identified, we don't need to create.
select * from pg_replication_slots;
Additional details for reference https://aws.amazon.com/premiumsupport/knowledge-center/dms-postgresql-fail-slots-in-use/ https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.RDSPostgreSQL
does AWS DMS support partition tables from postgresasked 5 months ago
RDS Postgresql migrate to Aurora Postgresql. Not showing "Aurora read replica" & "Migrate snapshot" options.Accepted Answerasked 4 months ago
FATAL: password authentication failed for user "postgresasked 4 years ago
RDS Database Instance does not show up in dropdown when creating RDS Proxyasked 3 months ago
LZ4 compression on RDS Postgres 14Accepted Answerasked 3 months ago
"Create Aurora Read Replica" option not available to migrate from RDS Postgres 11.12 to Aurora 11.12.asked 2 months ago
Aurora Postgres 13 with IAM Auth - Unable to establish logical replication connectionasked 5 months ago
Enable RDS Encryption with minimal downtime after creationAccepted Answerasked a year ago
Resolution for Fatal error when using DMS for on-going replication from RDS Postgres to S3Accepted Answerasked a year ago
Unable to use plugins to establish logical replication on source PostgreSQLasked 2 years ago