Searching for these SQL commands is tempting every time, whether you have done it before.To avoid collecting these commands from different sources and wasting a lot of time, we have collected them all here to be reachable and available for everyone, anytime. These are the ones we needed and considered to be very useful...
We have selected 5 SQL commands, for everyone who run PostgreSQL in Linux OS.
In our examples we used:
- CentOS 6
- PostgreSQL 9.4
- dbname "netvizura"
- schema "netflow"
Run this commands as a root user but not from the /root directory.
How to watch active PostgreSQL queries in real time?
# watch -n1 "sudo -u postgres psql netvizura -c \"select query_start, query from pg_stat_activity where datname = 'netvizura' and state = 'active';\""
How to watch database size change in real time?
# watch -n1 "sudo -u postgres psql netvizura -c \"SELECT pg_database_size('netvizura')\""
How to find the largest table in database?
# sudo -u postgres psql netvizura -c "SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;"
How to get all tables for specific schema?
# sudo -u postgres psql netvizura
netvizura=# SELECT n.nspname as "Schema", c.relname AS datname, u.usename as "Owner",
(SELECT obj_description(c.oid, 'pg_class')) AS comment
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname='netflow' AND c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
ORDER BY datname ASC;
How to get table sizes for specific schema?
# sudo -u postgres psql netvizura
netvizura=# SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname = 'netflow'
ORDER BY pg_relation_size(C.oid) DESC;
Hopefully this post will make your life easier when searching for these commands next time and will save your valuable time.
