MIME-Version: 1.0
References: <CAD=mzVXR3GjM0vcthMBwEdbOKqSKcv8oojSS9coczWRi9BRYTA@mail.gmail.com>
 <abd3bc064d16bc93a2d8661a692903da97d2c154.camel@cybertec.at>
 <CAD=mzVVvK8xk-9m8h3Xu27cGN7BW329HKYdO+0EMXfWvSD3AGA@mail.gmail.com>
 <bed28c629a839b1f354e18f416a87fd5f4f78ba7.camel@cybertec.at>
 <CAD=mzVVqR-mKUFHetsejFWSPQPbLjTVhCmBebJTFX5XmYp+nGg@mail.gmail.com>
 <891bcfec74f7358ef0212caf6565a35153dd2941.camel@cybertec.at>
 <CAD=mzVXRkNM6ATTtnCsZeA0sfD6S_UPU=i6vfMTfoTBuT0pKTw@mail.gmail.com>
 <CAEzWdqdix_ftiUuPJp_LZ3QjB6rDmHVfxtdVMOn+akhMAWEOGw@mail.gmail.com>
 <d18f56f9e8aec98b981ade94f300ec7473ec0cce.camel@cybertec.at>
 <CAEzWdqdPnErdeg6xe=zf7aF-fGy0Z42vXEm6zE6Ok25o=f6a7Q@mail.gmail.com>
 <56ad97911d83f721dd872e8ee68cd77d50d3eef6.camel@cybertec.at>
 <CAD=mzVU7Ry7xhZ=Kra4N87ugvAUubwGFqnLtXbcvy8yJasOVPQ@mail.gmail.com>
 <CAPOUM=cxpEaN9kSHnBAQFuiMKJ7iyD7+u4wS5djY-ZWRpo_Log@mail.gmail.com>
 <CAD=mzVVhE4Y637XOkqK1yMzP6U2aRhBi5xTcEZw6_Ppppb6mqA@mail.gmail.com>
 <c1d238173bf8ba94018b65beb4e1670f0d37f766.camel@cybertec.at> <CAEzWdqfzp7+buzXkpKnHqVpq=a2kaE33fWD-nXVeEdGiTBwGEQ@mail.gmail.com>
In-Reply-To: <CAEzWdqfzp7+buzXkpKnHqVpq=a2kaE33fWD-nXVeEdGiTBwGEQ@mail.gmail.com>
From: sud <suds1434@gmail.com>
Date: Sat, 8 Jun 2024 14:51:32 +0530
Message-ID: <CAD=mzVWy=0HtzkjtqqCZktRDA7w0AL+mVW5+dz_E1EHqiyRYkA@mail.gmail.com>
Subject: Re: Long running query causing XID limit breach
To: yudhi s <learnerdatabase99@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, Simon Elbaz <elbazsimon9@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000dc39b2061a5d717f"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVWy%3D0HtzkjtqqCZktRDA7w0AL%2BmVW5%2Bdz_E1EHqiyRYkA%40mail.gmail.com>
Precedence: bulk

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

On Thu, Jun 6, 2024 at 12:52=E2=80=AFAM yudhi s <learnerdatabase99@gmail.co=
m> wrote:

> On Wed, Jun 5, 2024 at 3:52=E2=80=AFPM Laurenz Albe <laurenz.albe@cyberte=
c.at>
> wrote:
>
>>
>> There should never be a restart unless you perform one or the standby
>> crashes.
>> If you mean that you want to avoid a crash caused by a full disk on the
>> standby,
>> the answer is probably "no".  Make sure that you have enough disk space
>> and
>> use monitoring.
>>
>> Yours,
>> Laurenz Albe
>>
>
> Is this because OP initially mentioned its RDS postgres, so in that case
> there is storage space restriction on 64TB(and 128TB in case of aurora
> postgres). So I believe this storage space combines data + WAL , so in th=
at
> case as you mentioned, appropriate monitoring needs to be put in place.
> Or else in the worst case scenario, if the storage consumption hit that
> hard limit , then there will be instance restart or crash?
>

Thank You so much Laurenz and Yudhi.

Yes its RDS and as you mentioned there does exist a space limitation of
~64TB but as Laurenz mentioned the only time the second standby may crash
would be probably because of  the storage space saturation and thus we need
to have appropriate monitoring in place to find this and get alerted
beforehand. And also a monitoring to see how much WAL gets generated per
hour/day to get an idea of the usage. I am not sure how to do it , but will
check on this.

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

<div dir=3D"ltr"><div dir=3D"ltr"></div><br><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Thu, Jun 6, 2024 at 12:52=E2=80=AFAM y=
udhi s &lt;<a href=3D"mailto:learnerdatabase99@gmail.com">learnerdatabase99=
@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Wed, Jun 5, 2024 at 3:52=E2=
=80=AFPM Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at" targe=
t=3D"_blank">laurenz.albe@cybertec.at</a>&gt; wrote:<br></div><div class=3D=
"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><br>
There should never be a restart unless you perform one or the standby crash=
es.<br>
If you mean that you want to avoid a crash caused by a full disk on the sta=
ndby,<br>
the answer is probably &quot;no&quot;.=C2=A0 Make sure that you have enough=
 disk space and<br>
use monitoring.<br>
<br>
Yours,<br>
Laurenz Albe<br></blockquote><div><br></div><div>Is this because OP initial=
ly mentioned its RDS postgres, so in that case there is storage=C2=A0space =
restriction on 64TB(and 128TB in case of aurora postgres). So I believe thi=
s storage space combines data=C2=A0+ WAL , so in that case as you mentioned=
, appropriate=C2=A0monitoring needs to be put in place.=C2=A0</div><div>Or =
else in the worst case scenario, if the storage consumption hit that hard l=
imit , then there will be instance restart or crash?</div></div></div></blo=
ckquote><div><br></div><div>Thank You so much Laurenz and Yudhi.</div><div>=
<br></div><div>Yes its RDS and as you mentioned there does exist=C2=A0a spa=
ce limitation of ~64TB but as Laurenz mentioned the only time the second st=
andby may crash would be probably because of=C2=A0 the storage=C2=A0space s=
aturation and thus we need to have appropriate monitoring in place to find =
this and get alerted beforehand. And also a monitoring to see how much WAL =
gets generated=C2=A0per hour/day to get an idea of the usage. I am not sure=
 how to do it , but will check on this.</div></div></div>

--000000000000dc39b2061a5d717f--