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

Re: DB Dump Size

From: Thomas Jacob <jacob(at)internet24(dot)de>
To: steve(at)outtalimits(dot)com(dot)au
Cc: Thomas Jacob <jacob(at)internet24(dot)de>, pgsql-admin(at)postgresql(dot)org
Subject: Re: DB Dump Size
Date: 2008-08-15 00:15:25
Message-ID: 20080815001525.GA26208@internet24.de (view raw or flat)
Thread:
Lists: pgsql-admin
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: steveDate: 2008-08-15 01:09:02
Subject: Re: DB Dump Size
Previous:From: steveDate: 2008-08-14 23:26:18
Subject: Re: DB Dump Size

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