Re: database is bigger after dump/restore - why? (60 GB to 109 GB)

On Sunday, February 27, 2011 12:49:48 am Aleksey Tsalolikhin wrote:

> so looks like it's something low-level, something about how the data is

> stored.

>

> i've installed the "pageinspect" contrib module as Tomas suggested but

> I don't know what to do with it or what to look at. I looked at the

> manual for it but it's totally over my head right now.

>

> What sections of the manual should I read to be able to use this

> module? (there are 2167 pages in the whole Postgres 8.4 manual and I

> don't have time to study the whole thing. :( I just need to study

> enough to understand how to use pageinspect.)

>

> (I'm not a Postgres DBA but I am having to become one to support our

> database.)

>

> Thanks again for the help. I'll be at SCALE 9x tomorrow helping Joe

> Conway and Bruce M and Richard B and company get the word out about

> Postgres.

Well it looks like the problem is not with the raw data. So far you have been using pg_total_relation_size to look at the table. It might be time to look at the individual components by using the other pg_* functions:

http://www.postgresql.org/docs/9.0/interactive/functions-admin.html

pg_table_size(regclass)

pg_indexes_size(regclass)

pg_relation_size(relation regclass, fork text)

In other words try to narrow down where the bloat is occurring.

>

> Best,

> Aleksey

--

Adrian Klaver

adrian.klaver@gmail.com