shoan · February 12, 2023 14:08
diff --git a/postgres_queries_and_commands.sql b/postgres_queries_and_commands.sql
 -- Queries running longer than 2 mins
 SELECT pid, now() - query_start as "runtime", usename, datname, state, now() - state_change as "state_changed_age", wait_event_type, wait_event, query
  FROM  pg_stat_activity
  WHERE now() - query_start > '2 minutes'::interval and state = 'active'
 ORDER BY runtime DESC;
 
 -- Query to kill queries
 SELECT pg_cancel_backend(pid)
  FROM  pg_stat_activity
  WHERE now() - query_start > '2 minutes'::interval and state = 'active'
  and query ilike 'select%'
  and query not ilike '%vacuum%' -- Don't kill vacuum queries
 ;
 
 -- Kill idle connections
 SELECT pg_terminate_backend(pid) FROM pg_stat_activity
 WHERE pid <> pg_backend_pid()
 AND state in ('idle') and usename<>'rdsadmin';

 -- Kill idle connections older than 2 minutes
 SELECT pg_terminate_backend(pid) FROM pg_stat_activity
 WHERE pid <> pg_backend_pid()
 AND state in ('idle') and usename<>'rdsadmin'
 AND now() - query_start > '2 minutes'::interval
 ;

 -- Find dead tuples
 SELECT relname, n_dead_tup FROM pg_stat_user_tables order by n_dead_tup desc;

 -- Find last vacuum stats
 SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;


 -- Users and their permissions
 SELECT grantee                               AS user,
       CONCAT(table_schema, '.', table_name) AS table,
       CASE
           WHEN COUNT(privilege_type) = 7 THEN 'ALL'
           ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
           END                               AS grants
 FROM information_schema.role_table_grants
 GROUP BY table_name, table_schema, grantee;


 -- database sizes
 select t1.datname AS db_name,  
       pg_size_pretty(pg_database_size(t1.datname)) as db_size
 from pg_database t1
 order by pg_database_size(t1.datname) desc;


 -- tables and sizes
 SELECT
    relname AS "relation",
    pg_size_pretty (
        pg_total_relation_size (C .oid)
    ) AS "total_size"
 FROM
    pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
 WHERE
    nspname NOT IN (
        'pg_catalog',
        'information_schema'
    )
 AND C .relkind <> 'i'
 AND nspname !~ '^pg_toast'
 ORDER BY
    pg_total_relation_size (C .oid) DESC
 ;


 -- ------------------------------
 -- show running queries (pre 9.2)
 SELECT procpid, age(clock_timestamp(), query_start), usename, current_query 
 FROM pg_stat_activity 
 WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
 ORDER BY query_start desc;

 -- show running queries (9.2)
 SELECT pid, age(clock_timestamp(), query_start), usename, query 
 FROM pg_stat_activity 
 WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
 ORDER BY query_start desc;

 -- kill running query
 SELECT pg_cancel_backend(procpid);

 -- kill idle query
 SELECT pg_terminate_backend(procpid);

 -- 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
	-- Queries running longer than 2 mins
	SELECT pid, now() - query_start as "runtime", usename, datname, state, now() - state_change as "state_changed_age", wait_event_type, wait_event, query
	FROM pg_stat_activity
	WHERE now() - query_start > '2 minutes'::interval and state = 'active'
	ORDER BY runtime DESC;

	-- Query to kill queries
	SELECT pg_cancel_backend(pid)
	FROM pg_stat_activity
	WHERE now() - query_start > '2 minutes'::interval and state = 'active'
	and query ilike 'select%'
	and query not ilike '%vacuum%' -- Don't kill vacuum queries
	;

	-- Kill idle connections
	SELECT pg_terminate_backend(pid) FROM pg_stat_activity
	WHERE pid <> pg_backend_pid()
	AND state in ('idle') and usename<>'rdsadmin';

	-- Kill idle connections older than 2 minutes
	SELECT pg_terminate_backend(pid) FROM pg_stat_activity
	WHERE pid <> pg_backend_pid()
	AND state in ('idle') and usename<>'rdsadmin'
	AND now() - query_start > '2 minutes'::interval
	;

	-- Find dead tuples
	SELECT relname, n_dead_tup FROM pg_stat_user_tables order by n_dead_tup desc;

	-- Find last vacuum stats
	SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;


	-- Users and their permissions
	SELECT grantee AS user,
	CONCAT(table_schema, '.', table_name) AS table,
	CASE
	WHEN COUNT(privilege_type) = 7 THEN 'ALL'
	ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
	END AS grants
	FROM information_schema.role_table_grants
	GROUP BY table_name, table_schema, grantee;


	-- database sizes
	select t1.datname AS db_name,
	pg_size_pretty(pg_database_size(t1.datname)) as db_size
	from pg_database t1
	order by pg_database_size(t1.datname) desc;


	-- tables and sizes
	SELECT
	relname AS "relation",
	pg_size_pretty (
	pg_total_relation_size (C .oid)
	) AS "total_size"
	FROM
	pg_class C
	LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
	WHERE
	nspname NOT IN (
	'pg_catalog',
	'information_schema'
	)
	AND C .relkind <> 'i'
	AND nspname !~ '^pg_toast'
	ORDER BY
	pg_total_relation_size (C .oid) DESC
	;


	-- ------------------------------
	-- show running queries (pre 9.2)
	SELECT procpid, age(clock_timestamp(), query_start), usename, current_query
	FROM pg_stat_activity
	WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
	ORDER BY query_start desc;

	-- show running queries (9.2)
	SELECT pid, age(clock_timestamp(), query_start), usename, query
	FROM pg_stat_activity
	WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
	ORDER BY query_start desc;

	-- kill running query
	SELECT pg_cancel_backend(procpid);

	-- kill idle query
	SELECT pg_terminate_backend(procpid);

	-- 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
No results found