Message-ID: <f1573804f5678fcc0efb42380d08ec5e6c255ae0.camel@cybertec.at>
Subject: Re: Queries are failing on standby server
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Keith Fiske <keith.fiske@crunchydata.com>, Fernando Hevia
 <fhevia@gmail.com>
Cc: Wasim Devale <wasimd60@gmail.com>, Scott Ribe
 <scott_ribe@elevated-dev.com>,  Ron Johnson <ronljohnsonjr@gmail.com>,
 pgsql-admin <pgsql-admin@postgresql.org>
Date: Fri, 26 Jul 2024 07:08:11 +0200
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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.3 (3.52.3-1.fc40) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/f1573804f5678fcc0efb42380d08ec5e6c255ae0.camel%40cybertec.at>
Precedence: bulk

On Thu, 2024-07-25 at 22:59 -0400, Keith Fiske wrote:
> On Thu, Jul 25, 2024 at 7:57=E2=80=AFPM 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 se=
rver. It happens that
> > when you run queries on a hot standby, the replication is temporarily p=
aused 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 con=
flict.
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 mast=
er happens to fail
> > while the=C2=A0replica 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 re=
play a lot of WAL.

> > To avoid having a standby server lagging too far behind Postgres will c=
ancel long running
> > queries on the replica. The parameter=C2=A0max_standby_streaming_delay =
defines the maximum
> > replication delay the standby will tolerate. Default is 30 seconds. Inc=
rease 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 cancel=
led.

> > A working alternative is to have one standby server exclusively for rep=
lication purposes
> > and another standby for reporting/read-only queries where you can incre=
ase the
> > max_standby_streaming_delay to accommodate your long running queries. O=
f course, this will
> > require additional computing and storage resources.

That is good advice.

> > >=20
> This is all true, but the hot_standby_feedback option is the way to get a=
round needing to
> worry about replication delay all together.

No, because there are other kinds of replication conflicts.  The most frequ=
ent 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 (wh=
ich can
  be disabled for individual tables).

- buffer pin conflicts

  It depends on the workload if you get them, but you cannot get rid of the=
m.

Yours,
Laurenz Albe