Forum OpenACS Q&A: Oracle 816 Data to PostgreSQL 7.1.3

Collapse
Posted by Sky Cree on
I am running ACS 4.2 Beta, Aolserver 3.1 and Oracle 8iR2 on Redhat
Linux 6.2 using Kernel 2.2.20.

I would like to migrate my Oracle data to PostgreSQL 7.1.3, OACS 4.5,
Aolserver 3.3.

Has anyone done this or know of a way to do this?

Thanks,
Sky

Collapse
Posted by Jade Rubick on
I think it's a pretty time-intensive thing to do. But it's probably no
more difficult than what our company is going to be doing soon,
migrating an ACS 3.4x installation to OACS 4.5+. We're not
switching databases for now, though. Still on Oracle.

How much data do you have? Is it really imporant for you to do
this?

Collapse
Posted by Sky Cree on
Hi,
Thanks for the response.

It is not terribly important that we do this now. I really wanted to see if it was possible, but from doing my own research I can see that it is going to be a very intense process. We will continue to use Oracle for now but also will think of switching to oacs.

Thanks

Collapse
Posted by Jon Griffin on
I will probably be doing this for a client.

I will make all scripts and documentation public. It is a very large job as nothing exists to even come close to doing this and the site is a production site.

Stay tuned.

Collapse
Posted by Andrew Piskorski on
Jon, that's awesome.  Good notes from the first person to blaze that trail would surely be hugely helpfull to anyone else considering such a switch.
Collapse
Posted by Jade Rubick on
Let me second this. If this worked flawlessly, it could save our
company a great deal of money down the road. When our Oracle
license comes up for renewal, I wouldn't mind going with
Postgres instead...

Seriously, Jon, there is a possibility our company would pay you
for this work if it worked flawlessly.

Collapse
Posted by Don Baccus on
Can't Oracle or some available tool dump database contents in the form of "insert" statements?

If so you should be in good shape as long as it outputs NULL rather than '' for NULL strings.

More problematic is tracking datamodel changes from 4.2 Beta to OACS 4.5 which was based on, but is not exactly in agreement with, ACS 4.2 final.

Collapse
Posted by Jon Griffin on
Don,

The big problem is the schema changes as well as the connect by use.

I think that data will have to be dumped and massaging done (thus some kind of scripts). Of course any ideas you have would be appreciated.

Collapse
Posted by Don Baccus on
Ignoring the whole ACS 4.2 vs. OACS 4.5 issue, if I were asked to move a bunch of data from OACS 4.5 Oracle to OACS 4.5 PG I would load the schema from source and only try to migrate data.

The tree sortkey stuff is automatically maintained by triggers so that's not an issue, really.  I think what Jon's pointing out is that Sky would have to port over any code they've written as well, and that CONNECT BY queries indeed take some work to port (well, lots of things do, oh, the memories!)

A big problem going from ACS 4.2 Beta to OACS 4.5 (Oracle or PG) is that there's probably enough differences in the initial stuff that's built when loading the core and various package schemas that object numbering won't be 1:1.  Ugh, we know for sure they won't be if you don't load schema in exactly the same order.

OK ... this is actually a lot harder than I was thinking.  It may almost make sense to from the beginning plan on writing migration scripts as was done to move the openacs.org 3.x site to the new openacs.org 4.x site (Sloan did something similar to move from SloanSpace V1 to dotLRN/SlonSpace V2).

I've pushed the openacs.org 3.x site from PG into OpenACS 4.5 Oracle using these migration scripts, too, just for the hell of it.  One of the beauties of AOLserver's letting you define db pools connected to different RDBMSs.

Collapse
Posted by Jon Griffin on
That is exactly the problem. I have a doc that I will eliminate any references to the client I am doing this for and it will be put into file-storage.

It really isn't trivial.

Collapse
Posted by Jon Griffin on
I added a (very) preliminary doc describing the steps and reasons to convert. Realize this is not strictly a technical discussion and includes some marketing stuff.

I ask that this isn't copied anywhere at this time as it is not even close to being ready for primetime.

http://jongriffin.com/static/openacs/acs-conversion/upgrade

Collapse
Posted by Michael A. Cleverly on
We did a conversion about a year ago from a custom AOLserver/Solid system to ACS 4.2 Classic on Oracle 8.1.6.  We wrote a series of scripts to crank data out of Solid, possibly munge it a bit, and insert it into Oracle.  We used (surprise, yet another shameless plug), nstcl.  This let us use the database API with both databases.  We could have configured an AOLserver instance to talk to both databases, but then we'd have had to use the old fashioned ns_db interface to one (or both) of them.

I'd be happy to share our scripts if someone is curious at looking at them, though since I imagine we're the only ones to have ever done an AOLserver/Solid to ACS/Oracle migration (and even if we weren't, no one else would have had our legacy data model :^) I don't know that they'd be of any real specific use to anyone.

I'd be happy to help with any effort to create conversion scripts for 4.2 Classic/Oracle to OpenACS/Postgres.

Collapse
Posted by Thara S Pillai on
Hello, I am a computer Application postgraduate student from India. I have to do one project that includes conversion module of oracle database at Unix server to postgresql at Linux. Server in use is Apache. Can you please forward some help? Many thanks in advance, Thara. Semester-6, MCA, College of Engg., Trivandrum Kerala, South India.
Collapse
Posted by Jade Rubick on
Thara, look at this document: http://openacs.org/faq/one-faq?faq_id=43841