Forked from rgreenjr/postgres_queries_and_commands.sql
Created
June 25, 2021 06:34
Revisions
-
rgreenjr revised this gist
Aug 12, 2018 . 1 changed file with 2 additions and 2 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,11 +1,11 @@ -- 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; -
rgreenjr renamed this gist
Sep 8, 2015 . 1 changed file with 0 additions and 0 deletions.There are no files selected for viewing
File renamed without changes. -
rgreenjr renamed this gist
Sep 8, 2013 . 1 changed file with 0 additions and 0 deletions.There are no files selected for viewing
File renamed without changes. -
rgreenjr revised this gist
Jun 20, 2013 . 1 changed file with 6 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -42,3 +42,9 @@ 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 -
rgreenjr revised this gist
Jun 20, 2013 . 1 changed file with 0 additions and 4 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -42,7 +42,3 @@ 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; -
rgreenjr revised this gist
Jun 20, 2013 . 1 changed file with 1 addition and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -44,5 +44,5 @@ SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_b FROM pg_statio_user_indexes; -- Dump database on remote host to file pg_dump -U username -h hostname databasename > dump.sql -
rgreenjr revised this gist
Jun 20, 2013 . 1 changed file with 4 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -42,3 +42,7 @@ 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 sideshow_user_production -h hideout.ziften.local sideshow_production > sideshow_production.sql -
rgreenjr revised this gist
Apr 16, 2013 . 1 changed file with 3 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -16,6 +16,9 @@ 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 '<%'; -
rgreenjr revised this gist
Apr 16, 2013 . 1 changed file with 1 addition and 4 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -28,10 +28,7 @@ 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) -
rgreenjr revised this gist
Apr 16, 2013 . 1 changed file with 9 additions and 21 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -23,34 +23,22 @@ select * from pg_stat_activity where current_query not like '<%'; 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; -
rgreenjr revised this gist
Apr 16, 2013 . 1 changed file with 7 additions and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,9 +1,15 @@ -- show running queries (pre 9.2) SELECT procpid, age(query_start, clock_timestamp()), 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(query_start, clock_timestamp()), 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); -
rgreenjr revised this gist
Oct 6, 2012 . 1 changed file with 3 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -7,6 +7,9 @@ ORDER BY query_start desc; -- kill running query SELECT pg_cancel_backend(procpid); -- kill idle query SELECT pg_terminate_backend(procpid); -- all database users select * from pg_stat_activity where current_query not like '<%'; -
rgreenjr revised this gist
Oct 3, 2012 . 1 changed file with 9 additions and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -36,4 +36,12 @@ SELECT 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; -
rgreenjr revised this gist
Oct 3, 2012 . 1 changed file with 19 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -18,3 +18,22 @@ 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; -
rgreenjr created this gist
Sep 5, 2012 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,20 @@ -- show running queries SELECT procpid, age(query_start, clock_timestamp()), usename, current_query FROM pg_stat_activity WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc; -- kill running query SELECT pg_cancel_backend(procpid); -- 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;