Folks,

I've been struggling to copy a large table (200 million
records, 60GB) to tape using:

   psql -qc "copy psc to STDOUT;" Winter99 | dd of=/dev/st0 bs=32k

After processing about 10 million records (this varies), I
get:

  FATAL 1:  Memory exhausted in AllocSetAlloc()

(The tape drive is a DLT 7000, and the tape is not filled at
this point).

There is no evidence that the backend has really exhausted
avail memory (I have 256MB but 1GB swap and the postgres
user and database user both have unlimited memory usage).

This is 6.5.1 on Linux 2.2.11 (w/Debian 2.1) on a dual
450Mhz Xeon box with a 128GB software Raid0 array.  I've set
SHMEM to 128MB am using "-B 12288 -S 8192".  I've been
trying to figure this out for a few weeks but can't seem to
get this table copied to tape.  Can one of the developers
offer a suggestion?

BTW, this is a large astronomical database which will
eventually grow to about 500 million records.  Besides this
tape problem, pgsql is now working nicely for our
application.

I've been following the mysql thread.  You folks may want
to add "works with databases over 2GB" to your plus column.

With thoughtful indexing, one can retrieve queries of
<100000 records in 1 to 15 minutes which competes nicely
with our main data server, a bunch of Sun Enterprise 5000
and 6000s running Informix.  Of course, many people using
this large system simultaneously, but our goal for this
project is to recommend an alternative hardware/software
solution to the astronomical community for <$10K.

--M

===========================================================================

Martin Weinberg                      Phone: (413) 545-3821
Dept. of Physics and Astronomy       FAX:   (413) 545-2117/0648
530 Graduate Research Tower	     weinberg@astro.umass.edu
University of Massachusetts	     http://www.astro.umass.edu/~weinberg/
Amherst, MA  01003-4525




Sat, 9 Oct 1999 10:51:24 -0400
Martin Weinberg <weinberg@osprey.astro.umass.edu>
memory problems in copying large table