如何在Bucardo Postgresql Multi Master中处理序列 [英] How to handle sequences in Bucardo Postgresql multi master

问题描述

我们正在三台不同的Postgresql服务器上建立数据库(也许将来还会有更多服务器)，目前正在使用bucardo多主机组同步所有表.

We are setting up a database on three different Postgresql servers (and maybe on more in the future), currently syncing all tables using bucardo multi-master groups.

我们不同步序列；我们尝试过，但是我们注意到，当同一张表在不同服务器上同时写入时，bucardo使我们丢失数据.由于它们使用相同的键，因此在同步时间，bucardo选择删除重复的行之一.

We are not syncing sequences; we tried that, and we noticed bucardo is making us lose data when simultaneous writes occur in the same table, on different servers. Since they use the same keys, on sync time bucardo chooses to drop one of the duplicate rows.

我们当前的方法是在数据库的每个实例上手动命名序列的名称.例如.实例1保持不变，所有instance2表序列都更新为从2 ^ 31/10开始，instance3表序列将被更改为从2 ^ 31/10 * 2开始.instance10序列从2 ^ 31/开始10 * 9.

Our current approach is to manually namespace the sequence on each instance of the database. E.g. instance 1 is left as it is, all instance2 table sequences are updated to start from 2^31/10, instances3 table sequences will be altered to start from 2^31/10*2 .. instance10 sequences to start from 2^31/10*9.

您对这种方法有何看法?您对Bucardo多主设置有什么其他建议?不能选择Postgresql BDR，因为它尚未被认为是稳定版本.

What is your opinion on this approach and what other advice do you have for a Bucardo multi-master setup? Postgresql BDR is not an option as it is not considered a stable release yet.

推荐答案

不得在带有Bucardo的多源设置中复制序列，否则会发生冲突.

Sequences must not be replicated in a multi-source setup with Bucardo as conflicts will arise.

以大数目开始序列是一种常见的，有效的方法.对我来说是一个关于以下问题:

Start the sequences in different big numbers is a common, valid, approach. For me is a question about:

• 如果您应用语义"到自动增量主键，例如插入顺序"
• 您如何看待数据
• 每个数据库中将生成的预期行数.例如，如果大多数行仅由一个来源生成，那么在所有来源中放置相同数量的可用自动增量并不是最佳策略.

如果同步中仅涉及两个来源，我的首选策略是对一个使用奇数，而对另一个使用奇数.

If there are only two sources involved in the sync my preferred strategy is use odd numbers for one, and even for the other.

如果最多有九个数据源，对我来说，很容易将数据设置为10，然后以不同的数字1、2，...，因此，第一个数据库"启动每个db. ;会生成1，11，21，...第二2，12，22，...这种方法的好处是，如果您从四个来源开始，并且需要另一个来源，则什么都不应更改.在建议的策略中，如果您在4个数据库之间分配了所有可用空间，而又需要再拆分一个，那么该空间将更加困难.

If there are up to nine sources, for me is easy to reason about the data setting up the autoincrement value to 10, and start each db in a different number, 1, 2, ... so, "first database" will generate 1, 11, 21, ... second 2, 12, 22, ... A good thing of this approach is that if you start with four sources and need another one, nothing should be changed. In your suggested strategy if you split all available space between 4 databases, and need another one split again the space is more difficult.

如果您不限于使用自动增量主键，则其他不错的选择是使用UUID，但可以肯定地

If you are not limited to use autoincremental primary keys, other good options is to use UUID, but for sure it have caveats.

这篇关于如何在Bucardo Postgresql Multi Master中处理序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！