From: Aaron Bono <postgresql@aranya.com>
To: Nicola Mauri <Nicola.Mauri@saga.it>
Cc: pgsql-admin@postgresql.org
Subject: Re: Dump size bigger than pgdata size?
Date: Wed, 21 Jun 2006 09:54:50 -0500
Message-ID: <bf05e51c0606210754o489d2fb7h2c0c6de60f93a2c5@mail.gmail.com> (raw)
In-Reply-To: <OF406B6E88.F5B37D76-ONC1257194.004D5FAC-C1257194.004F9F44@saga.it>
References: <OF406B6E88.F5B37D76-ONC1257194.004D5FAC-C1257194.004F9F44@saga.it>
I would dare guess, and it seems you suspect as well, that the binary data
is why you are not getting very good compression.
You may try dumping the tables individually with *--table=**table* to see
which tables are taking the most space in your dump. Once you find out
which tables are taking the most space, you can check to see what is in
those tables and provide more details on the problem.
Personally I don't use the built in compression in pg_dump but pipe it to
gzip instead (not sure if it makes any difference). See
http://manual.intl.indoglobal.com/ch06s07.html for details.
-Aaron
On 6/21/06, Nicola Mauri <Nicola.Mauri@saga.it> wrote:
>
>
> [sorry if this was previously asked: list searches seem to be down]
>
> I'm using pg_dump to take a full backup of my database using a compressed
> format:
> $ pg_dump -Fc my_db > /backup/my_db.dmp
>
> It produces a 6 GB file whereas the pgdata uses only 5 GB of disk space:
> $ ls -l /backup
> -rw-r--r-- 6592715242 my_db.dmp
> $ du -b /data
> 5372269196 /data
>
> How could it be?
> As far as I know, dumps should be smaller than filesystem datafile since
> they do not store indexes, etc.
>
> Database contains about one-hundred-thousands binary images, some of which
> may be already compressed. So i tried the --compress=0 option but this
> produces a dump that does not fit on my disk (more than 11 GB).
> I'm using postgres 8.1.2 on RHEL4.
>
> So, what can I do to diagnose the problem?
> Thanks in advance,
>
> Nicola
>
>
>
==================================================================
Aaron Bono
President Aranya Software Technologies, Inc.
http://www.aranya.com We take care of your technology needs.
Phone: (816) 695-6071
==================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: postgresql@aranya.com, Nicola.Mauri@saga.it
Subject: Re: Dump size bigger than pgdata size?
In-Reply-To: <bf05e51c0606210754o489d2fb7h2c0c6de60f93a2c5@mail.gmail.com>
References: <OF406B6E88.F5B37D76-ONC1257194.004D5FAC-C1257194.004F9F44@saga.it> <bf05e51c0606210754o489d2fb7h2c0c6de60f93a2c5@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox