Thread: equivalent of sqlload?
Oracle has a facility for loading in massive data files, sqlload I think is the name. I'd like to load some big data into PostgreSQL 6.4 for testing purposes. Is there a way built in or would a perl or c hack be the way to go? john holland
At 16:45 +0200 on 25/11/98, John Holland wrote: > Oracle has a facility for loading in massive data files, sqlload I think > is the name. I'd like to load some big data into PostgreSQL 6.4 for > testing purposes. Is there a way built in or would a perl or c hack be the > way to go? Use the COPY command. It is preferable not to define indices (or to drop the indices) on the table, then do the massive load with COPY, and then define the indices. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
Hello John,
mercoledì, 25 novembre 98, you wrote:
JH> Oracle has a facility for loading in massive data files, sqlload I think
JH> is the name. I'd like to load some big data into PostgreSQL 6.4 for
JH> testing purposes. Is there a way built in or would a perl or c hack be the
JH> way to go?
JH> john holland
use:
copy [binary] classname [with oids]
to|from 'filename'|stdin|stdout
[using delimiters 'delim']
or:
\copy classname from filename
-Jose'-
I am running v6.3.2 under Linux and have found that the "copy" command works only for small amounts of data. When trying to "copy" several thousand records I notice that system RAM and swap space continue to get eaten until there is no further memory available. "psql" then fails. What remains is a .../pgdata/base/XYZ file system with the table being copied into. That table may be several (tens, hundreds) of Meg in size, but a "psql -d XYS -c 'select count(*) table'" will only return a zero count. Now...I have broken the "copy" process down into smaller chunks and make multiple calls to "copy". I have a total of about 5.4 million records and the job isn't done yet...my Pentium 433 has been working on this copy for over 24 hours. I don't know if there are any changes that can be made to speed this type of process up, but this is definitely a black-mark. mike Dr Michael A. Koerber MIT/Lincoln Laboratory 781-981-3250 On Wed, 25 Nov 1998, Herouth Maoz wrote: > At 16:45 +0200 on 25/11/98, John Holland wrote: > > > > Oracle has a facility for loading in massive data files, sqlload I think > > is the name. I'd like to load some big data into PostgreSQL 6.4 for > > testing purposes. Is there a way built in or would a perl or c hack be the > > way to go? > > Use the COPY command. It is preferable not to define indices (or to drop > the indices) on the table, then do the massive load with COPY, and then > define the indices. > > Herouth > > -- > Herouth Maoz, Internet developer. > Open University of Israel - Telem project > http://telem.openu.ac.il/~herutma > > >
At 21:01 +0200 on 25/11/98, Michael A. Koerber wrote: > > Now...I have broken the "copy" process down into smaller chunks and make > multiple calls to "copy". I have a total of about 5.4 million records and > the job isn't done yet...my Pentium 433 has been working on this copy for > over 24 hours. Try putting all the separate COPYs in one transaction. BEGIN TRANSACTION; COPY... COPY... COPY... END; Without the surrounding transaction, each copy is a transaction and requires transaction overhead. I don't know how much time this will save, though. Are you sure you dropped all the indices? When you declare a primary key it declares a unique index, so watch out for that as well. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
> > Now...I have broken the "copy" process down into smaller chunks and make > > multiple calls to "copy". I have a total of about 5.4 million records and > > the job isn't done yet...my Pentium 433 has been working on this copy for > > over 24 hours. I have seen pretty much the same effect, to test this I set up script that copied in 10000 records at a time. The time it took for each copy increased linearly (starting at about 2.0 seconds on my PII-233) by approx 0.5 seconds per 10000. That means that by the time you have 5 million records it takes more than 4 minutes to insert 10000 records. Yes, I checked, there were NO indices defined on the table. Anton