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

On 27 Feb 2011, at 9:49, Aleksey Tsalolikhin wrote:

> Database versions are identical:
> A: PostgreSQL 8.4.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc
> (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
> B: PostgreSQL 8.4.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc
> (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
>
>
> Character encoding of the source and target databases are identical:
> UTF8. (As reported by "psql -l".)

I noticed in your table definition that you seem to store timestamps in text-fields. Restoring those from text-fields shouldn't make any difference, but perhaps your locales are set up differently between the machines and cause some type of conversion to take place?
I know, that's a pretty wild guess.

> When I pg_dump the 50 GB table, I get a 40 GB file.
>
> When I pg_dump the 100 GB table, I get a 40 GB file.

I think the possible causes of the problem being with the database have been rather exhausted by now. Maybe the difference is in how the OS was set-up on each system. So, more questions:

What type of file-system are you using on each database (for the table in question)?
Are these filesystems configured identically, or does one perhaps have a different block-size than the other?
Is it set up as a raid array? If so, which raid-level?
Are your dumps going to that same file-system, or to a different one?

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4d6a2d1b11731601256477!