role "rdsrepladmin" cannot SET ROLE to "rds_superuser" during blue/green deployment

Question

Hi AWS community,

Our team is currently in the process of upgrading the PostgreSQL engine from version 12 to version 16 on AWS RDS. To ensure a seamless migration with zero downtime, we've opted for the AWS blue/green deployment approach.

Following the steps outlined in the [official documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments-creating.html), we meticulously conducted a dry-run. However, we encountered an obstacle during this phase. The replication status for the green environment persistently remains at "error," and our logs consistently display the following message:

```
2024-05-01 01:00:03 UTC::@:[2014]:LOG:  logical replication apply worker for subscription "" has started
2024-05-01 01:00:03 UTC::@:[2014]:ERROR:  role "rdsrepladmin" cannot SET ROLE to "rds_superuser"
2024-05-01 01:00:03 UTC::@:[2014]:CONTEXT:  processing remote data for replication origin "" during message type "INSERT" in transaction , finished at 358B/94005820
```

It's worth noting that "rdsrepladmin" is a built-in role created by the RDS system, and we have refrained from making any modifications to it. We suspect a misconfiguration in our database settings is obstructing the SET ROLE operation.

Has anyone else encountered a similar issue during a PostgreSQL upgrade on AWS RDS? Any insights or suggestions would be greatly appreciated.

Thank you for your time and assistance.

Sincerely,
Lingtong

Answer

There can be multiple scenarios in which you can observe above error, due to certain known limitations:

**Extension Limitation** :

[+] Overview of Amazon RDS Blue/Green Deployments  - PostgreSQL extension limitations for blue/green deployments - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments-overview.html#blue-green-deployments-limitations-postgres-extensions

**Logical Replication Limitation**:

[+] Overview of Amazon RDS Blue/Green Deployments  - PostgreSQL logical replication limitations for blue/green deployments - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments-overview.html#blue-green-deployments-limitations-postgres

Kindly check if you are hitting any of the above limitation.

Hope it helps and if it does, I would appreciate if answer can be accepted so that community can benefit for clarity when searching for similar enquirers in Repost, thank you ;)

role "rdsrepladmin" cannot SET ROLE to "rds_superuser" during blue/green deployment

Relevant content