How to fix `column "xlog_position" does not exist` error when using AWS DMS for Postgres to Postgres data migration

2
votes

I'm trying to migrate and synchronize a PostgreSQL database using AWS DMS and I'm getting the following error.

Last Error Task error notification received from subtask 0, thread 0 [reptask/replicationtask.c:2673] [1020487] 
RetCode: "SQL_ERROR SqlState: 42703 NativeError: 1 
Message: ERROR: column "xlog_position" does not exist; No query has been executed with that handle; RetCode: SQL_ERROR SqlState: 42P01 NativeError: 1 
Message: ERROR: relation "pglogical.replication_set" does not exist; No query has been executed with that handle; RetCode: SQL_ERROR SqlState: 42703 NativeError: 1 Message: ERROR: column "xlog_position" does not exist; No query has been executed with that handle; 
Could not find any supported plugins available on source; Could not resolve default plugin; Could not assign a postgres plugin to use for replication; Failure in setting Postgres CDC agent control structure; Error executing command; Stream component failed at subtask 0, component st_0_JX7ONUUGB4A2AR2VQ4FMEZ7PFU ; Stream component 'st_0_JX7ONUUGB4A2AR2VQ4FMEZ7PFU' terminated [reptask/replicationtask.c:2680] [1020487] Stop Reason FATAL_ERROR Error Level FATAL

I'm using two PostgreSQL instances as both source and target. I have already tested and verified that both database instances are accessible by replication instance. Target instance user has full access to the database. Do I need to install any plugins or do additional configurations to get this migration setup working?

2
amazon-web-servicesaws-dms
I have the same problem, I followed the configuration setup as described in this link docs.aws.amazon.com/dms/latest/userguide/… – Luan Kevin Ferreira

2 Answers

3
votes

I managed to resolve the issue by following the steps mentioned at https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html.

The issue was due to the fact that I was using DMS engine v3.1.4 which required some additional configuration for the replication process to start. These instructions can be found at https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.v10

If you are experiencing this issue double check the DMS replication engine version. This can be viewed under Replication Instances in Resource Management.

3
votes

To enable logical decoding for an Amazon RDS for PostgreSQL DB instance

  1. The user account requires the rds_superuser role to enable logical replication. The user account also requires the rds_replication role to grant permissions to manage logical slots and to stream data using logical slots.

  2. Set the rds.logical_replication static parameter to 1. As part of applying this parameter, we also set the parameters wal_level, max_wal_senders, max_replication_slots, and max_connections. These parameter changes can increase WAL generation, so you should only set the rds.logical_replication parameter when you are using logical slots.

  3. Reboot the DB instance for the static rds.logical_replication parameter to take effect.

  4. Create a logical replication slot as explained in the next section. This process requires that you specify a decoding plugin. Currently we support the test_decoding output plugin that ships with PostgreSQL.

The last item can be done with the following command:

SELECT * FROM pg_create_logical_replication_slot('test_slot', 'test_decoding');

Reference: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication