CODEWITHSUNDEEP
postgresqlamazon-web-services
rmacqueen
rmacqueen

Reputation: 1071

How to modify the max_standby_archive_delay parameter on AWS RDS instance?

I need to modify both the max_standby_streaming_delay and max_standby_archive_delay parameters on my postgres database to allow for longer-running read queries. I'm trying to do this in AWS RDS but it won't allow me to, and keeps saying that those parameters are not modifiable:

 aws rds modify-db-parameter-group \                                                                                                 254 ↵
    --db-parameter-group-name myparametergroup \
    --parameters "ParameterName=max_standby_archive_delay,ParameterValue=-1,ApplyMethod=immediate"

An error occurred (InvalidParameterValue) when calling the ModifyDBParameterGroup operation: The parameter max_standby_archive_delay cannot be modified.

Any way to get around that?

Upvotes: 0

Views: 4186

Answers (2)

Roger Wang
Roger Wang

Reputation: 11

Ability to modify max_standby_streaming_delay and max_standby_archive_delay depends on what kind of parameter group family you are using.

aurora-postgresql10: neither are modifiable.

aurora-postgresql11 or later: max_standby_archive_delay no longer exist. max_standby_streaming_delay are modifiable with max of 30000 ms.

all postgresql: both are modifiable.

If you are using aurora-postgresql and set max_standby_streaming_delay to maximum of 30000 ms doesn't solve your issue for long-running read queries, you can take a look at this blog post https://aws.amazon.com/blogs/database/manage-long-running-read-queries-on-amazon-aurora-postgresql-compatible-edition/

Upvotes: 0

Alex Kovshovik
Alex Kovshovik

Reputation: 4215

You can't modify default parameter groups. Click "Create parameter group", select the Postgres server version - this will copy all values from default param group.

Modify the values in the copied group, then modify your DB instance - set this new parameter group.

In my case this only took 5 min to modify my read replica instance.

Upvotes: 1

Related Questions