Dobrica Pavlinušić's random unstructured stuff

PostgreSQL: Revision 5

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

^ Active locks

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