8 ms·

Ora2Pg – Oracle to PostgreSQL database schema converter

281 points by jka 6y ago

webmobdev 6y ago
Surprised that Oracle hasn't yet sued these developers for some api violation or similar bullshit.
- jolux 6y ago
  I mean, this is software that consumes an Oracle API, not software that replicates and exposes an Oracle API.
  - qz2 6y ago
    Larry's lawyers would probably see that as a technicality which can be resolved in court.
    - schoolornot 6y ago
      AWS DMS basically does this and then some.
      - jolux 6y ago
        Looks to be another migration service.
    - jolux 6y ago
      I wouldn’t put it past them but I think a more analogous case would be Postgres copying all the SQL for their default schemas for compatibility reasons.
      - pulse7 6y ago
        Just like Oracle copied IBM's SQL in the late 70's...
        jolux 6y ago
        People always say this but it’s not at all clear cut to me that this is covered by the appeals court’s ruling.
  - gpm 6y ago
    Wouldn't assuming an oracle api make it a derivative work though...
    - jolux 6y ago
      I mean I don’t think the Oracle license is viral? I don’t know much about proprietary software licensing though.
      - gpm 6y ago
        The default state of copyrighted works (and most commercial licenses) is "no derivatives allowed". A viral license is "derivative works allowed only if they are distributed under the same license". A permissive open source license is "derivative works allowed and they can be licensed freely". I imagine any license oracle has offered on it's db is the no derivative works kind not an open source kind.
        jolux 6y ago
        I understand copyright but I’m not sure an open source library that uses a proprietary API is considered a derivative work. Is there case law here?
        gpm 6y ago
        Oh, so the other context here is the Google Oracle lawsuit. In it Oracle is arguing that google implementing portions of the java standard library api in android was copyright infringement. It's due in front of the supreme court October 7th (Wednesday after next). It's... certainly a novel interpretation of what is copyrightable, and if they win it's going to cause quite a few problems (e.g. wine is going to be infringing windows copyright, and windows is going to be infringing Linux's copyright (with WSL and some of their other apis), and so on and so forth. Literally everyone, including oracle (see: sql, cloud apis, etc), has been acting like APIs aren't copyrightable. So anyways, no, no case law (yet) since no one has even established that APIs are copyrightable.
chousuke 6y ago
I've used this for migrating some applications. It worked well enough. In addition, the "orafce" extension was also useful in reducing required changes to the actual SQL queries the applications made. In my case, the applications were relatively simple, but many of the queries used sequences explicitly, and the lack of a common syntax there was a slight pain point.
robjan 6y ago
I used this just a few weeks ago and was impressed at how comprehensive the tool is as well as the level of detail in the readme.
majkinetor 6y ago
This is fantastic. I am monthly approached by Oracle the opposite way tho, to migrate services I architect from Postgres to Oracle db. Never succumbed to this date (I would rather switch to farming TBH). With this tool, I can go offensive :) Somebody also mentioned in the comments orafce extension - even more awesome, I can tell them that I already use Oracle db , its just inside Postgres which is technical detail :D.
- jmnicolas 6y ago
  > I would rather switch to farming Beware of John Deere though, I heard they're the Oracle of farming ;)
cifvts 6y ago
So happy to see this project in here. It was the very first OpenSource project I have ever contributed to.
forinti 6y ago
You could put your data in Postgresql and use Oracle XE/ORDS/APEX as a front-end and have the best of both worlds for free.
bdcravens 6y ago
I've been using AWS's Schema Conversion Tool to move from SQL Server to Postgresql, and it has been a good experience.
systemvoltage 6y ago
Honestly curious: Is there any objective reason for going with Oracle DB over PostgreSQL? Putting emotions aside :). Or is it that Oracle runs on the fumes of legacy lock ins?
- cdmckay 6y ago
  The first that comes to mind is cost.
  - Drdrdrq 6y ago
    I think he was asking the other way around. Unless you are getting a sales commission, of course - then it does make sense to go with Oracle because of the cost. :)
- pydry 6y ago
  Also Oracle salespeople.
  - forinti 6y ago
    I can deal with the salespeople, they are actually very nice. I'm fed up with support (it takes so much work to open a ticket) and with the documentation (excessively verbose and full of marketing).
