synchronizing two tables in postgres using DBlink

Question

I want to have two synchronized DB in two different servers (server1 and server2) so:

table1 in server1 updates table1 in server2 when table1 in server1 changes

and

table1 in server2 updates table1 in server1 when table1 in server2 changes

this way i can have both tables synchronized when updated from both server, i have managed to use DBlink without problems to update from server1 to server 2.

What is the problem? when both tables have triggers enabled it creates an infinite loop so server1 chenges then it updates server2, then it changes and it updates server1 and so on. Is there a way to do what i need without having this problem?

Thanks.

klin · Accepted Answer · 2015-08-10 23:53:04Z

The problem can be easily solved with an additional field (flag) in the table1 and a conditionally executed trigger.

Add a new column to the table:

alter table table1 add column sync boolean default false;

In a trigger function set sync to true:

create function on_update_table1()
...
--  update table1 on another server with dblink setting sync = true
...

Create trigger with a condition:

create trigger on_update_table1
after update on table1
for each row when (not new.sync)
execute procedure on_update_table1();

The trigger will be fired only if an update was not executed by a trigger from another server.

Note however, that in the proposed method of synchronization you can meet with more difficult problems. Especially, you should know how to solve the conflict of simultaneous changes of the same row on two servers. Your synchronization can be applied only when such conflicts are eliminated in the design time, or you'll have to implement your own locking system, which may be rather complicated issue.

Collectives™ on Stack Overflow

synchronizing two tables in postgres using DBlink

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related