pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOracle to PostgreSQL - DWH
13+ messages / 9 participants
[nested] [flat]
* Oracle to PostgreSQL - DWH
@ 2020-02-12 14:25 soumik.bhattacharjee@kpn.com
0 siblings, 3 replies; 13+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2020-02-12 14:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; pgsql-admin
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..
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Oracle to PostgreSQL - DWH
@ 2020-02-12 14:26 Andrew Kerber <andrew.kerber@gmail.com>
parent: soumik.bhattacharjee@kpn.com
2 siblings, 0 replies; 13+ messages in thread
From: Andrew Kerber @ 2020-02-12 14:26 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; +Cc: pgsql-admin@lists.postgresql.org; pgsql-admin
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.'
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Oracle to PostgreSQL - DWH
@ 2020-02-12 15:31 John Wiencek <jwiencek3@comcast.net>
parent: soumik.bhattacharjee@kpn.com
2 siblings, 2 replies; 13+ messages in thread
From: John Wiencek @ 2020-02-12 15:31 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; +Cc: pgsql-admin@lists.postgresql.org; pgsql-admin
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..
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Oracle to PostgreSQL - DWH
@ 2020-02-12 15:35 soumik.bhattacharjee@kpn.com
parent: John Wiencek <jwiencek3@comcast.net>
1 sibling, 1 reply; 13+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2020-02-12 15:35 UTC (permalink / raw)
To: jwiencek3@comcast.net; +Cc: pgsql-admin@lists.postgresql.org; pgsql-admin
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<mailto:soumik.bhattacharjee@kpn.com>> <soumik.bhattacharjee@kpn.com<mailto: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..
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Oracle to PostgreSQL - DWH
@ 2020-02-12 15:41 John Scalia <jayknowsunix@gmail.com>
parent: soumik.bhattacharjee@kpn.com
0 siblings, 0 replies; 13+ messages in thread
From: John Scalia @ 2020-02-12 15:41 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; +Cc: jwiencek3@comcast.net; pgsql-admin@lists.postgresql.org; pgsql-admin
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..
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Oracle to PostgreSQL - DWH
@ 2020-02-12 15:43 Amit jain <amit7.jain@gmail.com>
parent: soumik.bhattacharjee@kpn.com
2 siblings, 1 reply; 13+ messages in thread
From: Amit jain @ 2020-02-12 15:43 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; +Cc: pgsql-admin@lists.postgresql.org; pgsql-admin
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..
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Oracle to PostgreSQL - DWH
@ 2020-02-12 16:06 Gurudutt Dhareshwar <gurudutt.dhareshwar@gmail.com>
parent: Amit jain <amit7.jain@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Gurudutt Dhareshwar @ 2020-02-12 16:06 UTC (permalink / raw)
To: Amit jain <amit7.jain@gmail.com>; +Cc: soumik.bhattacharjee@kpn.com; pgsql-admin@lists.postgresql.org; pgsql-admin
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..
>>
>>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Oracle to PostgreSQL - DWH
@ 2020-02-12 16:24 Allan Davis <allandavisjr@gmail.com>
parent: John Wiencek <jwiencek3@comcast.net>
1 sibling, 1 reply; 13+ messages in thread
From: Allan Davis @ 2020-02-12 16:24 UTC (permalink / raw)
To: John Wiencek <jwiencek3@comcast.net>; +Cc: soumik.bhattacharjee@kpn.com; pgsql-admin@lists.postgresql.org; pgsql-admin
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
<http://allandavisjr.blogspot.com/p/perchance-to-scream.html; - Being
frightened has never been so much fun!
Photo Gallery <http://shardsandphractures.blogspot.com/p/gallery.html;
Isn't a good laugh worth a nickel...? <http://nickelatatime.blogspot.com/;
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Oracle to PostgreSQL - DWH
@ 2020-02-12 18:26 John Wiencek <jwiencek3@comcast.net>
parent: Allan Davis <allandavisjr@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: John Wiencek @ 2020-02-12 18:26 UTC (permalink / raw)
To: Allan Davis <allandavisjr@gmail.com>; +Cc: soumik.bhattacharjee@kpn.com; pgsql-admin@lists.postgresql.org; pgsql-admin
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...?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Oracle to PostgreSQL - DWH
@ 2020-02-12 19:36 Mohinder Raina <mohinder.raina41@gmail.com>
parent: John Wiencek <jwiencek3@comcast.net>
0 siblings, 2 replies; 13+ messages in thread
From: Mohinder Raina @ 2020-02-12 19:36 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; +Cc: Allan Davis <allandavisjr@gmail.com>; pgsql-admin@lists.postgresql.org, pgsql-admin@postgresql.org, John Wiencek <jwiencek3@comcast.net>
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
> <http://allandavisjr.blogspot.com/p/perchance-to-scream.html; - Being
> frightened has never been so much fun!
>
> Photo Gallery <http://shardsandphractures.blogspot.com/p/gallery.html;
>
> Isn't a good laugh worth a nickel...? <http://nickelatatime.blogspot.com/;
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Oracle to PostgreSQL - DWH
@ 2020-02-13 08:32 Bert <biertie@gmail.com>
parent: Mohinder Raina <mohinder.raina41@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Bert @ 2020-02-13 08:32 UTC (permalink / raw)
To: Mohinder Raina <mohinder.raina41@gmail.com>; +Cc: soumik.bhattacharjee@kpn.com, Allan Davis <allandavisjr@gmail.com>; pgsql-admin@lists.postgresql.org, pgsql-admin@postgresql.org, John Wiencek <jwiencek3@comcast.net>
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
>> <http://allandavisjr.blogspot.com/p/perchance-to-scream.html; - Being
>> frightened has never been so much fun!
>>
>> Photo Gallery <http://shardsandphractures.blogspot.com/p/gallery.html;
>>
>> Isn't a good laugh worth a nickel...?
>> <http://nickelatatime.blogspot.com/;
>>
>>
--
Bert Desmet
0477/305361
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Oracle to PostgreSQL - DWH
@ 2020-02-13 10:19 soumik.bhattacharjee@kpn.com
parent: Gurudutt Dhareshwar <gurudutt.dhareshwar@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2020-02-13 10:19 UTC (permalink / raw)
To: andyellicott@gmail.com; gurudutt.dhareshwar@gmail.com; +Cc: amit7.jain@gmail.com; pgsql-admin@lists.postgresql.org; pgsql-admin
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<https://swarm64.com/post/provisioning-postgres-aws-f1-fpga/;).
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.
There's a webcast on FPGA acceleration for PG next week (19th): https://postgresconf.org/conferences/postgres-webinar-series/program/proposals/fpga-an-easier-path-t...
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<mailto: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<mailto: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<mailto: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
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Oracle to PostgreSQL - DWH
@ 2020-02-13 10:22 soumik.bhattacharjee@kpn.com
parent: Mohinder Raina <mohinder.raina41@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2020-02-13 10:22 UTC (permalink / raw)
To: mohinder.raina41@gmail.com; +Cc: allandavisjr@gmail.com; pgsql-admin@lists.postgresql.org; pgsql-admin; jwiencek3@comcast.net
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<mailto: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<mailto: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<mailto: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<mailto:soumik.bhattacharjee@kpn.com>> <soumik.bhattacharjee@kpn.com<mailto: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<http://allandavisjr.blogspot.com/p/perchance-to-scream.html; - Being frightened has never been so much fun!
Photo Gallery<http://shardsandphractures.blogspot.com/p/gallery.html;
Isn't a good laugh worth a nickel...?<http://nickelatatime.blogspot.com/;
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2020-02-13 10:22 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-12 14:25 Oracle to PostgreSQL - DWH soumik.bhattacharjee@kpn.com
2020-02-12 14:26 ` Andrew Kerber <andrew.kerber@gmail.com>
2020-02-12 15:31 ` John Wiencek <jwiencek3@comcast.net>
2020-02-12 15:35 ` soumik.bhattacharjee@kpn.com
2020-02-12 15:41 ` John Scalia <jayknowsunix@gmail.com>
2020-02-12 16:24 ` Allan Davis <allandavisjr@gmail.com>
2020-02-12 18:26 ` John Wiencek <jwiencek3@comcast.net>
2020-02-12 19:36 ` Mohinder Raina <mohinder.raina41@gmail.com>
2020-02-13 08:32 ` Bert <biertie@gmail.com>
2020-02-13 10:22 ` soumik.bhattacharjee@kpn.com
2020-02-12 15:43 ` Amit jain <amit7.jain@gmail.com>
2020-02-12 16:06 ` Gurudutt Dhareshwar <gurudutt.dhareshwar@gmail.com>
2020-02-13 10:19 ` soumik.bhattacharjee@kpn.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox