public inbox for [email protected]  

help / color / mirror / Atom feed

From: veem v <[email protected]>
To: pgsql-general <[email protected]>
Subject: Correct query for monitor
Date: Fri, 26 Sep 2025 15:53:13 +0530
Message-ID: <CAB+=1TWW4DJwD0XPgud2bTJOuW0HkJ+rU11CL=mU93d4HXwfeA@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Correct query for monitor
  In-Reply-To: <CAB+=1TWW4DJwD0XPgud2bTJOuW0HkJ+rU11CL=mU93d4HXwfeA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox