Thread: How to convert Oracle database to Postgresql.

How to convert Oracle database to Postgresql.

From
Ursula Lee
Date:
Dear all,

Is there any script/tool I can use to convert Oracle database to Postgresql?

Thanks
Ursula



Re: How to convert Oracle database to Postgresql.

From
Mike Mascari
Date:
Ursula Lee wrote:

> Dear all,
>
> Is there any script/tool I can use to convert Oracle database to
> Postgresql?

The /contrib directory of the distribution contains an ora2pg script
used to generate a conversion. I've never used it though.

Mike Mascari
mascarm@mascari.com



Re: How to convert Oracle database to Postgresql.

From
Justin Clift
Date:
Hi Ursula,

That script only works for Oracle 8.x though, and needs modification for Oracle 9i.

:-)

Regards and best wishes,

Justin Clift


Mike Mascari wrote:
> Ursula Lee wrote:
>
>
>>Dear all,
>>
>>Is there any script/tool I can use to convert Oracle database to
>>Postgresql?
>
>
> The /contrib directory of the distribution contains an ora2pg script
> used to generate a conversion. I've never used it though.
>
> Mike Mascari
> mascarm@mascari.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly


--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi


Re: How to convert Oracle database to Postgresql.

From
"Carlos Oliva"
Date:
Is there a script or method to convert a DB2 database also?
Thanks

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Justin Clift
Sent: Wednesday, June 18, 2003 7:02 AM
To: Mike Mascari
Cc: Ursula Lee; pgsql-general@postgresql.org; gilles Darold
Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.


Hi Ursula,

That script only works for Oracle 8.x though, and needs modification for
Oracle 9i.

:-)

Regards and best wishes,

Justin Clift


Mike Mascari wrote:
> Ursula Lee wrote:
>
>
>>Dear all,
>>
>>Is there any script/tool I can use to convert Oracle database to
>>Postgresql?
>
>
> The /contrib directory of the distribution contains an ora2pg script
> used to generate a conversion. I've never used it though.
>
> Mike Mascari
> mascarm@mascari.com
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that
your
>       message can get through to the mailing list cleanly


--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org


Re: How to convert Oracle database to Postgresql.

From
"Wayne Armstrong"
Date:
** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed, 18 Jun
2003 08:12:00 -0400
Hi Carlos,
 We've just been through this recently.
 We used db2look to get the ddl for the database, ran some editing scripts over
it (just ex) and fed that into postgres.
 To load data from the db2 database, I wrote a smalltalk program that for each
table, read from the db2 dtabase and inserted into the postgres database. The
smalltalk needs no prior knowledge of the table layouts to do that- queries
syscat.tables in the db2 database to get a list of tables etc. You are welcome
to the smalltalk executable (and source if you want it (it is not really
production code though :) )

We used bytea in place of blob.
Triggers we did manually (we are not big users of triggers).

All in all it was pretty easy. Places we came unstuck were on views using the
"with temp" syntax, and some recursive sql which we rewrote using pgplsql and
temp tables. Also Db2 will hapilly store 24:00:00 as well as 00:00:00 for
midnight. It's worth updateing any occurennces of 24:00:00 to 00:00:00 before
you convert.


Regards,
Wayne Armstrong,
Bacchus Management Systems.

> Is there a script or method to convert a DB2 database also?
> Thanks
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Justin Clift
> Sent: Wednesday, June 18, 2003 7:02 AM
> To: Mike Mascari
> Cc: Ursula Lee; pgsql-general@postgresql.org; gilles Darold
> Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
>
>
> Hi Ursula,
>
> That script only works for Oracle 8.x though, and needs modification for
> Oracle 9i.
>
> :-)
>
> Regards and best wishes,
>
> Justin Clift
>
>
> Mike Mascari wrote:
> > Ursula Lee wrote:
> >
> >
> >>Dear all,
> >>
> >>Is there any script/tool I can use to convert Oracle database to
> >>Postgresql?
> >
> >
> > The /contrib directory of the distribution contains an ora2pg script
> > used to generate a conversion. I've never used it though.
> >
> > Mike Mascari
> > mascarm@mascari.com
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> >       subscribe-nomail command to majordomo@postgresql.org so that
> your
> >       message can get through to the mailing list cleanly
>
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
> - Indira Gandhi
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html


Re: How to convert Oracle database to Postgresql.

From
"Shridhar Daithankar"
Date:
On 18 Jun 2003 at 22:33, Wayne Armstrong wrote:

> ** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed, 18 Jun
> 2003 08:12:00 -0400
> Hi Carlos,
>  We've just been through this recently.
>  We used db2look to get the ddl for the database, ran some editing scripts over
> it (just ex) and fed that into postgres.
>  To load data from the db2 database, I wrote a smalltalk program that for each
> table, read from the db2 dtabase and inserted into the postgres database. The
> smalltalk needs no prior knowledge of the table layouts to do that- queries
> syscat.tables in the db2 database to get a list of tables etc. You are welcome
> to the smalltalk executable (and source if you want it (it is not really
> production code though :) )
>
> We used bytea in place of blob.
> Triggers we did manually (we are not big users of triggers).
>
> All in all it was pretty easy. Places we came unstuck were on views using the
> "with temp" syntax, and some recursive sql which we rewrote using pgplsql and
> temp tables. Also Db2 will hapilly store 24:00:00 as well as 00:00:00 for
> midnight. It's worth updateing any occurennces of 24:00:00 to 00:00:00 before
> you convert.

I would say this is worth a HOWTO. Would you do that?

Bye
 Shridhar

--
Decision maker, n.:    The person in your office who was unable to form a task
force    before the music stopped.


Re: How to convert Oracle database to Postgresql.

From
"Carlos Oliva"
Date:
Hi Wayne,
Yes.  I would not mind getting a copy of the code.  I wrote a java
program to copy the data via JDBC and it works but it is rather slow.  I
am not using triggers either but I a use lots of blobs (I converted them
into bytea also)
Regards

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wayne Armstrong
Sent: Wednesday, June 18, 2003 8:34 AM
To: Carlos; pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.


** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed, 18
Jun 2003 08:12:00 -0400 Hi Carlos,  We've just been through this
recently.  We used db2look to get the ddl for the database, ran some
editing scripts over it (just ex) and fed that into postgres.  To load
data from the db2 database, I wrote a smalltalk program that for each
table, read from the db2 dtabase and inserted into the postgres
database. The smalltalk needs no prior knowledge of the table layouts to
do that- queries syscat.tables in the db2 database to get a list of
tables etc. You are welcome to the smalltalk executable (and source if
you want it (it is not really production code though :) )

We used bytea in place of blob.
Triggers we did manually (we are not big users of triggers).

All in all it was pretty easy. Places we came unstuck were on views
using the "with temp" syntax, and some recursive sql which we rewrote
using pgplsql and temp tables. Also Db2 will hapilly store 24:00:00 as
well as 00:00:00 for midnight. It's worth updateing any occurennces of
24:00:00 to 00:00:00 before you convert.


Regards,
Wayne Armstrong,
Bacchus Management Systems.

> Is there a script or method to convert a DB2 database also? Thanks
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Justin Clift
> Sent: Wednesday, June 18, 2003 7:02 AM
> To: Mike Mascari
> Cc: Ursula Lee; pgsql-general@postgresql.org; gilles Darold
> Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
>
>
> Hi Ursula,
>
> That script only works for Oracle 8.x though, and needs modification
> for Oracle 9i.
>
> :-)
>
> Regards and best wishes,
>
> Justin Clift
>
>
> Mike Mascari wrote:
> > Ursula Lee wrote:
> >
> >
> >>Dear all,
> >>
> >>Is there any script/tool I can use to convert Oracle database to
> >>Postgresql?
> >
> >
> > The /contrib directory of the distribution contains an ora2pg script
> > used to generate a conversion. I've never used it though.
> >
> > Mike Mascari
> > mascarm@mascari.com
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> >       subscribe-nomail command to majordomo@postgresql.org so that
> your
> >       message can get through to the mailing list cleanly
>
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
> - Indira Gandhi
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


Re: How to convert Oracle database to Postgresql.

From
"Carlos Oliva"
Date:
It would be great if besides SmallTalk, the scripts could be written in
plain Linux scripts or Python
Regards

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Shridhar
Daithankar
Sent: Wednesday, June 18, 2003 8:42 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.


On 18 Jun 2003 at 22:33, Wayne Armstrong wrote:

> ** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed,
> 18 Jun 2003 08:12:00 -0400 Hi Carlos,
>  We've just been through this recently.
>  We used db2look to get the ddl for the database, ran some editing
scripts over
> it (just ex) and fed that into postgres.
>  To load data from the db2 database, I wrote a smalltalk program that
for each
> table, read from the db2 dtabase and inserted into the postgres
database. The
> smalltalk needs no prior knowledge of the table layouts to do that-
queries
> syscat.tables in the db2 database to get a list of tables etc. You are
welcome
> to the smalltalk executable (and source if you want it (it is not
really
> production code though :) )
>
> We used bytea in place of blob.
> Triggers we did manually (we are not big users of triggers).
>
> All in all it was pretty easy. Places we came unstuck were on views
> using the "with temp" syntax, and some recursive sql which we rewrote
> using pgplsql and temp tables. Also Db2 will hapilly store 24:00:00 as

