postgresql - 如何查询自指定点(时间戳或事务 ID)以来的 postgres 增量更新？

Question

我希望能够找出 postgresql 表的增量变化。

我知道我可以使用时间戳方法:添加一个时间戳列，当一行更新时，我可以用它保存一个时间戳，然后可以查询在指定时间戳之后更改的行。由于某种原因，数据库触发器对我来说不是一个选项，如果不使用数据库触发器，我必须在 Web 服务器上生成时间戳，这需要使用 NTP 来控制服务器之间的时间容差，这也不是一个选项对我来说，因为我无法控制服务器。

所以我转向另一种解决方案:选择在指定事务 ID 之后修改的行。在 Postgres 中，txid_current() 可以返回当前事务 ID，我可以通过 sql“select * from table_name where xmin > {transaction_id}”获得增量更新。

经过一些简单的测试，我发现它有效。一个已知的问题是事务id会随着时间的推移而增长，有一天需要由postgres重置，但我正在考虑在事务id接近最大值时暂时禁用增量更新功能，并在重置后重新启用它完成了。

问题是:我不确定事务 ID 和 xmin 是否可以可靠地用于检测增量更改(已知的 transaction_id 溢出/重置问题除外)。

感谢您对增量更新的任何建议。也许还有一些其他方法可以查询增量更新。

Answer 1

您真正想要的是 PostgreSQL 9.4 的 logical decoding支持，它允许您从服务器中提取更改流。要使用它，您需要一个逻辑解码插件，将服务器上的更改流转换为您的应用程序可以使用的内容。有一些正在开发中，但还为时过早。

由于您在评论中指出您正在使用 AWS RDS，此时您运气不佳，因为在撰写本文时 RDS 不提供任何解码插件，您需要 super 用户权限才能安装它们.

您不能使用 xmin 和 xmax 进行完整的增量复制，因为您不能在 PostgreSQL 中进行脏读，因此您无法查看一个元组是否已被 DELETEd。要使用事务 ID，您需要防止 VACUUM 删除“死”行，即当前没有事务仍需要正确执行的行。您还需要能够进行脏读。这些在 PostgreSQL 中都没有简单的解决方案。

如果您有仅插入表(或者您执行插入和更新，从不删除，并且从不更改行的主键)，那么您可以使用事务 xmin。您将不得不处理 transaction-id wraparound通过检查 pg_database.datfrozenxid 和 pg_class.relfrozenxid 来寻找感兴趣的关系。详见源码和代码中的注释。 VACUUM 如果您从不删除条目，那么没有脏读也不是问题，因为您不需要看到“消失的”行。

如果可能，在 9.4+ 中使用逻辑解码。对于旧版本，如果您需要完整复制，您需要积累一个带有触发器的更改队列。

如果您可以完全禁止删除和主键更改，您可以使用 xmin 查找更改的行，只要您注意确保没有运行 delete ，或任何更改 primary key 的 update。