Home

Awesome

pgnodemx

Overview

SQL functions that allow capture of node OS metrics from PostgreSQL

Security

Executing role must have been granted pg_monitor membership (pgmonitor for PostgreSQL version 9.6 and below - see Compatibility section below).

cgroup Related Functions

For detailed information about the various virtual files available on the cgroup file system, see:

General Access Functions

cgroup virtual files fall into (at least) the following general categories, each with a generic SQL access function:

In each case, the filename must be in the form <controller>.<metric>, e.g. memory.stat.

Get status of cgroup support

SELECT current_setting('pgnodemx.cgroup_enabled');

Get current cgroup mode

SELECT cgroup_mode();

Determine if Running Containerized

SELECT current_setting('pgnodemx.containerized');

Get cgroup Paths

SELECT controller, path FROM cgroup_path();

Get cgroup process count

SELECT cgroup_process_count();

Environment Variable Related Functions

Get Environment Variable as TEXT

SELECT envvar_text('PGDATA');

Get Environment Variable as BIGINT

SELECT envvar_bigint('PGPORT');

/proc Related Functions

For more detailed information about the /proc file system virtual files, please see: https://www.kernel.org/doc/html/latest/filesystems/proc.html

Get "/proc/diskstats" as a virtual table

SELECT * FROM proc_diskstats();

Get "/proc/self/mountinfo" as a virtual table

SELECT * FROM proc_mountinfo();

Get "/proc/meminfo" as a virtual table

SELECT * FROM proc_meminfo();

Get "/proc/self/net/dev" as a virtual table

SELECT * FROM proc_network_stats();

Get "/proc/<pid>/io" for all PostgreSQL processes as a virtual table

SELECT * FROM proc_pid_io();

Get the full command line, uid, and username for all PostgreSQL processes as a virtual table

SELECT * FROM proc_pid_cmdline();

Get "/proc/<pid>/stat" for all PostgreSQL processes as a virtual table

SELECT * FROM proc_pid_stat();

Get first line of "/proc/stat" as a virtual table

SELECT * FROM proc_cputime();

Get first line of "/proc/loadavg" as a virtual table

SELECT * FROM proc_loadavg();

pg_proctab Compatibility Functions for use with pg_top

Five functions are provided in an extension that match the SQL interface presented by the pg_proctab extension.

CREATE EXTENSION pg_proctab VERSION "0.0.10-compat";
SELECT * FROM pg_cputime();
SELECT * FROM pg_loadavg();
SELECT * FROM pg_memusage();
SELECT * FROM pg_diskusage();
SELECT * FROM pg_proctab();

These functions are not installed by default. They may be installed by installing pg_proctab VERSION "0.0.10-compat" after installing the pgnodemx extension.

System Information Related Functions

Get file system information as a virtual table

SELECT * FROM fsinfo(path text);

Get current FIPS mode

SELECT fips_mode();

Get openssl version string

select openssl_version();

Get source C library path for a function symbol

SELECT symbol_filename(sym_name text);

Convert number of kernel memory pages to bytes

SELECT kpages_to_bytes(num_k_pages numeric);

Kubernetes DownwardAPI Related Functions

For more detailed information about the Kubernetes DownwardAPI please see: https://kubernetes.io/docs/tasks/inject-data-application/downward-api-volume-expose-pod-information/

Get status of kdapi_enabled

SELECT current_setting('pgnodemx.kdapi_enabled');

Access "key equals quoted value" files

SELECT * FROM kdapi_setof_kv('filename');

Get scalar BIGINT from file

SELECT kdapi_scalar_bigint('filename text');

General Information Functions

Get pgnodemx version information

SELECT pgnodemx_version();

Get currently running PostgreSQL executable path

SELECT exec_path();

Get uid, username, gid, groupname, and filemode for a file

SELECT * FROM stat_file(filename);

Configuration

shared_preload_libraries = 'pgnodemx'

# enable or disable the cgroup facility
pgnodemx.cgroup_enabled = on
# force use of "containerized" assumptions for cgroup file paths
pgnodemx.containerized = off
# specify location of cgroup mount
pgnodemx.cgrouproot = '/sys/fs/cgroup'
# enable cgroup functions
pgnodemx.cgroup_enabled = on
# enable or disable the Kubernetes DownwardAPI facility
pgnodemx.kdapi_enabled = on
# specify location of Kubernetes DownwardAPI files
pgnodemx.kdapi_path = '/etc/podinfo'

Notes:

Installation

Compatibility

Compile and Install

Clone PostgreSQL repository:

$> git clone https://github.com/postgres/postgres.git

Checkout REL_12_STABLE (for example) branch:

$> git checkout REL_12_STABLE

Make PostgreSQL:

$> ./configure
$> make install -s

Change to the contrib directory:

$> cd contrib

Clone pgnodemx extension:

$> git clone https://github.com/crunchydata/pgnodemx

Change to pgnodemx directory:

$> cd pgnodemx

Build pgnodemx:

$> make

Install pgnodemx:

$> make install

Using PGXS

If an instance of PostgreSQL is already installed, then PGXS can be utilized to build and install pgnodemx. Ensure that PostgreSQL binaries are available via the $PATH environment variable then use the following commands.

$> make USE_PGXS=1
$> make USE_PGXS=1 install

Configure

The following bash commands should configure your system to utilize pgnodemx. Replace all paths as appropriate. It may be prudent to visually inspect the files afterward to ensure the changes took place.

Initialize PostgreSQL (if needed):
$> initdb -D /path/to/data/directory
Create Target Database (if needed):
$> createdb <database>
Install pgnodemx functions:

Edit postgresql.conf and add pgnodemx to the shared_preload_libraries line, and change custom settings as mentioned above.

Finally, restart PostgreSQL (method may vary):

$> service postgresql restart

Install the extension into your database:

psql <database>
CREATE EXTENSION pgnodemx;

TODO