MIME-Version: 1.0
From: veem v <veema0000@gmail.com>
Date: Fri, 26 Sep 2025 15:53:13 +0530
Message-ID: <CAB+=1TWW4DJwD0XPgud2bTJOuW0HkJ+rU11CL=mU93d4HXwfeA@mail.gmail.com>
Subject: Correct query for monitor
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f47516063fb1ac24"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TWW4DJwD0XPgud2bTJOuW0HkJ%2BrU11CL%3DmU93d4HXwfeA%40mail.gmail.com>
Precedence: bulk

--000000000000f47516063fb1ac24
Content-Type: text/plain; charset="UTF-8"

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

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

<div dir=3D"ltr">Hello,<br>We want to have monitoring on three things 1) If=
 the database restarted or went down in the last few hours? 2)If the connec=
tions are high 3) High tablespace growth . Want to understand , if we can u=
tilize below queries for the same or any flaws in this strategy?<br><br><fo=
nt face=3D"monospace">1)SELECT <br>=C2=A0 CASE <br>=C2=A0 =C2=A0 WHEN now()=
 - pg_postmaster_start_time() &lt; interval &#39;12 hours&#39; <br>=C2=A0 =
=C2=A0 THEN &#39;ALERT: DB was restarted in the last 12 hours&#39;<br>=C2=
=A0 =C2=A0 ELSE &#39;OK&#39;<br>=C2=A0 END AS status;<br>=C2=A0<br>2)SELECT=
 <br>=C2=A0 =C2=A0 CASE <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 WHEN conn_count &gt=
; max_conn * 0.8 THEN <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 &#39;AL=
ERT: Connection usage is above 80%&#39;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSE=
 <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 &#39;OK: Connection usage is=
 under control&#39;<br>=C2=A0 =C2=A0 END AS status,<br>=C2=A0 =C2=A0 conn_c=
ount AS current_connections,<br>=C2=A0 =C2=A0 max_conn AS max_connections,<=
br>=C2=A0 =C2=A0 ROUND(conn_count * 100.0 / max_conn, 2) AS percent_used<br=
>FROM (<br>=C2=A0 =C2=A0 SELECT <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 COUNT(*) AS=
 conn_count,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 (SELECT setting::int FROM pg_se=
ttings WHERE name =3D &#39;max_connections&#39;) AS max_conn<br>=C2=A0 =C2=
=A0 FROM pg_stat_activity<br>) sub;<br><br>3)SELECT spcname, pg_size_pretty=
(pg_tablespace_size(oid)) AS size<br>FROM pg_tablespace;</font><div><font f=
ace=3D"monospace"><br></font></div><div><font face=3D"monospace">Regards</f=
ont></div><div><font face=3D"monospace">Veem</font></div></div>

--000000000000f47516063fb1ac24--