MIME-Version: 1.0
Date: Fri, 15 Aug 2008 11:09:02 +1000
From: <steve@outtalimits.com.au>
To: Thomas Jacob <jacob@internet24.de>
Cc: pgsql-admin@postgresql.org
Subject: Re: DB Dump Size
In-Reply-To: <20080815001525.GA26208@internet24.de>
References: <326684d8c021f4afb82f8e921f9e0c40@127.0.0.1>
	<20080814083003.GA456@internet24.de>
	<02f4fac97d67bb4a1cee604724c8d62d@127.0.0.1>
	<20080815001525.GA26208@internet24.de>
Message-ID: <e7a17e8b398d35d87c0b50ce9eae7b4d@127.0.0.1>
User-Agent: RoundCube Webmail/0.2a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 8bit

Nice, that has cleared it up.

I am on 8.1 also.

On my test box, a standard dump took 6m 26sec & a -Fc dump took 11min 2sec.
That's not a great difference, but the size difference is quite noticeable.

Thanks for your help.
Steve.

On Fri, 15 Aug 2008 02:15:25 +0200, Thomas Jacob <jacob@internet24.de>
wrote:
> On Fri, Aug 15, 2008 at 09:26:18AM +1000, steve@outtalimits.com.au wrote:
>> G'day,
>> 
>> I am just running a straight pg_dump with no options.
>> With the -Fc option the DB size is reduced to about: 700MB
>> 
>> Is the -Fc a compressed format? Are there any limitations or side
> effects
>> to using this output, is it slower?? etc etc. Can this output be
> restored
>> via the normal method of: psql dbname < sql.dump
> 
> Yes -Fc is a compressed format. From the man page of pg_dump:
> 
> "custom: Output a custom archive suitable for input into pg_restore.
> This is the most flexible format in that it allows reordering of loading 
> data as well as  object  definitions. This format is also compressed by
> default."
> 
> Whether or not the dump takes less or more time probably depends
> on your spare CPU vs your spare IO capacity, in general I'd
> say it isn't slower, given today's CPU, but I haven't measured
> this. 
> 
> For restoring the DB you need to run the dump through pg_restore first,
> which gives you various dials to control what to restore
> and how, and then feed it into psql as usual. This gives you
> more flexibility than with the plain SQL dump. Also, you
> can recreate a plain SQL dump with pg_restore should
> you require one.
> 
> One drawback could be that if you lose your DB and something 
> also damages parts of your dumps, a compressed format might mean you
can't
> restore any data at all, vs. at least some data
> with plain SQL dump. But that's not a very likely
> scenario, is it? ;-)
> 
> Also, you can't always restore from an -Fc dump, if
> your pg_restore version is much older than the one that
> particular dump was created with. For instance, trying
> to restore an 8.3.3 dump  with a 7.4.19 pg_restore I
> get:
> 
> "pg_restore: [archiver] unsupported version (1.10) in file header"
> 
> When I use a 8.1.11 pg_restore, it seems to work fine.
> But that's hardly a suprising result.
> 
>   Thomas