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 2DAC3D8304 for ; Fri, 9 Sep 2005 15:32:34 -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 64296-10 for ; Fri, 9 Sep 2005 18:32:28 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 8DA36D7A82 for ; Fri, 9 Sep 2005 15:32:28 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id j89IWNHV079518 for ; Fri, 9 Sep 2005 18:32:24 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id j89I41Nd058632 for pgsql-admin@postgresql.org; Fri, 9 Sep 2005 18:04:01 GMT (envelope-from news) From: Chris Browne X-Newsgroups: pgsql.admin Subject: Re: Disabling WAL for bulk data loads Date: Fri, 09 Sep 2005 13:14:28 -0400 Organization: cbbrowne Computing Inc Lines: 34 Message-ID: <60mzmm6u4r.fsf@dba2.int.libertyrms.com> References: Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.17 (Jumbo Shrimp, linux) Cancel-Lock: sha1:jgvGryz/nh1gwnMlUAktsEI4348= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=-2.476 required=5 tests=[ALL_TRUSTED=-2.82, AWL=0.344] X-Spam-Level: X-Archive-Number: 200509/100 X-Sequence-Number: 18837 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. The options get a bit better in 8.1, as the COPY command supports some more format options. But you'll DEFINITELY get a big boost if you can format things so COPY can work for you. > 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. Arguably you could turn "fsync = off" in postgresql.conf; there is regrettably the increased risk that a crash would trash the ENTIRE database, not just the recently-loaded data. 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... -- output = ("cbbrowne" "@" "cbbrowne.com") http://cbbrowne.com/info/spreadsheets.html "If roach hotels worked on pointy haired people, Microsoft would die." -- Pete Koren