Oracle to PostgreSQL - DWH

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

Oracle to PostgreSQL - DWH

soumik.bhattacharjee

Hi Experts,

 

Greetings!!

 

Did anyone have some use cases for migrating Oracle to PostgreSQL in context to DWH(Data warehousing ) considering 5TB to 8TB database size (Source-Oracle)

 

 

Thanks..

 

Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL - DWH

Andrew Kerber
My thought would be dont do it. 

On Wed, Feb 12, 2020 at 8:26 AM <[hidden email]> wrote:

Hi Experts,

 

Greetings!!

 

Did anyone have some use cases for migrating Oracle to PostgreSQL in context to DWH(Data warehousing ) considering 5TB to 8TB database size (Source-Oracle)

 

 

Thanks..

 



--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'
Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL - DWH

jwiencek3
In reply to this post by soumik.bhattacharjee
EnterpriseDB has a product called Migration Toolkit (MTK) that can do this.


John

On Feb 12, 2020, at 8:25 AM, <[hidden email]> <[hidden email]> wrote:

Hi Experts,
 
Greetings!!
 
Did anyone have some use cases for migrating Oracle to PostgreSQL in context to DWH(Data warehousing ) considering 5TB to 8TB database size (Source-Oracle)
 
 
Thanks..

Reply | Threaded
Open this post in threaded view
|

RE: Oracle to PostgreSQL - DWH

soumik.bhattacharjee

Migration is fine, but is PostgreSQL good for DWH use cases ?

 

Can PostgreSQL support data warehousing loads?

 

From: John Wiencek <[hidden email]>
Sent: woensdag 12 februari 2020 16:31
To: Bhattacharjee, Soumik <[hidden email]>
Cc: [hidden email]; [hidden email]
Subject: Re: Oracle to PostgreSQL - DWH

 

EnterpriseDB has a product called Migration Toolkit (MTK) that can do this.

 

 

John



On Feb 12, 2020, at 8:25 AM, <[hidden email]> <[hidden email]> wrote:

 

Hi Experts,

 

Greetings!!

 

Did anyone have some use cases for migrating Oracle to PostgreSQL in context to DWH(Data warehousing ) considering 5TB to 8TB database size (Source-Oracle)

 

 

Thanks..

 

Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL - DWH

Jay at Verizon
Then you should possibly look at Greenplum, which is a PostgreSQL based system designed for data warehouses. True, they lag behind PostgreSQL versions considerably, but if your concern is data warehouses, that’s probably the way to go.
Jay

Sent from my iPad

On Feb 12, 2020, at 10:35 AM, [hidden email] wrote:



Migration is fine, but is PostgreSQL good for DWH use cases ?

 

Can PostgreSQL support data warehousing loads?

 

From: John Wiencek <[hidden email]>
Sent: woensdag 12 februari 2020 16:31
To: Bhattacharjee, Soumik <[hidden email]>
Cc: [hidden email]; [hidden email]
Subject: Re: Oracle to PostgreSQL - DWH

 

EnterpriseDB has a product called Migration Toolkit (MTK) that can do this.

 

 

John



On Feb 12, 2020, at 8:25 AM, <[hidden email]> <[hidden email]> wrote:

 

Hi Experts,

 

Greetings!!

 

Did anyone have some use cases for migrating Oracle to PostgreSQL in context to DWH(Data warehousing ) considering 5TB to 8TB database size (Source-Oracle)

 

 

Thanks..

 

Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL - DWH

Amit jain
In reply to this post by soumik.bhattacharjee
Greenplum is best suited for DW workload,based on Postgresql open source and commodity hardware can also support this.

Postgresql is OLTP DB.

Amit Jain
9833777592

On Wed, Feb 12, 2020, 7:56 PM <[hidden email]> wrote:

Hi Experts,

 

Greetings!!

 

Did anyone have some use cases for migrating Oracle to PostgreSQL in context to DWH(Data warehousing ) considering 5TB to 8TB database size (Source-Oracle)

 

 

Thanks..

 

Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL - DWH

Gurudutt Dhareshwar
Would suggest using snowflakes for DWH

Regards, 

Gurudutt Dhareshwar 
(469-288-7845)

On Feb 12, 2020, at 07:44, Amit jain <[hidden email]> wrote:


Greenplum is best suited for DW workload,based on Postgresql open source and commodity hardware can also support this.

Postgresql is OLTP DB.

Amit Jain
9833777592

On Wed, Feb 12, 2020, 7:56 PM <[hidden email]> wrote:

Hi Experts,

 

Greetings!!

 

Did anyone have some use cases for migrating Oracle to PostgreSQL in context to DWH(Data warehousing ) considering 5TB to 8TB database size (Source-Oracle)

 

 

Thanks..

 

Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL - DWH

Allan Davis
In reply to this post by jwiencek3
EnterpriseDB also has an Oracle Compatibility mode--a version of the Postgres database that has an Oracle look and feel to it.  Between this and the Migration Toolkit, 95% of Oracle "stuff" should move over without too much trouble.

(disclaimer:  I've been working for EnterpriseDB for two months)

-=ad=-

On Wed, Feb 12, 2020 at 10:31 AM John Wiencek <[hidden email]> wrote:
EnterpriseDB has a product called Migration Toolkit (MTK) that can do this.


John

On Feb 12, 2020, at 8:25 AM, <[hidden email]> <[hidden email]> wrote:

Hi Experts,
 
Greetings!!
 
Did anyone have some use cases for migrating Oracle to PostgreSQL in context to DWH(Data warehousing ) considering 5TB to 8TB database size (Source-Oracle)
 
 
Thanks..



--
Perchance to Scream - Being frightened has never been so much fun!

Photo Gallery


Isn't a good laugh worth a nickel...?
Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL - DWH

jwiencek3
Oracle compatibility mode is the default setting when you init an EPAS database.   

Jihn

Sent from my iPad

On Feb 12, 2020, at 10:24 AM, Allan Davis <[hidden email]> wrote:


EnterpriseDB also has an Oracle Compatibility mode--a version of the Postgres database that has an Oracle look and feel to it.  Between this and the Migration Toolkit, 95% of Oracle "stuff" should move over without too much trouble.

(disclaimer:  I've been working for EnterpriseDB for two months)

-=ad=-

On Wed, Feb 12, 2020 at 10:31 AM John Wiencek <[hidden email]> wrote:
EnterpriseDB has a product called Migration Toolkit (MTK) that can do this.


John

On Feb 12, 2020, at 8:25 AM, <[hidden email]> <[hidden email]> wrote:

Hi Experts,
 
Greetings!!
 
Did anyone have some use cases for migrating Oracle to PostgreSQL in context to DWH(Data warehousing ) considering 5TB to 8TB database size (Source-Oracle)
 
 
Thanks..



--
Perchance to Scream - Being frightened has never been so much fun!

Photo Gallery


Isn't a good laugh worth a nickel...?
Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL - DWH

Mohinder Raina
I am a solution architect and worked on an oracle to postgres project almost 10 years ago. This was a 9 TB oracle 10 to postgres 9.0, Community postgres wasn't a viable option and client leaned on to enterprisedb postgres because of pl/sql, table partitioning, optimizer hints and database links compatibility. They also got some assessment done before I jumped on this project. Believe me, with the help of streaming replication, read scaling wasn't an issue and with the help of pgpool reads were load balanced to standby servers. Table Partitiong and Partial Indexes were quite helpful in query optimization. Postgres required lots of parameter tuning and table level optimization(read about vacuuming and mvcc in postgres)
When you are moving out of oracle the first thought is "how much do I save not today but for the lifespan of an application" and then you think about how database landscape is changing which motivates you to look into databases beyong oracle. Today Postgres open source or any other fork of postgres(https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases) for sure can handle DWH workloads provided it is tuned for read and designed to be scalable.
The project I designed was a success.

Some of old Pain Points my client had:
Storage - No control or Quotas
Connections - Hundreds of idle connections which needs watch and cleanup
BLOBS, Unicode Data, Date Values, NULLs, Type Cast
No Incremental Backup - Today there is pgbackrest or enterprisedb backup tool  
No reliable HA tool for new 9.0 streaming standby failover - Today there is repmgr, partoni, stolon, pgpool, enterprisedb failover and many more

These points are just based on my experience. By the way I am retired now and I am not related to or work for any postgres community or company. 

On Wed, Feb 12, 2020 at 11:26 AM John Wiencek <[hidden email]> wrote:
Oracle compatibility mode is the default setting when you init an EPAS database.   

Jihn

Sent from my iPad

On Feb 12, 2020, at 10:24 AM, Allan Davis <[hidden email]> wrote:


EnterpriseDB also has an Oracle Compatibility mode--a version of the Postgres database that has an Oracle look and feel to it.  Between this and the Migration Toolkit, 95% of Oracle "stuff" should move over without too much trouble.

(disclaimer:  I've been working for EnterpriseDB for two months)

-=ad=-

On Wed, Feb 12, 2020 at 10:31 AM John Wiencek <[hidden email]> wrote:
EnterpriseDB has a product called Migration Toolkit (MTK) that can do this.


John

On Feb 12, 2020, at 8:25 AM, <[hidden email]> <[hidden email]> wrote:

Hi Experts,
 
Greetings!!
 
Did anyone have some use cases for migrating Oracle to PostgreSQL in context to DWH(Data warehousing ) considering 5TB to 8TB database size (Source-Oracle)
 
 
Thanks..



--
Perchance to Scream - Being frightened has never been so much fun!

Photo Gallery


Isn't a good laugh worth a nickel...?
Reply | Threaded
Open this post in threaded view
|

Re: Oracle to PostgreSQL - DWH

Bert-2
Hi,

We are running our datawarehous  on postgres (around 1TB), and are stuck on 9.4 for the moment
We have moved from IBM Netezza about 1 year ago, and this is what we learned:
* tune for reads
* set the default_statistics_target high
* Our data model that worked on NZ (in our case)  had to be optimized a bit.

And now it works fine.
And we are looking forward to what PgSQL 12 will do for us :-)

Bert

On Wed, Feb 12, 2020 at 8:36 PM Mohinder Raina <[hidden email]> wrote:
I am a solution architect and worked on an oracle to postgres project almost 10 years ago. This was a 9 TB oracle 10 to postgres 9.0, Community postgres wasn't a viable option and client leaned on to enterprisedb postgres because of pl/sql, table partitioning, optimizer hints and database links compatibility. They also got some assessment done before I jumped on this project. Believe me, with the help of streaming replication, read scaling wasn't an issue and with the help of pgpool reads were load balanced to standby servers. Table Partitiong and Partial Indexes were quite helpful in query optimization. Postgres required lots of parameter tuning and table level optimization(read about vacuuming and mvcc in postgres)
When you are moving out of oracle the first thought is "how much do I save not today but for the lifespan of an application" and then you think about how database landscape is changing which motivates you to look into databases beyong oracle. Today Postgres open source or any other fork of postgres(https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases) for sure can handle DWH workloads provided it is tuned for read and designed to be scalable.
The project I designed was a success.

Some of old Pain Points my client had:
Storage - No control or Quotas
Connections - Hundreds of idle connections which needs watch and cleanup
BLOBS, Unicode Data, Date Values, NULLs, Type Cast
No Incremental Backup - Today there is pgbackrest or enterprisedb backup tool  
No reliable HA tool for new 9.0 streaming standby failover - Today there is repmgr, partoni, stolon, pgpool, enterprisedb failover and many more

These points are just based on my experience. By the way I am retired now and I am not related to or work for any postgres community or company. 

On Wed, Feb 12, 2020 at 11:26 AM John Wiencek <[hidden email]> wrote:
Oracle compatibility mode is the default setting when you init an EPAS database.   

Jihn

Sent from my iPad

On Feb 12, 2020, at 10:24 AM, Allan Davis <[hidden email]> wrote:


EnterpriseDB also has an Oracle Compatibility mode--a version of the Postgres database that has an Oracle look and feel to it.  Between this and the Migration Toolkit, 95% of Oracle "stuff" should move over without too much trouble.

(disclaimer:  I've been working for EnterpriseDB for two months)

-=ad=-

On Wed, Feb 12, 2020 at 10:31 AM John Wiencek <[hidden email]> wrote:
EnterpriseDB has a product called Migration Toolkit (MTK) that can do this.


John

On Feb 12, 2020, at 8:25 AM, <[hidden email]> <[hidden email]> wrote:

Hi Experts,
 
Greetings!!
 
Did anyone have some use cases for migrating Oracle to PostgreSQL in context to DWH(Data warehousing ) considering 5TB to 8TB database size (Source-Oracle)
 
 
Thanks..



--
Perchance to Scream - Being frightened has never been so much fun!

Photo Gallery


Isn't a good laugh worth a nickel...?


--
Bert Desmet
0477/305361
Reply | Threaded
Open this post in threaded view
|

RE: Oracle to PostgreSQL - DWH

soumik.bhattacharjee
In reply to this post by Gurudutt Dhareshwar

Thanks Andy and all Experts here for your valuable inputs.

 

 

 

From: Andy Ellicott <[hidden email]>
Sent: woensdag 12 februari 2020 17:27
To: Gurudutt Dhareshwar <[hidden email]>
Cc: Amit jain <[hidden email]>; Bhattacharjee, Soumik <[hidden email]>; [hidden email]; [hidden email]
Subject: Re: Oracle to PostgreSQL - DWH

 

Hi all,

 

Rather than Greenplum, a fresher option for data warehousing is to use standard Postgres (or EDB), but deployed on FPGA-equipped servers. 

 

FPGAs were part of the magic inside of the old Netezza data warehouse appliances. And Amazon just announced they are using FPGA servers to speed up Redshift. FPGA-equipped hardware is becoming more common (e.g., AWS EC2 F1 instances).

 

You stay on free Postgres, but do require software to run on the FPGA chips... Swarm64.com (where I work) develops FPGA acceleration software for Postgres (v. 11 and up)...it adds a lot of parallel processing on the FPGA along with columnar indexing (foreign data table) to speed up queries and insertion. 

 

Depending on the data/queries, FPGA will accelerate PG to Oracle-type speeds at terabytes scale, but for a much lower cost of course.

 

 

I'll share a recording of the event on this thread afterwards, in case you'd like to watch it without signing up for anything.

 

Please excuse the commercial plug...just meaning to inform, since it seemed relevant.

 

Andy

 

On Wed, Feb 12, 2020 at 11:06 AM Gurudutt Dhareshwar <[hidden email]> wrote:

Would suggest using snowflakes for DWH

Regards, 

 

Gurudutt Dhareshwar 

(469-288-7845)



On Feb 12, 2020, at 07:44, Amit jain <[hidden email]> wrote:



Greenplum is best suited for DW workload,based on Postgresql open source and commodity hardware can also support this.

 

Postgresql is OLTP DB.

 

Amit Jain

9833777592

 

On Wed, Feb 12, 2020, 7:56 PM <[hidden email]> wrote:

Hi Experts,

 

Greetings!!

 

Did anyone have some use cases for migrating Oracle to PostgreSQL in context to DWH(Data warehousing ) considering 5TB to 8TB database size (Source-Oracle)

 

 

Thanks..

 


 

--

Andy Ellicott

(m) +1 603 205 2804

Reply | Threaded
Open this post in threaded view
|

RE: Oracle to PostgreSQL - DWH

soumik.bhattacharjee
In reply to this post by Mohinder Raina

Valuable inputs Mohinder.

 

 

From: Mohinder Raina <[hidden email]>
Sent: woensdag 12 februari 2020 20:36
To: Bhattacharjee, Soumik <[hidden email]>
Cc: Allan Davis <[hidden email]>; [hidden email]; [hidden email]; John Wiencek <[hidden email]>
Subject: Re: Oracle to PostgreSQL - DWH

 

I am a solution architect and worked on an oracle to postgres project almost 10 years ago. This was a 9 TB oracle 10 to postgres 9.0, Community postgres wasn't a viable option and client leaned on to enterprisedb postgres because of pl/sql, table partitioning, optimizer hints and database links compatibility. They also got some assessment done before I jumped on this project. Believe me, with the help of streaming replication, read scaling wasn't an issue and with the help of pgpool reads were load balanced to standby servers. Table Partitiong and Partial Indexes were quite helpful in query optimization. Postgres required lots of parameter tuning and table level optimization(read about vacuuming and mvcc in postgres)
When you are moving out of oracle the first thought is "how much do I save not today but for the lifespan of an application" and then you think about how database landscape is changing which motivates you to look into databases beyong oracle. Today Postgres open source or any other fork of postgres(https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases) for sure can handle DWH workloads provided it is tuned for read and designed to be scalable.
The project I designed was a success.


Some of old Pain Points my client had:
Storage - No control or Quotas
Connections - Hundreds of idle connections which needs watch and cleanup
BLOBS, Unicode Data, Date Values, NULLs, Type Cast
No Incremental Backup - Today there is pgbackrest or enterprisedb backup tool  
No reliable HA tool for new 9.0 streaming standby failover - Today there is repmgr, partoni, stolon, pgpool, enterprisedb failover and many more

These points are just based on my experience. By the way I am retired now and I am not related to or work for any postgres community or company. 

 

On Wed, Feb 12, 2020 at 11:26 AM John Wiencek <[hidden email]> wrote:

Oracle compatibility mode is the default setting when you init an EPAS database.   

 

Jihn

Sent from my iPad



On Feb 12, 2020, at 10:24 AM, Allan Davis <[hidden email]> wrote:



EnterpriseDB also has an Oracle Compatibility mode--a version of the Postgres database that has an Oracle look and feel to it.  Between this and the Migration Toolkit, 95% of Oracle "stuff" should move over without too much trouble.

 

(disclaimer:  I've been working for EnterpriseDB for two months)

 

-=ad=-

 

On Wed, Feb 12, 2020 at 10:31 AM John Wiencek <[hidden email]> wrote:

EnterpriseDB has a product called Migration Toolkit (MTK) that can do this.

 

 

John



On Feb 12, 2020, at 8:25 AM, <[hidden email]> <[hidden email]> wrote:

 

Hi Experts,

 

Greetings!!

 

Did anyone have some use cases for migrating Oracle to PostgreSQL in context to DWH(Data warehousing ) considering 5TB to 8TB database size (Source-Oracle)

 

 

Thanks..

 


 

--

Perchance to Scream - Being frightened has never been so much fun!

Photo Gallery

Isn't a good laugh worth a nickel...?