- crb002 6y ago
  PL/SQL - also if you plan on using Oracle Cloud which seems to have a decent serverless compute offering. I'm still curious why IBM hasn't gotten Amazon to host some System Z DB2 mainframes to compete. Mainframe is the CA of CAP. Personally I chose SQLite sharded on S3 and dynamodb.
  - theta_d 6y ago
    > I'm still curious why IBM hasn't gotten Amazon to host some System Z DB2 mainframes to compete. Why would they use AWS? They have their own cloud: https://cloud.ibm.com/catalog#services https://cloud.ibm.com/catalog#services
craigkerstiens 6y ago
A few other really handy tools when migrating from Oracle to Postgres: Orafce - An extension to bundle in Oracle functions -https://github.com/orafce/orafce https://github.com/orafce/orafce OracleFDW - A foreign data wrapper to query directly into Oracle from Postgres - https://github.com/laurenz/oracle_fdw https://github.com/laurenz/oracle_fdw
- sohooo 6y ago
  also check out ora_migrator https://github.com/cybertec-postgresql/ora_migrator https://github.com/cybertec-postgresql/ora_migrator (another extension from Cybertec) which uses oracle_fdw under the hood. intro blog post: https://www.cybertec-postgresql.com/en/ora_migrator-moving-from-oracle-to-postgresql-even-faster/ https://www.cybertec-postgresql.com/en/ora_migrator-moving-f...
  - lhenk 6y ago
    CYBERTEC also offers a more advanced Oracle to PostgreSQL migration product: https://www.cybertec-postgresql.com/de/produkte/cybertec-migrator/ https://www.cybertec-postgresql.com/de/produkte/cybertec-mig... Here's a talk from two weeks ago by the CEO: https://www.youtube.com/watch?v=dDRrlgnH7N8 https://www.youtube.com/watch?v=dDRrlgnH7N8 Disclaimer: I work at CYBERTEC
benatkin 6y ago
Ora is short for O'Reilly. http://ora.com/ http://ora.com/
- castorp 6y ago
  That's plain wrong. Ora is short for Oracle
  - benatkin 6y ago
    Do they own ora dot kahm?
phoe-krk 6y ago
Related: please consider getting your company to support pgloader[0] so we can get a piece of free software for migrating from Oracle into Postgres. [0] https://github.com/dimitri/pgloader https://github.com/dimitri/pgloader
data_ders 6y ago
Another option, migrate your codebase to use dbt (data build tool) [1] which will give you (among other things) the options of deploying your database project with minimal changes to any number of database products using custom adapters (e.g. dbt-oracle [2] dbt-presto [3]) [1]: https://www.getdbt.com/product/ https://www.getdbt.com/product/ [2]: https://github.com/techindicium/dbt-oracle https://github.com/techindicium/dbt-oracle [3]: https://github.com/fishtown-analytics/dbt-presto https://github.com/fishtown-analytics/dbt-presto
- eyeball 6y ago
  The oracle adapter is pretty incomplete.
