Oracle Migration Approach (Open source vs Vendor Specific)

EDB or Aurora to Open source Postgres assuming we dont use AWS services OR
would you suggest to move to Community version from the start by taking support/consultancy
from other companies like 2nd quadrant and etc?

EDB is mainly attractive to Oracle shops who want Oracle compatibility. In my previous work place
EDB turned out to be highly compatible with PLSQL, but performance was twice as slow. This was
ver 9.5 something. Also keep in kind EDB is not cheap. Once you are in EDB, you are in another
closed vendor and you are locked as long as you use PLSQL. Non EDB version of PG does not
support PLSQL.

All our developers are experienced in pl/sql so I assume it will be easier to learn
PL/pgsql  ,isnt that right? so far only things i dont like about PL/pgsql that validation
of program units doesnt happen until you run it ...

pl/pgsql is no match to PLSQL in productivity. I am not a fan of Oracle RDBMS, but I
think PL/SQL is top notch. If you are already using bulk collect type of features, it won't
be easy to replicate it in PL/PGSQL.
That being said, PG offers other languages too, like Java, Python, Perl. You can try them.

st 8. 5. 2019 v 17:40 odesílatel Sandeep Saxena <sandeep.lko@gmail.com>
napsal:

Hi All,

In our company we are looking to migrate oracle db to postgres , now since
we dont have any expertise in Postgres in our organization so we are
planning to go
with AWS aurora or EDB postgres so that we have helping hand in
migration(in dev and dba) while we get mature in postgres ... do you guys
think after 1-2 years if required it will be easy to move from EDB or
Aurora to Open source Postgres assuming we dont use AWS services OR would
you suggest to move to Community version from the start by taking
support/consultancy from other companies like 2nd quadrant and etc?

it is hard to say

All our developers are experienced in pl/sql so I assume it will be easier
to learn PL/pgsql ,isnt that right? so far only things i dont like about
PL/pgsql that validation of program units doesnt happen until you run it
... though i did find extension plpgsql_check to achieve that to some
extent.

PL/SQL is generic language ADA + SQL. PLpgSQL is reduced PL/SQL with some
more dynamic features. Ada language is generic language with lot of
features that are not practical for usage for stored procedures. PLpgSQL is
reduced language designed specially and only for stored procedures.

PLpgSQL is more simple language than PL/SQL, so it is more easy to use it,
learn it. But it is little bit different language implemented on different
technology - that can be hard to understand. PL/pgSQL and PL/SQL are
similar on top level and very different on low level - some knowledges and
patterns from PL/SQL can be contraproductive - other not.

PLpgSQL does syntax validation at creation time - any other is not possible
because Postgres has not global temporary tables. With late semantic check
PLpgSQL is much more dynamic than PL/SQL. I hope so almost all issues
related to this feature are solved by plpgsql_check (I am a author of
plpgsql_check). I had lot of migration from PL/SQL to PL/pgSQL.

Look on ora2pg. Now it can do lot of good work on automatic migration from
PL/SQL to PL/pgSQL.

Regards

Pavel Stehule

On 5/8/19 8:39 AM, Sandeep Saxena wrote:

Hi All,

In our company we are looking to migrate oracle db to postgres , now
since we dont have any expertise in Postgres in our organization so we
are planning to go
with AWS aurora or EDB postgres so that we have helping hand in
migration(in dev and dba) while we get mature in postgres ... do you
guys think after 1-2 years if required it will be easy to move from EDB
or Aurora to Open source Postgres assuming we dont use AWS services OR

Not sure how you can use Aurora without AWS?

would you suggest to move to Community version from the start by taking
support/consultancy from other companies like 2nd quadrant and etc?

I would say move directly to the community version, save yourself a
second transition cycle. Sign up with one the Postgres support companies
and/or use these lists.

All our developers are experienced in pl/sql so I assume it will be
easier to learn PL/pgsql  ,isnt that right? so far only things i dont
like about PL/pgsql that validation of program units doesnt happen until
you run it ... though i did find extension plpgsql_check to achieve that
to some extent.

Regards,
Sandeep

--
Adrian Klaver
adrian.klaver@aklaver.com

Actually I ran our schema against AWS Schema conversion tool ,and when i
looked at converted code then i saw that for some stuff it was using other
services of AWS which is only in AWS ,
So thats what i meant by not using AWS services and just keeping usage of
Postgres and its extensions.

@Ravi
My company is trying to avoid another vendor lockin too , thats why we are
bit skeptical on going to EDB as once we start using their Oracle
compatability
feature then it will be very difficult to move to community addition again.

@Pavel Stehule
Thank you for reply , I have run schema against Ora2Pg too ,and results
were encouraging again only issue was making sure all objects are valid and
wont throw error
and typical data type issues of migration but ora2pg do generate really
good report to guide newbies.

Regards,
Sandeep

On Wed, May 8, 2019 at 3:47 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

@Ravi 

My company is trying to avoid another vendor lockin too , thats why we are bit skeptical
on going to EDB as once we start using their Oracle compatability 
feature then it will be very difficult to move to community addition again.

As far as I know, vendor lock in applies only if you want to use Oracle features like PLSQL.
If I am not mistaken, EDB also supports community version of PG, no different than support from
Crunchy Data Solutions or 2nd Q. You have that option too if you don't want to be locked in.

On 5/8/19 3:30 PM, Sandeep Saxena wrote:
[snip]

@Ravi
My company is trying to avoid another vendor lockin too , thats why we are
bit skeptical on going to EDB as once we start using their Oracle
compatability
feature then it will be very difficult to move to community addition again.

What about converting using EDB and then -- once the database is up, running
and completely off of Oracle -- slowly converting to vanilla PG one "module"
at at time?

--
Angular momentum makes the world go 'round.

On Wed, May 8, 2019 at 04:23:06PM -0500, Ron wrote:

On 5/8/19 3:30 PM, Sandeep Saxena wrote:
[snip]

@Ravi
My company is trying to avoid another vendor lockin too , thats why we
are bit skeptical on going to EDB as once we start using their Oracle
compatability
feature then it will be very difficult to move to community addition again.

What about converting using EDB and then -- once the database is up, running
and completely off of Oracle -- slowly converting to vanilla PG one "module"
at at time?

I did blog posts about lockin and vendor selection strategy that might
help:

https://momjian.us/main/blogs/pgblog/2019.html#March_5_2019
https://momjian.us/main/blogs/pgblog/2019.html#March_7_2019

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +