Re: data transformation and replication

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(dot)klaver(at)aklaver(dot)com