MIME-Version: 1.0
References: <CAD=mzVXjx-ULCM-ZdSz2RDSoL8EA1Bsd+0gBxgOCTWV_tC4GDw@mail.gmail.com>
In-Reply-To: <CAD=mzVXjx-ULCM-ZdSz2RDSoL8EA1Bsd+0gBxgOCTWV_tC4GDw@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 8 Oct 2025 12:21:53 -0400
Message-ID: <CANzqJaAmKGtWFwEsLV9NQZhW8+Ud3x97mvPub5PmFfnV1JraKg@mail.gmail.com>
Subject: Re: Alerting on memory use and instance crash
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000cbe0420640a81536"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAmKGtWFwEsLV9NQZhW8%2BUd3x97mvPub5PmFfnV1JraKg%40mail.gmail.com>
Precedence: bulk

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

On Wed, Oct 8, 2025 at 11:42=E2=80=AFAM sud <suds1434@gmail.com> wrote:

> Hi Experts,
>
> It's postgres version 16. I have two questions on alerting as below.
>
> 1)If we want to have alerting on any node/instance that gets crashed :- I=
n
> other databases like Oracle the catalog Views like "GV$Instance" used to
> give information on whether the instances are currently active/down or no=
t.
> But in postgres it seems all the pg_* views are instance specific and are
> not showing information on the global/cluster level but are restricted to
> instance level only. So is there any other way to query the pg_* views to
> have alerts on the specific instance crash?
>

In Postgresql, cluster =3D=3D instance.  That's a historical fluke which mi=
ght
never go away.  Thus, if the cluster is down, you can't access anything.

Connection poolers that use virtual IP addresses and are the modern
definition of "cluster" sit on top of individual PG clusters.  Even though
the pooler auto-fails the (modern) cluster to the replica instance, PG
still thinks one cluster is down, and the former-replica cluster is now the
primary cluster.

Confusing?  Yes.  Just accept that *PG cluster =3D=3D instance*, and that
*Postgresql
is not Oracle*.


> 2)Is there a way to fetch the data from pg_* view to highlight the
> specific connection/session/sqls which is using high memory in postgres?
>


--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Oct 8, 2025 at 11:42=E2=80=AFAM s=
ud &lt;<a href=3D"mailto:suds1434@gmail.com">suds1434@gmail.com</a>&gt; wro=
te:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hi Experts,<br><br>It&#39=
;s postgres version 16. I have two questions on alerting as below.<br><br><=
div>1)If we want to have alerting on any node/instance that gets crashed :-=
 In other databases like Oracle the catalog Views like &quot;GV$Instance&qu=
ot; used to give information on whether the instances are currently active/=
down or not. But in postgres it seems all the pg_* views are instance speci=
fic and are not showing information on the global/cluster level but are res=
tricted to instance level only. So is there any other way to query the pg_*=
 views to have=C2=A0alerts on the specific instance crash?<br></div></div><=
/blockquote><div><br></div><div>In Postgresql, cluster =3D=3D instance.=C2=
=A0 That&#39;s a historical fluke which might never go away.=C2=A0 Thus, if=
 the cluster is down, you can&#39;t access anything.</div><div><br></div><d=
iv>Connection poolers that use virtual IP addresses and are the modern defi=
nition of &quot;cluster&quot; sit on top of individual PG clusters.=C2=A0 E=
ven though the pooler auto-fails the (modern) cluster to the replica instan=
ce, PG still thinks one cluster is down, and the former-replica cluster is =
now the primary cluster.</div><div><br></div><div>Confusing?=C2=A0 Yes.=C2=
=A0 Just accept that <b>PG cluster =3D=3D instance</b>, and that <b>Postgre=
sql is not Oracle</b>.</div><div>=C2=A0</div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex"><div dir=3D"ltr"><div>2)Is there a way to fetch the dat=
a from pg_* view to highlight the specific connection/session/sqls which is=
 using high memory in postgres?</div></div></blockquote><div><br></div><div=
></div></div><div><br></div><div><br></div><span class=3D"gmail_signature_p=
refix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D=
"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I=
&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div=
></div></div></div>

--000000000000cbe0420640a81536--