Thread: Oracle and PostgreSQL
Hello my friends,
We are going to change some Oracle 8i and 9i databases to PostgreSQL 8 at my company.
1st: Is there somebody who knows how to do it (had the same experience)? What are the limitations of this process (eg: incompatible data types and objects).
2nd: Which tools are available to do it? I’ve seen one called SQL Fairy (http://sqlfairy.sourceforge.net/). Is this one the most recommended?
3rd: Is there a way to make both databases talk (such as creating a DBLink between two Oracle Databases)?
And finally, do you know a good comparison chart or benchmark between these two databases? A Brazilian university teacher made one (http://www.ic.unicamp.br/~celio/livrobd/docs/benchmark.html) but I’d like to see other comparsions like in real production databases.
C ya,
Teolupus
Bruno Almeida do Lago wrote: > Hello my friends, > > > We are going to change some Oracle 8i and 9i databases to PostgreSQL > 8 at my company. > > > 1st: Is there somebody who knows how to do it (had the same > experience)? What are the limitations of this process (eg: > incompatible data types and objects). Many here have done a similar exercise. We are doing this now. There are of course differences, but each individual conversion faces its own hurdles. For example, if you are using partitioned tablespaces in Oracle, PostgreSQL doesn't have that concept, so you'll need to do something different. One point in PG's favor (and the reason we selected it as our Oracle alternative) is that it handles some of the non-standard SQL that makes Oracle so powerful (like allowing a complete SQL statement where a column normally appears in a SELECT clause.) > > 2nd: Which tools are available to do it? I've seen one called SQL > Fairy (http://sqlfairy.sourceforge.net/). Is this one the most > recommended? Take a look at ora2pg. It does a good job and migrating schema and data. > > 3rd: Is there a way to make both databases talk (such as creating a > DBLink between two Oracle Databases)? There is a dblink contrib package for PG that allows two PG databases to be linked. You probably won't ever get an Oracle DB to link to a PG one. But it might be possible in the other direction, since the PG dblink mechanism is pretty generic. > > > And finally, do you know a good comparison chart or benchmark between > these two databases? A Brazilian university teacher made one > (http://www.ic.unicamp.br/~celio/livrobd/docs/benchmark.html) but I'd > like to see other comparsions like in real production databases. > > > > C ya, > Teolupus -- Guy Rouillier
OK! I've got to run now, but will search more about it tomorrow. Could you give me more details / references? > You don't want to do it automatically. You want to do it by hand but it > isn't that hard. Automatically? How? About the link between the two databases, where can I find how to do it?? I'm very happy with the good news! Thank you, Teolupus -----Original Message----- From: Joshua D. Drake [mailto:jd@commandprompt.com] Sent: Thursday, January 27, 2005 4:32 PM To: Bruno Almeida do Lago Subject: Oracle and PostgreSQL > 1^st : Is there somebody who knows how to do it (had the same > experience)? What are the limitations of this process (eg: > incompatible data types and objects). > Command Prompt, Inc. http://www.commandprompt.com > 2^nd : Which tools are available to do it? I've seen one called SQL > Fairy (http://sqlfairy.sourceforge.net/). Is this one the most > recommended? > You don't want to do it automatically. You want to do it by hand but it isn't that hard. > 3^rd : Is there a way to make both databases talk (such as creating a > DBLink between two Oracle Databases)? > Yes. > And finally, do you know a good comparison chart or benchmark between > these two databases? A Brazilian university teacher made one > (http://www.ic.unicamp.br/~celio/livrobd/docs/benchmark.html > <http://www.ic.unicamp.br/%7Ecelio/livrobd/docs/benchmark.html>) but > I'd like to see other comparsions like in real production databases. > You can't legally benchmark between the two because of Oracles license. Sincerely, Joshua D. Drake > C ya, > > Teolupus > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
Bruno Almeida do Lago wrote: > OK! I've got to run now, but will search more about it tomorrow. > > Could you give me more details / references? > > >>You don't want to do it automatically. You want to do it by hand but it >>isn't that hard. > > > Automatically? How? > > About the link between the two databases, where can I find how to do it?? > in the /contrib/dblink directory of your installation Alternatively, you can use a function written in plperl and use DBI/DBD to pull from the Oracle database. -- _______________________________ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. _______________________________
On Thu, Jan 27, 2005 at 03:32:38PM -0200, Bruno Almeida do Lago wrote: > Hello my friends, > > We are going to change some Oracle 8i and 9i databases to PostgreSQL > 8 at my company. > > 1st: Is there somebody who knows how to do it (had the same > experience)? What are the limitations of this process (eg: > incompatible data types and objects). It depends how much of Oracle's functionality you used. Do you have table types or other exotics like custom types? > 2nd: Which tools are available to do it? I've seen one called SQL > Fairy (http://sqlfairy.sourceforge.net/). Is this one the most > recommended? Try it and see. > 3rd: Is there a way to make both databases talk (such as creating a > DBLink between two Oracle Databases)? Yes! For all-PostgreSQL environments, use contrib/dblink. For heterogeneous environments, there's http://pgfoundry.org/projects/dbi-link/ Please send bug reports/feature requests for DBI-Link if needed. > And finally, do you know a good comparison chart or benchmark > between these two databases? A Brazilian university teacher made one > (http://www.ic.unicamp.br/~celio/livrobd/docs/benchmark.html) but > I'd like to see other comparsions like in real production databases. The best benchmark, of course, is your application. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!