SHARE
TWEET

Untitled

a guest Mar 21st, 2019 46 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- show running queries (pre 9.2)
  2. SELECT procpid, age(clock_timestamp(), query_start), usename, current_query
  3. FROM pg_stat_activity
  4. WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
  5. ORDER BY query_start desc;
  6.  
  7. -- show running queries (>=9.2)
  8. SELECT pid, age(clock_timestamp(), query_start), usename, query
  9. FROM pg_stat_activity
  10. WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
  11. ORDER BY query_start desc;
  12.  
  13. -- kill running query
  14. SELECT pg_cancel_backend(procpid);
  15.  
  16. -- kill idle query
  17. SELECT pg_terminate_backend(procpid);
  18.  
  19. -- vacuum command
  20. VACUUM (VERBOSE, ANALYZE);
  21.  
  22. -- detailed info on running / idle queries
  23. SELECT * FROM pg_stat_activity WHERE query NOT LIKE '<%';
  24.  
  25. -- all database users
  26. SELECT
  27.     *
  28. FROM
  29.     pg_user;
  30.  
  31. -- all databases and their sizes
  32. SELECT
  33.     datname,
  34.     pg_size_pretty(pg_database_size(datname))
  35. FROM
  36.     pg_database
  37. ORDER BY
  38.     pg_database_size(datname) DESC;
  39.  
  40. -- all tables and their size, ordered by schema then size
  41. SELECT
  42.     schemaname || '.' || tablename AS "tableName",
  43.     pg_size_pretty(pg_table_size(schemaname || '.' || tablename)) AS "tableSize"
  44. FROM
  45.     pg_tables
  46. ORDER BY
  47.     schemaname ASC,
  48.     pg_table_size(schemaname || '.' || tablename) DESC;
  49.  
  50. -- cache hit rates (should not be less than 0.99)
  51. 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
  52. FROM pg_statio_user_tables;
  53.  
  54. -- table index usage rates (should not be less than 0.99)
  55. SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
  56. FROM pg_stat_user_tables
  57. ORDER BY n_live_tup DESC;
  58.  
  59. -- how many indexes are in cache
  60. 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
  61. FROM pg_statio_user_indexes;
  62.  
  63. -- Dump database on remote host to file
  64. $ pg_dump -U username -h hostname databasename > dump.sql
  65.  
  66. -- Import dump into existing database
  67. $ psql -d newdb -f dump.sql
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top