From: Tom Lane <tgl@sss.pgh.pa.us>
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 13:00:22 -0400
Message-ID: <11486.1150909222@sss.pgh.pa.us> (raw)
In-Reply-To: <OF406B6E88.F5B37D76-ONC1257194.004D5FAC-C1257194.004F9F44@saga.it>
References: <OF406B6E88.F5B37D76-ONC1257194.004D5FAC-C1257194.004F9F44@saga.it>
"Nicola Mauri" <Nicola.Mauri@saga.it> writes:
> 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:
> ...
> Database contains about one-hundred-thousands binary images, some of which
> may be already compressed.
Are those stored as bytea fields, or large objects, or what?
I can easily imagine bytea inflating to be larger in a dump than it is
on disk, since the text format for non-ASCII byte values looks like "\\nnn"
ie 5 bytes for only one byte on disk. Assuming that ASCII bytes make up
about half of the data, the average expansion factor would be about 2.5x.
Compression of the dump text would buy back some of this bloat but
probably not all.
This could be avoided by using COPY BINARY format, but I don't see any
very nice way to do that in the context of pg_dump --- it needs to
intermix COPY data with SQL commands ...
regards, tom lane
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: tgl@sss.pgh.pa.us, Nicola.Mauri@saga.it
Subject: Re: Dump size bigger than pgdata size?
In-Reply-To: <11486.1150909222@sss.pgh.pa.us>
References: <OF406B6E88.F5B37D76-ONC1257194.004D5FAC-C1257194.004F9F44@saga.it> <11486.1150909222@sss.pgh.pa.us>
* 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