system_stats

Overview

PGDG RPM and PIGSTY DEB package/source version 4.1 cover PostgreSQL 14-18; SQL/control version is 4.0.

Version

Build

You can build the DEB packages for system_stats using pig build:

pig build pkg system_stats         # build DEB packages

Install

You can install system_stats directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

pig install system_stats;          # Install for current active PG version

pig ext install -y system_stats -v 18  # PG 18
pig ext install -y system_stats -v 17  # PG 17
pig ext install -y system_stats -v 16  # PG 16
pig ext install -y system_stats -v 15  # PG 15
pig ext install -y system_stats -v 14  # PG 14

dnf install -y system_stats_18       # PG 18
dnf install -y system_stats_17       # PG 17
dnf install -y system_stats_16       # PG 16
dnf install -y system_stats_15       # PG 15
dnf install -y system_stats_14       # PG 14

apt install -y postgresql-18-system-stats   # PG 18
apt install -y postgresql-17-system-stats   # PG 17
apt install -y postgresql-16-system-stats   # PG 16
apt install -y postgresql-15-system-stats   # PG 15
apt install -y postgresql-14-system-stats   # PG 14

Create Extension:

CREATE EXTENSION system_stats;

Usage

Source: README, Release v4.0, SQL install script

system_stats exposes operating-system metrics through SQL functions. It supports Linux, macOS, and Windows, returning NULL for fields that are not meaningful on the current platform.

Main functions

CREATE EXTENSION system_stats;

SELECT * FROM pg_sys_os_info();
SELECT * FROM pg_sys_cpu_info();
SELECT * FROM pg_sys_cpu_usage_info();
SELECT * FROM pg_sys_memory_info();
SELECT * FROM pg_sys_io_analysis_info();
SELECT * FROM pg_sys_disk_info();
SELECT * FROM pg_sys_load_avg_info();
SELECT * FROM pg_sys_process_info();
SELECT * FROM pg_sys_network_info();
SELECT * FROM pg_sys_cpu_memory_by_process();

These functions cover OS identity, CPU inventory and usage, memory, block-device I/O, disks, load average, process counts, network interfaces, and per-process CPU and memory usage.

Access control

GRANT monitor_system_stats TO nagios;
GRANT EXECUTE ON FUNCTION pg_sys_os_info() TO pg_monitor;

• The extension creates a monitor_system_stats role and grants execution on the shipped functions to that role.
• Functions are revoked from PUBLIC.

Caveats

• The monitor_system_stats role is not dropped automatically when the extension is removed.
• macOS cannot expose full per-process details for processes owned by other users; those rows may contain only PID and process name.
• Pigsty metadata tracks system_stats 4.0 for PostgreSQL 14-18; the RPM and DEB package names differ (system_stats_$v vs. postgresql-$v-system-stats).
• Current v4.0 upstream docs keep the same user-facing function family and security model; the refresh here mainly aligns names, privileges, and platform notes with the current README and SQL script.