postgres outils

postgres10

-- active queries
SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age 
FROM pg_stat_activity order by age;

-- waiting activities
SELECT * FROM pg_stat_activity WHERE wait_event IS NOT NULL AND backend_type = 'client backend';


-- need vacuum ?
-- https://severalnines.com/blog/why-postgresql-running-slow-tips-tricks-get-source
SELECT * from pg_stat_user_tables where relname like 'machine_data_%' and n_dead_tup > 0;


-- enable pg_stat_statements
-- https://pganalyze.com/docs/install/01_enabling_pg_stat_statements

-- progress indicator
-- https://stackoverflow.com/questions/26212484/how-to-track-query-progress-in-postgresql
-- https://dba.stackexchange.com/questions/50602/how-do-i-find-out-how-far-along-my-postgresql-query-is


-- get the pid from the 3d column
SELECT * FROM pg_stat_activity; 
-- install strace
strace -p 35676 -s 8192


select * from pg_last_committed_xact();