Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8AF4964FFC8 for ; Sat, 26 Jul 2008 14:43:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 62844-09 for ; Sat, 26 Jul 2008 14:43:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id AFFCF64FD37 for ; Sat, 26 Jul 2008 14:43:24 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m6QHh6qC024328; Sat, 26 Jul 2008 13:43:07 -0400 (EDT) To: Stephen Frost cc: Simon Riggs , pgsql-patches@postgresql.org Subject: Re: pg_dump additional options for performance In-reply-to: <20080726164355.GH16005@tamriel.snowman.net> 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> <20080726164355.GH16005@tamriel.snowman.net> Comments: In-reply-to Stephen Frost message dated "Sat, 26 Jul 2008 12:43:55 -0400" Date: Sat, 26 Jul 2008 13:43:06 -0400 Message-ID: <24327.1217094186@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200807/260 X-Sequence-Number: 6954 Stephen Frost writes: > * Tom Lane (tgl@sss.pgh.pa.us) wrote: >> 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. Well, that's easily avoided: just replace the third step by restoring directly to the target database. pg_restore --schema-before-data whole.dump >before.sql edit before.sql pg_restore --schema-after-data whole.dump >after.sql edit after.sql psql -f before.sql target_db pg_restore --data-only -d target_db whole.dump psql -f after.sql target_db regards, tom lane