Correct query for monitor

From: veem v <veema0000(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Correct query for monitor
Date: 2025-09-26 10:23:13
Message-ID: CAB+=1TWW4DJwD0XPgud2bTJOuW0HkJ+rU11CL=mU93d4HXwfeA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,
We want to have monitoring on three things 1) If the database restarted or
went down in the last few hours? 2)If the connections are high 3) High
tablespace growth . Want to understand , if we can utilize below queries
for the same or any flaws in this strategy?

1)SELECT
CASE
WHEN now() - pg_postmaster_start_time() < interval '12 hours'
THEN 'ALERT: DB was restarted in the last 12 hours'
ELSE 'OK'
END AS status;

2)SELECT
CASE
WHEN conn_count > max_conn * 0.8 THEN
'ALERT: Connection usage is above 80%'
ELSE
'OK: Connection usage is under control'
END AS status,
conn_count AS current_connections,
max_conn AS max_connections,
ROUND(conn_count * 100.0 / max_conn, 2) AS percent_used
FROM (
SELECT
COUNT(*) AS conn_count,
(SELECT setting::int FROM pg_settings WHERE name =
'max_connections') AS max_conn
FROM pg_stat_activity
) sub;

3)SELECT spcname, pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace;

Regards
Veem

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dan Mahoney (Gushi) 2025-09-26 12:05:58 pgpass file in postresql.auto.conf?
Previous Message Laurenz Albe 2025-09-26 05:25:39 Re: Compatibility Informations