Skip to content

How to create pg_stat_activity historical table

dbacvetkov edited this page Jul 3, 2018 · 10 revisions

If you need a historical data of your database active sessions for viewing it later in PASH-viewer, then do:

  1. Table for storing historical data:
create table pg_stat_activity_history as 
SELECT clock_timestamp() as sample_time, datname, pid, usesysid, usename, backend_type, application_name, client_hostname, client_addr, wait_event_type, wait_event, query, query_start, 1000 * EXTRACT(EPOCH FROM (clock_timestamp()-query_start)) as duration
from pg_stat_activity where 1=2;
  1. Index by sample_time for deleting old data:
create index pg_stat_activity_history_idx on pg_stat_activity_history(sample_time);
  1. Function, which will take 60 snapshots every 1 minute:
CREATE OR REPLACE FUNCTION pg_stat_activity_snapshot() RETURNS void AS
$$
DECLARE
start_ts timestamp := (select clock_timestamp());
ldiff numeric := 0;
BEGIN
	WHILE ldiff < 60
	LOOP
		insert into pg_stat_activity_history SELECT clock_timestamp() as sample_time, datname, pid, usesysid, usename, backend_type, application_name, client_hostname, client_addr, wait_event_type, wait_event, query, query_start, 1000 * EXTRACT(EPOCH FROM (clock_timestamp()-query_start)) as duration from pg_stat_activity where state='active' and pid != pg_backend_pid();
		perform pg_stat_clear_snapshot();
		perform pg_sleep(1);
		ldiff := EXTRACT (EPOCH FROM (clock_timestamp() - start_ts));
	END LOOP;
END
$$ LANGUAGE plpgsql;
  1. Bash script, which will run pg_stat_activity_snapshot function:
cat /var/opt/rh/rh-postgresql10/lib/pgsql/script/pg_stat_activity_snapshot.sh

#!/bin/bash
export PATH=$PATH:$HOME/bin:/opt/rh/rh-postgresql10/root/usr/bin
export LD_LIBRARY_PATH=/opt/rh/rh-postgresql10/root/usr/lib64
export PGDATA=/var/opt/rh/rh-postgresql10/lib/pgsql/data
/opt/rh/rh-postgresql10/root/usr/bin/psql -c "SELECT pg_stat_activity_snapshot();" >/dev/null
  1. Crontab:
*     *       *       *       *       /var/opt/rh/rh-postgresql10/lib/pgsql/script/pg_stat_activity_snapshot.sh
  1. Set up deletion of old data as you wish. Queries for example:
delete from pg_stat_activity_history where sample_time < clock_timestamp() - interval '1 day';
delete from pg_stat_activity_history where sample_time < clock_timestamp() - interval '15 min';
You can’t perform that action at this time.