Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.postgresql > #134
| From | Mladen Gogala <no@email.here.invalid> |
|---|---|
| Newsgroups | comp.databases.postgresql |
| Subject | Re: migrating oracle to postgres |
| Date | 2011-06-20 20:08 +0000 |
| Organization | solani.org |
| Message-ID | <pan.2011.06.20.20.08.08@email.here.invalid> (permalink) |
| References | <ito056$ido$1@bruford.hrz.tu-chemnitz.de> |
On Mon, 20 Jun 2011 19:28:45 +0200, Username wrote: > Hello group, > > we are facing the task of migrating a large (600k LOC) oracle backend to > postgresql. As i figured out by now is that we theoretically have two > options: > > 1. teach Postgres to understand PL/SQL by using 3rd-party-software 2. > convert oracle PL/SQL to PG PL/SQL > > Has anyone of you already experience with such a job? Where are problems > to be expected? > Are there some tools for automated translating oracle PL/SQL to PG > PL/SQL? How good are they? > Are there other options except the two above? What option is to be > preferred? > > ....and many, many more... > > Thank you in advance. > > Thomas I've had such a pilot project but was forced to give it up. There are several major snags with Postgres: 1) Postgres is the only major database without hints. That makes keeping any deadlines impossible and makes it necessary to rewrite each and every SQL. Tuning SQL in Postgres is done by rewriting it and fixing the model, which takes time, in my case more time than I was alloted. They do have "set mode" statements for the session, but that's in effect for the entire session. Granularity is wrong. 2) Postgres partitioning is not very robust. It's even weaker than Oracle7 partition views because partition elimination only happens during the parse phase. Once the statement is parsed, that's it. Did I mention that there are no global indexes? Also, optimizer seems to be confused with partitioning and is frequently producing bad plans, based on the full table scan of the entire table. 3) The most important problem are the people leading the community. They stubbornly refuse to even talk about hints. Also, there is a closed source version of Postgres, called EnterpriseDB which has hints. One of the pillars of Postgres community is a guy who works for EnterpriseDB and is opposed to hints in the open source version but is merrily selling them in the closed source version. Here is a pearl of wisdom, contributed to this valley of tears by another shining pillar of the Postgres community: http://tinyurl.com/68gu822 To dispel any doubts, I am the DBA that this genius is talking about. 4) Postgres optimizer is bad. Statistics anomalies are frequent, there is an email list devoted to Postgres performance and every other question is "how to speed up this query". There is no tuning methodology, no event interface, nothing. 5) There is no parallelism. Oracle can parallelize queries, Postgres can not. It's as simple as that. There is no cure in sight. There are some other problems, like not caching parsed SQL (no shared pool) but these were sufficient to cast serious doubt on Postgres as a viable option. I am an Oracle DBA for more than 20 years and recent changes in the pricing policy have motivated the company that I work for to look for alternatives, starting with OSS software. Sadly, there are no open source alternatives. The spirit in the Postgres community ensures that things will remain as they are, for the foreseeable future. The next step is to look for the commercial alternatives, like DB/2. However, if you are building a data warehouse, take a look at MongoDB. It's a NoSQL database which can be used to create a very decent data warehouse. If you want to rewrite Oracle code and use it on Postgres, forget it. It's going to be a frustrating and humiliating experience and will produce an underperforming application system. You can contact me directly, should you need any more information. -- http://mgogala.byethost5.com
Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar
migrating oracle to postgres Username <user@domain.tld> - 2011-06-20 19:28 +0200
Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-20 20:08 +0000
Re: migrating oracle to postgres Username <user@domain.tld> - 2011-06-20 23:45 +0200
Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-20 22:37 +0000
Re: migrating oracle to postgres Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-21 02:01 +0000
Re: migrating oracle to postgres "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-21 09:18 +0200
Re: migrating oracle to postgres Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-21 13:38 +0000
Is PostgreSQL good? (was: migrating oracle to postgres) "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-22 12:30 +0200
Re: Is PostgreSQL good? Harry Tuttle <OTPXDAJCSJVU@spammotel.com> - 2011-06-22 13:15 +0200
Re: Is PostgreSQL good? (was: migrating oracle to postgres) Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-22 12:02 +0000
Re: Is PostgreSQL good? "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-06-22 21:52 +0200
Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-22 22:43 +0000
Re: Is PostgreSQL good? (was: migrating oracle to postgres) "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-24 12:29 +0200
Re: Is PostgreSQL good? Hans Castorp <REWYRLXHEGHO@spammotel.com> - 2011-06-24 12:57 +0200
Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-24 14:53 +0200
Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-24 16:32 +0000
Re: Is PostgreSQL good? (was: migrating oracle to postgres) Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-24 13:53 +0000
Re: Is PostgreSQL good? (was: migrating oracle to postgres) "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-27 11:17 +0200
Re: Is PostgreSQL good? (was: migrating oracle to postgres) Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-28 06:11 +0000
Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-28 10:34 +0200
Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-28 13:30 +0000
Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-29 09:50 +0200
Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-29 21:38 +0000
Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-30 09:14 +0200
Re: Is PostgreSQL good? Mladen Gogala <mgogala@no.address.invalid> - 2011-06-30 13:22 +0000
Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-06-30 07:05 -0700
Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-30 15:41 +0000
Re: Is PostgreSQL good? Richard Kettlewell <rjk@greenend.org.uk> - 2011-06-30 16:49 +0100
Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-30 19:24 +0000
Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-06-30 13:21 -0700
Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-06-30 22:42 +0000
Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-01 11:57 -0700
Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-07-01 22:58 +0000
Re: Is PostgreSQL good? Richard Kettlewell <rjk@greenend.org.uk> - 2011-07-01 14:07 +0100
Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-07-01 13:29 +0000
Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-06-30 13:17 -0700
Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-30 23:53 +0000
Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-01 10:13 +0200
Re: Is PostgreSQL good? Mladen Gogala <gogala.mladen@gmail.com> - 2011-07-01 13:38 +0000
Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-01 16:58 +0200
Re: Is PostgreSQL good? Mladen Gogala <no@email.here.invalid> - 2011-07-01 15:33 +0000
Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-01 11:39 -0700
Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-04 16:39 +0200
Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-04 12:11 -0700
Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-05 08:58 +0200
Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-05 11:29 -0700
Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-06 09:00 +0200
Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-06 01:00 -0700
Re: Is PostgreSQL good? "Laurenz Albe" <invite@spam.to.invalid> - 2011-07-07 09:05 +0200
Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-07 00:34 -0700
Re: Is PostgreSQL good? Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2011-07-04 20:12 +0100
Re: Is PostgreSQL good? Don Y <nowhere@here.com> - 2011-07-04 12:24 -0700
Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-23 17:48 +0200
Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-23 16:42 +0000
Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-23 18:57 +0000
Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-26 17:35 +0200
Re: migrating oracle to postgres Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-26 19:08 +0000
Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-27 00:32 -0700
Re: migrating oracle to postgres Mladen Gogala <no@email.here.invalid> - 2011-06-28 16:00 +0000
Re: migrating oracle to postgres Robert Klemme <shortcutter@googlemail.com> - 2011-06-28 22:28 +0200
csiph-web