Dobrica Pavlinušić's random unstructured stuff

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;

Number of rows in all tables


  nspname AS schemaname,relname,reltuples::int
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  nspname NOT IN ('pg_catalog', 'information_schema') AND
ORDER BY reltuples DESC;

Script to display database size

Script: pg_size

all databases

$ pg_size

all databases, grouped by user

$ pg_size -u

Active locks

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


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

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

Current query activity

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

To actually see queries you also have to edit postgresql.conf to include:

stats_command_string = yes

Quickly convert database table to SQLite

pg_dump --inserts --no-owner --no-privileges --table hr_edu_orgs a3c | grep -v ^SET | sqlite3 var/a3c.sqlite


Upload Files

Click "Browse" to find the file you want to upload. When you click "Upload file" your file will be uploaded and added to the list of attachments for this page.

Maximum file size: 50MB

File Name Author Date Uploaded Size

Save Page As

Enter a meaningful and distinctive title for your page.

Page Title:

Tip: You'll be able to find this page later by using the title you choose.

Page Already Exists

There is already a page named XXX. Would you like to:

Save with a different name:

Save the page with the name "XXX"

Append your text to the bottom of the existing page named: "XXX"

Upload Files

Click "Browse" to find the file you want to upload. When you click "Add file" this file will be added to the list of attachments for this page, and uploaded when you save the page.

Add Tags

Enter a tag and click "Add tag". The tag will be saved when you save the page.