Thread: Migrating Oracle to PostgreSQL
Hi there, Has anyone migrated Oracle database to Postgres? If yes, could you please help me - what are the steps involved, complications you faced, any other info you think is appropriate. Thanks in advance. Regards, Gera Makarov Oracle DBA CitiPower Pty ********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This email message has been swept for the presence of computer viruses CitiPower Pty. ACN 064 651 056 **********************************************************************
On Thu, Apr 18, 2002 at 03:24:32PM +1000, Makarov Gera x8521 wrote: > Hi there, > > Has anyone migrated Oracle database to Postgres? If yes, could you please > help me - what > are the steps involved, complications you faced, any other info you think is > appropriate. > Thanks in advance. See: http://techdocs.postgresql.org/ (Converting from other Databases to PostgreSQL) Karel PS. Why you send our question to _all_ PostgreSQL lists?! -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
Hi, Have a look at www.openacs.org. This is an open project for converting the ACS (Oracle based) web system into an open source system based on PostgreSQL (in the first place). I believe there is a migration document somewhere in there. Greetings, Leif On Thu, 18 Apr 2002, Makarov Gera x8521 wrote: > Hi there, > > Has anyone migrated Oracle database to Postgres? If yes, could you please > help me - what > are the steps involved, complications you faced, any other info you think is > appropriate. > Thanks in advance. > > Regards, > > Gera Makarov > Oracle DBA > CitiPower Pty > > > > ********************************************************************** > This email and any files transmitted with it are confidential and > intended solely for the use of the individual or entity to whom they > are addressed. If you have received this email in error please notify > the system manager. > > This email message has been swept for the presence of > computer viruses > > CitiPower Pty. ACN 064 651 056 > ********************************************************************** > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
On Thu, Apr 18, 2002 at 03:21:46PM +0200, Leif Jensen wrote: > > Hi, > > Have a look at www.openacs.org. This is an open project for converting > the ACS (Oracle based) web system into an open source system based on > PostgreSQL (in the first place). I believe there is a migration document > somewhere in there. I participate in the OpenACS project, and I wrote the Oracle Porting chapter on the PostgreSQL documentation hoping that it would help other PostgreSQL users, drawing from the experience of working on OpenACS. See the PostgreSQL PL/pgSQL documentation. -Roberto -- +----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer Don't worry, I'm go³ng tµ bäckup tüdäæ!&%#~%
> Hi there, > > Has anyone migrated Oracle database to Postgres? If yes, could you please > help me - what > are the steps involved, complications you faced, any other info you think is > appropriate. Sorry to answer so lately! I made some of this migrations. From Oracle 7 to 8i databases. Basicaly, have a look at Gilles Darold's tool called ora2pg. You'll find this tool under /contrib/oracle/ in PG sources. Or maybe, using a distribution, you'll have to install postgresql-contrib package. Depending the distribution you use, it may differ. For example, under Debian, install : postgresql-contrib - Additional facilities for PostgreSQL It's a Perl script that is really operational now. look at http://www.samse.fr/GPL/ for more stuff on ora2pg (it's Gilles'www) Ora2Pg as ability to connect to your existing Oracle DB, get the schema, translate it in PG, connect to PG, then put the schema in PG. Then, it can also get the datas from Oracle and put in either in a flat file or directly in the database. I prefer working with flat file to make some tests at first, verifying what ora2pg has done. You'll never had any problem since your Oracle database doesn't use esoterical Oracle datatypes. I give you a hint: *NEVER* translate Oracle's NUMBER(4,0) into PG NUMERIC(4,0) or you'll have performances problems. Just transalate NUMBER(4,0) into INTEGER. PG is well known to work better with INTEGERs. In fact INTEGER datatype in PG is the best compromise between space needed on the hard drive and performances. With NUMBER(x,y) with y not null, it's you to decide and making tests. Maybe you can multiply per 100 if y=2? and then be able to translate also in INTEGER? Or this is maybe to costly for client-side applications (too much modify)... Be aware that NUMERIC(x,y) in PG is very powerfull for atypic uses, for example (18,9) ... and not that much for other uses. That's my point of view. I give you other Oracle 2 Pg links with much interest: Open ACS links: I understood they migrated from Oracle to PostgreSQL. They put many sources and comments on this subject (thanks to Open ACS!!) http://openacs.org/doc/openacs/html/oracle-to-pg-porting.html (by James Shannon, Ben Adida, and Don Baccus) http://openacs.org/ If you have some CONNECT BY statments, I really think the best way is to use a sort_key column as explained here: http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=0000j6&topic_id=12&topic=OpenACS%204%2e0%20Design (note, this is still from OpenACS guys) The implementation they made is based on Miguel Sofer (a mathematician) solution: http://www.utdt.edu/~mig/trees.tar.gz And finally, have a look at the bests technicals links for PG at Justin'Cliff www: http:\\techdocs.postgresql.org Don't hesitate in sending questions, we will answer as far as we know on the subject. Please don't post at to many lists. Thanks. -- Jean-Paul ARGUDO IDEALX S.A.S Consultant bases de données 15-17, av. de Ségur http://www.idealx.com F-75007 PARIS