> well as 00:00:00 for midnight. It's worth updateing any occurennces of

> 24:00:00 to 00:00:00 before you convert.

I would say this is worth a HOWTO. Would you do that?

Bye
 Shridhar

--
Decision maker, n.:    The person in your office who was unable to form
a task
force    before the music stopped.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


Re: How to convert Oracle database to Postgresql.

From
"Wayne Armstrong"
Date:
** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed, 18 Jun
2003 08:49:52 -0400
Hi Carlos,
 Is email ok for delivery (probably about 10 meg all up)?

Regards,
Wayne



> Hi Wayne,
> Yes.  I would not mind getting a copy of the code.  I wrote a java
> program to copy the data via JDBC and it works but it is rather slow.  I
> am not using triggers either but I a use lots of blobs (I converted them
> into bytea also)
> Regards
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wayne Armstrong
> Sent: Wednesday, June 18, 2003 8:34 AM
> To: Carlos; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
>
>
> ** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed, 18
> Jun 2003 08:12:00 -0400 Hi Carlos,  We've just been through this
> recently.  We used db2look to get the ddl for the database, ran some
> editing scripts over it (just ex) and fed that into postgres.  To load
> data from the db2 database, I wrote a smalltalk program that for each
> table, read from the db2 dtabase and inserted into the postgres
> database. The smalltalk needs no prior knowledge of the table layouts to
> do that- queries syscat.tables in the db2 database to get a list of
> tables etc. You are welcome to the smalltalk executable (and source if
> you want it (it is not really production code though :) )
>
> We used bytea in place of blob.
> Triggers we did manually (we are not big users of triggers).
>
> All in all it was pretty easy. Places we came unstuck were on views
> using the "with temp" syntax, and some recursive sql which we rewrote
> using pgplsql and temp tables. Also Db2 will hapilly store 24:00:00 as
> well as 00:00:00 for midnight. It's worth updateing any occurennces of
> 24:00:00 to 00:00:00 before you convert.
>
>
> Regards,
> Wayne Armstrong,
> Bacchus Management Systems.
>
> > Is there a script or method to convert a DB2 database also? Thanks
> >
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Justin Clift
> > Sent: Wednesday, June 18, 2003 7:02 AM
> > To: Mike Mascari
> > Cc: Ursula Lee; pgsql-general@postgresql.org; gilles Darold
> > Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
> >
> >
> > Hi Ursula,
> >
> > That script only works for Oracle 8.x though, and needs modification
> > for Oracle 9i.
> >
> > :-)
> >
> > Regards and best wishes,
> >
> > Justin Clift
> >
> >
> > Mike Mascari wrote:
> > > Ursula Lee wrote:
> > >
> > >
> > >>Dear all,
> > >>
> > >>Is there any script/tool I can use to convert Oracle database to
> > >>Postgresql?
> > >
> > >
> > > The /contrib directory of the distribution contains an ora2pg script
> > > used to generate a conversion. I've never used it though.
> > >
> > > Mike Mascari
> > > mascarm@mascari.com
> > >
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > >       subscribe-nomail command to majordomo@postgresql.org so that
> > your
> > >       message can get through to the mailing list cleanly
> >
> >
> > --
> > "My grandfather once told me that there are two kinds of people: those
> > who work and those who take the credit. He told me to try to be in the
> > first group; there was less competition there."
> > - Indira Gandhi
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faqs/FAQ.html
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html


Re: How to convert Oracle database to Postgresql.

From
"Carlos Oliva"
Date:
I think so.  You may want to consider zipping the file also.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wayne Armstrong
Sent: Wednesday, June 18, 2003 8:59 AM
To: Carlos; pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.


** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed, 18
Jun 2003 08:49:52 -0400 Hi Carlos,  Is email ok for delivery (probably
about 10 meg all up)?

Regards,
Wayne



> Hi Wayne,
> Yes.  I would not mind getting a copy of the code.  I wrote a java
> program to copy the data via JDBC and it works but it is rather slow.

> I am not using triggers either but I a use lots of blobs (I converted
> them into bytea also) Regards
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wayne
> Armstrong
> Sent: Wednesday, June 18, 2003 8:34 AM
> To: Carlos; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
>
>
> ** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed,
> 18 Jun 2003 08:12:00 -0400 Hi Carlos,  We've just been through this
> recently.  We used db2look to get the ddl for the database, ran some
> editing scripts over it (just ex) and fed that into postgres.  To load

