system_stats is a Postgres extension that provides functions to access system level statistics that can be used for monitoring. It supports Linux, macOS and Windows.
Note that not all values are relevant on all operating systems. In such cases NULL is returned for affected values.
Copyright (c) 2019 - 2023, EnterpriseDB Corporation. All Rights Reserved.
The module can be built using the PGXS framework:
For example:
tar -zxvf system_stats-1.0.tar.gz
cd system_stats-1.0
PATH="/usr/local/pgsql/bin:$PATH" make USE_PGXS=1
sudo PATH="/usr/local/pgsql/bin:$PATH" make install USE_PGXS=1
The module built using the Visual Studio project file:
Unpack the extensions files in $PGSRC/contrib/system_stats
Set PG_INCLUDE_DIR and PG_LIB_DIR environment variables to make sure the PostgreSQL include and lib directories can be found for compilation. For example:
PG_INCLUDE_DIR=C:\Program Files\PostgreSQL\12\include
PG_LIB_DIR=C:\Program Files\PostgreSQL\12\lib
Open the Visual Studio project file "system_stats.vcxproj" and build the project.
Once the code has been built and installed, you can install the extension in a database using the following SQL command:
CREATE EXTENSION system_stats;
Due to the nature of the information returned by these functions, access is restricted to superusers and members of the monitor_system_stats role which will be created when the extension is installed. The monitor_system_stats role will not be removed when you run DROP EXTENSION. This means that any users or roles that were granted permissions to the monitor_system_stats role will still have those permissions even after the extension has been dropped. To allow users to access the functions without granting them superuser access, add them to the monitor_system_stats role. For example:
GRANT monitor_system_stats to nagios;
User can grant execute rights for all the below functions to pg_monitor
role explicitly
e.g.
GRANT EXECUTE ON FUNCTION pg_sys_os_info() TO pg_monitor;
The following functions are provided to fetch system level statistics for all platforms.
This interface allows the user to get operating system statistics.
This interface allows the user to get CPU information.
This interface allows the user to get CPU usage information. Values are a percentage of time spent by CPUs for all operations.
This interface allows the user to get memory usage information. All the values are in bytes.
This interface allows the user to get an I/O analysis of block devices.
This interface allows the user to get the disk information.
This interface allows the user to get the average load of the system over 1, 5, 10 and 15 minute intervals.
This interface allows the user to get process information.
This interface allows the user to get network interface information.
This interface allows the user to get the CPU and memory information for each process ID.
NOTE: macOS does not allow access to to process information for other users. e.g. If the database server is running as the postgres user, this function will fetch information only for processes owned by the postgres user. Other processes will be listed and include only the process ID and name; other columns will be NULL.