My bad
db1 I have two tables t1 and t2 (or more) db2 has one table t3 for example which can get data aggregated from one or more multiple tables from the above set . I can updates/inserts/deletes in db1.t1 and/or db1.t2 which combined may mean related data in db.t3 would need to be inserted/deleted/updated. Think of it like ETL processing if you will. This is what I mean by data massaging/transformation db1 and db2 are two different servers. So I was initially thinking that I can have on db2 the same set of tables from db1, replication being done using pglogical. Once data gets to db2 t1 and t2, I can have on db2 a set of functions/triggers which can transform the data and as such do the relevant inserts/updates/delete from db2.t3 Apparently though that is not possible unless I am missing something I reached that conclusion by using a trigger and a function like the auditing one to track insers/updates/deletes in an audit table Having these said I was thinking (a) - On db1 I will have the t3 table as is on dsb2. All data transformation goes into db1.t3 which on it's turn will replicate to db2.t3 using pglogical (b) - On db2 I will have the t1 t2 as they are on db1. Those are replicated using Slony/Bucardo. Once data lands on db2.t1 and db2.t2 another set of triggers/functions responsible for data transformation will do the inserts/deletes/updates in db2.t3 I wold much prefer pglogical approach as stated in the what I see as a failed case If the only options is Slony/Bucardo , so be it. but that begs the following questions - which one has the smallest overhead ? - which one is the easiest to manage ? - which one is the most reliable ? - I recall data transformation can be used in Bucardo but did not see any examples on that. Any pointers ? Thanks Armand On May 8, 2017, at 4:49 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/08/2017 12:46 PM, Armand Pirvu (home) wrote: >> Hi >> >> Here it is a scenario which I am faced with and I am hoping to find a >> pointer/tip/help >> >> db1 is the OLTP system >> db2 is the Reporting system >> >> The data from db1 needs to get to db2, but the database on those two have >> tables with different layout/structure and hence data will need to suffer >> some transformation in between in real time >> >> I was looking at something like >> >> db1 -> db2 replicates the same set of tables and with the same structures >> using pglogical for example >> db2.tbl1 -> db2.tbl2 data gets massages/transformed based on what replicates >> from db1.tbl1 using triggers and functions >> >> >> Other than that I reckon db1 -> db2 would be trigger based using something >> like slonik maybe (?) and data massage/transformation gets moved from db2 to >> db1 machine and then db1.tbl2 -> db2.tbl2 using pglogical > > I was following you until the last part, "... moved from db2 to db1 machine > and then db1.tbl2 -> db2.tbl2 ..." > > Is this correct? > > If so why db1 --> db2 --> db1 --> db2? > > A complete answer is going to depend on at least an outline of what you mean > by massage/transform? > >> >> >> Is this doable ? If so any pointers as to where to look about it ? >> >> >> Many thanks >> Armand >> >> >> >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com