irrational 6y ago
We used Org2Pg to move our Oracle database (which we had been on for 15 years) to Postgres recently. Even using Ora2Pg it took us 2 years. 1. Ora2Pg will create a file that has all of your tables, columns, indices, keys etc. in Postgres format. Unfortunately it is great as a first pass, but we found that we had to do a lot of manually tweaking to get it into a really usable state. For instance, it does it's best guess at picking the right data type, but we didn't agree with all of its choices so we had to look through each one and decide which needed changing. Also, the way primary keys are setup on Oracle and Postgres is very different Ora2Pg takes a literal translation of translating how Oracle primary keys are set up instead of using the Postgres way - so we had to fix all of these. 2. It can't really convert functions and triggers. All of these had to be rewritten by hand. 3. It takes a ton of tweaking and experimentation to get Ora2Pg to transfer the data in a decent amount of time. Our first try took nearly 24 hears. We eventually tweaked it down to about an hour. 4. You will still need to rewrite all of your SQL queries into Postgres format. This is the part that took the most time since we have about 5,000 queries and each one had to be checked. 5. The biggest pain is that we couldn't stop development while the database migration was going on. So every time changes were made to the Oracle database, we had to manually make those same changes to the Postgres DDL files. Sigh. I am so glad that project is behind me. I don't ever want to be involved in something like that again. It was totally worth it (Postgres is a dream to work with compared to Oracle), but the process was a nightmare. Some notes about converting queries from Oracle to Postgres: "foo IN (a, b, c)" can often be better done as "foo = ANY(ARRAY[a, b, c])" in Postgres - not least because you can shift to passing in an array of variable size without having to construct a special statement for each. for sorting on the result of a function, consider using a subselect. "SELECT foo FROM (select fun(z) as foo from blaz) tmp order by 1". replace nvl with coalesce replace rownum <= 1 with LIMIT 1 replace listagg with string_agg replace recursive hierarchy (start with/connect by/prior) with recursive replace minus with except replace SYSDATE with CURRENT_TIMESTAMP replace trunc(sysdate) with CURRENT_DATE replace trunc(datelastupdated) with DATE(datelastupduted) or datelastupdated::date replace artificial date sentinels/fenceposts like to_date(’01 Jan 1900’) with '-infinity'::date remove dual table references replace decode with case statements replace unique with distinct replace to_number with ::integer replace regexp_like() with ~ or ~* replace mod with % operator replace merge into with INSERT ... ON CONFLICT… DO UPDATE/NOTHING change the default of any table using sys_guid() as a default to gen_random_uuid() oracle pivot and unpivot do not work in postgres - use unnest ORDER BY NLSSORT(english, 'NLS_SORT=generic_m') becomes ORDER BY gin(insensitive_query(english) gin_trgm_ops) replace UNISTR( with U&’ Oracle: uses IS NULL to check for empty string; postgres uses empty string and null are different If a varchar/text column has a unique index a check needs to be made to make sure empty strings are changed to nulls before adding or updating the column. PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias must be provided for it. - SELECT * FROM ( ) A any functions in the order by clause must be moved to the select statement (e.g. order by lower(column_name)) Any sort of numeric/integer/bigint/etc. inside of a IN statement must not be a string (including 'null' - don't bother trying to use null="", it won't work). Concatenating a NULL with a NOT NULL will result in a NULL. Pay attention to any left joins. If a column from a left join is used in a where or select clause it might be null. For sequences, instead of sequence_name.nextval use nextval('sequence_name')
gogopuppygogo 6y ago
Interesting. I’ve been looking at writing a script that parses the daily files for the California State Senate and State Assembly to create an API along with exposing a web app that offers a UI to view and search changes so folks interested can look around. https://www.senate.ca.gov/dailyfile https://www.senate.ca.gov/dailyfile https://clerk.assembly.ca.gov/daily-file https://clerk.assembly.ca.gov/daily-file They give raw sql files in there but also a bunch of oracle that I have had no way to read. This tool might help me see if there are differences in the file types.

281 points by jka 6y ago

webmobdev 6y ago

Surprised that Oracle hasn't yet sued these developers for some api violation or similar bullshit.

jolux 6y ago
I mean, this is software that consumes an Oracle API, not software that replicates and exposes an Oracle API.
- qz2 6y ago
  Larry's lawyers would probably see that as a technicality which can be resolved in court.
  - schoolornot 6y ago
    AWS DMS basically does this and then some.
    - jolux 6y ago
      Looks to be another migration service.
  - jolux 6y ago
    I wouldn’t put it past them but I think a more analogous case would be Postgres copying all the SQL for their default schemas for compatibility reasons.
    - pulse7 6y ago
      Just like Oracle copied IBM's SQL in the late 70's...
      - jolux 6y ago
        People always say this but it’s not at all clear cut to me that this is covered by the appeals court’s ruling.
- gpm 6y ago
  Wouldn't assuming an oracle api make it a derivative work though...
  - jolux 6y ago
    I mean I don’t think the Oracle license is viral? I don’t know much about proprietary software licensing though.
    - gpm 6y ago
      The default state of copyrighted works (and most commercial licenses) is "no derivatives allowed". A viral license is "derivative works allowed only if they are distributed under the same license". A permissive open source license is "derivative works allowed and they can be licensed freely". I imagine any license oracle has offered on it's db is the no derivative works kind not an open source kind.
      - jolux 6y ago
        I understand copyright but I’m not sure an open source library that uses a proprietary API is considered a derivative work. Is there case law here?
        gpm 6y ago
        Oh, so the other context here is the Google Oracle lawsuit. In it Oracle is arguing that google implementing portions of the java standard library api in android was copyright infringement. It's due in front of the supreme court October 7th (Wednesday after next). It's... certainly a novel interpretation of what is copyrightable, and if they win it's going to cause quite a few problems (e.g. wine is going to be infringing windows copyright, and windows is going to be infringing Linux's copyright (with WSL and some of their other apis), and so on and so forth. Literally everyone, including oracle (see: sql, cloud apis, etc), has been acting like APIs aren't copyrightable. So anyways, no, no case law (yet) since no one has even established that APIs are copyrightable.

