Preventing an Aurora Postgres Reader from ever being promoted to a Writer in the event of a failover

The scenario you have shared is clear. You would like to prevent failover of a writer to a low-capacity reader that is used for reporting.

While it's not possible to completely prevent such a scenario, you can minimize its likelihood and implement notifications.

To reduce the likelihood, set up the following configuration:

• Writer instance: failover Tier-0 (highest priority)
• Strong reader: failover Tier-1
• Reporting instance: failover Tier-15 (lowest priority) [1]

Failover events typically resolve quickly, usually within 60 seconds and often in less than 30 seconds [2].

During a failover event, the system automatically begins recovering the failed instance in parallel. The chance is low that during this recovery period (while the original writer is being restarted), the promoted strong reader would also fail and trigger another failover to the weaker reporting instance. Important to note: during recovery, the failed instance maintains its original Tier-0 priority once it restart and comes back online.

In parallel , for monitoring, you can set up failover notifications by creating an SNS Topic and configuring it with event-categories of “failover" for the cluster. This will alert you when failover events has occurred.

1. https://aws.amazon.com/blogs/aws/additional-failover-control-for-amazon-aurora/
2. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Concepts.AuroraHighAvailability.html
3. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Events.Messages.html

To prevent a specific Aurora PostgreSQL reader instance from being promoted to a writer during a failover, you can use the promotion tier feature. Here's how to achieve what you're looking for:

1. For your existing writer and reader instances (db.r8g.xlarge), set their promotion tier to 0. This ensures they have the highest priority for promotion during a failover.

2. When you create the new, less powerful reader instance for reporting:

   • Choose the desired lower instance class (e.g., a smaller and cheaper option).
   • Set its promotion tier to a higher number, such as 15 (the maximum value).

By setting the promotion tier of your new reader to a higher number, you ensure it has the lowest priority for promotion. In the event of a failover, Aurora will always attempt to promote instances with lower tier numbers first.

This configuration allows you to:

• Maintain your high-performance writer and reader in Zones A and B for your primary workload.
• Add a cost-effective reader for reporting purposes.
• Ensure the reporting reader will not be promoted to writer during a failover scenario.

Remember to monitor the performance of your reporting reader to ensure it can handle the expected query load for your reports. If you need to adjust its capacity in the future, you can modify the instance class while maintaining its high promotion tier to prevent promotion.
Sources
Fast recovery after failover with cluster cache management for Aurora PostgreSQL - Amazon Aurora
Aurora PostgreSQL Writer and Reader Region & AZ both shows as being at ap-southeast-2b. Is this an issue that has to be corrected? | AWS re:Post

The issue with the answer is in the statement:

'Ensure the reporting reader will not be promoted to writer during a failover scenario.'

There is a possibility that the 'higher' tiered reader will still be promoted.

How do I eliminate this possibility?

Currently, there is no way to completely eliminate the possibility but it likely would take multiple failures at nearly the same time to cause the small reader to be promoted. Consider an Aurora cluster with the writer in AZ1, a "large" reader with promotion tier set to 0 in AZ2, and a "small" reader with promotion tier set to 15 in AZ3. If AZ2 were to completely fail, as long as the writer and AZ1 remain happy, no failover would occur. Of course, now the "small" reader in AZ3 would also now have to service all the read traffic that would have gone to the "large" reader in AZ2. Only in the case where after AZ2 fails, the writer fails, would the small reader be promoted. I guess the question I would ask is whether in this rare double failure scenario it is better to failover to an under-powered instance than have no writer instance at all?

As a side note, you'll probably want to consider using a "custom" endpoint for the small reader running reports. See the docs at https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Endpoints.Custom.html