From: Jim C. Nasby <jnasby@pervasive.com>
To: Kevin Keith <kevinrkeith@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Disabling WAL for bulk data loads
Date: Fri, 9 Sep 2005 12:55:43 -0500
Message-ID: <20050909175543.GH7630@pervasive.com> (raw)
In-Reply-To: <BAY108-F115459FF84EB8232582111B9980@phx.gbl>
References: <BAY108-F115459FF84EB8232582111B9980@phx.gbl>
On Fri, Sep 09, 2005 at 11:02:33AM -0500, Kevin Keith wrote:
> 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.
Well, COPY will give you the best performance. It's also fairly flexable
when it comes to formatting. It shouldn't be difficult to get Oracle's
export tool to generate data that COPY can load.
> 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.
At this point, your only option to change fsync to off in
postgresql.conf and reload the postmaster (kill -HUP). There are plans
to improve support for non-logged tables, but I don't think any of it is
in 8.1.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: jnasby@pervasive.com, kevinrkeith@hotmail.com
Subject: Re: Disabling WAL for bulk data loads
In-Reply-To: <20050909175543.GH7630@pervasive.com>
References: <BAY108-F115459FF84EB8232582111B9980@phx.gbl> <20050909175543.GH7630@pervasive.com>
* 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