Why is my AWS DMS task that uses PostgreSQL as the source failing with all of the replication slots in use?
"I have an AWS Database Migration Service (AWS DMS) task that uses an Amazon Relational Database Service (Amazon RDS) DB instance that is running PostgreSQL as the source. My task is failing, all of the replication slots are in use, and I received an error message. Why is my task failing, and how do I resolve these errors?"
"I have an AWS Database Migration Service (AWS DMS) task that uses an Amazon Relational Database Service (Amazon RDS) DB instance that is running PostgreSQL as the source. My task is failing, all of the replication slots are in use, and I received an error message. Why is my task failing, and how do I resolve these errors?Short descriptionFor Amazon RDS for PostgreSQL instances, AWS DMS uses native replication slots to perform the logical replication for change data capture (CDC).The number of replication slots that a PostgreSQL instance has is controlled by the max_replication_slots parameter. By default, there are five replication slots for RDS PostgreSQL instances. If you exceed the maximum number of replication slots, then you see log entries like these:Messages[SOURCE_CAPTURE ]E: Failed (retcode -1) to execute statement [1022502] (ar_odbc_stmt.c:2579)[SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 53400 NativeError: 1 Message: ERROR: all replication slots are in use;To resolve these errors, remove the used replication slots or increase the value of the max_replication_slots parameter.ResolutionRemove used replication slotsIf you are running multiple AWS DMS tasks or you have old tasks running on same DB instance, then remove used replication slots. These replication slots continue to occupy space, so by removing then you make the slots available for new tasks.First, identify the maximum number of replication slots. Then, remove or "drop" the used replication slots.Run this query to check the maximum number of replication slots:SELECT * FROM pg_replication_slots;     slot_name    |    plugin     | slot_type | datoid | database | active |  xmin  | catalog_xmin | restart_lsn -----------------+---------------+-----------+--------+----------+--------+--------+--------------+-------------old_and_used_slot | test_decoding | logical   |  12052 | postgres | f      |        |          684 | 0/16A4408Run this query to drop a used replication slot:SELECT pg_drop_replication_slot('old_and_used_slot');Note: Replace old_and_used_slot with the name of your replication slot.Increase the value of the max_replication_slots parameterModify the DB parameter in the custom DB parameter groups that is attached to the Amazon RDS DB instance. Then, increase the value of the max_replication_slots parameter. This is a static parameter, so be sure to reboot the DB instance after changing the parameter value.After you remove the used replication slots or increase the value of the max_replication_slots parameter, restart the task.Related informationPostgreSQL on Amazon RDSUsing a PostgreSQL database as an AWS DMS sourcePostgreSQL documentation for Logical decoding examplesFollow"