3

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?

Improve this question
1

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

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

Improve this answer

1 Comment

Thanks for taking your time to write this answer. But I'm not using a Amazon RDS instance as the source. My source is a Pg server running on a EC2 instance.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.