Обсуждение: Alerting on memory use and instance crash
Hi Experts,
It's postgres version 16. I have two questions on alerting as below.
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" 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 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?
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?
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
On Wed, Oct 8, 2025 at 11:42 AM 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 :- In other databases like Oracle the catalog Views like "GV$Instance" 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 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 == instance. That's a historical fluke which might 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 == 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?
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
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" 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 > 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? 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
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 PM 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" 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
> 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?
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
On Wed, Oct 8, 2025 at 2:58 PM sud <suds1434@gmail.com> wrote:
[snip]
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?
Yup.
Pardon if it's a silly one to ask.
A Google for "what's the difference between Oracle and Postgresql" _might_ help. I've never done that, so don't know what you'll find.
As far as how Aurora works... you need to ask AWS. It's been too heavily modified for a list dedicated to pure/unmodified Postgresql to help.
On Wed, Oct 8, 2025 at 9:52 PM 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" 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
> 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?
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
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Thank you.
The other question I had was , are there any pg_* views using which, we are able to see which session/connection is using the highest amount of memory? I don't see any such columns in pg_stats_activity.
On Thu, Oct 9, 2025 at 12:37 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Wed, Oct 8, 2025 at 2:58 PM sud <suds1434@gmail.com> wrote:[snip]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?Yup.Pardon if it's a silly one to ask.A Google for "what's the difference between Oracle and Postgresql" _might_ help. I've never done that, so don't know what you'll find.As far as how Aurora works... you need to ask AWS. It's been too heavily modified for a list dedicated to pure/unmodified Postgresql to help.On Wed, Oct 8, 2025 at 9:52 PM 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" 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
> 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?
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--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
On 10/8/25 11:58, sud wrote: > 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. > It would be helpful if you specified exactly what variety of Postgres you are using and it's version. If you are using AWS Aurora Postgres then you will need to look at pages like this: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Managing.html This list is for the community version of Postgres and it's been a long time since AWS saw fit to have someone on the list and when they where here they did not really provide answers. -- Adrian Klaver adrian.klaver@aklaver.com