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

1: Oracle 816 Data to PostgreSQL 7.1.3

Posted by Sky Cree on 08/15/02 03:45 AM

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

2: Response to Oracle 816 Data to PostgreSQL 7.1.3 (response to 1)

Posted by Jade Rubick on 08/22/02 12:36 AM

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?

3: Response to Oracle 816 Data to PostgreSQL 7.1.3 (response to 1)

Posted by Sky Cree on 08/22/02 12:48 AM

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

4: Response to Oracle 816 Data to PostgreSQL 7.1.3 (response to 1)

Posted by Jon Griffin on 08/22/02 01:45 AM

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.

5: Response to Oracle 816 Data to PostgreSQL 7.1.3 (response to 1)

Posted by Andrew Piskorski on 08/22/02 09:46 AM

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.

6: Response to Oracle 816 Data to PostgreSQL 7.1.3 (response to 1)

Posted by Jade Rubick on 08/22/02 08:34 PM

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.

7: Response to Oracle 816 Data to PostgreSQL 7.1.3 (response to 1)

Posted by Don Baccus on 08/22/02 08:48 PM

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.

8: Response to Oracle 816 Data to PostgreSQL 7.1.3 (response to 1)

Posted by Jon Griffin on 08/22/02 09:48 PM

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.

9: Response to Oracle 816 Data to PostgreSQL 7.1.3 (response to 1)

Posted by Don Baccus on 08/23/02 01:07 AM

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.

10: Response to Oracle 816 Data to PostgreSQL 7.1.3 (response to 1)

Posted by Jon Griffin on 08/23/02 01:50 AM

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.

11: Response to Oracle 816 Data to PostgreSQL 7.1.3 (response to 1)

Posted by Jon Griffin on 08/23/02 04:09 AM

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

12: Response to Oracle 816 Data to PostgreSQL 7.1.3 (response to 1)

Posted by Michael A. Cleverly on 09/04/02 07:56 AM

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.

13: Re: Response to Oracle 816 Data to PostgreSQL 7.1.3 (response to 12)

Posted by Thara S Pillai on 03/02/03 04:42 PM

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.

14: Re: Response to Oracle 816 Data to PostgreSQL 7.1.3 (response to 13)

Posted by Jade Rubick on 03/03/03 07:21 PM

Thara, look at this document: https://openacs.org/faq/one-faq?faq_id=43841