How to convert Oracle database to Postgresql.

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

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

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

** 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.

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.

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)

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)

** 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

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

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)               |
+-----------------------------------------------------------

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

** 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