使用DBlink在Postgres中同步两个表 [英] synchronizing two tables in postgres using DBlink

问题描述

我想在两个不同的服务器（server1和server2）中有两个同步的数据库，所以：

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

• table1更新server2中的table1当server1中的table1
  更改

和

$ b当服务器2中的表1
更改时，服务器2中的$ b
• 表1更新服务器1中的表1

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

这样我可以同时拥有两个从两个服务器更新时，表同步，我已经成功地使用DBlink从server1更新到服务器2。

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.

出了什么问题？当两个表都启用了触发器时，它将创建一个无限循环，因此server1会选择然后更新server2，然后更改并更新server1，依此类推。
有没有办法解决我所需要的问题？

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?

谢谢。

推荐答案

使用table1中的附加字段（标志）和有条件执行的触发器可以轻松解决该问题。

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

添加新的表中的列：

alter table table1 add column sync boolean default false;

在触发功能中，将 sync 设置为 true ：

In a trigger function set sync to true:

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

在另一台服务器上更新table1

创建触发器的条件是：

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.

这篇关于使用DBlink在Postgres中同步两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！