Re: Confused about max_standby_streaming_delay

On Wed, Sep 6, 2017 at 9:43 AM, Robert Inder <robert(at)interactive(dot)co(dot)uk>
wrote:

...

> And I've read that the answer to this is to set
> max_standby_streaming_delay in postgresql94.conf.
> So I've set it to "600s" -- ten minutes.
>
> I thought this would mean that when there was a conflict with an update
> from the live server, Postgres would give the dump 10 minutes "grace" in
> which to finish before killing it.
>
> Ten minutes may or may not be enough. But in a case where it isn't
> enough, and the dump is abandonned, I would expect to see something like
>
> the dump of database_a finishing at 5 minutes past the hour,
> the dump of database_b
> starting after the dump of database_a,
> having a conflict,
> being given 10 minutes to complete, and then
> being abandonned
> the dump of database_c starting after the dump of database_b and
> finishing (say) 3 minutes later
>
> So the dump of database_c should finish at around 18 minutes past the hour.
>
> BUT that is not what I am seeing.
> On occasions where the dump of database_b is being abandonned, the
> successful dump of
> database_c is timestamped less than 10 minutes after the dump of database_a
>
> Which does not fit with the dump of database_b being given 10 minutes in
> which to finish
>
> Have I misunderstood something? Or is Postgres not actually configured
> the way I think it is?
>

The standby will wait for ten minutes to obtain the lock it wishes to
obtain. In 9.4, if something other than dump of database b was already
blocking it for 8 minutes before the dump starts, then the dump of database
b will only have 2 minutes, not 10, before it gets cancelled. So, are
there any long running jobs in database b other than the pg_dump?

Cheers,

Jeff