Re: db size and tables size difference

Isabella Ghiurea <isabella(dot)ghiurea(at)nrc-cnrc(dot)gc(dot)ca> writes:
> SELECT nspname || '.' || relname AS
> "relation",pg_size_pretty(pg_total_relation_size(nspname || '.' || relname))
> AS "s
> ize"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> ORDER BY pg_total_relation_size(nspname || '.' || relname) DESC
> LIMIT 1000;

Hmph ... I can't see anything wrong with that query, so it seems that
we're left with the conclusion that there are files in the database
directory that don't match any entry in the catalogs. AFAIK this'd
only be possible if you'd had a crash while deleting tables or some
similar problem. What you'll need to do next is poke around in the
data directory and see if you can identify any large files that do
not correspond to any entry in pg_class.relfilenode. You should
read the internals docs first, if you're not familiar with this
chapter:
http://www.postgresql.org/docs/8.3/static/storage.html

regards, tom lane