Re: [HACKERS] Online DW

[Craig Ringer]

On 11 June 2016 at 12:29, Sridhar N Bamandlapally 
wrote:

> I need every transaction coming from application sync with both production
> and archive db,
>
> but the transactions I do to clean old data(before 7 days) on production
> db in daily maintenance window should not sync with archive db,
>
> Archive db need read-only, used for maintaining integrity with other
> business applications
>

In a separate mail to -general I've asked Sridhar to keep this discussion
to -general, not -hackers, from now on.


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Online DW

[Sridhar N Bamandlapally]

Ok, let me put this way,

I need every transaction coming from application sync with both production
and archive db,
but the transactions I do to clean old data(before 7 days) on production db
in daily maintenance window should not sync with archive db,

Archive db need read-only, used for maintaining integrity with other
business applications

Issue here is,
1. etl is scheduler, cannot run on every transaction, even if it does, its
expensive

2. Materialize view(refresh on commit) or slony, will also sync clean-up
transactions

3. Replication is not archive, definitely not option

I say, every online archive db is use case for this.

Thanks
Sridhar
Opentext


On 10 Jun 2016 22:36, "David G. Johnston" 
wrote:

> On Fri, Jun 10, 2016 at 4:11 AM, Sridhar N Bamandlapally <
> sridhar@gmail.com> wrote:
>
>> Hi
>>
>> Is there any feature in PostgreSQL where online DW (Dataware housing) is
>> possible ?
>>
>> am looking for scenario like
>>
>> 1. Production DB will have CURRENT + LAST 7 DAYS data only
>>
>> 2. Archive/DW DB will have CURRENT + COMPLETE HISTORY
>>
>> expecting something like streaming, but not ETL
>>
>>
> ​The entire DB couldn't operate this way since not every record has a
> concept of time and if you use any kind of physical time you are going to
> have issues as well.
>
> First impression is you want to horizontally partition your
> "time-impacted" tables so that each partition contains only data having the
> same ISO Week number in the same ISO Year.
>
> Remove older tables from the inheritance and stick them on a separate
> tablespace and/or stream them to another database.
>
> As has been mentioned there are various tools out there today that can
> likely be used to fulfill whatever fundamental need you have.  "Not ETL" is
> not a need though, its at best a "nice-to-have" unless you are willing to
> forgo any solution to your larger problem just because the implementation
> is not optimal.
>
> Unless you define your true goals and constraints its going to be hard to
> make recommendations.
>
> David J.
>
>

Re: [HACKERS] Online DW

[David G. Johnston]

On Fri, Jun 10, 2016 at 4:11 AM, Sridhar N Bamandlapally <
sridhar@gmail.com> wrote:

> Hi
>
> Is there any feature in PostgreSQL where online DW (Dataware housing) is
> possible ?
>
> am looking for scenario like
>
> 1. Production DB will have CURRENT + LAST 7 DAYS data only
>
> 2. Archive/DW DB will have CURRENT + COMPLETE HISTORY
>
> expecting something like streaming, but not ETL
>
>
​The entire DB couldn't operate this way since not every record has a
concept of time and if you use any kind of physical time you are going to
have issues as well.

First impression is you want to horizontally partition your "time-impacted"
tables so that each partition contains only data having the same ISO Week
number in the same ISO Year.

Remove older tables from the inheritance and stick them on a separate
tablespace and/or stream them to another database.

As has been mentioned there are various tools out there today that can
likely be used to fulfill whatever fundamental need you have.  "Not ETL" is
not a need though, its at best a "nice-to-have" unless you are willing to
forgo any solution to your larger problem just because the implementation
is not optimal.

Unless you define your true goals and constraints its going to be hard to
make recommendations.

David J.

Re: [HACKERS] Online DW

[Sridhar N Bamandlapally]

One thing looks possible ( feature not available), just an idea

example/syntax:

BEGIN NOARCHIVE;

  --- transaction-1
  --- transaction-2
  .
  .
  --- transaction-N

END;

This/These will be performed in Production to clean-up archive which will
not be sync with Archive/DW DB only

one heads-up is Archive/DW DB may need to build WITHOUT CONSTRAINTS

May need to introduce ARCHIVE system/tag in pg_hba.conf

Thanks
Sridhar
OpenText














On Fri, Jun 10, 2016 at 2:22 PM, Craig Ringer  wrote:

> On 10 June 2016 at 16:11, Sridhar N Bamandlapally 
> wrote:
>
>> Hi
>>
>> Is there any feature in PostgreSQL where online DW (Dataware housing) is
>> possible ?
>>
>> am looking for scenario like
>>
>> 1. Production DB will have CURRENT + LAST 7 DAYS data only
>>
>> 2. Archive/DW DB will have CURRENT + COMPLETE HISTORY
>>
>> expecting something like streaming, but not ETL
>>
>
> There's nothing built-in, but that's exactly the sort of thing pglogical
> is intended for. You can also build something along those lines with
> Londiste fairly easily.
>
> Hopefully this is the sort of thing we can move toward with built-in
> logical replication in coming releases.
>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [HACKERS] Online DW

[Craig Ringer]

On 10 June 2016 at 16:11, Sridhar N Bamandlapally 
wrote:

> Hi
>
> Is there any feature in PostgreSQL where online DW (Dataware housing) is
> possible ?
>
> am looking for scenario like
>
> 1. Production DB will have CURRENT + LAST 7 DAYS data only
>
> 2. Archive/DW DB will have CURRENT + COMPLETE HISTORY
>
> expecting something like streaming, but not ETL
>

There's nothing built-in, but that's exactly the sort of thing pglogical is
intended for. You can also build something along those lines with Londiste
fairly easily.

Hopefully this is the sort of thing we can move toward with built-in
logical replication in coming releases.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

[HACKERS] Online DW

[Sridhar N Bamandlapally]

Hi

Is there any feature in PostgreSQL where online DW (Dataware housing) is
possible ?

am looking for scenario like

1. Production DB will have CURRENT + LAST 7 DAYS data only

2. Archive/DW DB will have CURRENT + COMPLETE HISTORY

expecting something like streaming, but not ETL

Thanks
Sridhar