-3

Client has an old system based on Oracle DB. With the current (2013) versions of MySQL or PostGreSQL (consideration also of stored procedures, triggers, etc), which one is easier (would take less man days/ hours) for Oracle DB data to be transferred to?

Or is it irrelevant?

Manny
  • 6,277
  • 3
  • 31
  • 45
  • 1
    There is no way of knowing for sure without information but I just wanted to point out that there is a [commercial PostgreSQL version](http://www.enterprisedb.com/products-services-training/products/postgres-plus-advanced-server/top-10-reasons-use) that boasts Oracle compatibility. – madth3 Jul 19 '13 at 04:53

3 Answers3

3

If it is a reasonably simple database (no complex user functions or types etc), you could try ora2pg. I've had success with that in the past. Testing is required after porting, obviously.

Alternative option -- if you have the same db structure (tables are named the same, same columns, etc), you could create a schema-only postgres db, dump the Oracle tables one by one to csv, then load them into postgres using COPY.

Another option is to create the postgres db (no data, schemas only), and use the oracle_fdw Foreign Data Wrapper to pull data from Oracle directly into your postgres db (executed from within the postgres db)

bma
  • 9,424
  • 2
  • 33
  • 22
1

Nobody can answer this type of question, except for the person who has actually ported it really: Even if it compiles, and you can install it, this doesn't mean that it will run fast (or fast enough).

It depends on the SQL features being used in the application, and how these map to the new database.

So your port could be very easy for 95% of your application, but if you have to find a quick replacement for Oracle's materialized views, it could be cumbersome on PostgreSql, for example.

So a port really starts at the design phase of an application, otherwise you might run in lots of problems, have a look at this.

In your case you have to identify all SQL features, and how easily these map on PostgreSql and MySQL.

You can see differences between PostgreSql and MySQL here, for example. I would opt for PostgreSql, because I haven't had major problems to develop applications which run on Oracle and PostgreSql: Features like window aggregates, recursive CTEs, stored procecedures in Java etc. are available in both systems.

Community
  • 1
  • 1
Beryllium
  • 12,808
  • 10
  • 56
  • 86
1

I have used oracle before and am using postgresql now.

Structure-wise, they are quite similar. But...porting one application from one DB to another DB is not one day, one week, or one month job.

Be prepare to:

1.Test every SQL

2.Test every Stored procedure, trigger, function, etc

3.Test every application module

4.Find out alternative for oracle specific feature, such as model clause, snapshot,etc

In short, there is no magic pill. But from my experience with both, you would find it easier to port them.

hanzpk
  • 123
  • 1
  • 7