How to solve ERROR: could not access file "pglogical": No such file or directory

Question

Error:

2019-06-17T05:00:11 [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 58P01 NativeError: 1 Message: ERROR: could not access file "pglogical": No such file or directory;, Error while executing the query [1022502] (ar_odbc_stmt.c:2546)
2019-06-17T05:00:11 [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42P01 NativeError: 1 Message: ERROR: relation "pglogical.replication_set" does not exist;, No query has been executed with that handle [1022502] (ar_odbc_stmt.c:3722)

We receive this error every 3 months or so. Restarting the task solves the problem but resume doesn't work.

Replication slot exists but seems like it's in inactive state:

   postgres=> select * from pg_replication_slots ;
                               slot_name                            |    plugin     | slot_type | datoid |   database    | active | active_pid | xmin | catalog_xmin | restart_lsn
    ----------------------------------------------------------------+---------------+-----------+--------+---------------+--------+------------+------+--------------+--------------
     juvmrynv47ajpwrc_00016389_f28d6802_db75_43d6_8058_315783e9b1b2 | test_decoding | logical   |  16389 | postgres | f      |            |      |    148271120 | 8DF/B292FB48
    (1 row)

Answer 1

In our case, active transactions were causing this failure. So, while DMS task is resumed after a failure or manually it will try to create a dummy replication slot which requires no active transactions on the source database, by default DMS will wait of 10 minutes for active transactions to complete then Task will fail with below error.

2019-09-02T06:48:10 [SOURCE_CAPTURE  ]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:2581)

Solution -

• Increase task settings TransactionConsistencyTimeout from 600 seconds to 1800 seconds.
• Check if your replication instance is consuming too much memory.

• Enable debug mode in your task settings & get more details about failure -

  "LogComponents": [
    {
      "Id": "SOURCE_UNLOAD",
      "Severity": "LOGGER_SEVERITY_DETAILED_DEBUG"
    },
    {
      "Id": "SOURCE_CAPTURE",
      "Severity": "LOGGER_SEVERITY_DETAILED_DEBUG"
    },
    {
      "Id": "TARGET_LOAD",
      "Severity": "LOGGER_SEVERITY_DETAILED_DEBUG"
    },
    {
      "Id": "TARGET_APPLY",
      "Severity": "LOGGER_SEVERITY_DETAILED_DEBUG"
    },
    {
      "Id": "TASK_MANAGER",
      "Severity": "LOGGER_SEVERITY_DETAILED_DEBUG"
    }
  ]