Forum OpenACS Q&A: Oracle to Postgres migration

Collapse
1: Oracle to Postgres migration
Posted by Keith Paskett on 04/05/05 12:28 AM
Has anyone attemped migrating a site from Oracle to Postgres.

Moving users is fairly easy and any packages that don't use the @#@#@##@# content repository won't be a big deal, but how would I go about moving the bug-tracker or lars-blogger?

Collapse
2: Re: Oracle to Postgres migration (response to 1)
Posted by Malte Sussdorff on 09/24/05 01:37 PM
I am facing the same situation. Has anyone succeeded in this? Keith, why would the CR be a problem? Isn't it as easy as "just move all tables over" and store blobs as text ?
Collapse
3: Re: Oracle to Postgres migration (response to 1)
Posted by Malte Sussdorff on 09/24/05 01:47 PM
I found a tool ora2pg. Has anyone used this successfully?
Collapse
4: Re: Oracle to Postgres migration (response to 1)
Posted by Tracy Adams on 09/26/05 01:58 AM
I've done some major migrations - including for Sloan (3.4 to 4.x) and from Oracle to Postgres for Guatamela (3.4 to 5.x)

Since the data model is fairly complex and doesn't always involve a 1-to-1 table mapping and involved various business logic, I built an engine that would

a) first query the old site for a given type of data (user, group, group membership, forum post, etc)

b) use the APIs of the new site to recreate the data

c) store various mappings like the user id in the old system to the user id in the new system. This allowed me to port things like the author of the bboard message

Collapse
5: Re: Oracle to Postgres migration (response to 1)
Posted by Nima Mazloumi on 09/26/05 10:30 AM
Now that Heidelberg is migrating from Oracle to PostgreSQL I wonder how many installations exists with Oracle. Wouldn't it make sense to ask the rest to migrate as well and focus on the PG development in future?
Collapse
6: Re: Oracle to Postgres migration (response to 1)
Posted by Malte Sussdorff on 09/26/05 05:28 PM
Careful with your horses Nima. Heidelberg is *not* migrating to PostgreSQL at this point in time. Any assumption in this regard is premature and my comment was primarily for gaining knowledge as there is a chance that some of our Oracle clients will move to PostgreSQL
Collapse
7: Re: Oracle to Postgres migration (response to 1)
Posted by Keith Paskett on 11/03/05 08:25 PM
I don't fully understand how the content repository works, which is no doubt why I dislike it. On a postgres instance at least some content is stored in the file system, while an Oracle instance puts it in the DB.

The solution I've come up with, that I'm happy with for now is...

I Set my new postgres site up with a second database connection to the old Oracle site's DB.

Before creating the site I change the object_id_seq create statement to start at a number higher than the last object_id in the old site.

Migrating data that is closely tied to the CR or acs_objects (Groups, memberships, permissions, etc) is then a matter of selecting from the old DB via -dbn switch and calling the appropriate package proc to add the data to the new site. The site superuser id has to be mapped but everything else keeps the object_id it had in the old site.

This approach also lets me leave a lot of old cruft behind.

Collapse
8: Re: Re: Oracle to Postgres migration (response to 4)
Posted by Rocael Hernández Rizzardini on 11/04/05 12:16 AM
The migration to postgres shouldn't be a big deal for the standard OpenACS packages. And probably a good path will be to follow the process that Tracy created for 3.x to 5.x to ensure that everything works fine. A direct tool will be worth to test it as well, and if you previously move your lobs of the CR to the file system, even easy.
Collapse
9: Re: Oracle to Postgres migration (response to 1)
Posted by Malte Sussdorff on 11/04/05 10:11 AM
Would it be wise to upgrade to .LRN 2.2 before switching to PostgreSQL 8 ? And why should one follow the path from 3 to 5 as documented by Tracy (where?)
Collapse
10: Re: Oracle to Postgres migration (response to 1)
Posted by Rajeev Moudgil on 04/11/07 08:26 AM
Hello,

We plan to migrate oracle9i schema to Postgres 8.1. We have downloaded the tool ora2pg including the drivers dbi, dbd::pg, dbd::Oracle.

Is there any user manual or a short document on how to use the tool? Has anyone used this tool to convert the oracle schema?

At this stage we are looking for converting only the database objects excluding the stores procedures/triggers etc.

ANy hints / pointers would be greatly appreciated.

Thanks
Rajeev

Collapse
11: Re: Oracle to Postgres migration (response to 1)
Posted by Kashif_ur-Rehman Qureshi on 05/04/07 12:12 PM
We have used ora2pg migration.
http://www.it.iitb.ac.in/~it603/post/postgresql-8.0.3/contrib/oracle/ora2pg.html

COPY command was working fine but it did not convert VIEWS at all.
It did convert triggers but change trigger definition to RULE in postgre script.
We dont understand why it did it.
We are using Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product. and Postgrees 8.2.3

We required help.

Collapse
12: Re: Oracle to Postgres migration (response to 11)
Posted by Victor Horn on 09/27/15 06:23 PM
I believe you will find all you need here:

https://wiki.postgresql.org/wiki/Main_Page

Collapse
14: Re: Oracle to Postgres migration (response to 13)
Posted by Dave Bauer on 09/28/15 01:48 PM
I believe what we did for an Oracle to PG migration was to do a clean install of OpenACS into Postgresql, then load the data on top of that using ora2pg with some custom SQL.

This means all the stored procedures etc do not need to be ported at all, unless you are using custom non-PG packges but you can write sql install scripts for those instead of trying to automate it.

The only catch is to match sure the few initial install object_ids match up and that you don't have any object_id conflicts when loading the data.