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
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 |