Skip site navigation (1) Skip section navigation (2)

Re: DB Dump Size

From: <steve(at)outtalimits(dot)com(dot)au>
To: Thomas Jacob <jacob(at)internet24(dot)de>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: DB Dump Size
Date: 2008-08-15 01:09:02
Message-ID: e7a17e8b398d35d87c0b50ce9eae7b4d@127.0.0.1 (view raw or flat)
Thread:
Lists: pgsql-admin
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(at)internet24(dot)de>
wrote:
> On Fri, Aug 15, 2008 at 09:26:18AM +1000, steve(at)outtalimits(dot)com(dot)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

In response to

Responses

pgsql-admin by date

Next:From: Tino SchwarzeDate: 2008-08-15 10:24:32
Subject: Re: DB Dump Size
Previous:From: Thomas JacobDate: 2008-08-15 00:15:25
Subject: Re: DB Dump Size

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group