Correct query for monitor

Поиск
Список
Период
Сортировка
От veem v
Тема Correct query for monitor
Дата
Msg-id CAB+=1TWW4DJwD0XPgud2bTJOuW0HkJ+rU11CL=mU93d4HXwfeA@mail.gmail.com
обсуждение исходный текст
Список 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

В списке pgsql-general по дате отправления: