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
[EMAIL PROTECTED]
_______________________________________________
EUGLUG mailing list
[email protected]
http://www.euglug.org/mailman/listinfo/euglug
