system_stats

pig build pkg system_stats;		# build deb

pig repo add pgsql -u   # add both repo and update cache

pig install system_stats;		# install via package name, for the active PG version

pig install system_stats -v 18;   # install for PG 18
pig install system_stats -v 17;   # install for PG 17
pig install system_stats -v 16;   # install for PG 16
pig install system_stats -v 15;   # install for PG 15
pig install system_stats -v 14;   # install for PG 14

CREATE EXTENSION system_stats;

-- Operating system info (name, version, hostname, uptime, etc.)
SELECT * FROM pg_sys_os_info();

-- CPU info (vendor, model, cores, clock speed, cache sizes)
SELECT * FROM pg_sys_cpu_info();

-- CPU usage (user, system, idle, iowait percentages)
SELECT * FROM pg_sys_cpu_usage_info();

-- Memory info (total, used, free, swap, cache in bytes)
SELECT * FROM pg_sys_memory_info();

-- Disk I/O analysis (reads, writes, bytes, time per device)
SELECT * FROM pg_sys_io_analysis_info();

-- Disk info (filesystem, mount point, total/used/available space)
SELECT * FROM pg_sys_disk_info();

-- Load average (1, 5, 10, 15 minute intervals)
SELECT * FROM pg_sys_load_avg_info();

-- Process info (total, running, sleeping, stopped, zombie counts)
SELECT * FROM pg_sys_process_info();

-- Network info (interface, IP, bytes/packets sent/received, errors)
SELECT * FROM pg_sys_network_info();

-- Per-process CPU and memory usage
SELECT * FROM pg_sys_cpu_memory_by_process();

-- Grant access to a monitoring user
GRANT monitor_system_stats TO nagios;

-- Or grant per-function to pg_monitor
GRANT EXECUTE ON FUNCTION pg_sys_os_info() TO pg_monitor;

SELECT * FROM pg_sys_load_avg_info();  -- load averages
SELECT memfree, memused, swapfree FROM pg_sys_memory_info();  -- memory
SELECT * FROM pg_sys_cpu_usage_info();  -- CPU usage breakdown