Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

Eugene Poole

I've asked most of my OS based questions on another mail list, but I think this is more specific:

On a physical machine I'm running CentOS-6 64-bit with Oracle 11gR2 and I want to migrate to another physical machine running CentOS-7 64-bit with PostgreSQL ??

1. What version of PostgreSQL should I use?

2. I don't want to use a RPM because I like controlling where software is installed, so where can I locate the required tar ball?

3. Would I use ora2pg do do the move? Is ora2pg still maintained?

4. Is there a better conversion package?

5. What additional information might be helpful?

TIA

Gene

-- 
Eugene Poole
Woodstock, Georgia
Reply | Threaded
Open this post in threaded view
|

RE: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

Kevin Brannen

From: Eugene Poole [mailto:[hidden email]]
Sent: Tuesday, December 05, 2017 12:59 PM
To: [hidden email]
Subject: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

 

I've asked most of my OS based questions on another mail list, but I think this is more specific:

On a physical machine I'm running CentOS-6 64-bit with Oracle 11gR2 and I want to migrate to another physical machine running CentOS-7 64-bit with PostgreSQL ??

1. What version of PostgreSQL should I use?

The latest that works for you. Some people don’t like .0 releases for various reasons and if that applies to you, then use the latest of 9.6.x (9.6.5 I think); else use 10.0.

2. I don't want to use a RPM because I like controlling where software is installed, so where can I locate the required tar ball?

You can download the source and build it yourself; there’s an arg for where you want to install it. The instructions are pretty easy to follow if you’ve ever built anything.

3. Would I use ora2pg do do the move? Is ora2pg still maintained?

4. Is there a better conversion package?

5. What additional information might be helpful?

I find building & testing on a VM to be useful, as I can snapshop every step of the way and revert if required. I’ve never done an Oracle -> Pg conversion, but it’s been discussed on the list so you might want to search the archives for advice. In fact the last time wasn’t all that long ago, maybe a month?

HTH, Kevin

TIA

Gene

-- 
Eugene Poole
Woodstock, Georgia
Untitled Document
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.
Reply | Threaded
Open this post in threaded view
|

Re: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

Jeremy Schneider
On 12/5/17 12:07, Kevin Brannen wrote:
>     1. What version of PostgreSQL should I use?
>
>     The latest that works for you. Some people don’t like .0 releases
>     for various reasons and if that applies to you, then use the latest
>     of 9.6.x (9.6.5 I think); else use 10.0.

In addition, 10.1 is available as of Nov 9th


>     2. I don't want to use a RPM because I like controlling where
>     software is installed, so where can I locate the required tar ball?

I wonder if the PGDG rpms are relocatable packages? I haven't tried that
yet.


>     You can download the source and build it yourself; there’s an arg
>     for where you want to install it. The instructions are pretty easy
>     to follow if you’ve ever built anything.

Agree you can run the build script easily enough, although personally
I'd prefer a package over doing my own builds. Maybe it's not a priority
to you, but if you're building stuff yourself and you really want to do
it right then you should be tracking a lot of stuff and making sure you
are using all the right configure options and making sure you can always
perfectly reproduce builds. Probably should be creating and archiving
your own packages... but depends on your requirements.  :)


>     5. What additional information might be helpful?

Just last night we had a great talk at the Seattle PostgreSQL User Group
about moving from MSSQL to PG and many considerations apply equally. In
particular, you can find lots of code to make PostgreSQL act more like
your source DB... but you want to carefully consider whether this is the
right approach in your situation.  (There be flame-wars here.)

And speaking of PUGs, you could check for a local one of those and get
connected up to ask future questions face-to-face too.  :)

-J

--
http://about.me/jeremy_schneider

Reply | Threaded
Open this post in threaded view
|

Re: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

John R Pierce
In reply to this post by Eugene Poole
On 12/5/2017 10:59 AM, Eugene Poole wrote:
> 2. I don't want to use a RPM because I like controlling where software
> is installed ....

then why are you using CentOS/RHEL ?   those are by design RPM managed
distribution, and rpms with their service scripts etc tend to be pretty
fixed about where they need to be.   things like selinux all need to
know where the components live.

