On Sat, 11 Jun 2016 09:59:59 +0530 Sridhar N Bamandlapally <sridhar.bn1@xxxxxxxxx> 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@xxxxxxxx> -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
