From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Keith Fiske <keith.fiske@crunchydata.com>
To: Fernando Hevia <fhevia@gmail.com>
Cc: Wasim Devale <wasimd60@gmail.com>
Cc: Scott Ribe <scott_ribe@elevated-dev.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Queries are failing on standby server
Date: Fri, 26 Jul 2024 07:08:11 +0200
Message-ID: <f1573804f5678fcc0efb42380d08ec5e6c255ae0.camel@cybertec.at> (raw)
In-Reply-To: <CAODZiv6GJ+6L+mE=0Dx3B1eDpqZiTvO-xd=USx3x+G-D4o6Lzg@mail.gmail.com>
References: <CAB5fag6D3HR7b1KdKBJ9033TNwbYgwwXO5NtUgFRJmf8aFPRzw@mail.gmail.com>
<CAODZiv5CEmeWrBWJpQ_TxzEKL_tHqRWjWxaz7TSAQT3kcbdeog@mail.gmail.com>
<CAB5fag4RaEM-V6Hr9KagXOhtV_mYQONWGB5XApBMCCghwtd9cw@mail.gmail.com>
<CANzqJaAp1gDLRU1HZXGjn6+awtGEPeR_y4CNXaHCdpgPfUjVWw@mail.gmail.com>
<CAB5fag6RB8iNVATzDesgeyjqQCD4_VJ2vscuLxUDv_fVZ_wJUw@mail.gmail.com>
<ABB3CD9E-6F9C-4191-B2C6-8DD024DF14BD@elevated-dev.com>
<CAB5fag6z+24Sc5tpoF86Be-6m1xT6TpywpEz0mrVK_5tgmsPSA@mail.gmail.com>
<CAGYT1XTfFPG_ye04tGur-kgWSkVqc7+xZBb+cXviVyMPexSEPA@mail.gmail.com>
<CAODZiv6GJ+6L+mE=0Dx3B1eDpqZiTvO-xd=USx3x+G-D4o6Lzg@mail.gmail.com>
On Thu, 2024-07-25 at 22:59 -0400, Keith Fiske wrote:
> On Thu, Jul 25, 2024 at 7:57 PM Fernando Hevia <fhevia@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, keith.fiske@crunchydata.com, fhevia@gmail.com, wasimd60@gmail.com, scott_ribe@elevated-dev.com, ronljohnsonjr@gmail.com
Subject: Re: Queries are failing on standby server
In-Reply-To: <f1573804f5678fcc0efb42380d08ec5e6c255ae0.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox