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" <david.g.johnston@xxxxxxxxx> wrote:
HiIs there any feature in PostgreSQL where online DW (Dataware housing) is possible ?am looking for scenario like1. Production DB will have CURRENT + LAST 7 DAYS data only2. Archive/DW DB will have CURRENT + COMPLETE HISTORYexpecting something like streaming, but not ETLThe 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.
