Обсуждение: anyone use Ora2Pg?

Howdy:

Has anyone used the Ora2Pg tool for exporting
data to PostgreSQL?  I'm trying to move data
over, and my perl script to insert records
into the PostgreSQL table is going very slowly.

Suggestions?

Thanks!

-X

* Johnson, Shaunn (SJohnson6@bcbsm.com) wrote:
> Has anyone used the Ora2Pg tool for exporting
> data to PostgreSQL?  I'm trying to move data
> over, and my perl script to insert records
> into the PostgreSQL table is going very slowly.

Use copy?  I'm pretty sure that's what I did...

    Stephen

--howdy - thanks for the reply ...

--i had tried to use 'copy', but it was taking
--so long ... it took over a weekend to load
--about the same amount of records as using 'insert' (i have
--been running this program for over 2 days and only
--have 2800 records to show out of 1/2 a million records).

--i was looking for something faster.  could be my server ...
--looking at the system usage, it doesn't seem very high.

-X

-----Original Message-----
From: Stephen Frost [mailto:sfrost@snowman.net]

* Johnson, Shaunn (SJohnson6@bcbsm.com) wrote:
> Has anyone used the Ora2Pg tool for exporting
> data to PostgreSQL?  I'm trying to move data
> over, and my perl script to insert records
> into the PostgreSQL table is going very slowly.

Use copy?  I'm pretty sure that's what I did...

        Stephen

* Johnson, Shaunn (SJohnson6@bcbsm.com) wrote:
> --i had tried to use 'copy', but it was taking
> --so long ... it took over a weekend to load
> --about the same amount of records as using 'insert' (i have
> --been running this program for over 2 days and only
> --have 2800 records to show out of 1/2 a million records).
>
> --i was looking for something faster.  could be my server ...
> --looking at the system usage, it doesn't seem very high.

Something sounds pretty off.  It didn't take nearly that long for me to
load up a fair bit of data.

    Stephen

--howdy:

--okay, then i'm doing something silly. :(

--what i did the first time was to copy
--data from a text file.  basically, i did this:

\copy t_table from 'file.txt' using delimiters '|'

--and that was taking too long.  i got the bright
--idea to use a perl script to load data with
--'insert'.  that, too, is taking too long.

--so, i remembered the Ora2Pg tool.

--how did you use copy to move data?

-X

-----Original Message-----
From: Stephen Frost [mailto:sfrost@snowman.net]
Sent: Friday, September 19, 2003 4:27 PM
To: Johnson, Shaunn
Cc: PgSQL General ML
Subject: Re: [GENERAL] anyone use Ora2Pg?

* Johnson, Shaunn (SJohnson6@bcbsm.com) wrote:
> --i had tried to use 'copy', but it was taking
> --so long ... it took over a weekend to load
> --about the same amount of records as using 'insert' (i have
> --been running this program for over 2 days and only
> --have 2800 records to show out of 1/2 a million records).
>
> --i was looking for something faster.  could be my server ...
> --looking at the system usage, it doesn't seem very high.

Something sounds pretty off.  It didn't take nearly that long for me to
load up a fair bit of data.

        Stephen

* Johnson, Shaunn (SJohnson6@bcbsm.com) wrote:
> --how did you use copy to move data?

I set up the Ora2Pg object using the appropriate settings, dumped all
the tables out to a file and then did psql < file.  2 million or so rows
across a bunch of tables, didn't take too long.

    Stephen

Johnson, Shaunn wrote:

> howdy:
>
> okay, then i'm doing something silly. :(
>
> what i did the first time was to copy
> data from a text file.  basically, i did this:
>
> \copy t_table from 'file.txt' using delimiters '|'
>
> and that was taking too long.  i got the bright
> idea to use a perl script to load data with
> 'insert'.  that, too, is taking too long.

On an old dual pentium 400Mhz intel box with 40MBs SCSI and 7200 RPM
drives I loaded > million rows in less than an hour.

Are there any indexes on this table? Any triggers? Can we see the
schema definition of this table? What version of PostgreSQL? What type
of machine is this? TRS-80 perhaps? ;-)

Mike Mascari
mascarm@mascari.com

--howdy - thanks for the reply -

--i must be so far out of it, then - i
--have a sinble 1.2Ghz proc with a table
--that has about half a million rows.

On an old dual pentium 400Mhz intel box with 40MBs SCSI and 7200 RPM
drives I loaded > million rows in less than an hour.

--no indexes, triggers or anything useful.

Are there any indexes on this table? Any triggers?

--not sure about how to send this; i don't have multiple
--schemas created ... everything is sorta lumped together.
--how can i display the schema definition?

Can we see the schema definition of this table?

--PostgreSQL 7.2.1.

What version of PostgreSQL?

--*lol*

What type of machine is this? TRS-80 perhaps? ;-)

--but seriously, using '\copy' worked that well for
--you? ... something else is going on, then ...

-X

On Fri, 2003-09-19 at 15:32, Johnson, Shaunn wrote:
[snip]
> * Johnson, Shaunn (SJohnson6@bcbsm.com) wrote:
> > --i had tried to use 'copy', but it was taking
> > --so long ... it took over a weekend to load
> > --about the same amount of records as using 'insert' (i have
> > --been running this program for over 2 days and only
> > --have 2800 records to show out of 1/2 a million records).
> >
> > --i was looking for something faster.  could be my server ...
> > --looking at the system usage, it doesn't seem very high.
>
> Something sounds pretty off.  It didn't take nearly that long for me
> to
> load up a fair bit of data.

Could the table be chock full of indexes?

Still, 2 days to insert 2800 records is less than 1 record per
*minute*.  That's not "pretty off", that's mondo off.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Fair is where you take your cows to be judged."
Unknown

Johnson, Shaunn wrote:

> howdy - thanks for the reply -
>
> i must be so far out of it, then - i
> have a sinble 1.2Ghz proc with a table
> that has about half a million rows.
>
> no indexes, triggers or anything useful.

Hmm.

> not sure about how to send this; i don't have multiple
> schemas created ... everything is sorta lumped together.
> how can i display the schema definition?

'Schema' is an overloaded word. I meant the definition of the table,
\dt <tablename>.

> PostgreSQL 7.2.1.

You could try and turn off fsync for the initial load in
postgresql.conf and restart the postmaster:

fsync = off;

Then turn it back on after the load is complete.

> but seriously, using '\copy' worked that well for
> you? ... something else is going on, then ...

Yes. In fact, it was a restore of an entire database where one of the
tables had > 1 million rows of data that took less than an hour. In
fact, IIRC, the restore of the entire dump took around 30 minutes.

pg_dump generates output that:

1. Creates the tables (and dependent objects)
2. Uses COPY to import the data
3. Creates indexes over the newly imported data

Something else must be going on, as you say.

Mike Mascari
mascarm@mascari.com