Helpful_Postgres_Queries_Commands.sql

/*	[+] 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;


/*  [+] FIND CARDINALITY OF INDEX  */
SELECT schema_name, object_name, object_type, CARDINALITY, pages
FROM ( SELECT pg_catalog.pg_namespace.nspname AS schema_name, relname AS object_name, relkind AS object_type, reltuples AS CARDINALITY, relpages AS pages
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON
relnamespace = pg_catalog.pg_namespace.oid ) t
WHERE schema_name NOT LIKE 'pg_%'
AND schema_name <> 'information_schema'
--and schema_name = '$schema_name'
--and object_name = '$object_name'
ORDER BY pages DESC, schema_name, object_name;

/*  [+] Check Connection Type and Count  */
SELECT count(*), state
FROM pg_stat_activity
GROUP BY 2;
    

/*  [+] KILL ALL RUNNING CONNECTIONS OF A CURRENT DATABASE  */
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()
AND pid <> pg_backend_pid();


/*  [+] 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;


/*  [+] GET ALL TABLE SIZE  */
SELECT schema_name, relname, pg_size_pretty(table_size) AS SIZE, table_size
FROM ( SELECT pg_catalog.pg_namespace.nspname AS schema_name, relname, pg_relation_size(pg_catalog.pg_class.oid) AS table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON
relnamespace = pg_catalog.pg_namespace.oid ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;


/*  [+] GET SCHEMA S SIZES  */
SELECT schema_name, pg_size_pretty(sum(table_size) :: bigint), ( sum(table_size) / pg_database_size(current_database()) ) * 100
FROM ( SELECT pg_catalog.pg_namespace.nspname AS schema_name, pg_relation_size(pg_catalog.pg_class.oid) AS table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON
relnamespace = pg_catalog.pg_namespace.oid ) t
GROUP BY schema_name
ORDER BY schema_name


/*  [+] QUERY WHICH HOLDS THE LOCK IN POSTGRES  */
SELECT pid, usename, pg_blocking_pids(pid) AS blocked_by, query AS blocked_query
FROM pg_stat_activity
WHERE CARDINALITY(pg_blocking_pids(pid)) > 0;

/*  [+] 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