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;
