Why does my AWS DMS task that uses PostgreSQL as the source fail when all replication slots are in use?

Short description

To resolve these errors, remove inactive and unnecessary replication slots, or increase the value of the max_replication_slots parameter.

Resolution

AWS DMS uses native replication slots to perform the logical replication for change data capture (CDC) on Amazon RDS for PostgreSQL instances.

The max_replication_slots parameter controls the number of replication slots available. If you exceed this value, then the task fails with errors similar to the following:

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

Remove unnecessary inactive replication slots

Replication slots from old or stopped DMS tasks remain on the DB instance. If you run multiple tasks on the same DB instance, then remove unnecessary slots that are no longer active.

Complete the following steps:

1. Run the following query to check the current value of replication slots:

   SHOW max_replication_slots;

   Example output of maximum number of replication slots available on the instance:

   max_replication_slots
   -----------------------
    20
   (1 row)
   

   Note: The default value of max_replication_slots varies by engine version.

2. Run the following query to identify inactive replication slots, and then review the active column:

   SELECT * FROM pg_replication_slots;
        slot_name    |    plugin     | slot_type | datoid | database | active |  xmin  | catalog_xmin | restart_lsn
    -----------------+---------------+-----------+--------+----------+--------+--------+--------------+-------------
   old_and_unused_slot | test_decoding | logical   |  12052 | postgres | f      |        |          684 | 0/16A4408
   

   Note: active: t (true) means that the slot is in use, and active: f (false) means that the slot isn't in use. In the preceding and following queries, replace old_and_unused_slot with the name of your inactive replication slot.

3. Run the following query to remove the inactive replication slot:

   SELECT pg_drop_replication_slot('old_and_unused_slot');

4. Restart the task.

Increase the value of the max_replication_slots parameter

Modify the DB parameter group attached to your RDS DB instance. Filter for max_replication_slots, and then increase the value.

Then, reboot the DB instance and restart the task.

Note: Because the max_replication_slots parameter is static, you must reboot the DB instance when you make changes to this parameter.

Related information

Activating CDC using logical replication

Amazon RDS for PostgreSQL

Using a PostgreSQL database as an AWS DMS source

Logical decoding examples on the PostgreSQL website