On 05/08/2017 08:31 PM, Armand Pirvu (home) wrote:
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.
What are the Postgres versions?
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
Probably this:
https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/
"4.11 Triggers
Apply process and the initial COPY process both run with
session_replication_role set to replica which means that ENABLE REPLICA
and ENABLE ALWAYS triggers will be fired."
https://www.postgresql.org/docs/9.6/static/sql-altertable.html
"DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
... The trigger firing mechanism is also affected by the
configuration variable session_replication_role. Simply enabled triggers
will fire when the replication role is "origin" (the default) or
"local". Triggers configured as ENABLE REPLICA will only fire if the
session is in "replica" mode, and triggers configured as ENABLE ALWAYS
will fire regardless of the current replication mode.
...
"
So did you ENABLE REPLICA or ALWAYS on the db2 table triggers?
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.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
