Re: Disk space usage discrepancy

Yang Zhang <yanghatespam(at)gmail(dot)com> writes:
> 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):

> select sum(pg_relation_size(oid)) from pg_class where relkind = 'r'

I think you need pg_total_relation_size() if you're doing it that way.
As is, you're missing out indexes and toast tables, and possibly also
secondary forks (I forget if 8.4 had those).

regards, tom lane