Обсуждение: 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?

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

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

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

Please un-subscribe me..
thanks

-----Original Message-----
From: John R Pierce [mailto:pierce@hogranch.com]
Sent: 06 December 2017 06:11:PM
To: pgsql-general@lists.postgresql.org
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
scriptsetc tend to be pretty fixed about where they need to be.   things like selinux all need to know where the
componentslive.
 

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
beforeI initialize the database.    reason I do the pgsql dir and not the version specific data directory is so
pg_upgradecan use link mode to upgrade a cluster.
 




--
john r pierce, recycling bits in santa cruz


::DISCLAIMER::
----------------------------------------------------------------------------------------------------------------------------------------------------
Thecontents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail
transmissionis 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
withoutreferred 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
opinionsof HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification,
distributionand / or publication of this message without the prior written consent of authorized representative of HCL
isstrictly prohibited. If you have received this email in error please delete it and notify the sender immediately.
Beforeopening any email and/or attachments, please check them for viruses and other defects.
----------------------------------------------------------------------------------------------------------------------------------------------------

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

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

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