the yum.postgresql.org RPMs all install the binary code to
/usr/pgsql-X.Y/ and default the database location to
/var/lib/pgsql/X.Y/data... if I need the data on a dedicated volume, I
tend to mount said volume to /var/lib/pgsql before I initialize the
database.    reason I do the pgsql dir and not the version specific data
directory is so pg_upgrade can use link mode to upgrade a cluster.




--
john r pierce, recycling bits in santa cruz


Reply | Threaded
Open this post in threaded view
|

RE: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

Vijay Chaudhery
Please un-subscribe me..
thanks

-----Original Message-----
From: John R Pierce [mailto:[hidden email]]
Sent: 06 December 2017 06:11:PM
To: [hidden email]
Subject: Re: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

On 12/5/2017 10:59 AM, Eugene Poole wrote:
> 2. I don't want to use a RPM because I like controlling where software
> is installed ....

then why are you using CentOS/RHEL ?   those are by design RPM managed distribution, and rpms with their service scripts etc tend to be pretty fixed about where they need to be.   things like selinux all need to know where the components live.

the yum.postgresql.org RPMs all install the binary code to /usr/pgsql-X.Y/ and default the database location to /var/lib/pgsql/X.Y/data... if I need the data on a dedicated volume, I tend to mount said volume to /var/lib/pgsql before I initialize the database.    reason I do the pgsql dir and not the version specific data directory is so pg_upgrade can use link mode to upgrade a cluster.




--
john r pierce, recycling bits in santa cruz


::DISCLAIMER:: ---------------------------------------------------------------------------------------------------------------------------------------------------- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates. Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of authorized representative of HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any email and/or attachments, please check them for viruses and other defects. ----------------------------------------------------------------------------------------------------------------------------------------------------
Reply | Threaded
Open this post in threaded view
|

Re: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

John R Pierce
In reply to this post by Eugene Poole
On 12/5/2017 10:59 AM, Eugene Poole wrote:
>
> 3. Would I use ora2pg do do the move? Is ora2pg still maintained?
>
> 4. Is there a better conversion package?
>

there are a variety of "ETL" tools out there that can extract data from
one database and load it into another.   I was going to suggest
pgloader, but that doesn't support oracle, only mysql and ms sql server.

a full scale conversion of a complex application suite, however, can be
a several year project.     Our old Oracle application was very heavy on
plsql.  We chose to reimplement the vast majority of the business logic
in appserver modules outside the database and only used plpgsql for
things where performance mattered significantly.

the more heavily your apps use Oracle specific features, the more work
the conversion will be.

--
john r pierce, recycling bits in santa cruz


Reply | Threaded
Open this post in threaded view
|

Re: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

Eugene Poole
Thanks, That's why I'm starting now and I have until 2020.

What happens if I use the Oracle DDL on PostgreSQL?   It's not much with
plsql but it has a lot of foreign keys and sequence fields.

TIA

Gene


On 12/7/2017 12:02 AM, John R Pierce wrote:

> On 12/5/2017 10:59 AM, Eugene Poole wrote:
>>
>> 3. Would I use ora2pg do do the move? Is ora2pg still maintained?
>>
>> 4. Is there a better conversion package?
>>
>
> there are a variety of "ETL" tools out there that can extract data
> from one database and load it into another.   I was going to suggest
> pgloader, but that doesn't support oracle, only mysql and ms sql server.
>
> a full scale conversion of a complex application suite, however, can
> be a several year project.     Our old Oracle application was very
> heavy on plsql.  We chose to reimplement the vast majority of the
> business logic in appserver modules outside the database and only used
> plpgsql for things where performance mattered significantly.
>
> the more heavily your apps use Oracle specific features, the more work
> the conversion will be.
>

--
Eugene Poole
Woodstock, Georgia


Reply | Threaded
Open this post in threaded view
|

Re: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

Laurenz Albe
Eugene Poole wrote:
> What happens if I use the Oracle DDL on PostgreSQL?   It's not much with
> plsql but it has a lot of foreign keys and sequence fields.

These are the easy part, and ora2pg can definitely help there.

You'll have to modify the DDL statements, but the functionality
exists in PostgreSQL.
Unless you have things like objects or nested tables, I wouldn't
expect much trouble there.

The hard part will probably be to teach the application not
to use Oracle outer join syntax and that '' IS NOT NULL.

Yours,
Laurenz Albe

Previous Thread Next Thread