I have a question regarding the Bucardo capabilities in Postgre SQL. Bucardo puts in sync tables between several databases. Imagine we have table Orders in DB1 and DB2.
create table orders(order_id integer primary key, item_id integer, quantity integer);
And we're making changes to Orders table in DB1.
insert into orders(item_id,quantity) values(1,235);
Then Bucardo replicates all these changes to Orders table in DB2. But apart from this sync I want Bucardo to modify table Stock in DB2
create table stock(item_id integer primary key, name varchar(50), quantity integer);
Just to decrease the quantity field of stock table in DB2 by the value 235 (the value inserted to quantity field of orders table in DB1) for the record with item_id = 1. Is it possible to customize the Bucardo in this way? And what is the best way to implement this functionality?
This will be hard to achieve as bucardo doesn't replicate based on SQL statements. You can however use stored procedures on the primary which is DB1 and also replicate that table.