Обсуждение: Oracle Migration Approach (Open source vs Vendor Specific)
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?
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
> 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 gowith 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
Regards,Sandeep
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:
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
>@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 +