Просмотр истории запросов в PostgreSQL

 --create table for historical data PG10+
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;

--create function, which will take 60 snapshots every 1 minute PG10+
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;