Forum OpenACS Q&A: Migration from ACS/pg to ACS/Oracle?
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?
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.