Tuning PostgreSQL for bulk imports

[Kirk Strauser]

I have an hourly job that converts our legacy Foxpro database into
PostgreSQL tables so that our web applications, etc. can run reports off
the data in a reasonable amount of time.  Believe it or not, this has been
running perfectly in production for over a year.  The only problem I'd
still like to solve is that loading the data pegs the filesystem at 100%
for many minutes at a time.

The biggest table we have holds about 800MB of data in about 8 million
rows.  My program loads the Foxpro table files and generates another file
that looks like the following, which then gets piped into the psql command:

-----------------------
begin;
drop table ln;
create table ln (invid integer, ln integer, charge text, servcode varchar(10), odn varchar(1), itemcode varchar(6), qty1 varchar(10), uom1 varchar(2), qty2 varchar(10), uom2 varchar(2), aw integer, bw integer, rate text, perc integer, miles integer, loc1qual varchar(3), loc1id integer, loc2qual varchar(3), loc2id integer);
\copy ln from stdin
1078987 1           518.73      LHS     N       LHS              0                       0              785   785           66.0800    100     0       PW      -1049   DA      16736
...
[snip 8 million lines]
...
\.
create index ln_invid on ln(invid);
create index ln_uom1 on ln(uom1);
commit;
-----------------------

The /usr/local/pgsql/data filesystem is on two SCSI 320 drives via a
geom_stripe with a 128KB stripe size.  The drives are of unequal size.
I've had a new set of four matched drives on order from our purchasing
department for about size months now - yes, Mike, I'm looking at you -
but I'm doing the best I can with what I have.  The filesystem itself
has soft updates enabled but was otherwise newfs'ed with the defaults.

So, given that I'd like to throw more hardware at the problem but can't yet,
is there anything I could do to make these imports go faster, short of
running it async (which is far more dangerous than we're willing to risk)?
-- 
Kirk Strauser
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 187 bytes
Desc: not available
Url : http://lists.freebsd.org/pipermail/freebsd-questions/attachments/20070107/3a159bdc/attachment.pgp