Re: Oracle to PostgreSQL - DWH

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(dot)raina41(at)gmail(dot)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(at)comcast(dot)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(at)gmail(dot)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(at)comcast(dot)net>
>> wrote:
>>
>>> EnterpriseDB has a product called Migration Toolkit (MTK) that can do
>>> this.
>>>
>>>
>>> John
>>>
>>> On Feb 12, 2020, at 8:25 AM, <soumik(dot)bhattacharjee(at)kpn(dot)com> <
>>> soumik(dot)bhattacharjee(at)kpn(dot)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