From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-patches@postgresql.org
Subject: Re: pg_dump additional options for performance
Date: Sat, 26 Jul 2008 12:43:55 -0400
Message-ID: <20080726164355.GH16005@tamriel.snowman.net> (raw)
In-Reply-To: <23540.1217089204@sss.pgh.pa.us>
References: <20080721115602.GG18846@tamriel.snowman.net>
<1216831237.3894.676.camel@ebony.2ndQuadrant>
<1216832987.3894.678.camel@ebony.2ndQuadrant>
<20080724032047.GI18846@tamriel.snowman.net>
<1216885031.3894.739.camel@ebony.2ndQuadrant>
<21687.1216886041@sss.pgh.pa.us>
<1216889809.3894.747.camel@ebony.2ndQuadrant>
<2818.1217027784@sss.pgh.pa.us>
<1217063104.3894.1100.camel@ebony.2ndQuadrant>
<23540.1217089204@sss.pgh.pa.us>
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Fri, 2008-07-25 at 19:16 -0400, Tom Lane wrote:
> >> The key problem is that pg_restore is broken:
>
> > The key capability here is being able to split the dump into multiple
> > pieces. The equivalent capability on restore is *not* required, because
> > once the dump has been split the restore never needs to be.
>
> This argument is nonsense. The typical usage of this capability, IMHO,
> will be
>
> pg_dump -Fc >whole.dump
> pg_restore --schema-before-data whole.dump >before.sql
> pg_restore --data-only whole.dump >data.sql
> pg_restore --schema-after-data whole.dump >after.sql
>
> followed by editing the schema pieces and then loading.
I dislike, and doubt that I'd use, this approach. At the end of the
day, it ends up processing the same (very large amount of data) multiple
times. We have >60G dump files sometimes, and there's no way I'm going
to dump that into a single file first if I can avoid it. What we end up
doing today is --schema-only followed by vi'ing it and splitting it up
by hand, etc, then doing a seperate --data-only dump.
> One reason
> is that this gives you a consistent dump, whereas three successive
> pg_dump runs could never guarantee any such thing.
While this is technically true, in most cases people have control over
the schema bits and would likely be able to ensure that the schema
doesn't change during the time. At that point it's only the data, which
is still done in a transactional way.
> Another reason is that you may well not know when you prepare the
> dump that you will need split output, because the requirement to edit
> the dump is likely to be realized only when you go to load it.
This is a good point. My gut reaction is that, at least in my usage, it
would be more about "if it's larger than a gig, I might as well split it
out, just in case I need to touch something". Honestly, it's rare that
I don't have to make *some* change. Often that's the point of dumping
it out.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 196B) download
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 using the --to and --cc options:
reply via email
To: pgsql-patches@postgresql.org
Cc: sfrost@snowman.net, tgl@sss.pgh.pa.us, simon@2ndquadrant.com
Subject: Re: pg_dump additional options for performance
In-Reply-To: <20080726164355.GH16005@tamriel.snowman.net>
* 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