Why is my AWS DMS task that uses PostgreSQL as the source failing with all of the replication slots in use?

Last updated: 2019-10-01

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 Description

For RDS DB instances that run PostgreSQL, AWS DMS uses native replication slots to perform the logical replication for change data capture (CDC), i.e. ongoing continuous replication.

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 DB instances that run PostgreSQL. If you exceed the maximum number of replication slots, you see log entries similar to the following:

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.

Resolution

Remove used replication slots

If you are running multiple AWS DMS tasks or you have old tasks running on same DB instance, remove the used replication slots that continue to occupy space so that the slots are available for new tasks. First, identify the maximum number of replication slots. Then, remove or "drop" the used replication slots so that they can be reused by a new task.

Run the following 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/16A4408

Run the following 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 parameter

Modify the DB parameter in the custom DB parameter group that is attached to the Amazon RDS DB instance to increase the value of the max_replication_slots parameter. This is a static parameter, so you must 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 AWS DMS task.


Did this article help you?

Anything we could improve?


Need more help?