postgresql - 在两个 PostgreSQL 服务器之间传输数据

Question

我有两台 PostgreSQL 服务器，一台集中式服务器，一台外围设备。这个想法是让外围服务器从同一台机器上运行的各种进程中收集数据。定期(也许每小时或大约)，数据从外围服务器传输到中央服务器。确认服务器成功后，外围服务器将删除其内容以使其实现尽可能轻量级。

虽然最初这是一个相当直接的场景，其中有许多现有的解决方案，但我确信，有几个因素限制了我的选择:

• 外围服务器和中央服务器之间的链接可能一次中断几天，在这种情况下，它只会保留所有数据并重试下一次预定的传输，数据量会增加一倍。

• 重要的是没有记录是重复的，因此删除的记录与转移的记录完全匹配是很重要的。

• 外围服务器很可能在传输过程中积累了额外的数据，因此删除并重新创建表是不可行的，因为这会清除一些尚未传输的记录。

---

我从几个角度审视了这个问题，并得出结论，我很可能正在尝试重新发明轮子，因为有多种方法几乎可以满足我的需要。因此，我决定退后几步，看看针对这种情况会出现什么建议。那么我的问题是:

• 当链接不可靠时，推荐的传输方法是什么？

• 转账验证方式有哪些？

• 当外围数据库在传输过程中仍在累积数据时，如何确保删除日期与传输数据完全匹配？

在 debian 上使用 postgresql 9.4.9。

---

一个想法的粗略概述:

1. 该表有一个 serial 列，用于跟踪记录 ID。

2. pg_dump 数据到一个文件。前面提到的序列号用于文件名，基本上“这包含从 X 到 Y 的所有记录”

3. 转储文件通过rsync等方式复制到中央服务器。

4. 中央服务器加载数据，并以某种方式将验证发送回包含序列号的外围服务器。考虑重复此操作几次，以确保以后不会传输重复项。

5. 外围服务器核对与已验证连续剧有关的所有数据。

有什么想法吗？

Answer 1

您提出的方法是一个合理的起点，但在第一点就存在缺陷。您隐含地假设 serial列提供了一些可靠的排序边界。 它没有。

1. Txn 1 开始
2. Txn 2 开始
3. Txn 1 得到序列值 4
4. Txn 2 得到序列值 5
5. Txn 2 提交
6. Txn 3 开始
7. Txn 3 拷贝出数据
8. Txn 3 通知最高 promise 值为 5
9. Txn 1 提交
10. Txn 3 删除所有 id <= 5 的数据

糟糕。 Txn 3 删除了 id=4，但它不会被复制。这种特殊情况通过制作 txn3 SERIALIZABLE 来解决。 ，但是如果 Txn3 提交而其他一些 tx txn4 执行删除，问题又回来了。

为了安全起见，您必须在 5 和 7 之间的某处添加屏障，您可以在此处 LOCK TABLE ... IN EXCLUSIVE MODE并等待获取锁。这确保没有未完成的写入，所有内容都已提交。您现在可以相信，以后不会提交低于最高值读取的值。

从一个不同的(新开始的)xact，然后你 pg_export_snapshot .

导出快照后，您可以回滚获取锁的 xact 并让写入继续。

让导出快照的 xact 保持打开状态。将快照 ID 传递给 pg_dump与 --snapshot ，因此它从您知道最高提交 ID 的虚拟时间点准确地转储。在转储完成之前，您必须保持保存快照的 xact 打开，但同时可以继续写入。

您现在可以安全地 DELETE FROM ... WHERE id < x对于 x 的值你抬头看着锁。您知道，由于导出的快照，您没有转储任何具有更高 ID 的内容。而且您知道，由于锁定，您不会错过任何稍后以较低 ID 提交的内容。

---

看起来很复杂？

相反，我建议使用 PostgreSQL 的逻辑解码功能。工具pglogical ，它使用逻辑解码，具有使您想做的事情相对容易的功能；它消除了排序问题并保证行被复制恰好一次。很容易将它设置为复制插入，但不复制删除，所以你可以 DELETE FROM mytable时不时地。

(当然，我是 pglogical 及其相关工具 BDR 开发团队的一员，所以我有偏见)。

您可能面临的主要问题是您必须保留更多数据，因为您需要保留 WAL，而不仅仅是表内容。 (从技术上讲，您实际上可以 DELETE 数据一旦被 INSERT 编辑并且它会复制得很好，但您可能不想这样做)。因此，如果磁盘空间是个问题，您可能希望使用转储方法。但是 pglogical3 中的一些重大改进将使这个问题消失。