Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 80E3D65046E for ; Sun, 27 Jul 2008 22:32:13 -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 47552-04 for ; Sun, 27 Jul 2008 22:32:06 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from tamriel.snowman.net (tamriel.snowman.net [72.66.115.51]) by postgresql.org (Postfix) with ESMTP id 41E2B6504AC for ; Sun, 27 Jul 2008 21:44:28 -0300 (ADT) Received: by tamriel.snowman.net (Postfix, from userid 1000) id B64738407D; Sun, 27 Jul 2008 20:44:26 -0400 (EDT) Date: Sun, 27 Jul 2008 20:44:26 -0400 From: Stephen Frost To: Tom Lane Cc: Simon Riggs , pgsql-patches@postgresql.org Subject: Re: pg_dump additional options for performance Message-ID: <20080728004426.GI16005@tamriel.snowman.net> Mail-Followup-To: Tom Lane , Simon Riggs , pgsql-patches@postgresql.org References: <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> <24327.1217094186@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="+PbGPm1eXpwOoWkI" Content-Disposition: inline In-Reply-To: <24327.1217094186@sss.pgh.pa.us> X-Editor: Vim http://www.vim.org/ X-Info: http://www.snowman.net X-Operating-System: Linux/2.6.25-2-amd64 (x86_64) X-Uptime: 20:27:11 up 13 days, 23:41, 28 users, load average: 0.09, 0.09, 0.05 User-Agent: Mutt/1.5.18 (2008-05-17) 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/269 X-Sequence-Number: 6963 --+PbGPm1eXpwOoWkI Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable * Tom Lane (tgl@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > 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. >=20 > Well, that's easily avoided: just replace the third step by restoring > directly to the target database. >=20 > 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 This would depend on the dump being in the custom format, though I suppose that ends up being true for any usage of these options. I've never really been a fan of the custom format, in large part because it doesn't really buy you all that much and makes changing things more difficult (by having to extract out what you want to change, and then omit it from the restore). I can see some advantage to having the entire dump contained in a single file and still being able to pull out pieces based on before/after. Should we get a binary format which is much faster, I could see myself being more likely to use pg_restore. Same for parallelization or, in my fantasies, the ability to copy schema, tables, indexes, etc, in 'raw' PG format between servers. Worse than having to vi an insanely large file, or split it up to be able to modify the pieces you want, is having to rebuild indexes, especially GIST ones. That's another topic though. Thanks, Stephen --+PbGPm1eXpwOoWkI Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkiNFmoACgkQrzgMPqB3kigtRQCfUXmh+kjiTIu3ncPwZ2iB0ce3 OekAn3s6/9vfEU+3IpqMNDWR7GWHJ4Oy =TT4d -----END PGP SIGNATURE----- --+PbGPm1eXpwOoWkI--