Disk space usage discrepancy

[Yang Zhang <yanghatespam@xxxxxxxxx>]

We're trying to figure out how to account for our disk space
consumption in a database.

$ sudo du -shx /var/lib/postgresql/8.4/main/
1.9G    /var/lib/postgresql/8.4/main/

But when we query Postgresql to find out how much disk space is
actually being used by the various databases, we get a total of under
600MB (the exact query we use for determining this is below, derived
from the example query in the PG documentation):

$ pg-dbspace
psql: FATAL:  database "template0" is not currently accepting connections
  1272446976 rp
   971186176 sfbox
   513794048 yang
    30326784 ch
    16400384 reviewboard
    14958592 pod
     6733824 cbkup
     5767168 redmine_default
     2138112 ibkup
     2138112 foo
     2113536 template1
     2113536 postgres

There are two databases with tablespaces on different volumes than
what /var/lib/postgresql/ is on - their PG-reported consumption is
~2.1GB, and they take up about ~1.5x more on disk:

$ df -h | fgrep /mnt
/dev/sdf              2.0G  1.4G  502M  74% /mnt/box
/dev/sdg              5.0G  2.1G  2.7G  44% /mnt/rp

We're also curious about the 1.5x overhead, but we're mainly not sure
why the rest of the database takes up 3x more than reported, even
discounting pg_xlog (which is for the entire cluster):

$ sudo -i 'du -shx /var/lib/postgresql/8.4/main/*'
1.8G    /var/lib/postgresql/8.4/main/base
816K    /var/lib/postgresql/8.4/main/global
144K    /var/lib/postgresql/8.4/main/pg_clog
28K     /var/lib/postgresql/8.4/main/pg_multixact
192K    /var/lib/postgresql/8.4/main/pg_stat_tmp
80K     /var/lib/postgresql/8.4/main/pg_subtrans
4.0K    /var/lib/postgresql/8.4/main/pg_tblspc
4.0K    /var/lib/postgresql/8.4/main/pg_twophase
4.0K    /var/lib/postgresql/8.4/main/PG_VERSION
129M    /var/lib/postgresql/8.4/main/pg_xlog
4.0K    /var/lib/postgresql/8.4/main/postmaster.opts
4.0K    /var/lib/postgresql/8.4/main/postmaster.pid
0       /var/lib/postgresql/8.4/main/server.crt
0       /var/lib/postgresql/8.4/main/server.key

Any hints?  Thanks in advance.

The queries were using:

$ type pg-dbspace
pg-dbspace is a function
pg-dbspace ()
{
    for db in $(psql -Atc 'select datname from pg_database');
    do
        printf '%12d %s\n' "$(PGDATABASE=$db pg-space total)" "$db";
    done | sort -rn
}

$ type pg-space
pg-space is a function
pg-space ()
{
    local schema=${schema:-${1:-}} flags=;
    case ${schema:-} in
        total)
            local query='select sum(bytes) from schemas' flags=-At
        ;;
        '*')
            local query='select * from tables'
        ;;
        '')
            local query='select * from schemas'
        ;;
        *)
            local query="select * from tables where _schema = '$schema'"
        ;;
    esac;
    psql $flags -c "
    with
      total as (
        select sum(pg_relation_size(oid)) from pg_class where relkind = 'r'
      ),
      basic as (
        select
          n.nspname as _schema,
          relname as _table,
          pg_relation_size(r.oid) as bytes,
          (100*pg_relation_size(r.oid)/(select * from
total))::numeric(4,1) as pct
        from pg_class r inner join pg_namespace n on (n.oid = relnamespace)
        where relkind = 'r'
      ),
      tables as (
        select
          _schema,
          _table,
          bytes,
          lpad(pg_size_pretty(bytes), 9) as size,
          pct
        from basic
        order by bytes desc
      ),
      schemas as (
        select
          _schema,
          sum(bytes) as bytes,
          lpad(pg_size_pretty(sum(bytes)::int), 9) as size,
          sum(pct) as pct
        from basic
        group by _schema
        order by bytes desc
      )
    $query;
  "
}

-- 
Yang Zhang
http://yz.mit.edu/

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general