PostgreSQL 如何查找最近 n 分钟内的任何更改
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13768274/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL how to find any changes in the last n-minutes
提问by Andrew
I am writing a program that syncs PostgreSQL and MS SQL server databases (and adds some changes in this transition). With multi million records, it takes a long time, and loads server pretty bad with select *; it also takes more resources to parse unchanged records and validate them against MS SQL server.
我正在编写一个同步 PostgreSQL 和 MS SQL 服务器数据库的程序（并在此转换中添加一些更改）。拥有数百万条记录，需要很长时间，并且服务器负载非常糟糕select *；解析未更改的记录并针对 MS SQL 服务器验证它们也需要更多资源。
Are there any logs in PostgreSQL that I can parse to find out the changes that took place in the last n-minutes? This would allow me to select only those records that I need to work; improving performance.
我可以解析 PostgreSQL 中的任何日志以找出过去 n 分钟内发生的更改吗？这将允许我只选择那些我需要工作的记录；提高性能。
回答by Eric Leschinski
Postgresql, find changes in the last n minutes:
Postgresql，查找最近n分钟的变化：
Postgresql does not automatically store the time that rows were added/updated/deleted (it would really slow things down for postgresql to handle timestamps like this if you didn't want it to).
Postgresql 不会自动存储添加/更新/删除行的时间（如果你不希望它处理这样的时间戳，它真的会减慢 postgresql 的速度）。
You'll have to do it yourself: Add a timestamp column to the table. When you insert a row into the table, have it update the timestamp column to the current_timestamp. When you are selecting the row, use a select statement that filters down where timestamp is greater than N minutes ago as follows:
您必须自己完成：向表中添加时间戳列。当您在表中插入一行时，让它将时间戳列更新为current_timestamp. 选择行时，使用 select 语句过滤时间戳大于 N 分钟前的位置，如下所示：
Get rows where a timestamp is greater than a date:
获取时间戳大于日期的行：
SELECT * from yourtable
WHERE your_timestamp_field > to_date('05 Dec 2000', 'DD Mon YYYY');
Get rows that have been changed in the last n minutes:
获取在过去 n 分钟内已更改的行：
SELECT * from yourtable
WHERE your_timestamp_field > current_timestamp - interval '5 minutes'
回答by sega_sai
You can use a trigger based approach described here:
您可以使用此处描述的基于触发器的方法：
http://wiki.postgresql.org/wiki/Audit_trigger
http://wiki.postgresql.org/wiki/Audit_trigger
Essentially every table change fires a trigger which can write some info in a log table.
本质上，每个表更改都会触发一个触发器，该触发器可以在日志表中写入一些信息。
