Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save biinari/7090ec0fdce094c6a0ec02df9f27866f to your computer and use it in GitHub Desktop.
Save biinari/7090ec0fdce094c6a0ec02df9f27866f to your computer and use it in GitHub Desktop.
Useful PostgreSQL Queries and Commands
-- show running queries (>= 9.2, < 9.6)
SELECT pid,
age(clock_timestamp(), query_start) AS age,
usename, state, query, waiting,
age(clock_timestamp(), xact_start) AS xact_age
FROM pg_stat_activity
WHERE state != 'idle'
AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY age DESC;
-- show running queries (>= 9.6)
SELECT pid,
age(clock_timestamp(), query_start) AS age,
usename, state, query, wait_event_type, wait_event,
age(clock_timestamp(), xact_start) AS xact_age
FROM pg_stat_activity
WHERE state != 'idle'
AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY age DESC;
-- kill running query
SELECT pg_cancel_backend(procpid);
-- kill idle query
SELECT pg_terminate_backend(procpid);
-- all database users
SELECT usename,usecreatedb,usesuper,usecatupd,userepl,valuntil,useconfig FROM pg_user;
-- all databases and their size on disk
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;
-- all tables and their size on disk
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;
-- list tables with row count (only populated on master)
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup 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,
CASE WHEN sum(heap_blks_hit) != 0
THEN (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit)
ELSE 0
END AS ratio
FROM pg_statio_user_tables;
-- table index usage rates (should not be less than 0.99)
SELECT relname,
CASE WHEN (seq_scan + idx_scan) != 0
THEN 100.0 * idx_scan / (seq_scan + idx_scan)
ELSE 0
END AS percent_of_times_index_used,
n_live_tup AS 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;
-- show locks
SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted
FROM pg_locks l, pg_stat_all_tables t
WHERE l.relation=t.relid
ORDER BY relation ASC;
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment