X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 757B9D87D7 for ; Fri, 9 Sep 2005 16:23:40 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02397-07 for ; Fri, 9 Sep 2005 19:23:35 +0000 (GMT) Received: from mail.libertyrms.com (unknown [207.219.45.62]) by svr1.postgresql.org (Postfix) with ESMTP id B18EED86C3 for ; Fri, 9 Sep 2005 16:23:35 -0300 (ADT) Received: from dba6.int.libertyrms.com ([10.1.3.26] helo=dba6) by mail.libertyrms.com with esmtp (Exim 4.22) id 1EDoTI-0002hU-9g for pgsql-admin@postgresql.org; Fri, 09 Sep 2005 15:23:36 -0400 Received: by dba6 (Postfix, from userid 1265) id 25C3AE4009; Fri, 9 Sep 2005 15:23:36 -0400 (EDT) Date: Fri, 9 Sep 2005 15:23:36 -0400 To: pgsql-admin@postgresql.org Subject: Re: Disabling WAL for bulk data loads Message-ID: <20050909192335.GG13854@dba6.int.libertyrms.com> Mail-Followup-To: pgsql-admin@postgresql.org References: <60mzmm6u4r.fsf@dba2.int.libertyrms.com> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="jaTU8Y2VLE5tlY1O" Content-Disposition: inline In-Reply-To: <60mzmm6u4r.fsf@dba2.int.libertyrms.com> User-Agent: Mutt/1.5.10i From: tgoodair@ca.afilias.info (Tim Goodaire) X-SA-Exim-Mail-From: tgoodair@ca.afilias.info X-SA-Exim-Scanned: No; SAEximRunCond expanded to false X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.531 required=5 tests=[FORGED_RCVD_HELO=0.05, INFO_TLD=0.481] X-Spam-Level: X-Archive-Number: 200509/101 X-Sequence-Number: 18838 --jaTU8Y2VLE5tlY1O Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Fri, Sep 09, 2005 at 01:14:28PM -0400, Chris Browne wrote: > kevinrkeith@hotmail.com ("Kevin Keith") writes: > > I am coming from an Oracle background - which in the case of bulk data > > loads there were several options I had where I could disable writing > > to the redo log to speed up the bulk data load (i.e. direct load, set > > the user session in no archive logging, set the affected tables to > > have no logging). > > > > I know the COPY command is one option - however it appears the data > > would need to be in formatted file in order to use it correct? I want > > to avoid writing a new file out for the COPY command and loading that. >=20 > The options get a bit better in 8.1, as the COPY command supports some > more format options. >=20 > But you'll DEFINITELY get a big boost if you can format things so COPY > can work for you. >=20 > > What other options does Postgres 7.4 provide which would allow data > > loads to bypass writing to the WAL? I don't need to have this enabled > > - because in the event of a database crash, I would simply reload the > > data from the source files as recovery. >=20 > Arguably you could turn "fsync =3D off" in postgresql.conf; there is > regrettably the increased risk that a crash would trash the ENTIRE > database, not just the recently-loaded data. Actually, it may trash all of the databases on that cluster! >=20 > It's somewhat regrettable that you can't turn off logging just for a > single transaction's context; that would probably be the ideal for > this... > --=20 > output =3D ("cbbrowne" "@" "cbbrowne.com") > http://cbbrowne.com/info/spreadsheets.html > "If roach hotels worked on pointy haired people, Microsoft would die." > -- Pete Koren >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >=20 --=20 Tim Goodaire 416-673-4126 tgoodair@ca.afilias.info Database Administrator, Afilias Canada Corp. --jaTU8Y2VLE5tlY1O Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFDIeE3W1uYbjhxf+0RAs4+AJ9m0/tRzX9x+9Mho+yZqqVJZOyfqwCglfLE 9RTiw3cPkoh0OhqUbPobWwo= =p0pa -----END PGP SIGNATURE----- --jaTU8Y2VLE5tlY1O--