Dobrica Pavlinušić's random unstructured stuff
PostgreSQL: Revision 11
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 # CREATE AGGREGATE array_accum (anyelement) ( sfunc = array_append, stype = anyarray, initcond = '{}' ); # 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 To actually *see* queries you also have to edit `postgresql.conf` to include: .pre stats_command_string = yes .pre ^ Quickly convert database table to SQLite .pre pg_dump --inserts --no-owner --no-privileges --table hr_edu_orgs a3c | grep -v ^SET | sqlite3 var/a3c.sqlite .pre |