Forum OpenACS Q&A: RFC: How to port from oracle to postgres and vice versa?

Collapse
1: RFC: How to port from oracle to postgres and vice versa?
Posted by Nima Mazloumi on 05/06/04 10:53 AM
Hi there.

Has someone some notes on how to port packages between oracle and postgres?

I would like to port the webmail package to postgres and would like to know what pitfalls will be along the way.

Maybe we could take it as a case study for the documentation. So just post what ever comes to your mind that one should take care of.

Greetings,
Nima

Collapse
2: Re: RFC: How to port from oracle to postgres and vice versa? (response to 1)
Posted by Ola Hansson on 05/06/04 12:08 PM
Nima,

There is a porting document on the old site: http://sdm.openacs.org/4/porting-resources

It is probably a good starting point ...

Collapse
3: Re: RFC: How to port from oracle to postgres and vice versa? (response to 1)
Posted by Ola Hansson on 05/06/04 12:14 PM
I swear I migrated the porting FAQ once upon a time, but it seems to be gone now 😟
Collapse
4: Re: RFC: How to port from oracle to postgres and vice versa? (response to 1)
Posted by Nima Mazloumi on 05/06/04 12:53 PM
Great..I will check it out. Do you still have a copy of that faq?
Collapse
5: Re: RFC: How to port from oracle to postgres and vice versa? (response to 1)
Posted by Ola Hansson on 05/06/04 01:20 PM
It's still on our old site: http://sdm.openacs.org/faq/one?scope=public&faq_id=23

I should probably point out, though, that the webmail package has been deemed unworthy of being ported by several OpenACS hackers, at least in part due to its dependency on Java and the desire to keep the number of programming languages involved to a minimum.

OTOH, I think it would rock to integrate *some* kind of webmail solution with OpenACS.

Just my $ .02

Collapse
6: Re: RFC: How to port from oracle to postgres and vice versa? (response to 1)
Posted by Silviu Dascal on 02/20/07 10:56 AM
Hi Ola ,
i saw one of your postings on openacs forum regarding porting from Postgre to Oracle and vice versa . I am quite interested in this topic . U gave there a link : http://sdm.openacs.org/faq/one?scope=public&faq_id=23
But sadly that link is expired . Can u pls tell me where i could find more information about this issue ?
thank u verry much for your kind help
Also any help with migration would be usefull . tnks ,
silviu
Ps i would prefer migrating without using third party tools ...
Collapse
7: Re: RFC: How to port from oracle to postgres and vice versa? (response to 6)
Posted by Caroline Meeks on 02/20/07 03:11 PM
Hi Silviu,

Are you trying to port the code of a specific package or are you trying to port an existing installation from one database to another?

Thanks

Collapse
8: Re: RFC: How to port from oracle to postgres and vice versa? (response to 7)
Posted by Silviu Dascal on 02/20/07 03:20 PM
Hi Caroline ,
i am trying to move one complete instalation from one database to another . I am using right now post gre and want to move everything to oracle . Is there a way to do this without major efforts ?
Except flat files or third party solutions ?
tnks ,
Silviu
Collapse
9: Re: RFC: How to port from oracle to postgres and vice versa? (response to 3)
Posted by Caroline Meeks on 02/21/07 05:25 PM
are you using openacs?
Collapse
10: Re: RFC: How to port from oracle to postgres and vice versa? (response to 9)
Posted by Silviu Dascal on 02/22/07 10:51 AM
what do u mean by using openacs ?
Collapse
11: Re: RFC: How to port from oracle to postgres and vice versa? (response to 6)
Posted by Håkan Ståby on 02/23/07 10:23 AM
Here is some migration information that I used a few years ago when I did some migration work. I don't remember the details but here are a few recent links to such information:

From Postgres own site: http://www.postgresql.org/docs/8.0/interactive/plpgsql-porting.html
There are several more entries on that topic. Just search for it on the PostgreSQL site.

A quick summary of common conversion issues:
http://www.cs.cmu.edu/~pmerson/docs/OracleToPostgres.pdf

An old document from this site:
https://openacs.org/doc/openacs-3/html/oracle-to-pg-porting.html

Regarding converting "connect by" from Oracle, here is an example that I believe is accurate. You can use the implementation tree_sortkey that is used in several packages in OACS to create a hierarchy similar to what you can achieve with "start with...connect by" in Oracle.

<fullquery name="package_recreate_hierarchy.select_object_types">
<querytext>

select t.object_type
from acs_object_types t
where t.dynamic_p = 't'
start with t.object_type = :object_type
connect by prior t.object_type = t.supertype

</querytext>
</fullquery>

<fullquery name="package_recreate_hierarchy.select_object_types">
<querytext>

select t2.object_type
from acs_object_types t1, acs_object_types t2
where t2.dynamic_p = 't'
and t2.tree_sortkey between t1.tree_sortkey and tree_right(t1.tree_sortkey)
and t1.object_type = :object_type

</querytext>
</fullquery>

I hope this information helps. It will take time to convert complex queries, otherwise it's rather straightforward.

But then again, you might be looking at a dump and load or similar and I have no personal experience with moving a whole installation of OACS between databases. I am guessing that you need to create your own export and import scripts if that's the case.