[Eug-lug] Translate file name to Postgres DB name

[Matthew Jarvis]

Matthew Jarvis wrote:
> Rob Hudson wrote:
> 
>> Is there a way, from within Postgresql, to view the filesize usage of
>> its databases and tables?  That might be the best approach and sounds
>> easier than looking at the raw data directory.
>>
> 
> -- select size of tables and indices in random order
> SELECT relname, reltuples, relpages FROM pg_class ;
> 
> -- select size of tables and indices in descending order of size
> SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC ;
> 

I've solved my problem but never figured out how to match the cryptic 
dir names to DB names...

As a followup, here's what I managed to do:

cd /var/lib/psql/data

du -h    (benchmark where what I'm starting with)

Who's the pig?

Start postgres, picking a likely DB as a starting point. As it happens I 
got it right on the first try.

--select size of tables and indices in descending order of size
SELECT relname, relkind, reltuples, relpages FROM pg_class ORDER BY 
relpages DESC ;

I added the 'relkind' field - tells me if it's a table, index or 
whatever. I started using 'relkind' when I couldn't match up the 
'relname' to an actual table.... ahhhh - the lightbulb finally comes on....

This put a name to the offenders. I was looking for tables, but it was 
the indices that were the troublemakers...

Once I got that list, it was a simple matter of

reindex index <troublemaker>;

This locks the index so it's unavailable, but at the time I didn't 
really care, so just went for it...

I just freed up over 15% of total disk space...

Matthew S. Jarvis
IT Manager
Bike Friday - "Performance that Packs."
www.bikefriday.com
541/687-0487 x140
mattj at bikefriday.com