chousuke 6y ago

I've used this for migrating some applications. It worked well enough. In addition, the "orafce" extension was also useful in reducing required changes to the actual SQL queries the applications made. In my case, the applications were relatively simple, but many of the queries used sequences explicitly, and the lack of a common syntax there was a slight pain point.

robjan 6y ago

I used this just a few weeks ago and was impressed at how comprehensive the tool is as well as the level of detail in the readme.

majkinetor 6y ago

This is fantastic. I am monthly approached by Oracle the opposite way tho, to migrate services I architect from Postgres to Oracle db. Never succumbed to this date (I would rather switch to farming TBH). With this tool, I can go offensive :) Somebody also mentioned in the comments orafce extension - even more awesome, I can tell them that I already use Oracle db , its just inside Postgres which is technical detail :D.

jmnicolas 6y ago
> I would rather switch to farming Beware of John Deere though, I heard they're the Oracle of farming ;)

cifvts 6y ago

So happy to see this project in here. It was the very first OpenSource project I have ever contributed to.

forinti 6y ago

You could put your data in Postgresql and use Oracle XE/ORDS/APEX as a front-end and have the best of both worlds for free.

bdcravens 6y ago

I've been using AWS's Schema Conversion Tool to move from SQL Server to Postgresql, and it has been a good experience.

systemvoltage 6y ago

Honestly curious: Is there any objective reason for going with Oracle DB over PostgreSQL? Putting emotions aside :). Or is it that Oracle runs on the fumes of legacy lock ins?

cdmckay 6y ago
The first that comes to mind is cost.
- Drdrdrq 6y ago
  I think he was asking the other way around. Unless you are getting a sales commission, of course - then it does make sense to go with Oracle because of the cost. :)
pydry 6y ago
Also Oracle salespeople.
- forinti 6y ago
  I can deal with the salespeople, they are actually very nice. I'm fed up with support (it takes so much work to open a ticket) and with the documentation (excessively verbose and full of marketing).
crb002 6y ago
PL/SQL - also if you plan on using Oracle Cloud which seems to have a decent serverless compute offering. I'm still curious why IBM hasn't gotten Amazon to host some System Z DB2 mainframes to compete. Mainframe is the CA of CAP. Personally I chose SQLite sharded on S3 and dynamodb.
- theta_d 6y ago
  > I'm still curious why IBM hasn't gotten Amazon to host some System Z DB2 mainframes to compete. Why would they use AWS? They have their own cloud: https://cloud.ibm.com/catalog#services https://cloud.ibm.com/catalog#services

craigkerstiens 6y ago

A few other really handy tools when migrating from Oracle to Postgres: Orafce - An extension to bundle in Oracle functions -https://github.com/orafce/orafce https://github.com/orafce/orafce OracleFDW - A foreign data wrapper to query directly into Oracle from Postgres - https://github.com/laurenz/oracle_fdw https://github.com/laurenz/oracle_fdw

sohooo 6y ago
also check out ora_migrator https://github.com/cybertec-postgresql/ora_migrator https://github.com/cybertec-postgresql/ora_migrator (another extension from Cybertec) which uses oracle_fdw under the hood. intro blog post: https://www.cybertec-postgresql.com/en/ora_migrator-moving-from-oracle-to-postgresql-even-faster/ https://www.cybertec-postgresql.com/en/ora_migrator-moving-f...
- lhenk 6y ago
  CYBERTEC also offers a more advanced Oracle to PostgreSQL migration product: https://www.cybertec-postgresql.com/de/produkte/cybertec-migrator/ https://www.cybertec-postgresql.com/de/produkte/cybertec-mig... Here's a talk from two weeks ago by the CEO: https://www.youtube.com/watch?v=dDRrlgnH7N8 https://www.youtube.com/watch?v=dDRrlgnH7N8 Disclaimer: I work at CYBERTEC

benatkin 6y ago

Ora is short for O'Reilly. http://ora.com/ http://ora.com/

castorp 6y ago
That's plain wrong. Ora is short for Oracle
- benatkin 6y ago
  Do they own ora dot kahm?

phoe-krk 6y ago

Related: please consider getting your company to support pgloader[0] so we can get a piece of free software for migrating from Oracle into Postgres. [0] https://github.com/dimitri/pgloader https://github.com/dimitri/pgloader

data_ders 6y ago

