8

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)
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
vivekyad4v
  • 13,321
  • 4
  • 55
  • 63
  • we are facing same issues. you got any solution? @vivekyad4v – prakharjain Dec 12 '19 at 12:45
  • we are getting these two: `RetCode: SQL_ERROR SqlState: XX000 NativeError: 1 Message: ERROR: pglogical is not in shared_preload_libraries; Error while executing the query [1022502] (ar_odbc_stmt.c:2581)` `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:3752)` – prakharjain Dec 12 '19 at 12:53
  • 1
    change your parameter group to support logical replication i.e set "rds.logical_replication = 1" . – vivekyad4v Dec 12 '19 at 13:03
  • Check the answer. I have added my findings. – vivekyad4v Dec 12 '19 at 13:30
  • 1
    I have already set rds.logical_replication=1, But I am now adding pglogical to shared_preload_libraries as evident from the error message. – prakharjain Dec 13 '19 at 16:09
  • And given the previously described settings, consider this `CREATE EXTENSION pglogical;` on the DB being replicated. You should notice the _pglogical_ schema. The [related details](https://dbagurujee.blogspot.com/2020/05/pglogical-logical-replication-on.html) – bvj Apr 08 '21 at 08:52

3 Answers3

3

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"
  }
]
vivekyad4v
  • 13,321
  • 4
  • 55
  • 63
2

I'm facing the same error that OP describe. For me the environment that I'm running is : - Using DMS version 3.1.4 - Using target and source as Postgresql Aurora 10.7

Later on while checking the document on setting up this one on https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html, found out that actually for Postgres version > 10, I must use DMS versions 3.3.0 onward.

Change the version actually make it work, so make sure you check document by Amazon before come here :)

Sơn Đỗ Đình Thy
  • 125
  • 1
  • 8
0

I was able to find a solution for this issue by updating the rds.logical_replication setting. The steps to do this are here - Replication Slots error while replicating data from RDS Postgres(read replica) to Redshift using AWS DMS

ctay
  • 379
  • 4
  • 7