Useful PostgreSQL Queries and Commands

postgres_queries_and_commands.sql

-- 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);

-- 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;

-- Show size of one table and it's indexes
select
    pg_size_pretty(pg_table_size(oid)) table_size
    ,pg_size_pretty(pg_indexes_size(oid)) indexes_size
    ,pg_size_pretty(pg_total_relation_size(oid))
from pg_class
where relname = 'TABLENAME';

-- 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

-- return row sizes of tables
SELECT relname as "Table"
     , to_char(reltuples::bigint, '9,999,999,999,999') AS "~Rows"
  FROM pg_class
 WHERE relname NOT LIKE 'pg%'
   AND relkind = 'r'
ORDER BY 2 DESC, 1;

-- create a view of locks
CREATE VIEW lock_monitor AS(
SELECT COALESCE(blockingl.relation::regclass::text,blockingl.locktype) AS locked_item
     , now() - blockeda.query_start                                    AS waiting_duration
     , blockeda.pid                                                    AS blocked_pid
     , blockeda.query                                                  AS blocked_query
     , blockedl.mode                                                   AS blocked_mode
     , blockinga.pid                                                   AS blocking_pid
     , blockinga.query                                                 AS blocking_query
     , blockingl.mode                                                  AS blocking_mode
  FROM pg_catalog.pg_locks blockedl
  JOIN pg_stat_activity blockeda
    ON blockedl.pid = blockeda.pid
  JOIN pg_catalog.pg_locks blockingl
    ON (((       blockingl.transactionid = blockedl.transactionid)
         OR (    blockingl.relation = blockedl.relation
             AND blockingl.locktype = blockedl.locktype))
        AND blockedl.pid != blockingl.pid)
  JOIN pg_stat_activity blockinga
    ON blockingl.pid = blockinga.pid
   AND blockinga.datid = blockeda.datid
 WHERE NOT blockedl.granted
   AND blockinga.datname = current_database()
);
-- select * from lock_monitor;

-- select open locks
  SELECT a.datname,
         c.relname,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query, 
         a.query_start,
         age(now(), a.query_start) AS "age", 
         a.pid 
    FROM  pg_stat_activity a
     JOIN pg_locks         l ON l.pid = a.pid
     JOIN pg_class         c ON c.oid = l.relation
    ORDER BY a.query_start;