7 ms·

ARIN migrated from Oracle to PostgreSQL

116 points by jeffdavis 13y ago

diminoten 13y ago
I wonder if this was as much work as I suspect it was.
- tracker1 13y ago
  Depends on the complexity of their schema, and how many and of what complexity any stored procedures they relied on were.
  - DrJokepu 13y ago
    In many ways PostgreSQL is fairly similar to Oracle and it also supports its own version of PL/SQL. There are tools to automate migration of both schema and data. I reckon the real difficulty of such a transition is updating application code (and legacy code) to work with PostgreSQL.
    - midas007 13y ago
      This is by design because EnterpriseDB (based on Postgres) is basically intended as a replacment for Oracle. Anything commercial that uses fancy Oracle features would want to look at this because it would be easier to support.
  - raverbashing 13y ago
    This is the real story "Migrating from Oracle to PSQL" means almost nothing (from a technical point of view) But what were the issues? How did their schema look like then and now? Any stored procedures? Any Oracle-only features that had to be migrated? Any PSQL-only features now being used?
    - diminoten 13y ago
      Having worked on this team (QA side) for 3 years, I'm "just guessing" this was a big effort. Very cool Mark Kosters and Andy Newton gave the dev team the bandwidth to do this, their plates are almost always full of new features and whatnot.
jeffdavis 13y ago
It should also be noted that Afilias, which manages .org, .info, .mobi (and more), uses PostgreSQL. So PostgreSQL is pretty heavily involved in internet infrastructure right now.
- rch 13y ago
  As it has been. Is this an under-documented fact?
  - ghostdiver 13y ago
    What version, configuration, hardware, do they use replication and how?
    - walshemj 13y ago
      Me to having worked for a registry ICANS requirements for up time and availability are "challenging" Would be very interesting to see how they setup PostgreSQL to support HA
    - rch 13y ago
      I just remember reading that the .org TLD was running on Postgres ten plus years ago. Yahoo knows a thing or two about scaling up on Postgres as well, as I recall. When these decisions were made, it was generally expected that one would have quite a lot of work to do, regardless.
      - ghostdiver 13y ago
        Makes me wonder how much hacking of postgres source code it required.
trippy_biscuits 13y ago
I especially appreciate PostgreSQL's documentation. I find it easy to locate, search, and understand. I certainly have not had that experience with Oracle docs.
- octix 13y ago
  Why one would need docs if there are plenty of 100-200$/hours oracle consultants :)
  - samplonius 13y ago
    It means you need a $300/hr consultant :)
- dragonwriter 13y ago
  I've always found Oracle's database documentation to be reasonably good -- PostgreSQL's is better, but I'd rather be using Oracle's than Microsoft's SQL Server docs.
  - radicalbyte 13y ago
    To be fair, Microsoft's reference material is pretty good - it's standardized, comprehensive and available for every version. You just need to know what to google to find the right page..
    - dragonwriter 13y ago
      Yeah, that's why its bad: the key to the utility of reference material is organization.
- electrum 13y ago
  Oracle's documentation is excellent and is WAY more comprehensive than PostgreSQL. For example, most of PostgreSQL's SQL functions have a single line in a table to describe them, whereas each one in Oracle has it's own section with a full description, examples, etc. I agree that the relative small size of PostgreSQL documentation and the fact that it fits in a single "book" compared to the many books that comprise the Oracle documentation can make it easier to find something. But can't say I've ever seen a product with as much quality documentation as Oracle. You can find the documentation for Oracle 12c here: http://www.oracle.com/pls/db121/homepage http://www.oracle.com/pls/db121/homepage As a user, you want to look in "SQL Language Reference" first, followed by "Data Warehousing Guide" (found in a separate folder on the left side). As an administrator, "Concepts" is required reading followed by "Administrator's Guide".
derleth 13y ago
Does anyone here know how big the relevant database actually is?
- midas007 13y ago
  We might try publicly brow-beating Mark Kosters for an answer, that would totally work. Or maybe: asking nicely. most probable email addr: markk at arin dot net https://www.arin.net/announcements/2013/20131214.html https://www.arin.net/announcements/2013/20131214.html
  - derleth 13y ago
    You can brow-beat, I asked nicely.
elchief 13y ago
There's an extension for PG that adds many of Oracle's functions: http://pgxn.org/dist/orafce/ http://pgxn.org/dist/orafce/ "This module allows use a well known Oracle's functions and packages inside PostgreSQL"

116 points by jeffdavis 13y ago

diminoten 13y ago

I wonder if this was as much work as I suspect it was.

tracker1 13y ago
Depends on the complexity of their schema, and how many and of what complexity any stored procedures they relied on were.
- DrJokepu 13y ago
  In many ways PostgreSQL is fairly similar to Oracle and it also supports its own version of PL/SQL. There are tools to automate migration of both schema and data. I reckon the real difficulty of such a transition is updating application code (and legacy code) to work with PostgreSQL.
  - midas007 13y ago
    This is by design because EnterpriseDB (based on Postgres) is basically intended as a replacment for Oracle. Anything commercial that uses fancy Oracle features would want to look at this because it would be easier to support.
- raverbashing 13y ago
  This is the real story "Migrating from Oracle to PSQL" means almost nothing (from a technical point of view) But what were the issues? How did their schema look like then and now? Any stored procedures? Any Oracle-only features that had to be migrated? Any PSQL-only features now being used?
  - diminoten 13y ago
    Having worked on this team (QA side) for 3 years, I'm "just guessing" this was a big effort. Very cool Mark Kosters and Andy Newton gave the dev team the bandwidth to do this, their plates are almost always full of new features and whatnot.

jeffdavis 13y ago

It should also be noted that Afilias, which manages .org, .info, .mobi (and more), uses PostgreSQL. So PostgreSQL is pretty heavily involved in internet infrastructure right now.

rch 13y ago
As it has been. Is this an under-documented fact?
- ghostdiver 13y ago
  What version, configuration, hardware, do they use replication and how?
  - walshemj 13y ago
    Me to having worked for a registry ICANS requirements for up time and availability are "challenging" Would be very interesting to see how they setup PostgreSQL to support HA
  - rch 13y ago
    I just remember reading that the .org TLD was running on Postgres ten plus years ago. Yahoo knows a thing or two about scaling up on Postgres as well, as I recall. When these decisions were made, it was generally expected that one would have quite a lot of work to do, regardless.
    - ghostdiver 13y ago
      Makes me wonder how much hacking of postgres source code it required.

trippy_biscuits 13y ago

I especially appreciate PostgreSQL's documentation. I find it easy to locate, search, and understand. I certainly have not had that experience with Oracle docs.

octix 13y ago
Why one would need docs if there are plenty of 100-200$/hours oracle consultants :)
- samplonius 13y ago
  It means you need a $300/hr consultant :)
dragonwriter 13y ago
I've always found Oracle's database documentation to be reasonably good -- PostgreSQL's is better, but I'd rather be using Oracle's than Microsoft's SQL Server docs.
- radicalbyte 13y ago
  To be fair, Microsoft's reference material is pretty good - it's standardized, comprehensive and available for every version. You just need to know what to google to find the right page..
  - dragonwriter 13y ago
    Yeah, that's why its bad: the key to the utility of reference material is organization.
electrum 13y ago
Oracle's documentation is excellent and is WAY more comprehensive than PostgreSQL. For example, most of PostgreSQL's SQL functions have a single line in a table to describe them, whereas each one in Oracle has it's own section with a full description, examples, etc. I agree that the relative small size of PostgreSQL documentation and the fact that it fits in a single "book" compared to the many books that comprise the Oracle documentation can make it easier to find something. But can't say I've ever seen a product with as much quality documentation as Oracle. You can find the documentation for Oracle 12c here: http://www.oracle.com/pls/db121/homepage http://www.oracle.com/pls/db121/homepage As a user, you want to look in "SQL Language Reference" first, followed by "Data Warehousing Guide" (found in a separate folder on the left side). As an administrator, "Concepts" is required reading followed by "Administrator's Guide".

derleth 13y ago

Does anyone here know how big the relevant database actually is?

midas007 13y ago
We might try publicly brow-beating Mark Kosters for an answer, that would totally work. Or maybe: asking nicely. most probable email addr: markk at arin dot net https://www.arin.net/announcements/2013/20131214.html https://www.arin.net/announcements/2013/20131214.html
- derleth 13y ago
  You can brow-beat, I asked nicely.

elchief 13y ago

There's an extension for PG that adds many of Oracle's functions: http://pgxn.org/dist/orafce/ http://pgxn.org/dist/orafce/ "This module allows use a well known Oracle's functions and packages inside PostgreSQL"