Skip to content

Instantly share code, notes, and snippets.

@alex-nexus

alex-nexus/postgres_queries_and_commands.sql

Forked from rgreenjr/postgres_queries_and_commands.sql
Created July 12, 2017 05:25
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save alex-nexus/192f754bdeb9ce0ac5c50fbe16f77816 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save alex-nexus/192f754bdeb9ce0ac5c50fbe16f77816 to your computer and use it in GitHub Desktop.
Download ZIP
Useful PostgreSQL Queries and Commands
Raw
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;
-- 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
@alex-nexus
Copy link
Author

alex-nexus commented Apr 3, 2018

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

@alex-nexus
Copy link
Author

alex-nexus commented May 18, 2018

SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY age(clock_timestamp(), query_start) DESC

SELECT pg_cancel_backend(12575);

SELECT pg_terminate_backend(27524)

@alex-nexus
Copy link
Author

alex-nexus commented Aug 31, 2018 •
edited
Loading

detect locking

SELECT 
  query,
  pg_namespace.nspname AS schemaname,
  pg_class.relname AS tablename,
  pg_locks.mode AS lock_type,
  age(now(),pg_stat_activity.query_start) AS time_running 
FROM pg_class
JOIN pg_locks ON pg_locks.relation = pg_class.oid
JOIN pg_database ON pg_database.oid = pg_locks.database
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
JOIN pg_stat_activity ON pg_stat_activity.pid = pg_locks.pid
WHERE pg_class.relkind = 'r'
ORDER BY time_running

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment