pauldevos · October 31, 2018 04:21
diff --git a/postgres_queries_and_commands.sql b/postgres_queries_and_commands.sql
 -- CHECK TIMINGS ON ACTIVE AND EXPENSIVE QUERIES
 SELECT activity.*
 FROM (
 SELECT 
   pid, 
   CASE WHEN state = 'active' THEN AGE(clock_timestamp(), query_start) 
        ELSE AGE(state_change, query_start)
   END as query_duration, -- This is how long the most recent query was running (or is running so far, if still active)
   AGE(clock_timestamp(), xact_start) as xact_duration, -- Same, but for the currently active transaction
   CASE WHEN state = 'active' THEN INTERVAL '0'
        ELSE AGE(clock_timestamp(), state_change)
   END as sleep_age, -- For idle connections, how long ago the last query completed.
   state, 
   query,
   client_addr,
   query_start,
   state_change
 FROM pg_stat_activity) activity
 WHERE (activity.sleep_age < INTERVAL '5 minutes' AND activity.query_duration > INTERVAL '5 ms') OR activity.state = 'active'
 ORDER BY query_duration DESC, xact_duration DESC;


 -- THIS KILLS THE QUERY - kill long-running quries.
 SELECT pg_cancel_backend(pid) as killed, pid, AGE(clock_timestamp(), query_start) as age, client_addr, query
 FROM pg_stat_activity 
 WHERE state = 'active' 
  AND query NOT ILIKE '%pg_stat_activity%' 
  AND AGE(clock_timestamp(), query_start) > INTERVAL '10 seconds'
 ORDER BY age desc;


 -- Forcibly kill idle connections or queries, or mid-transaction statement batches.
 SELECT pg_terminate_backend(procpid);

 -- Below are less custom tailored for us, but useful references --

 -- show running queries
 SELECT pid, AGE(clock_timestamp(), query_start) as age, query, usename, *
 FROM pg_stat_activity 
 WHERE state != 'idle' 
  AND query NOT ILIKE '%pg_stat_activity%' 
 ORDER BY age desc;

 -- Show outstanding connections
 SELECT client_hostname, client_addr, COUNT(*)
 FROM pg_stat_activity 
 GROUP BY client_hostname, client_addr
 ORDER BY count DESC;

 -- count of running queries
 SELECT COUNT(*)
 FROM pg_stat_activity 
 WHERE state != 'idle' 
  AND query NOT ILIKE '%pg_stat_activity%' 

 -- count expensive or stalled queries
 SELECT COUNT(*)
 FROM pg_stat_activity 
 WHERE state != 'idle' 
  AND query NOT ILIKE '%pg_stat_activity%' 
  AND AGE(clock_timestamp(), query_start) > INTERVAL '30 seconds'

 -- kill running query
 SELECT pg_cancel_backend(procpid);

 -- kill long running active queries
 SELECT pg_cancel_backend(pid) as killed, pid, AGE(clock_timestamp(), query_start) as age, query, usename, *
 FROM pg_stat_activity 
 WHERE state != 'idle' 
  AND query NOT ILIKE '%pg_stat_activity%' 
  AND AGE(clock_timestamp(), query_start) > INTERVAL '1 minute'
 ORDER BY age desc;



 -- Below are much less useful for us --
 -- vacuum command
 VACUUM (VERBOSE, ANALYZE);

 -- all database users
 select * from pg_stat_activity where current_query not like '<%';

 -- all databases and their sizes
 select * from pg_user;

 -- all tables and their size, with/without indexes
 select datname, pg_size_pretty(pg_database_size(datname))
 from pg_database
 order by pg_database_size(datname) desc;

 -- cache hit rates (should not be less than 0.99)
 SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
 FROM pg_statio_user_tables;

 -- table index usage rates (should not be less than 0.99)
 SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
 FROM pg_stat_user_tables 
 ORDER BY n_live_tup DESC;

 -- how many indexes are in cache
 SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
 FROM pg_statio_user_indexes;

 -- Dump database on remote host to file
 $ pg_dump -U username -h hostname databasename > dump.sql

 -- Import dump into existing database
 $ psql -d newdb -f dump.sql
	-- CHECK TIMINGS ON ACTIVE AND EXPENSIVE QUERIES
	SELECT activity.*
	FROM (
	SELECT
	pid,
	CASE WHEN state = 'active' THEN AGE(clock_timestamp(), query_start)
	ELSE AGE(state_change, query_start)
	END as query_duration, -- This is how long the most recent query was running (or is running so far, if still active)
	AGE(clock_timestamp(), xact_start) as xact_duration, -- Same, but for the currently active transaction
	CASE WHEN state = 'active' THEN INTERVAL '0'
	ELSE AGE(clock_timestamp(), state_change)
	END as sleep_age, -- For idle connections, how long ago the last query completed.
	state,
	query,
	client_addr,
	query_start,
	state_change
	FROM pg_stat_activity) activity
	WHERE (activity.sleep_age < INTERVAL '5 minutes' AND activity.query_duration > INTERVAL '5 ms') OR activity.state = 'active'
	ORDER BY query_duration DESC, xact_duration DESC;


	-- THIS KILLS THE QUERY - kill long-running quries.
	SELECT pg_cancel_backend(pid) as killed, pid, AGE(clock_timestamp(), query_start) as age, client_addr, query
	FROM pg_stat_activity
	WHERE state = 'active'
	AND query NOT ILIKE '%pg_stat_activity%'
	AND AGE(clock_timestamp(), query_start) > INTERVAL '10 seconds'
	ORDER BY age desc;


	-- Forcibly kill idle connections or queries, or mid-transaction statement batches.
	SELECT pg_terminate_backend(procpid);

	-- Below are less custom tailored for us, but useful references --

	-- show running queries
	SELECT pid, AGE(clock_timestamp(), query_start) as age, query, usename, *
	FROM pg_stat_activity
	WHERE state != 'idle'
	AND query NOT ILIKE '%pg_stat_activity%'
	ORDER BY age desc;

	-- Show outstanding connections
	SELECT client_hostname, client_addr, COUNT(*)
	FROM pg_stat_activity
	GROUP BY client_hostname, client_addr
	ORDER BY count DESC;

	-- count of running queries
	SELECT COUNT(*)
	FROM pg_stat_activity
	WHERE state != 'idle'
	AND query NOT ILIKE '%pg_stat_activity%'

	-- count expensive or stalled queries
	SELECT COUNT(*)
	FROM pg_stat_activity
	WHERE state != 'idle'
	AND query NOT ILIKE '%pg_stat_activity%'
	AND AGE(clock_timestamp(), query_start) > INTERVAL '30 seconds'

	-- kill running query
	SELECT pg_cancel_backend(procpid);

	-- kill long running active queries
	SELECT pg_cancel_backend(pid) as killed, pid, AGE(clock_timestamp(), query_start) as age, query, usename, *
	FROM pg_stat_activity
	WHERE state != 'idle'
	AND query NOT ILIKE '%pg_stat_activity%'
	AND AGE(clock_timestamp(), query_start) > INTERVAL '1 minute'
	ORDER BY age desc;



	-- Below are much less useful for us --
	-- vacuum command
	VACUUM (VERBOSE, ANALYZE);

	-- all database users
	select * from pg_stat_activity where current_query not like '<%';

	-- all databases and their sizes
	select * from pg_user;

	-- all tables and their size, with/without indexes
	select datname, pg_size_pretty(pg_database_size(datname))
	from pg_database
	order by pg_database_size(datname) desc;

	-- cache hit rates (should not be less than 0.99)
	SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
	FROM pg_statio_user_tables;

	-- table index usage rates (should not be less than 0.99)
	SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
	FROM pg_stat_user_tables
	ORDER BY n_live_tup DESC;

	-- how many indexes are in cache
	SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
	FROM pg_statio_user_indexes;

	-- Dump database on remote host to file
	$ pg_dump -U username -h hostname databasename > dump.sql

	-- Import dump into existing database
	$ psql -d newdb -f dump.sql