MIME-Version: 1.0
References: <CAKeSjMhHx1cMSTPTP7szhyV5Mt21YuA98Q+hS9+W2nrj0U9N3Q@mail.gmail.com>
 <CAKkG4_=V2UN62_CjRohbpZ_uxP56ai465ohNp1bt-+r41wCxig@mail.gmail.com>
In-Reply-To: <CAKkG4_=V2UN62_CjRohbpZ_uxP56ai465ohNp1bt-+r41wCxig@mail.gmail.com>
From: Jethish Jethish <jethish777@gmail.com>
Date: Thu, 23 May 2024 16:15:58 +0530
Message-ID: <CAKeSjMh3YKDkcDORDL8cE4DbwaAuWrRz_aoS1GoS-XX=Ki5KvQ@mail.gmail.com>
Subject: Re: Backup failure Postgres
To: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000004dcfa206191cc219"
Archived-At: <https://www.postgresql.org/message-id/CAKeSjMh3YKDkcDORDL8cE4DbwaAuWrRz_aoS1GoS-XX%3DKi5KvQ%40mail.gmail.com>
Precedence: bulk

--0000000000004dcfa206191cc219
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Torsten,

I have tried by increasing the max_standby_streaming_delay but I'm facing
lag issues on the replica server.

When i increase the max_standby_streaming_delay even if a query runs for 2
minutes I'm facing lag issues for 2 minutes.

Please suggest here.
Data size is 3TB

On Thu, May 23, 2024, 3:53=E2=80=AFPM Torsten F=C3=B6rtsch <tfoertsch123@gm=
ail.com>
wrote:

> As the error message says, your query was aborted due to it conflicting
> with recovery. There are many ways to deal with that. You could enable
> hot_standby_feedback on the replica. You could disconnect the replica fro=
m
> the master for the time the COPY takes (reset primary_conninfo). You coul=
d
> increase max_standby_streaming_delay. Perhaps you could also wrap the COP=
Y
> operation in pg_wal_replay_pause() / pg_wal_replay_resume().
>
> On Thu, May 23, 2024 at 11:59=E2=80=AFAM Jethish Jethish <jethish777@gmai=
l.com>
> wrote:
>
>> I'm frequently facing the below error while performing backup. Someone
>> please tell how solve this issues.
>>
>>
>> Failed : pg_dump: error: Dumping the contents of table "botsession"
>> failed: PQgetResult() failed. pg_dump: error: Error message from server:
>> ERROR: canceling statement due to conflict with recovery DETAIL: User qu=
ery
>> might have needed to see row versions that must be removed. pg_dump: err=
or:
>> The command was: COPY public.botsession (id, userid, data, iscompressed)=
 TO
>> stdout;
>>
>

--0000000000004dcfa206191cc219
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">Hi Torsten,<div dir=3D"auto"><br></div><div dir=3D"auto">=
I have tried by increasing the max_standby_streaming_delay but I&#39;m faci=
ng lag issues on the replica server.</div><div dir=3D"auto"><br></div><div =
dir=3D"auto">When i increase the max_standby_streaming_delay even if a quer=
y runs for 2 minutes I&#39;m facing lag issues for 2 minutes.</div><div dir=
=3D"auto"><br></div><div dir=3D"auto">Please suggest here.</div><div dir=3D=
"auto">Data size is 3TB</div></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Thu, May 23, 2024, 3:53=E2=80=AFPM Torsten=
 F=C3=B6rtsch &lt;<a href=3D"mailto:tfoertsch123@gmail.com">tfoertsch123@gm=
ail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"=
margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=3D"=
ltr">As the error message says, your query was aborted due to it conflictin=
g with recovery. There are many ways to deal with that. You could enable ho=
t_standby_feedback on the replica. You could disconnect the replica from th=
e master for the time the COPY takes (reset primary_conninfo). You could in=
crease max_standby_streaming_delay. Perhaps you could also wrap the COPY op=
eration in pg_wal_replay_pause() / pg_wal_replay_resume().</div><br><div cl=
ass=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, May 23, 2=
024 at 11:59=E2=80=AFAM Jethish Jethish &lt;<a href=3D"mailto:jethish777@gm=
ail.com" target=3D"_blank" rel=3D"noreferrer">jethish777@gmail.com</a>&gt; =
wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"auto">I&#39;m frequently facing the below error while performing backup=
. Someone please tell how solve this issues.<div dir=3D"auto"><br></div><di=
v dir=3D"auto"><br></div><div dir=3D"auto"><p style=3D"margin-top:0px;margi=
n-bottom:0px">Failed : pg_dump: error: Dumping the contents of table &quot;=
botsession&quot; failed: PQgetResult() failed. pg_dump: error: Error messag=
e from server: ERROR: canceling statement due to conflict with recovery DET=
AIL: User query might have needed to see row versions that must be removed.=
 pg_dump: error: The command was: COPY public.botsession (id, userid, data,=
 iscompressed) TO stdout;</p></div></div>
</blockquote></div>
</blockquote></div>

--0000000000004dcfa206191cc219--