Thread: Oracle to PostgreSQL transition?

On Fri, Apr 5, 2013 at 2:12 AM, Szymon Guz <mabewlun@gmail.com> wrote:
> On 4 April 2013 16:16, Roy Anderson <roy.anderson@gmail.com> wrote:
>>
>> Hey all,
>>
>> We have a very robust Oracle and SQL Server presence at work but we're
>> looking to farm out some of the load to PostgreSQL to limit costs. I'm
>> curious if there are any DBAs out there who have gone down this route
>> before. Any tips, tricks, failures, successes, etc.? I would just like to
>> hear some first-hand commentary on this topic.
>>
>> Thank you so much!
>>
>> Roy
>
>
> Hi Roy,
> maybe this would be helpful:
>
> http://wiki.postgresql.org/wiki/PostgreSQL_for_Oracle_DBAs
> http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
> http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Oracle
>

Yeah, it's worth reading through those links to get an idea of things;
you'll find much less literature (and tools) for MSSQL, but the
principals are mostly the same. One thing to decide on is if you are
going to port applications wholesale, or try to run some kind of
hybrid oracle/mssql -> postgres mix. If it's the latter, you'll
probably need to write your own tools; at least we've always done that
as we've never found anything that worked with both Oracle and MSSQL
into Postgres reliably; but really it shouldn't be too difficult;
basically just ETL or some home brew replication scripting to glue
things together.

Robert Treat
conjecture: xzilla.net
consulting: omniti.com

On 04/04/2013 09:16 AM, Roy Anderson wrote:

> We have a very robust Oracle and SQL Server presence at work but we're
> looking to farm out some of the load to PostgreSQL to limit costs.

You can do what we did. EnterpriseDB has built-in Oracle compatibility
through a series of plugins, patches, and wrappers. You can generally
convert with only a few small changes.

 From there, you can take your time converting to PG syntax and go
through more iterations in your dev and staging environments.

If it's early enough in the lifetime of your platform, you could also
just convert everything at once. That's not always an option for
everyone though. :)

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

On 04/05/2013 08:03 AM, Robert Treat wrote:
>
> On Fri, Apr 5, 2013 at 2:12 AM, Szymon Guz <mabewlun@gmail.com> wrote:
>> On 4 April 2013 16:16, Roy Anderson <roy.anderson@gmail.com> wrote:
>>>
>>> Hey all,
>>>
>>> We have a very robust Oracle and SQL Server presence at work but we're
>>> looking to farm out some of the load to PostgreSQL to limit costs. I'm
>>> curious if there are any DBAs out there who have gone down this route
>>> before. Any tips, tricks, failures, successes, etc.? I would just like to
>>> hear some first-hand commentary on this topic.
>>>
>>> Thank you so much!

Yes, we have done this for customers on more than one occasion. The
single biggest tip I can give you is to remember that PostgreSQL is not
Oracle. You are porting to PostgreSQL, don't expect it to act like
Oracle. As long as you keep that in mind, and you aren't running RAC
things are pretty straightforward. They can also be quite a bit easier
(and cheaper) to get a lot of external functionality such as replication.

Sincerely,

Joshua D. Drake

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC
@cmdpromptinc - 509-416-6579

2013/4/5 Robert Treat <rob@xzilla.net>:
>
> Yeah, it's worth reading through those links to get an idea of things;
> you'll find much less literature (and tools) for MSSQL, but the
> principals are mostly the same. One thing to decide on is if you are
> going to port applications wholesale, or try to run some kind of
> hybrid oracle/mssql -> postgres mix. If it's the latter, you'll
> probably need to write your own tools; at least we've always done that
> as we've never found anything that worked with both Oracle and MSSQL
> into Postgres reliably; but really it shouldn't be too difficult;
> basically just ETL or some home brew replication scripting to glue
> things together.

I gave some training a few years ago for devs that were migrating from
SQLServer to PostgreSQL and they had a bunch of SP, some of easy
resolution, and others that where impossible with PG functions written
in plpgsql. They used CURSORS a lot to simulate what they did with
SQLServer (there was a better solution, but time was crucial, and they
decided for that approach)


--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador