MIME-Version: 1.0
References: <CAD=mzVXjx-ULCM-ZdSz2RDSoL8EA1Bsd+0gBxgOCTWV_tC4GDw@mail.gmail.com>
 <9f175444-6bfc-4c6e-a609-e552d85c45a1@aklaver.com>
In-Reply-To: <9f175444-6bfc-4c6e-a609-e552d85c45a1@aklaver.com>
From: sud <suds1434@gmail.com>
Date: Thu, 9 Oct 2025 00:28:11 +0530
Message-ID: <CAD=mzVVBHZ6480qrbPYhvpkkKqGeBpahV-+=r0xXU7O0XXQ5zw@mail.gmail.com>
Subject: Re: Alerting on memory use and instance crash
To: Adrian Klaver <adrian.klaver@aklaver.com>, Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e43a7b0640aa4473"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVVBHZ6480qrbPYhvpkkKqGeBpahV-%2B%3Dr0xXU7O0XXQ5zw%40mail.gmail.com>
Precedence: bulk

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

Thank you.
My understanding may be wrong here.And my apology as I am using the example
of Oracle again even though these two are not the same. But being worked
for a long time in Oracle so trying to understand exactly how it's
different.

In oracle RAC(real application cluster) database, we have single databases
with multiple nodes/instances/memory, which means the underlying storage is
same but the memory/cpu of each of those instances are different and any of
the instances can be down but the database still operates routing the
application traffic of the downed node to others. Similarly even in AWS
Aurora postgres also there can be multiple instances like Writer and Reader
instances/nodes and the underlying storage being the same. So I was
thinking of any such cluster level pg_* views available by querying which
we would be able to know if any one of the nodes is down ?   Also , I don't
see any such pg_* view which can show the statistics of all the instances
combinely i.e. cluster level statistics.

Do you mean in normal Postgres it's alway a single instance/memory and
single storage attached? then I also do not see any such cluster level
views in aws aurora postgres too? Pardon if it's a silly one to ask.


On Wed, Oct 8, 2025 at 9:52=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver=
.com>
wrote:

> On 10/8/25 08:42, sud 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 :-
> > In other databases like Oracle the catalog Views like "GV$Instance" use=
d
> > to give information on whether the instances are currently active/down
> > or not. But in postgres it seems all the pg_* views are instance
> > specific and are not showing information on the global/cluster level bu=
t
> > 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?
>
> 1) When you say instance do you mean database?
>
> 2) Not all system tables/views are database only.
>
> For instance:
> https://www.postgresql.org/docs/current/catalog-pg-database.html
> https://www.postgresql.org/docs/current/catalog-pg-auth-members.html
> https://www.postgresql.org/docs/current/catalog-pg-authid.html
> https://www.postgresql.org/docs/current/view-pg-roles.html
>
>
> > 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=
?
> >
> > Appreciate your guidance.
> >
> > Regards
> > Sud
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>

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

<div dir=3D"ltr"><div>Thank you.</div>My understanding may be wrong here.An=
d my apology as I am using the example of Oracle again even though these=C2=
=A0two are not the same. But being worked for a long time in Oracle so tryi=
ng to understand exactly how it&#39;s different.<div><br></div><div>In orac=
le RAC(real application cluster) database, we have single databases with mu=
ltiple nodes/instances/memory, which means the underlying storage is same b=
ut the memory/cpu of each of those instances are different and any of the i=
nstances can be=C2=A0down but the database still operates routing the appli=
cation traffic of the downed node to others. Similarly even in AWS Aurora p=
ostgres also there can be multiple instances like Writer and Reader instanc=
es/nodes and the underlying storage being the same. So I was thinking of an=
y=C2=A0such cluster level pg_* views available=C2=A0by querying which we wo=
uld be able to know if any one of the nodes is down ?=C2=A0 =C2=A0Also , I =
don&#39;t see any such pg_* view which can show the statistics of all the i=
nstances combinely i.e. cluster level statistics.=C2=A0</div><div><br></div=
><div>Do you mean in normal Postgres it&#39;s alway a single instance/memor=
y and single storage attached? then I also do not see any such cluster leve=
l views in aws aurora postgres too? Pardon if it&#39;s a silly one to ask.=
=C2=A0</div><div><br></div><div><br></div></div><br><div class=3D"gmail_quo=
te gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Oct=
 8, 2025 at 9:52=E2=80=AFPM Adrian Klaver &lt;<a href=3D"mailto:adrian.klav=
er@aklaver.com">adrian.klaver@aklaver.com</a>&gt; wrote:<br></div><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex">On 10/8/25 08:42, sud wrote:<br>
&gt; Hi Experts,<br>
&gt; <br>
&gt; It&#39;s postgres version 16. I have two questions on alerting as belo=
w.<br>
&gt; <br>
&gt; 1)If we want to have alerting on any node/instance that gets crashed :=
- <br>
&gt; In other databases like Oracle the catalog Views like &quot;GV$Instanc=
e&quot; used <br>
&gt; to give information on whether the instances are currently active/down=
 <br>
&gt; or not. But in postgres it seems all the pg_* views are instance <br>
&gt; specific and are not showing information on the global/cluster level b=
ut <br>
&gt; are restricted to instance level only. So is there any other way to <b=
r>
&gt; query the pg_* views to have=C2=A0alerts on the specific instance cras=
h?<br>
<br>
1) When you say instance do you mean database?<br>
<br>
2) Not all system tables/views are database only.<br>
<br>
For instance:<br>
<a href=3D"https://www.postgresql.org/docs/current/catalog-pg-database.html=
" rel=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/docs/curr=
ent/catalog-pg-database.html</a><br>
<a href=3D"https://www.postgresql.org/docs/current/catalog-pg-auth-members.=
html" rel=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/docs/=
current/catalog-pg-auth-members.html</a><br>
<a href=3D"https://www.postgresql.org/docs/current/catalog-pg-authid.html" =
rel=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/docs/curren=
t/catalog-pg-authid.html</a><br>
<a href=3D"https://www.postgresql.org/docs/current/view-pg-roles.html" rel=
=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/docs/current/v=
iew-pg-roles.html</a><br>
<br>
<br>
&gt; 2)Is there a way to fetch the data from pg_* view to highlight the <br=
>
&gt; specific connection/session/sqls which is using high memory in postgre=
s?<br>
&gt; <br>
&gt; Appreciate your guidance.<br>
&gt; <br>
&gt; Regards<br>
&gt; Sud<br>
<br>
<br>
-- <br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank">adrian.klave=
r@aklaver.com</a><br>
</blockquote></div>

--000000000000e43a7b0640aa4473--