Re: [GENERAL] Database takes up MUCH more disk space than it should

2012-01-22 Thread Adrian Klaver
On Sunday, January 22, 2012 12:26:22 am Dan Charrois wrote: > > Thank you Adrian. I think that you seem to have found the trouble. For > most of the TOAST tables I have, oid=relfilenode, but not for that one. I > found the table that has reltoastrelid linking to that huge TOAST table.. > and i

Re: [GENERAL] Database takes up MUCH more disk space than it should

2012-01-22 Thread Tom Lane
Dan Charrois writes: > It's too bad \dt+ doesn't take into account the related TOAST table > too - if it had, I would have expected that much disk space right from > the get-go, and never thought twice about it. FWIW, that's been changed as of 9.1. regards, tom lane --

Re: [GENERAL] Database takes up MUCH more disk space than it should

2012-01-22 Thread John R Pierce
On 01/22/12 12:32 AM, Dan Charrois wrote: It looks like a large TOAST table, not reported by \dt+ was the biggest culprit, but I thought it was orphaned. Due to some help by Adrian Klaver, it looks like I was mistaken - it was in fact used by one of my tables. So it looks like there wasn't r

Re: [GENERAL] Database takes up MUCH more disk space than it should

2012-01-22 Thread Dan Charrois
On 2012-Jan-21, at 6:39 PM, Scott Marlowe wrote: > On Sat, Jan 21, 2012 at 1:37 AM, Dan Charrois wrote: >> Hi everyone. I'm currently in the situation of administering a rather large >> PostgreSQL database which for some reason seems to be even much larger than >> it should be. >> >> I'm cur

Re: [GENERAL] Database takes up MUCH more disk space than it should

2012-01-22 Thread Dan Charrois
>> >> SELECT relname, pg_size_pretty(relpages::bigint * 8 *1024) AS size, CASE >> WHEN relkind = 't' THEN (SELECT pgd.relname FROM pg_class pgd WHERE >> pgd.relfilenode::text = SUBSTRING(pg.relname FROM 10)) ELSE (SELECT >> pgc.relname FROM pg_class pgc WHERE pg.reltoastrelid = pgc.relfilenode) >>

Re: [GENERAL] Database takes up MUCH more disk space than it should

2012-01-21 Thread Scott Marlowe
On Sat, Jan 21, 2012 at 1:37 AM, Dan Charrois wrote: > Hi everyone.  I'm currently in the situation of administering a rather large > PostgreSQL database which for some reason seems to be even much larger than > it should be. > > I'm currently running version 8.4.5 - not the latest and greatest,

Re: [GENERAL] Database takes up MUCH more disk space than it should

2012-01-21 Thread Adrian Klaver
On Saturday, January 21, 2012 12:37:17 am Dan Charrois wrote: > Hi everyone. I'm currently in the situation of administering a rather > large PostgreSQL database which for some reason seems to be even much > larger than it should be. > > I'm currently running version 8.4.5 - not the latest and gr

[GENERAL] Database takes up MUCH more disk space than it should

2012-01-21 Thread Dan Charrois
Hi everyone. I'm currently in the situation of administering a rather large PostgreSQL database which for some reason seems to be even much larger than it should be. I'm currently running version 8.4.5 - not the latest and greatest, I know - but this is a live database that would problematic t