'AWS RDS Postgres: WAL_LEVEL not set to 'logical' after changing rds.logical_replication = 1

I am in the process of setting up Debezium to work with AWS Aurora Postgres (postgres version 12.6).

For Debezium to work, the WAL (Write-ahead-logging) must be set to 'logical' and not 'replica'. On AWS, this would require a DBA to set the rds.logical_replication parameter in the parameter group to be set to 1.

The above was done. The database instance was restarted.

To verify that the WAL level was changed to 'logical', I ran the following query:

show wal_level.

However, after running this query in postgres on the target database the result showed replica.

I further looked at the log events in the AWS management console and I saw these log events.

enter image description here

Would anyone have an idea why this might be? There is another environment where we were able to successfully set the rds.logical_replication to 1 and following a database restart, the WAL was set to logical. But for our main environment this is not the case. Looking at the parameter groups, between these two environments, they are the same.

Any help/ advice is appreciated. thanks.

Solution 1:[1]

Ok after contacting the AWS support I got the information that the parameter "rds.logical_replication=1" is only active on the instance that has the Writer role (open for read-write). When you set this parameter you will have to use the writer instance for logical replication. You can connect to the writer instance either via Cluster endpoint (recommended) or instance endpoint.

I was checking the read-only instance with SHOW wal_level; but in fact it was set on the read/write instance !!!


This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 bguess