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

On Fri, Mar 4, 2011 at 4:45 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On Friday, March 04, 2011 2:03:23 pm Aleksey Tsalolikhin wrote:
>> On Fri, Mar 4, 2011 at 7:53 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>> >What is the data being stored in the table?
>>
>> For the main part, it's an XML file, we store it in the third field.
>> Our XML files are 13KB - 48 KB in length.
>>
>
> And you are positive that field has not had its storage changed to something
> other than EXTENDED?
Quite.  I just double checked on both servers at it is EXTENDED for
everything but
the timestamp (5th field) which is PLAIN (on both servers).



>> From my observation, when the table was under 30 GB in size,
>> TOAST compression worked fine; when it got above 35 GB in size,
>> now TOAST compression is not working.
>
> More questions:)
> How do you know that?
> I thought the size problem only came to light when you tried to set up another
> server.
> Was there some sort of monitoring going on previous to setting up the new
> server?
> Anything else happen around that time?

OK, I have to withdraw my "observation".  Let me refine it (which I am
afraid makes
it less useful):  I've done pg_dump/restore of the database earlier, when it was
smaller (< 30 GB) and did not notice such a doubling in size; I don't think it
occurred.  Certainly the last time we moved the database from the DR back to
primary site, it did not double in size from 1 GB (it was around 1 GB then).

Here is what I did see:  we've had Slony replication running for a
while (over half
a year) from Primary to DR; and file system utilization on Primary and
DR was about
the same.  (around 75%).

Also, I had done some pg_dump's / pg_restore's from DR to Dev and Stage, and
the database size was about 1:1.

But most recently, I shut down replication, and modified the cluster config
(added several tables and sequences; plus some tables were modified so
I wanted to get a clean start on replicating them).  I removed the slony schemas
and re-created a Slony replication set -- my filesystem was 75% full
on the master,
and it hit 100% on the slave!  So I lost my slave!

Then I tried pg_dump/pg_restore and noticed the same thing, that one table
doubles in size.

Last time I did a full Slony re-sync like this was around 30 GB.  Now
we're up to
40-50 GB and hit the 1:2 factor.

I can't think of anything else happening around this time...

I'm going to try splitting the pg_dump file (40 GB) in half, and load the 20 GB
file, and see how much space it takes up in the database, I'm curious if I can
replicate the 1:2 swelling with this smaller table.

Aleksey