Viswamitra's blog

Create extension “plsh” by following below method. This is used to execute the shell scripts from postgres functions.

wget https://github.com/petere/plsh/archive/1.20171014.zip
unzip 1.20171014.zip
cd plsh-1.20171014/
make
make install

postgres=# create extension plsh ;
CREATE EXTENSION

Create below functions which take the process id (PID) and give the cpu and memory utilisation.

CREATE OR REPLACE FUNCTION MEM_UTIL(pid int) RETURNS text AS ‘
#!/bin/sh
cat /proc/$1/smaps | grep -i PSS | awk ”{SUM=SUM+$2}END{print SUM}”
‘ LANGUAGE plsh;

CREATE OR REPLACE FUNCTION CPU_UTIL(pid int) RETURNS text AS ‘
#!/bin/sh
ps –pid=$1 -o %cpu -h
‘ LANGUAGE plsh;

Execute the below command to list the sessions with memory and cpu utilisation.

postgres=# select datname,pid,usename,application_name,state,MEM_UTIL(pid) as MEM_UTIL_KB,CPU_UTIL(pid) as CPU_UTIL_PER from pg_stat_activity where datname is not null;
 datname  |  pid  | usename  | application_name | state  | mem_util_kb | cpu_util_per
———-+——-+———-+——————+——–+————-+————–
 postgres |  9413 | postgres | psql             | active | 6620        |  0.0
 postgres | 16291 | postgres | psql             | idle   | 1059        |  0.0
 postgres | 16293 | postgres | psql             | idle   | 1059        |  0.0
 postgres | 16296 | postgres | psql             | idle   | 1061        |  0.0
 postgres | 16298 | postgres | psql             | idle   | 1060        |  0.0
 postgres | 16330 | postgres | psql             | idle   | 1059        |  0.0
(6 rows)