On 2012-Jan-21, at 6:39 PM, Scott Marlowe wrote:
> On Sat, Jan 21, 2012 at 1:37 AM, Dan Charrois <dan...@syz.com> 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, I know -
>> but this is a live database that would problematic to take down to upgrade
>> unless all else fails - especially considering its size if it does need to
>> be rebuilt somehow.
>>
>> Anyway, I'm no stranger to SQL, but new to PostgreSQL - all my SQL
>> administration in the past has been with MySQL. So I'm somewhat bumbling my
>> way through administrative commands trying to solve this - please bear with
>> me.
>>
>> The size of the tables reported by \dt+ add up to around 120 GB. The size
>> of the indexes reported with \di+ adds up to around 15 GB. This is pretty
>> consistent with what I would expect the data to require.
>>
>> The problem is, the disk usage of the pgsql directory where the data is kept
>> (as reported by 'du') comes to 647 GB - significantly more than it should.
>> select pg_database_size('mydatabase') confirms this, returning 690830939920.
>>
>> Vacuuming the tables (full and otherwise) hasn't helped, but then
>> considering how the database is used, I didn't really expect it to. It's
>> strictly a read-only database, with the exception of once a month when it is
>> refreshed by loading new data into newly created tables, and once that is
>> done, vacuum analyzing the new tables, dropping the old tables, then
>> renaming the new ones to have the name of the old ones. Vacuums never claim
>> to recover any space, and the disk usage stays the same.
>>
>> So how do I find out what's eating up all this extra space?
>
> Real quick, if you run pg_database_size(name) for each db, including
> template1 and postgres, what do you get back?
Thanks for your reply, Scott.
My database: 697490323216
postgres: 5537796
template0: 5537796
template1: 5537796
pg_database_size(mydatabase) did return a value consistent with disk usage.
But that value didn't jive with what I expected from summing up the sizes I got
from \dt+
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 really a problem at all - other than my data still
taking up a lot more physical storage than I thought it did. I don't think
there's much I can do about it, but that's much better than assuming it was
related to a problem that needed fixing.
Thanks again!
Dan
--
Syzygy Research & Technology
Box 83, Legal, AB T0G 1L0 Canada
Phone: 780-961-2213
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
