From: | obi reddy <obireddy(dot)g1997(at)gmail(dot)com> |
---|---|
To: | Wasim Devale <wasimd60(at)gmail(dot)com> |
Cc: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Keith Fiske <keith(dot)fiske(at)crunchydata(dot)com>, Fernando Hevia <fhevia(at)gmail(dot)com>, Scott Ribe <scott_ribe(at)elevated-dev(dot)com>, Ron Johnson <ronljohnsonjr(at)gmail(dot)com>, pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Queries are failing on standby server |
Date: | 2024-07-26 06:24:24 |
Message-ID: | CAO7L2Zzcw5Tijv4=Yteuc2iROSscppmOjNTr3y6RqbyVemFK9Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi ,
Set the below parameters on standby node.
max_standby_archive_delay = 300s
max_standby_streaming_delay = 300s
Thanks
Obireddy.G
On Fri, 26 Jul 2024, 11:44 Wasim Devale, <wasimd60(at)gmail(dot)com> wrote:
> Thanks everyone for your inputs and solutions.
>
> On Fri, 26 Jul, 2024, 10:38 am Laurenz Albe, <laurenz(dot)albe(at)cybertec(dot)at>
> wrote:
>
>> On Thu, 2024-07-25 at 22:59 -0400, Keith Fiske wrote:
>> > On Thu, Jul 25, 2024 at 7:57 PM Fernando Hevia <fhevia(at)gmail(dot)com>
>> wrote:
>> > > 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.
>>
>> Replication (applying the WAL information) is only paused if there is a
>> conflict.
>> Even when replay is paused, the WAL is still replicated to the standby
>> and piles up there.
>>
>> > > 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.
>>
>> No, because the WAL is replayed.
>> What happens is that promoting the standby will take longer if it has to
>> replay a lot of WAL.
>>
>> > > 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.
>>
>> Yes, increasing "max_standby_streaming_delay" is the correct solution.
>> You can set it to -1 to prevent any queries on the standby from bein
>> cancelled.
>>
>> > > 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.
>>
>> That is good advice.
>>
>> > > >
>> > This is all true, but the hot_standby_feedback option is the way to get
>> around needing to
>> > worry about replication delay all together.
>>
>> No, because there are other kinds of replication conflicts. The most
>> frequent are:
>>
>> - lock conflicts
>>
>> They can occur whenever an ACCESS EXCLUSIVE lock on the primary
>> conflicts with
>> a query on the standby. The most frequent cause is VACUUM truncation
>> (which can
>> be disabled for individual tables).
>>
>> - buffer pin conflicts
>>
>> It depends on the workload if you get them, but you cannot get rid of
>> them.
>>
>> Yours,
>> Laurenz Albe
>>
>
From | Date | Subject | |
---|---|---|---|
Next Message | khan Affan | 2024-07-26 10:49:38 | Re: How to detect if a postgresql gin index is bloated |
Previous Message | Wasim Devale | 2024-07-26 06:20:29 | Migration from CentOS7 to RHEL red hat Linux |