X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id DE49B9FA42B for ; Wed, 21 Jun 2006 11:55:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 03191-02 for ; Wed, 21 Jun 2006 11:54:55 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.193]) by postgresql.org (Postfix) with ESMTP id DEA5A9FA19B for ; Wed, 21 Jun 2006 11:54:55 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id s13so131924wxc for ; Wed, 21 Jun 2006 07:54:54 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=Sehf7KiCVh7mgULb3Wf3NGABFpIYc4aXB8K6XPAIxSubSo0JIGZ3zlWX1nzW5HuWg1HU59L+Os24ERudHY6fY1Z5bNYaXVSFWfMmNyvOzdXEiTgR+oQE/a53+6qOrD+0/2TGrFvf2R1o3+WwuXNBfMNoEgPQ37Olq2HCdbB73uo= Received: by 10.70.91.12 with SMTP id o12mr1232302wxb; Wed, 21 Jun 2006 07:54:50 -0700 (PDT) Received: by 10.70.66.15 with HTTP; Wed, 21 Jun 2006 07:54:50 -0700 (PDT) Message-ID: Date: Wed, 21 Jun 2006 09:54:50 -0500 From: "Aaron Bono" To: "Nicola Mauri" Subject: Re: Dump size bigger than pgdata size? Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1787_19489712.1150901690662" References: X-Google-Sender-Auth: 2c44a27ea412327c X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/173 X-Sequence-Number: 22135 ------=_Part_1787_19489712.1150901690662 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 ================================================================== ------=_Part_1787_19489712.1150901690662 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
================================================================== ------=_Part_1787_19489712.1150901690662--