Forum OpenACS Q&A: Oracle 816 Data to PostgreSQL 7.1.3
Linux 6.2 using Kernel 2.2.20.
I would like to migrate my Oracle data to PostgreSQL 7.1.3, OACS 4.5,
Has anyone done this or know of a way to do this?
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
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.
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.
company a great deal of money down the road. When our Oracle
license comes up for renewal, I wouldn't mind going with
Seriously, Jon, there is a possibility our company would pay you
for this work if it worked flawlessly.
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.
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.
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.
It really isn't trivial.
I ask that this isn't copied anywhere at this time as it is not even close to being ready for primetime.
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.