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(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/>
>
>