> data from the db2 database, I wrote a smalltalk program that for each
> table, read from the db2 dtabase and inserted into the postgres
> database. The smalltalk needs no prior knowledge of the table layouts
> to do that- queries syscat.tables in the db2 database to get a list of

> tables etc. You are welcome to the smalltalk executable (and source if

> you want it (it is not really production code though :) )
>
> We used bytea in place of blob.
> Triggers we did manually (we are not big users of triggers).
>
> All in all it was pretty easy. Places we came unstuck were on views
> using the "with temp" syntax, and some recursive sql which we rewrote
> using pgplsql and temp tables. Also Db2 will hapilly store 24:00:00 as

> well as 00:00:00 for midnight. It's worth updateing any occurennces of

> 24:00:00 to 00:00:00 before you convert.
>
>
> Regards,
> Wayne Armstrong,
> Bacchus Management Systems.
>
> > Is there a script or method to convert a DB2 database also? Thanks
> >
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Justin
> > Clift
> > Sent: Wednesday, June 18, 2003 7:02 AM
> > To: Mike Mascari
> > Cc: Ursula Lee; pgsql-general@postgresql.org; gilles Darold
> > Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
> >
> >
> > Hi Ursula,
> >
> > That script only works for Oracle 8.x though, and needs modification
> > for Oracle 9i.
> >
> > :-)
> >
> > Regards and best wishes,
> >
> > Justin Clift
> >
> >
> > Mike Mascari wrote:
> > > Ursula Lee wrote:
> > >
> > >
> > >>Dear all,
> > >>
> > >>Is there any script/tool I can use to convert Oracle database to
> > >>Postgresql?
> > >
> > >
> > > The /contrib directory of the distribution contains an ora2pg
> > > script used to generate a conversion. I've never used it though.
> > >
> > > Mike Mascari
> > > mascarm@mascari.com
> > >
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an
appropriate
> > >       subscribe-nomail command to majordomo@postgresql.org so that
> > your
> > >       message can get through to the mailing list cleanly
> >
> >
> > --
> > "My grandfather once told me that there are two kinds of people:
> > those who work and those who take the credit. He told me to try to
> > be in the first group; there was less competition there."
> > - Indira Gandhi
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faqs/FAQ.html
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


Re: How to convert Oracle database to Postgresql.

From
Ron Johnson
Date:
On Wed, 2003-06-18 at 07:49, Carlos Oliva wrote:
> Hi Wayne,
> Yes.  I would not mind getting a copy of the code.  I wrote a java
> program to copy the data via JDBC and it works but it is rather slow.  I
> am not using triggers either but I a use lots of blobs (I converted them
> into bytea also)
> Regards
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wayne Armstrong
> Sent: Wednesday, June 18, 2003 8:34 AM
> To: Carlos; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
>
>
> ** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed, 18
> Jun 2003 08:12:00 -0400 Hi Carlos,  We've just been through this
> recently.  We used db2look to get the ddl for the database, ran some
> editing scripts over it (just ex) and fed that into postgres.  To load
> data from the db2 database, I wrote a smalltalk program that for each
> table, read from the db2 dtabase and inserted into the postgres
> database. The smalltalk needs no prior knowledge of the table layouts to
> do that- queries syscat.tables in the db2 database to get a list of
> tables etc. You are welcome to the smalltalk executable (and source if
> you want it (it is not really production code though :) )
>
> We used bytea in place of blob.
> Triggers we did manually (we are not big users of triggers).
>
> All in all it was pretty easy. Places we came unstuck were on views
> using the "with temp" syntax, and some recursive sql which we rewrote
> using pgplsql and temp tables. Also Db2 will hapilly store 24:00:00 as
> well as 00:00:00 for midnight. It's worth updateing any occurennces of
> 24:00:00 to 00:00:00 before you convert.

Wouldn't it be faster to do host-based conversions to tab-delimited
files that can be loaded into postgres via COPY?

--
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| "Oh, great altar of passive entertainment, bestow upon me |
|  thy discordant images at such speed as to render linear  |
|  thought impossible" (Calvin, regarding TV)               |
+-----------------------------------------------------------


Re: How to convert Oracle database to Postgresql.

From
"Carlos Oliva"
Date:
Hi Ron,
Probably ....  The java program I wrote works by copying the tables via
JDBC so it does not produce any text files and relies on the capabilies
of JDBC to treat SQL types without having to specify them.  It is
similar to methods published to copy databases in the server.  In fact
it copied a BLOB from DB2 into a bytea of PostgreSQL without my having
to deal with SQL types (I tried a program that copy tables via ODBC but
it failed everytime that a BLOB had to be copied).

