Re: [HACKERS] Online DW

On Sat, 11 Jun 2016 09:59:59 +0530
Sridhar N Bamandlapally <sridhar(dot)bn1(at)gmail(dot)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(at)yahoo(dot)es>