Thread: Online DW

On 6/10/2016 1:11 AM, Sridhar N Bamandlapally wrote:
> 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

you'd need to manage that yourself.

I'd probably use a custom ETL job that pulls data from the production
database and sends it to the EDW using a message queue (EMS, JMS, AMQ,
etc).   the production database would be partitioned by day, and once a
day drop the 7+ day old partition, while the EDW wouldn't ever drop, and
if its partitioned, maybe by week or month.

It would probably be easier if the the EDW didn't need the latest
transactions, only data an hour to a day old.   If the EDW needed
near-live data, then I'd want to be using the same sort of messaging
queue platform (EMS, JMS, AMQ, etc) to send the data to the production
database, so that the EDW feed process could subscribe to the same events.

this sort of pattern works easier if your data is inserted once and not
updated, but it can be made to handle updates, too.   the message queue
paradigm ('publish/subscribe') is a very powerful way of implementing
complex distributed systems.




--
john r pierce, recycling bits in santa cruz

On 6/10/2016 2:18 AM, Sridhar N Bamandlapally wrote:
> 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

there's a whole lot of implied magic here unless you want to get way
more specific what these features do, exactly, under all possible
conditions.



--
john r pierce, recycling bits in santa cruz

This is what I feel will give me solution to maintain production (current+7days) and archive(current+history) without any etl/scheduler

But there is no feature available in any database

Sridhar
Opentext

One thing we can restrict to "begin noarchive" transaction block are DELETE and SELECT only

Sridhar
Opentext

I may be wrong but ...

On Fri, Jun 10, 2016 at 6:33 PM, Sridhar N Bamandlapally
<sridhar.bn1@gmail.com> wrote:
> One thing we can restrict to "begin noarchive" transaction block are DELETE
> and SELECT only
> On 10 Jun 2016 21:57, "Sridhar N Bamandlapally" <sridhar.bn1@gmail.com>
> wrote:
>> This is what I feel will give me solution to maintain production
>> (current+7days) and archive(current+history) without any etl/scheduler

It seems ( to me ) you have a grossly underspecificied ( in the list )
problem and you have invented an even more underspecified keyword to
magically solve it, and expect someone to develop it.

And you haven't even bothered to avoid top posting, which is frowned
upon on this list and makes infering your problems solution even more
difficult.

If you want to have something like this you'll need to post much more
details on what you are proposing, what are the use cases for the
general public, etc.. Just eyeballing it I would estimate this will
need many pages just to state the problems and the intended semantics
of your proposal.

Regards.
   Francisco Olarte.

This is what I feel will give me solution to maintain production (current+7days) and archive(current+history) without any etl/scheduler

But there is no feature available in any database

Sridhar
Opentext 

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 Sat, 11 Jun 2016 09:59:59 +0530
Sridhar N Bamandlapally <sridhar.bn1@gmail.com> wrote:

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

As I see, you have 2 options (A, and B)

A) With FDW

1) Create 2 databases on production:
  a) first, a normal database for production, with 7 last days data,
  b) second, as postgres_fdw remote database on archive server.

https://www.postgresql.org/docs/current/static/postgres-fdw.html

2) Make a trigger on each table in production database to replicate
inserts, updates and deletes to fdw remote tables.

3) At your scheduled time, truncate tables (don't delete content or the
trigger will fire up) on your production db.


Note, make date part of your primary keys, if not, the same pk may be
reused on production tables and the insert on archive fdw tables will
fail.


B) Use pgpoolII, use replication to store the same data on both
databases. On production db, delete old data. Don't connect both
databases with streaming replication nor slony or anything else,
pgpoolII will do the work.

http://pgpool.net/mediawiki/index.php/Main_Page
http://www.pgpool.net/docs/latest/tutorial-en.html#replication-config

Read full documentation, above url cites point directly to the replication part but read full documentation.

HTH

> Thanks
> Sridhar
> Opentext


---   ---
Eduardo Morras <emorrasg@yahoo.es>

On Sat, 11 Jun 2016 09:59:59 +0530
Sridhar N Bamandlapally <sridhar.bn1@gmail.com> wrote:

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

As I see, you have 2 options (A, and B)

A) With FDW

1) Create 2 databases on production:
  a) first, a normal database for production, with 7 last days data,
  b) second, as postgres_fdw remote database on archive server.

https://www.postgresql.org/docs/current/static/postgres-fdw.html

2) Make a trigger on each table in production database to replicate
inserts, updates and deletes to fdw remote tables.

3) At your scheduled time, truncate tables (don't delete content or the
trigger will fire up) on your production db.


Note, make date part of your primary keys, if not, the same pk may be
reused on production tables and the insert on archive fdw tables will
fail.


B) Use pgpoolII, use replication to store the same data on both
databases. On production db, delete old data. Don't connect both
databases with streaming replication nor slony or anything else,
pgpoolII will do the work.

http://pgpool.net/mediawiki/index.php/Main_Page
http://www.pgpool.net/docs/latest/tutorial-en.html#replication-config

Read full documentation, above url cites point directly to the replication part but read full documentation.

HTH

> Thanks
> Sridhar
> Opentext


---   ---
Eduardo Morras <emorrasg@yahoo.es>