Postgres 序列不同步

Question

我正在运行带有bucardo和postgres的多主设置。

我发现我的一些表序列彼此不同步。特别是自动递增的 id。

例：

DB1 - 表1

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')

新行的 id 为 1

DB2 - 表1

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')

新行的 id 为 1

db2 上新行的 id 应为 2，因为 bucardo 已从 db1 复制了数据，但 db2 的自动增量基于：

nextval('oauth_sessions_id_seq'::regclass)

如果我们检查"oauth_sessions_id_seq"，我们看到最后一个值为 0。

唷。。。有意义？

无论如何，我可以执行以下任何操作吗？

1. 使用 bucardo 复制会话表，以便共享每个数据库的会话？

2. 操作上面的默认自动增量功能以考虑表中的最大现有项目数？

如果您有更好的想法，请随时提出。问题只是问，谢谢任何帮助。

Answer 1

您将不得不更改 id 生成方法，因为根据常见问题解答中的此注释，没有 Bucardo 解决方案。

布卡多可以复制DDL吗？

不，Bucardo 依赖于触发器，Postgres 尚未提供 DDL 触发器或其系统表上的触发器。

由于 Bucardo 使用触发器，因此它无法"看到"序列更改，只能"看到"表中的数据，并对其进行复制。序列是不支持触发器的有趣对象，但您可以手动更新它们。我想您可以在INSERT之前添加类似以下代码的内容，但仍然存在问题。

SELECT setval('oauth_sessions_id_seq', (SELECT MAX(did) FROM distributors));

有关详细信息，请参阅此问题。

我还没有完全了解所涉及的所有问题，但是您可以手动执行最大计算并在重试循环中进行插入操作。如果您实际上在两个数据库上进行插入并允许 Bucardo 复制，我怀疑它会起作用，但如果您可以保证一次只有一个数据库更新，那么您可以尝试类似 UPSERT 重试循环。有关更多信息，请参阅此帖子。循环的"胆量"可能如下所示：

INSERT INTO  distributors (did, dname) 
    VALUES ((SELECT max(did)+1 FROM distributors), 'XYZ Widgets');

Answer 2

无论数据库（PostgreSQL，Oracle等）如何，都会为每个具有与其关联的主键的表创建动态序列。每当发生大量数据导入或某人手动修改表序列时，大多数序列都会不同步。

解决方案：我们可以设置序列的唯一方法是获取 PK 表的最大值并将序列设置为下一个值。

以下查询将列出在数据库架构中创建的所有序列：

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';
SELECT MAX('primary_key') from table;
SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);