Forked from rgreenjr/postgres_queries_and_commands.sql
Last active
April 24, 2019 19:28
-
-
Save cooljl31/52348c1d12cc9cde36b4327ec7d3f7fc to your computer and use it in GitHub Desktop.
Useful PostgreSQL Queries and Commands
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 | |
SELECT pid, now() - query_start as "runtime", usename, datname, waiting, state, query | |
FROM pg_stat_activity | |
WHERE now() - query_start > '2 minutes'::interval and state = 'active' | |
ORDER BY runtime DESC; | |
Tables and views used by a given view: | |
-- with recursive view_tree(parent_schema, parent_obj, child_schema, child_obj, ind, ord) as | |
( | |
select vtu_parent.view_schema, vtu_parent.view_name, | |
vtu_parent.table_schema, vtu_parent.table_name, | |
'', array[row_number() over (order by view_schema, view_name)] | |
from information_schema.view_table_usage vtu_parent | |
where vtu_parent.view_schema = '<SCHEMA NAME>' and vtu_parent.view_name = '<VIEW NAME>' | |
union all | |
select vtu_child.view_schema, vtu_child.view_name, | |
vtu_child.table_schema, vtu_child.table_name, | |
vtu_parent.ind || ' ', | |
vtu_parent.ord || (row_number() over (order by view_schema, view_name)) | |
from view_tree vtu_parent, information_schema.view_table_usage vtu_child | |
where vtu_child.view_schema = vtu_parent.child_schema | |
and vtu_child.view_name = vtu_parent.child_obj | |
) | |
select tree.ind || tree.parent_schema || '.' || tree.parent_obj | |
|| ' depends on ' || tree.child_schema || '.' || tree.child_obj txt, tree.ord | |
from view_tree tree | |
order by ord; | |
-- Check the size (as in disk space) of all databases: | |
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner, | |
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') | |
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) | |
ELSE 'No Access' | |
END AS SIZE | |
FROM pg_catalog.pg_database d | |
ORDER BY | |
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') | |
THEN pg_catalog.pg_database_size(d.datname) | |
ELSE NULL | |
END; | |
-- 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment