On Sat, Jun 8, 2024 at 2:51 PM sud <suds1434@xxxxxxxxx> wrote:
Thank You so much Laurenz and Yudhi.Yes its RDS and as you mentioned there does exist a space limitation of ~64TB but as Laurenz mentioned the only time the second standby may crash would be probably because of the storage space saturation and thus we need to have appropriate monitoring in place to find this and get alerted beforehand. And also a monitoring to see how much WAL gets generated per hour/day to get an idea of the usage. I am not sure how to do it , but will check on this.
Not exactly related but just for our information, While going through the "aurora postgres" database docs in regards to similar concepts which are getting discussed here, I am finding some interesting stuff.
Cancel the conflicting query on the reader node if the conflict lasts longer than max_standby_streaming_delay (maximum 30 seconds). This is different from Amazon RDS or self-managed PostgreSQL. With Amazon RDS or self-managed PostgreSQL, the instance has its own physical copy of the database, and you’re able to set the parameter max_standby_streaming_delay as high as you want to prevent query cancellation.
If the conflicting query can’t cancel in time, or if multiple long-running queries are causing the replication lag to go beyond 60 seconds, Aurora restarts the reader node to ensure it’s not lagging far behind the primary node.
If the conflicting query can’t cancel in time, or if multiple long-running queries are causing the replication lag to go beyond 60 seconds, Aurora restarts the reader node to ensure it’s not lagging far behind the primary node.
So if i get it correct it means, even if
hot_standby_feedback is set to OFF, the constraints of max_standby_streaming_delay (30 seconds) and the 60-second replication lag limit applies. And thus Aurora may cancel long-running queries or restart reader nodes to maintain synchronization even if it just runs for >60seconds. So it's really odd but does that mean , by no way you can guarantee a query to run >60 seconds on read replica in aurora postgres?