Dobrica Pavlinušić's random unstructured stuff
PostgreSQL: Revision 6

SQL snippets for PostgreSQL



Size of transaction log

select sum((pg_stat_file('pg_xlog/' || file)).size)
from pg_ls_dir('pg_xlog') as file
where file ~ '^[0-9A-F]';

Size of tables in current database

select relname,pg_relation_size(oid) as size
from pg_class
where relkind = 'r' and relname not like 'pg_%' order by size desc;

Active locks

select * from pg_locks where pid not in (select procpid from pg_stat_activity);

Graphics

Taken from http://lca2007.linux.org.au/att_data/Miniconfs(2f)PostgreSQL/attachments/advanced.pdf

# select d, count(*),
       array_to_string(array_accum('+'::text),'') as graph
       from hits group by 1 order by 1 asc;
     d       | count | graph
------------+-------+----------
 2007-01-13 |      8 | ++++++++
 2007-01-14 |      7 | +++++++
 2007-01-15 |      4 | ++++
(3 rows)