How do I troubleshoot an AWS DMS task that fails with the "canceling statement due to statement timeout" error?

Short description

If PostgreSQL is the source, then AWS DMS reads data from the table during the full load phase. During the change data capture (CDC) phase, AWS DMS reads from the write-ahead logs (WALs) that the replication slot maintains.

If PostgreSQL is the target, then AWS DMS reads data from the source and creates CSV files on the replication instance. AWS DMS then runs a COPY command to insert those records into the target during the full load phase. During the CDC phase in transactional apply mode, AWS DMS runs the exact DML statements from the source WAL logs. In batch apply mode, AWS DMS also creates CSV files during the CDC phase. It then runs a COPY command to insert the net changes to the target.

AWS DMS uses a default timeout of 60 seconds for commands against the source or target. If the database is heavily loaded or table locks exist, AWS DMS can't complete commands within that window. The task then fails with one of the following timeout errors:

"]E: RetCode: SQL_ERROR SqlState: 57014 NativeError: 1 Message: ERROR: canceling statement due to statement timeout; Error while executing the query [1022502] (ar_odbc_stmt.c:2738)"

"]E: test_decoding_create_replication_slot(...) - Unable to create slot 'lrcyli7hfxcwkair_00016402_8917165c_29f0_4070_97dd_26e78336e01b' (on execute(...) phase) [1022506] (postgres_test_decoding.c:392))"

Resolution

Identify the command that caused the timeout

Review the AWS DMS task log and the table statistics section of the task. Look for the command that failed to complete within the timeout period.

You can also find this information in the PostgreSQL error log. Set the log_min_error_statement parameter to ERROR or a lower severity to capture it.

Example error message from the PostgreSQL error log:

ERROR: canceling statement due to statement timeout STATEMENT: <The statement executed>"

After you identify the failed command, check the associated table for the following issues.

Check for table locks

Run the following query on the source or target, based on where the error appears:

SELECT blocked_locks.pid AS blocked_pid,
 blocked_activity.usename AS blocked_user,
 blocking_locks.pid AS blocking_pid,
         blocking_activity.usename AS blocking_user, 
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks 
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype 
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid 
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;

If you find blocked PIDs, then run the following command to stop or terminate the blocked PID:

SELECT pg_terminate_backend(blocking_pid);

Check for dead rows

Dead rows or "tuples" can increase SELECT time. Run the following command to check for large numbers of dead rows in the source tables:

select * from pg_stat_user_tables where relname= 'table_name';

Check for missing primary keys or unique indexes

Verify that the failed target table has a primary key or unique index. Without one, PostgreSQL performs a full table scan for every UPDATE statement. This scan can exceed the timeout window.

Increase the timeout value

AWS DMS uses the executeTimeout extra connection attribute (ECA) on both source and target endpoints. The default value for executeTimeout is 60 seconds, so AWS DMS times out if a query takes longer than 60 seconds to run.

If the error appears in Source_Unload or Source_Capture, then set the timeout value for executeTimeout in the source. If the error appears in Target_Load or Target_Apply, set the timeout value for executeTimeout in the target.

Complete the following steps:

1. Open the AWS DMS console.

2. Choose Endpoints from the navigation pane.

3. Choose the PostgreSQL endpoint.

4. Choose Actions, and select Modify.

5. Expand the Endpoint-specific settings section.

6. In the field for Extra connection attributes, enter this value:

   executeTimeout=3600;

7. Choose Save.

8. From the Endpoints pane, choose the name of your PostgreSQL endpoint.

9. From the Connections section, the Status of the endpoint changes from Testing to Successful.

You can also increase the statement_timeout parameter in milliseconds on the PostgreSQL DB instance. The default value is 0 turns off timeouts for queries. You can also increase the lock_timeout parameter. The default value is 0 turns off timeouts for locks.

Troubleshoot slot creation issues

If the timeout occurred when you created the replication slot in the PostgreSQL database, then you see log entries similar to the following:

"]E: test_decoding_create_replication_slot(...) - Unable to create slot 'lrcyli7hfxcwkair_00016402_8917165c_29f0_4070_97dd_26e78336e01b' (on execute(...) phase) [1022506] (postgres_test_decoding.c:392)"

Set the TransactionConsistencyTimeout parameter in the Task settings section to increase this timeout. The default value is 600 seconds.

PostgreSQL can't create the replication slot if active locks exist on the database user tables. Run the following command to check for locks:

select * from pg_locks;

Then, run the following command to test whether you can manually create the replication slot:

select  xlog_position FROM pg_create_logical_replication_slot('<Slot name as per
    the task log>', 'test_decoding');

If the command still fails, then work with a PostgreSQL DBA to identify the bottleneck and adjust your database configuration. If the command succeeds, then delete the slot that you created as a test:

select pg_drop_replication_slot('<slot name>');

Finally, restart your migration task.

Related information

PostgreSQL documentation for client connection defaults on the Postgre website

Endpoint settings and Extra Connection Attributes (ECAs) when using PostgreSQL as a target for AWS DMS

Endpoint settings and Extra Connection Attributes (ECAs) when using PostgreSQL as a DMS source

Using a PostgreSQL database as an AWS DMS source