I have two RDS servers, one main and the other a replica of the main one. Let's call them server #1 and server #2.
Sometimes the roles switch between the servers so server #2 is the main DB (write role) and #1 is the replica (read role). Probably because of an upgrade or some other event, don't see anything in the logs.
The switch is problematic because we have two kinds of apps: read/write apps that connect to the writer cluster endpoint and read-only apps that connect to the replica. The switch of the DNS for the writer instance is handled by AWS through a CNAME switch, but the read endpoint never gets a DNS switch so after the change of roles all my read-only apps connect to the new main DB.
Is there a way to handle this automatically? I could create a manual CNAME record and switch it when I see this has happened, but this is neither reliable (I'm not notified when the replica gets promoted) neither automatic.