AWS DMS Error - Unable to use plugins to establish logical replication on source PostgreSQL instance

Question

I'm getting this error trying to replicate a postgre database (not RDS) to another postgre database (also not RDS). I get this connection error but the endpoints (source and target) are tested successfully. Any ideas?

Error: Last Error Unable to use plugins to establish logical replication on source PostgreSQL instance. Follow all prerequisites for 'PostgreSQL as a source in DMS' from https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html Task error notification received from subtask 0, thread 0 [reptask/replicationtask.c:2880] [1020490] Remaining 1 error messages are truncated. Check the task log to find details Stop Reason FATAL_ERROR Error Level FATAL

Unsay

Please provide enough code so others can better understand or reproduce the problem. – Foxhound

Answer 1

I used DMS to reduce over-provisioned RDS storage size. Set following values in db parameter group in both source and destination endpoints and restart. Maybe this will help for non-RDS endpoints if you add the same in Postgres configuration.

logical_replication = 1
max_wal_senders = 10
max_replication_slots = 10
wal_sender_timeout = 0
max_worker_processes = 8
max_logical_replication_workers = 8
max_parallel_workers = 8
max_worker_processes = 8

Bonneau

Answer 2

You need at least to set logical_replication = 1 in your source database configuration.
And then set the max_replication_slots = N, N being higher or equal to the number of replication processes you plan to run.

Repeal

Answer 3

I have this problem with setting up the ongoing replication at the AWS DMS migration task.

I change the source and target endpoint Parameter groups setting below:

session_replication_role = replica
rds.logical_replication = 1
wal_sender_timeout = 0

and keep the remaining setting as the default

max_replication_slots = 20
max_worker_processes = GREATEST(${DBInstanceVCPU*2},8)
max_logical_replication_workers = null
autovacuum_max_workers =  GREATEST({DBInstanceClassMemory/64371566592},3)
max_parallel_workers = GREATEST(${DBInstanceVCPU/2},8)      
max_connections = LEAST({DBInstanceClassMemory/9531392},5000)

Tactless

Answer 4

You need to follow all the steps in the guide that is displayed in the error. You need to update pg_hba.conf to allow the DMS instance to access. E.g. if the private IP on the DMS instance is on the 10.6.x.x range:

host    your_db         dms             10.6.0.0/16             md5
host    replication     all             10.6.0.0/16             md5

Then you'll need to create a dms user and role with superuser privileges.

Then follow the guide to update postgresql.conf with the wal settings, e.g. wal_level = logical and so on.

Whyte

Answer 5

I was able to resolve this by granting the source database user the right access roles on the pglogical schema

grant usage on schema pglogical to <replication user>;
grant select on all tables in schema pglogical to <replication user>;

Immethodical