Re: DB Dump Size

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