Useful PostgreSQL Queries and Commands

postgres_queries_and_commands.sql

-- Show queries running since 2 minutes (Sometimes, remove waiting)
SELECT now() - query_start as "runtime", usename, datname, waiting, state, query
FROM  pg_stat_activity
WHERE now() - query_start > '2 minutes'::interval
ORDER BY runtime 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

-- Kill Inactive Connections                                                                                         -- 
WITH inactive_connections AS (
                        SELECT
                            pid,
                            rank() over (partition by client_addr order by backend_start ASC) as rank
                        FROM 
                            pg_stat_activity
                        WHERE
                            -- Exclude the thread owned connection (ie no auto-kill)
                            pid <> pg_backend_pid( )
                        AND
                            -- Exclude known applications connections
                            application_name !~ '(?:psql)|(?:pgAdmin.+)'
                        AND
                            -- Include connections to the same database the thread is connected to
                            datname = current_database() 
                        AND
                            -- Include connections using the same thread username connection
                            usename = current_user 
                        AND
                            -- Include inactive connections only
                            state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
                        AND
                            -- Include old connections (found with the state_change field)
                            current_timestamp - state_change > interval '2 minutes' 
                    )
                    SELECT
                        pg_terminate_backend(pid)
                    FROM
                        inactive_connections 
                    WHERE
                        rank > 1 
                                                                                              
                                                                                               
-- Check the size (as in disk space) of each table:
SELECT nspname || '.' || 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;