Issue with AWS DMS continuous replication

Question

I am trying to create a DMS task to migrate data from RDS Postgres instance to S3 bucket. The full load works all fine, but the continuous replication is failing. Its giving this error:

"logical decoding requires wal_level >= logical"

When I checked the system settings from pg_settings, its showing that the setting "wal_level" has value "replica". So I tried to change the setting wal_level, but I am not able to find this setting in the Parameter Group in RDS. My RDS instance is using 9.6 version of parameters.

When I tried "ALTER SYSTEM SET wal_level TO 'logical'", it fails saying that "must be superuser to execute ALTER SYSTEM command", even though the user is under rds_superuser role.

Please suggest.

Melanoma

Answer 1

The Parameter name in Parameter Group is "rds.logical_replication" which needs to be changed to 1. The default value was 0.

This property changed "wal_level" value to "Logical".

Melanoma

I needed to restart my RDS instance after updating this value in my parameter group in order to clear this error. – Huggermugger

@Huggermugger life-saver – Abut

I'm surprised this is not mentioned in the documentation, thanks folks! – Alkahest

Answer 2

You can use an AWS-managed PostgreSQL DB instance as a source for AWS DMS. You can perform both full-load tasks and change data capture (CDC) tasks using an AWS-managed PostgreSQL source.

In order to do that you have to:

Set the in your DB CLUSTER parameter group, rds.logical_replication parameter to 1. This static parameter requires a reboot of the DB instance to take effect.

As part of applying this parameter, AWS DMS sets the wal_level, max_wal_senders, max_replication_slots, and max_connections parameters. These parameter changes can increase write ahead log (WAL) generation, so only set rds.logical_replication when you use logical replication slots.

AWS docs

Stopping