Another option, migrate your codebase to use dbt (data build tool) [1] which will give you (among other things) the options of deploying your database project with minimal changes to any number of database products using custom adapters (e.g. dbt-oracle [2] dbt-presto [3]) [1]: https://www.getdbt.com/product/ https://www.getdbt.com/product/ [2]: https://github.com/techindicium/dbt-oracle https://github.com/techindicium/dbt-oracle [3]: https://github.com/fishtown-analytics/dbt-presto https://github.com/fishtown-analytics/dbt-presto

eyeball 6y ago
The oracle adapter is pretty incomplete.

irrational 6y ago

We used Org2Pg to move our Oracle database (which we had been on for 15 years) to Postgres recently. Even using Ora2Pg it took us 2 years. 1. Ora2Pg will create a file that has all of your tables, columns, indices, keys etc. in Postgres format. Unfortunately it is great as a first pass, but we found that we had to do a lot of manually tweaking to get it into a really usable state. For instance, it does it's best guess at picking the right data type, but we didn't agree with all of its choices so we had to look through each one and decide which needed changing. Also, the way primary keys are setup on Oracle and Postgres is very different Ora2Pg takes a literal translation of translating how Oracle primary keys are set up instead of using the Postgres way - so we had to fix all of these. 2. It can't really convert functions and triggers. All of these had to be rewritten by hand. 3. It takes a ton of tweaking and experimentation to get Ora2Pg to transfer the data in a decent amount of time. Our first try took nearly 24 hears. We eventually tweaked it down to about an hour. 4. You will still need to rewrite all of your SQL queries into Postgres format. This is the part that took the most time since we have about 5,000 queries and each one had to be checked. 5. The biggest pain is that we couldn't stop development while the database migration was going on. So every time changes were made to the Oracle database, we had to manually make those same changes to the Postgres DDL files. Sigh. I am so glad that project is behind me. I don't ever want to be involved in something like that again. It was totally worth it (Postgres is a dream to work with compared to Oracle), but the process was a nightmare. Some notes about converting queries from Oracle to Postgres: "foo IN (a, b, c)" can often be better done as "foo = ANY(ARRAY[a, b, c])" in Postgres - not least because you can shift to passing in an array of variable size without having to construct a special statement for each. for sorting on the result of a function, consider using a subselect. "SELECT foo FROM (select fun(z) as foo from blaz) tmp order by 1". replace nvl with coalesce replace rownum <= 1 with LIMIT 1 replace listagg with string_agg replace recursive hierarchy (start with/connect by/prior) with recursive replace minus with except replace SYSDATE with CURRENT_TIMESTAMP replace trunc(sysdate) with CURRENT_DATE replace trunc(datelastupdated) with DATE(datelastupduted) or datelastupdated::date replace artificial date sentinels/fenceposts like to_date(’01 Jan 1900’) with '-infinity'::date remove dual table references replace decode with case statements replace unique with distinct replace to_number with ::integer replace regexp_like() with ~ or ~* replace mod with % operator replace merge into with INSERT ... ON CONFLICT… DO UPDATE/NOTHING change the default of any table using sys_guid() as a default to gen_random_uuid() oracle pivot and unpivot do not work in postgres - use unnest ORDER BY NLSSORT(english, 'NLS_SORT=generic_m') becomes ORDER BY gin(insensitive_query(english) gin_trgm_ops) replace UNISTR( with U&’ Oracle: uses IS NULL to check for empty string; postgres uses empty string and null are different If a varchar/text column has a unique index a check needs to be made to make sure empty strings are changed to nulls before adding or updating the column. PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias must be provided for it. - SELECT * FROM ( ) A any functions in the order by clause must be moved to the select statement (e.g. order by lower(column_name)) Any sort of numeric/integer/bigint/etc. inside of a IN statement must not be a string (including 'null' - don't bother trying to use null="", it won't work). Concatenating a NULL with a NOT NULL will result in a NULL. Pay attention to any left joins. If a column from a left join is used in a where or select clause it might be null. For sequences, instead of sequence_name.nextval use nextval('sequence_name')

gogopuppygogo 6y ago

Interesting. I’ve been looking at writing a script that parses the daily files for the California State Senate and State Assembly to create an API along with exposing a web app that offers a UI to view and search changes so folks interested can look around. https://www.senate.ca.gov/dailyfile https://www.senate.ca.gov/dailyfile https://clerk.assembly.ca.gov/daily-file https://clerk.assembly.ca.gov/daily-file They give raw sql files in there but also a bunch of oracle that I have had no way to read. This tool might help me see if there are differences in the file types.