pg_proctab

Overview

Bundled with pgnodemx package/source 2.0.1 for PostgreSQL 14-18; this package ships pg_proctab as a compatibility stub backed by pgnodemx.

Version

Build

You can build the RPM / DEB packages for pgnodemx using pig build:

pig build pkg pgnodemx         # build RPM / DEB packages

Install

You can install pgnodemx 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 pgnodemx;          # Install for current active PG version

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

dnf install -y pgnodemx_18       # PG 18
dnf install -y pgnodemx_17       # PG 17
dnf install -y pgnodemx_16       # PG 16
dnf install -y pgnodemx_15       # PG 15
dnf install -y pgnodemx_14       # PG 14

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

Create Extension:

CREATE EXTENSION pg_proctab;

Usage

pg_proctab: access OS process table from PostgreSQL

pg_proctab allows querying operating system process, CPU, memory, disk, and load statistics from within PostgreSQL via SQL functions.

Functions

Process information (pg_proctab()):

-- All PostgreSQL process info
SELECT pid, comm, state, utime, stime, vsize, rss, reads, writes
FROM pg_proctab();

-- Join with pg_stat_activity for per-query resource usage
SELECT a.pid, a.query, p.utime, p.stime, p.vsize, p.rss
FROM pg_stat_activity a
JOIN pg_proctab() p ON a.pid = p.pid;

Returns per-process: pid, comm, fullcomm, state, ppid, utime, stime, priority, nice, num_threads, vsize, rss, processor, uid, username, rchar, wchar, reads, writes, and more.

CPU time (pg_cputime()):

SELECT "user", nice, system, idle, iowait FROM pg_cputime();

Load average (pg_loadavg()):

SELECT load1, load5, load15, last_pid FROM pg_loadavg();

Memory usage (pg_memusage()):

SELECT memused, memfree, memshared, membuffers, memcached,
       swapused, swapfree, swapcached
FROM pg_memusage();

Disk usage (pg_diskusage()):

SELECT devname, reads_completed, writes_completed,
       sectors_read, sectors_written
FROM pg_diskusage();