如何查询自指定点（时间戳或事务ID）以来的postgres增量更新？ [英] How to query postgres incremental updates since a specified point (a timestamp or a transaction id)?

问题描述

我希望能够找出postgresql表的增量更改。

I want to be able to find out incremental changes of postgresql tables.

我知道我可以使用timestamp方法：添加一个timestamp列，并在更新一行时保存一个时间戳，然后可以查询更改后的行指定的时间戳记。由于某些原因，数据库触发器不是我的选择，如果不使用数据库触发器，则必须在Web服务器上生成时间戳，这需要使用NTP来控制服务器之间的时间容忍度，这也不是选项对我来说，因为我没有服务器的控制权。

I know I could use timestamp method: add a timestamp column, and when a row is updated, I could save a timestamp with it, and then could query rows changed after a specified timestamp. Database trigger is not an option for me for some reason, and if don't use database trigger, I have to generate timestamp on web servers, which needs to use NTP to control the time tolerance between servers, and this is also not an option for me as I don't have the control of the servers.

因此，我转向另一种解决方案：选择在指定交易ID之后修改的行。在Postgres中，txid_current（）可以返回当前的事务ID，并且我可以通过sql从table_name中选择*，其中xmin> {transaction_id}来获取增量更新。

So I'm turning to another solution: select rows that are modified after a specified transaction id. In Postgres txid_current() could return the current transaction id, and I could get incremental updates by sql "select * from table_name where xmin > {transaction_id}".

简单的测试我发现它可行。一个已知的问题是，事务ID将随着时间增长，并且有一天需要通过postgres进行重置，但是我正在考虑在事务ID接近最大值时暂时禁用增量更新功能，并在重置后重新启用它

After some simple testing I find it works. One known issue is that the transaction id will grow over time and one day will needs to reset by postgres, but I'm considering to temporarily disable the incremental updates feature when transaction id is approaching the max value and re-enabling it after the reset is done.

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

The question is: I'm not sure if transaction id and xmin could be reliably used to detect incremental changes (except the known transaction_id overflow/reset issue).

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

Thanks for any suggestions on incremental updates. Maybe there're some other ways to query incremental updates.

推荐答案

您真正想要的是PostgreSQL 9.4的逻辑解码支持，可让您从中提取变更流服务器。要使用它，您需要一个逻辑解码插件，该插件将服务器上的更改流转换为您的应用程序可以使用的内容。有大量开发中，但仍处于早期阶段。

What you really want is PostgreSQL 9.4's logical decoding support, which lets you extract a change stream from the server. To use it you need a logical decoding plugin that turns the change stream on the server into something your app can consume. There are a number in development, but it's still pretty early.

由于您在评论中指出您正在使用AWS RDS，因此此时您已退出使用幸运的是，由于RDS在编写本文时不提供任何解码插件，因此您需要超级用户特权才能安装它们。

Since you noted in a comment that you're using AWS RDS, at this time you're out of luck, since RDS at time of writing doesn't offer any decoding plugins and you'd need superuser privileges to install them.

您不能使用xmin和xmax来进行完整的增量复制，因为您无法在PostgreSQL中进行脏读，因此您无法查看是否已将元组 DELETE d删除。要使用交易ID，您需要防止 VACUUM 删除无效行，即当前交易仍不需要正确执行的行。您还需要能够进行脏读。

You cannot use xmin and xmax for complete incremental copying, because you can't do dirty reads in PostgreSQL, so you can't see if a tuple has been DELETEd. To use transaction IDs you'd need to prevent VACUUM from removing "dead" rows, i.e. rows that no current transaction still needs to perform correctly. You'd also need to be able to do dirty reads. Neither of those have easy solutions in PostgreSQL.

如果您有仅插入表（或者您进行插入和更新，从不删除，并且永不更改行的主键），则可以使用事务xmin。您需要通过选中 transaction-id环绕来处理 code> pg_database.datfrozenxid 和 pg_class.relfrozenxid 的兴趣关系。有关详细信息，请参见源代码和代码中的注释。如果您从不删除条目，则 VACUUM 以及缺少脏读操作就不是问题，因为您无需查看消失的行。

If you have insert-only tables (or you do inserts and updates, never deletes, and never change the primary key of a row) then you can possibly use the transaction xmin. You will have to deal with transaction-id wraparound by checking pg_database.datfrozenxid and pg_class.relfrozenxid for the relation of interest. See the source code and comments in the code for details. VACUUM and the lack of dirty reads isn't a problem if you never delete an entry, since you don't need to see "vanished" rows.

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

Use logical decoding in 9.4+ if possible. For older versions if you need complete replication you need to accumulate a change queue with triggers.

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

If you can prohibit deletes and primary key changes completely you can use xmin to find changed rows, so long as you take care to ensure there are no deletes run, or any updates that change primary keys.

这篇关于如何查询自指定点（时间戳或事务ID）以来的postgres增量更新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！