Dobrica Pavlinušić's random unstructured stuff
PostgreSQL: Revision 8
SQL snippets for PostgreSQL

{toc: }

^ Size of transaction log

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

^ Size of tables in current database

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

^ Script to display database size

Script: {file: pg_size}

^^ all databases

.pre
$ pg_size
.pre

^^ all databases, grouped by user

.pre
$ pg_size -u
.pre

^ Active locks

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

^ Graphics

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

.pre
# 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)
.pre

^ Current query activity

.pre
select datid,datname,procpid,current_query,query_start from pg_stat_activity where current_query not like '<IDLE>' ;
.pre