Forum OpenACS Q&A: Migration from ACS/pg to ACS/Oracle?

Not that I'm planning on doing this in the foreseeable future, but
it occurs to me that there may be times when one would want to
migrate a site (and all its data) from ACS/pg to ACS/Oracle. How
hard would that be to accomplish?
Posted by Don Baccus on
Postgresql's pg_dump includes an option that will dump your data using  insert statements rather than the bulk-copy statement that it normally uses.  This would facilitate moving your data to another db.

You'd probably be best off loading the schema from the standard Oracle  release, then loading the inserts generated by pg_dump.  Since things  like posting dates, keys derived from sequenes, etc will be passed as constant data rather than calls to things like sysdate() and nextval('sequence_name'), chances are fairly good that much data would  slurp in without change.

EXCEPT for one very important part: text messages > 4KB in length.  Remember that Oracle won't let you insert long text literals into CLOBs directly, you have to do the inserts by segment.

Because of this, it might be easiest to write a Tcl script to dump (say) the bboard table from the PG version (which will soon sport the possibility of really long text messages as a feature) into the proper CLOB insert format.  Ugh.  Minorly ugly, but not too bad.

Other differences might force this approach for the rest of the tables involved, too, but hopefully not...

This is a good question, and one that might be fun for someone to explore once we've got everything ported.  A HOWTO on moving from Oracle->PG and PG->Oracle would be useful.