PostgreSQL Commands
Useful SQL for DBAs
List all tables in the current database:
=> SELECT tablename FROM pg_tables where tablename not like 'pg_%';
Limit rows returned as in where rownum <= 10
=> select tablename from pg_tables limit 10;
List tables in one schema
=> select tablename from pg_tables where tableowner='qedit';
List running queries
=> select * from pg_stat_activity;
List database activity
=> select * from pg_stat_database;
List table IO
=> select * from pg_statio_all_tables;
List largest tables
=> SELECT relname, relpages * 8 as kilobytes FROM pg_class
=> WHERE relname not like 'pg_%' order by relpages DESC;
Show row counts for all tables
=> ANALYZE;
=> SELECT n.nspname, relname, reltuples
FROM pg_class c, pg_namespace n
WHERE c.relnamespace=n.oid
AND relkind='r'
AND NOT n.nspname ~ '^pg_'
ORDER BY 1,2;