Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save MelissaKaulfuss/0328397cef9f993b020c68dcdfc3497c to your computer and use it in GitHub Desktop.
Save MelissaKaulfuss/0328397cef9f993b020c68dcdfc3497c to your computer and use it in GitHub Desktop.
Conversation on viewsource slack about postgresql and AWS datamigration service.

Conversation between Melissa Kaulfuss and George Sheppard.

mel [11:36 AM] 
joined #aws

mel 
[11:37 AM] 
sup

mel 
[11:39 AM] 
Does anyone have any experience with AWS Data Migration Service? I get an error when I’m running complex SQL queries. I’ve found some posts that suggest setting `max_standby_streaming_delay` to `-1` so that the vacuum is delayed indefinitely but that’s not working for us.

[11:40] 
Can’t seem to find any answers in the docs

wolfeidau [11:45 AM] 
@mel Are you doing this for a customer?

mel 
[11:46 AM] 
We’re executing the SQL on our DR db (edited)

wolfeidau [11:46 AM] 
@mel do you have an account manager at amazon?

mel 
[11:46 AM] 
And getting an error: ERROR:  canceling statement due to conflict with recovery

wolfeidau [11:46 AM] 
I would step 1, raise a support ticket in your main account

mel 
[11:46 AM] 
We’re trying to figure it out ourselves. It’s 10% time :slightly_smiling_face:

wolfeidau [11:47 AM] 
Step two is hassle your Amazon account rep

mel 
[11:47 AM] 
Okay, hahah

[11:47] 
I don’t know if we have one, that’s the Ops peeps thing.

fuzzmonkey [11:47 AM] 
@mel are you doing this on a replica ?

mel 
[11:47 AM] 
I just love this stuff.

fuzzmonkey [11:47 AM] 
because "canceling statement due to conflict with recovery" happens when your query takes too long and postgres cancels the query because it can't guarantee rows haven't changed

mel 
[11:48 AM] 
Yeah, that’s what I’ve discovered

[11:49] 
So it’s a postgres error, we’ve tried to fix this with the suggested `max_standby_streaming_delay = -1` but no luck

[11:49] 
Says you can do that on the server but not so

[11:49] 
May have to go in a config file?

fuzzmonkey [11:50 AM] 
i'm not sure that will fix it but i'm not a postgres expert :smile:

mel 
[11:51 AM] 
hehe well, you’re probably more an expert than me

fuzzmonkey [11:56 AM] 
so my understanding is, that postgres cancels the query on the replica because rows have changed on the master so it can't guarantee the result of the query, the time is takes before cancelling is based on max_standby_streaming_delay and -1 basically lets the replica wait indefinitely.

[11:56] 
but only applies on hot standby configurations

[11:57] 
where is the replica? RDS?

mel 
[11:57 AM] 
yeah that’s what I read. I have no idea whether we have a hot standby config

[11:57] 
Yeah RDS I think

fuzzmonkey [11:59 AM] 
how did you set max_standby_streaming_delay to -1, via the parameter group assigned to that RDS instance?

fuzzmonkey [12:00 PM] 
also this:

>Note that max_standby_streaming_delay is not the same as the maximum length of time a query can run before cancellation; rather it is the maximum total time allowed to apply WAL data once it has been received from the primary server. Thus, if one query has resulted in significant delay, subsequent conflicting queries will have much less grace time until the standby server has caught up again.

fuzzmonkey [12:11 PM] 
@mel so i just tried that setting max_standby_streaming_delay = -1 on my read replica, updated parameter group assigned to both primary + replica. it was a dynamic update so didn't require restarting either instance. Seems to have fixed the cancelling query due to conflict problem so you are on the right tracks i think. Also thanks for the info!


mel 
[1:35 PM] 
Thanks @fuzzmonkey!

wolfeidau [1:59 PM] 
@mel welcome to devops :wink:

mel 
[1:59 PM] 
thank you, I do really like this stuff.


[2:04] 
TIL hot standby is the same as a recovery database?

[2:04] 
Sounds much more serious.

[2:05] 
Oh wait, no it’s not.


wolfeidau [7:50 AM] 
@mel @fuzzmonkey you need to gist this conversation or it will be lost forever!
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment