Обсуждение: Oracle to PostgreSQL - DWH

Поиск
Список
Период
Сортировка

Oracle to PostgreSQL - DWH

От
Дата:

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..

 

Re: Oracle to PostgreSQL - DWH

От
Andrew Kerber
Дата:
My thought would be dont do it. 

On Wed, Feb 12, 2020 at 8:26 AM <soumik.bhattacharjee@kpn.com> 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.'

Re: Oracle to PostgreSQL - DWH

От
John Wiencek
Дата:
EnterpriseDB has a product called Migration Toolkit (MTK) that can do this.


John

On Feb 12, 2020, at 8:25 AM, <soumik.bhattacharjee@kpn.com> <soumik.bhattacharjee@kpn.com> 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..

RE: Oracle to PostgreSQL - DWH

От
Дата:

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

 

Can PostgreSQL support data warehousing loads?

 

From: John Wiencek <jwiencek3@comcast.net>
Sent: woensdag 12 februari 2020 16:31
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>
Cc: pgsql-admin@lists.postgresql.org; pgsql-admin@postgresql.org
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, <soumik.bhattacharjee@kpn.com> <soumik.bhattacharjee@kpn.com> 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..

 

Re: Oracle to PostgreSQL - DWH

От
John Scalia
Дата:
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, soumik.bhattacharjee@kpn.com wrote:



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

 

Can PostgreSQL support data warehousing loads?

 

From: John Wiencek <jwiencek3@comcast.net>
Sent: woensdag 12 februari 2020 16:31
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>
Cc: pgsql-admin@lists.postgresql.org; pgsql-admin@postgresql.org
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, <soumik.bhattacharjee@kpn.com> <soumik.bhattacharjee@kpn.com> 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..

 

Re: Oracle to PostgreSQL - DWH

От
Amit jain
Дата:
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 <soumik.bhattacharjee@kpn.com> 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..

 

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 <amit7.jain@gmail.com> 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 <soumik.bhattacharjee@kpn.com> 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..

 

Re: Oracle to PostgreSQL - DWH

От
Allan Davis
Дата:
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 <jwiencek3@comcast.net> wrote:
EnterpriseDB has a product called Migration Toolkit (MTK) that can do this.


John

On Feb 12, 2020, at 8:25 AM, <soumik.bhattacharjee@kpn.com> <soumik.bhattacharjee@kpn.com> 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...?

Re: Oracle to PostgreSQL - DWH

От
John Wiencek
Дата:
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 <allandavisjr@gmail.com> 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 <jwiencek3@comcast.net> wrote:
EnterpriseDB has a product called Migration Toolkit (MTK) that can do this.


John

On Feb 12, 2020, at 8:25 AM, <soumik.bhattacharjee@kpn.com> <soumik.bhattacharjee@kpn.com> 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...?

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 <jwiencek3@comcast.net> 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 <allandavisjr@gmail.com> 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 <jwiencek3@comcast.net> wrote:
EnterpriseDB has a product called Migration Toolkit (MTK) that can do this.


John

On Feb 12, 2020, at 8:25 AM, <soumik.bhattacharjee@kpn.com> <soumik.bhattacharjee@kpn.com> 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...?

Re: Oracle to PostgreSQL - DWH

От
Bert
Дата:
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 <mohinder.raina41@gmail.com> 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 <jwiencek3@comcast.net> 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 <allandavisjr@gmail.com> 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 <jwiencek3@comcast.net> wrote:
EnterpriseDB has a product called Migration Toolkit (MTK) that can do this.


John

On Feb 12, 2020, at 8:25 AM, <soumik.bhattacharjee@kpn.com> <soumik.bhattacharjee@kpn.com> 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

RE: Oracle to PostgreSQL - DWH

От
Дата:

Thanks Andy and all Experts here for your valuable inputs.

 

 

 

From: Andy Ellicott <andyellicott@gmail.com>
Sent: woensdag 12 februari 2020 17:27
To: Gurudutt Dhareshwar <gurudutt.dhareshwar@gmail.com>
Cc: Amit jain <amit7.jain@gmail.com>; Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>; pgsql-admin@lists.postgresql.org; pgsql-admin@postgresql.org
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 <gurudutt.dhareshwar@gmail.com> wrote:

Would suggest using snowflakes for DWH

Regards, 

 

Gurudutt Dhareshwar 

(469-288-7845)



On Feb 12, 2020, at 07:44, Amit jain <amit7.jain@gmail.com> 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 <soumik.bhattacharjee@kpn.com> 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

RE: Oracle to PostgreSQL - DWH

От
Дата:

Valuable inputs Mohinder.

 

 

From: Mohinder Raina <mohinder.raina41@gmail.com>
Sent: woensdag 12 februari 2020 20:36
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>
Cc: Allan Davis <allandavisjr@gmail.com>; pgsql-admin@lists.postgresql.org; pgsql-admin@postgresql.org; John Wiencek <jwiencek3@comcast.net>
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 <jwiencek3@comcast.net> 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 <allandavisjr@gmail.com> 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 <jwiencek3@comcast.net> wrote:

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

 

 

John



On Feb 12, 2020, at 8:25 AM, <soumik.bhattacharjee@kpn.com> <soumik.bhattacharjee@kpn.com> 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...?