Wondering why the AWS DMS task fails with all of the replication slots? We can help you.

Here at Bobcares, we assist our customers with similar AWS queries as part of our AWS Support Services.

Today, let’s see how our support techs resolve this DMS task fail issue.

AWS DMS task fails with all of the replication slots

AWS Database Migration Service(DMS) helps to migrate databases securely with no downtime. We can migrate data from one or many PostgreSQL databases using Amazon DMS. AWS DMS supports change data capture (CDC) using logical replication.

Our AWS customers often report to us that their AWS DMS task that uses PostgreSQL failing with all replication slots and receiving the following errors:

[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;

For the databases on an Amazon RDS DB instance, AWS DMS uses replication slots to perform logical replication.

From this error message, we can see that the error is ‘all replication slots are in use. So the issue is that it exceeds the maximum number of replication slots.

In the PostgreSQL configuration file Postgresql.conf, there is a parameter max_replication_slots, which specifies the number of replication slots.

How to fix DMS task fails with all replication slots

Now, let’s see how our Support Engineers fix this DMS task failure issue.

There are two options to fix this issue, either we can remove the used replication slots or increase the max_replication_slots parameter value.

How to remove the used replication Slot

1. First, we have to identify the maximum number of replication slots. For that, run the following query:

postgres=# SELECT * FROM pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn
--------------+--------------+-----------+--------+----------+--------+------+--------------+-------------
used_replication_slot | test_decoding | logical | 12141 | postgres | f | | 374 | E/FE8D9010

2. Then, remove the used replication slots by running the following query so that they can be reused by a new task.

postgres=# SELECT pg_drop_replication_slot('used_replication_slot');

Also, note that to replace the used_replication_slot with the name of the replication slot.

3. Finally, restart the AWS DMS task

How to increase max_replication_slots parameter value

To increase the max_replication_slots parameter, we have to alter the DB parameter in the custom DB parameter group that is attached to the Amazon RDS DB instance. We can also change this value by editing postgresql.conf configuration file. After the changes, restart the AWS DMS task.

Conclusion

To conclude, today we saw the methods followed by our Support Techs to fix this ‘AWS DMS task fails with all of the replication slots’ issue.