Re: Queries are failing on standby server

Hi Wasim,

I think you might have misinterpreted the explanation given to you. The
cancellation of the query on the standby server isn't related to the load
on the primary server. It happens that when you run queries on a hot
standby, the replication is temporarily paused in order to not modify data
the running queries on the standby server need. Once the queries end,
replication resumes.
The problem of this behaviour is that the standby server starts to fall
behind in relation to the master, a scenario which presents a risky
condition: if the master happens to fail while the replica is delayed you
end up with data loss.
To avoid having a standby server lagging too far behind Postgres will
cancel long running queries on the replica. The
parameter max_standby_streaming_delay defines the maximum replication delay
the standby will tolerate. Default is 30 seconds. Increase the value to
allow for longer running queries on the standby server bearing in mind that
you could end up with data loss if the master fails at the wrong moment.

A working alternative is to have one standby server exclusively for
replication purposes and another standby for reporting/read-only queries
where you can increase the max_standby_streaming_delay to accommodate your
long running queries. Of course, this will require additional computing and
storage resources.

Cheers,
Fernando.

El jue, 25 jul 2024 a la(s) 3:41 p.m., Wasim Devale (wasimd60(at)gmail(dot)com)
escribió:

> Our company's production in the evening only and has heavy loading and
> unloading of data. So I can suggest them not to run the long running
> queries in bulk for analysis at that peak time and will ask them to use
> them off peak hours. Correct? Any suggestions from you.
>
> On Fri, 26 Jul, 2024, 12:05 am Scott Ribe, <scott_ribe(at)elevated-dev(dot)com>
> wrote:
>
>> > On Jul 25, 2024, at 11:17 AM, Wasim Devale <wasimd60(at)gmail(dot)com> wrote:
>> >
>> > So how to tackle the above error.
>>
>> ???
>>
>> The two options to mitigate this, and their side effects, were explained
>> to you. What more do you want?
>
>