Re: Unexpectedly high disk space usage

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Tue, Nov 6, 2012 at 10:49 AM, Lists <lists(at)benjamindsmith(dot)com> wrote:
>> I followed your example, the result is at the bottom. Based on this it would
>> seem that there are 3-4 databases that seem to be the culprit. How could I
>> get more depth/detail on what specifically is the problem?

> If you have installed the contrib modules (oid2name specifically), you
> can use that to get the name of the bloated database:
> oid2name | fgrep 607471

Or, if you didn't install contrib, try

select datname from pg_database where oid = 607471

> If the name of the database doesn't give you any insight, then look
> for large files in the directory base/607471 that whose names all
> start with the same digits and use oid2name to get the names of the
> relations for those files.

> oid2name -d <name of database> -o <base name of large files>

For this you can try

select relname from pg_class where relfilenode = <whatever>

Or let the database do the work:

select relname, pg_relation_size(oid) from pg_class order by 2 desc;

regards, tom lane