Re: How to convert Oracle database to Postgresql.

** Reply to message from "Carlos Oliva" <carlos(at)pbsinet(dot)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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Wayne Armstrong
> Sent: Wednesday, June 18, 2003 8:34 AM
> To: Carlos; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
>
>
> ** Reply to message from "Carlos Oliva" <carlos(at)pbsinet(dot)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(at)postgresql(dot)org
> > [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Justin Clift
> > Sent: Wednesday, June 18, 2003 7:02 AM
> > To: Mike Mascari
> > Cc: Ursula Lee; pgsql-general(at)postgresql(dot)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(at)mascari(dot)com
> > >
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo(at)postgresql(dot)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(at)postgresql(dot)org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html