Useful PostgreSQL Queries

-- idle queries

SELECT now()-xact_start, usename 

FROM pg_stat_activity 

WHERE current_query != '<IDLE>' 

ODER BY xact_start asc 

LIMIT 5;

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

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