I imagine that one could output data from tables into text files by
outputing the data as objects.  In this way one may not have to deal
with the different SQL types.  I am not sure about other aspects of the
database such as triggers

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Johnson
Sent: Wednesday, June 18, 2003 10:12 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.


On Wed, 2003-06-18 at 07:49, Carlos Oliva wrote:
> Hi Wayne,
> Yes.  I would not mind getting a copy of the code.  I wrote a java
> program to copy the data via JDBC and it works but it is rather slow.

> I am not using triggers either but I a use lots of blobs (I converted
> them into bytea also) Regards
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wayne
> Armstrong
> Sent: Wednesday, June 18, 2003 8:34 AM
> To: Carlos; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
>
>
> ** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed,
> 18 Jun 2003 08:12:00 -0400 Hi Carlos,  We've just been through this
> recently.  We used db2look to get the ddl for the database, ran some
> editing scripts over it (just ex) and fed that into postgres.  To load

> data from the db2 database, I wrote a smalltalk program that for each
> table, read from the db2 dtabase and inserted into the postgres
> database. The smalltalk needs no prior knowledge of the table layouts
> to do that- queries syscat.tables in the db2 database to get a list of

> tables etc. You are welcome to the smalltalk executable (and source if

> you want it (it is not really production code though :) )
>
> We used bytea in place of blob.
> Triggers we did manually (we are not big users of triggers).
>
> All in all it was pretty easy. Places we came unstuck were on views
> using the "with temp" syntax, and some recursive sql which we rewrote
> using pgplsql and temp tables. Also Db2 will hapilly store 24:00:00 as

> well as 00:00:00 for midnight. It's worth updateing any occurennces of

> 24:00:00 to 00:00:00 before you convert.

Wouldn't it be faster to do host-based conversions to tab-delimited
files that can be loaded into postgres via COPY?

--
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           | "Oh, great

| altar of passive entertainment, bestow upon me |  thy discordant
| images at such speed as to render linear  |
|  thought impossible" (Calvin, regarding TV)               |
+-----------------------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html


Re: How to convert Oracle database to Postgresql.

From
"Wayne Armstrong"
Date:
** Reply to message from Ron Johnson <ron.l.johnson@cox.net> on 18 Jun 2003
09:11:34 -0500
Yup,
 But then you need to reliably convert from quote, separated to tab delimited,
look after different representation of time and timestamp and null values (and
some other formatting issues which I can't remember. If youv'e got clobs/blobs
to transfer (or carriage returns in varchar fields (fine in db2))also, there is
just no good way to do it.
 So slower, but lots less labor intensive is ok for me.

Regards,
Wayne

> On Wed, 2003-06-18 at 07:49, Carlos Oliva wrote:
> > Hi Wayne,
> > Yes.  I would not mind getting a copy of the code.  I wrote a java
> > program to copy the data via JDBC and it works but it is rather slow.  I
> > am not using triggers either but I a use lots of blobs (I converted them
> > into bytea also)
> > Regards
> >
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wayne Armstrong
> > Sent: Wednesday, June 18, 2003 8:34 AM
> > To: Carlos; pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
> >
> >
> > ** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed, 18
> > Jun 2003 08:12:00 -0400 Hi Carlos,  We've just been through this
> > recently.  We used db2look to get the ddl for the database, ran some
> > editing scripts over it (just ex) and fed that into postgres.  To load
> > data from the db2 database, I wrote a smalltalk program that for each
> > table, read from the db2 dtabase and inserted into the postgres
> > database. The smalltalk needs no prior knowledge of the table layouts to
> > do that- queries syscat.tables in the db2 database to get a list of
> > tables etc. You are welcome to the smalltalk executable (and source if
> > you want it (it is not really production code though :) )
> >
> > We used bytea in place of blob.
> > Triggers we did manually (we are not big users of triggers).
> >
> > All in all it was pretty easy. Places we came unstuck were on views
> > using the "with temp" syntax, and some recursive sql which we rewrote
> > using pgplsql and temp tables. Also Db2 will hapilly store 24:00:00 as
> > well as 00:00:00 for midnight. It's worth updateing any occurennces of
> > 24:00:00 to 00:00:00 before you convert.
>
> Wouldn't it be faster to do host-based conversions to tab-delimited
> files that can be loaded into postgres via COPY?
>
> --
> +-----------------------------------------------------------+
> | Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
> | Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
> |                                                           |
> | "Oh, great altar of passive entertainment, bestow upon me |
> |  thy discordant images at such speed as to render linear  |
> |  thought impossible" (Calvin, regarding TV)               |
> +-----------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html