如何确定何时将数据插入Postgres？ [英] How to find out when data was inserted to Postgres?

问题描述

我继承了一个现有的充满数据的Postgres数据库。大多数数据具有 created_date列值。

I have inherited an existing Postgres database full of data. Most of the data has a 'created_date' column value. Some of the earlier data was inserted before this was being tracked.

是否存在隐藏在某个位置的Postgres元数据表，该位置跟踪 INSERT 查询已完成？

Is there a Postgres metadata table hidden away somewhere that tracks when INSERT queries were done?

推荐答案

Postgres 9.5或更高版本

您可以启用 <$ c $ postgresql.conf 中的c> track_commit_timestamp （并重新启动）以开始跟踪提交时间戳。然后，您可以获取 xmin 的时间戳。相关答案：

Postgres 9.5 or later

You can enable track_commit_timestamp in postgresql.conf (and restart) to start tracking commit timestamps. Then you can get a timestamp for your xmin. Related answer:

• 提交交易时以原子方式设置SERIAL值

• Atomically set SERIAL value when committing transaction

除非您自己记录，否则PostgreSQL中没有此类元数据。

There is no such metadata in PostgreSQL unless you record it yourself.

您也许可以推断行标题（HeapTupleHeaderData）中的一些信息。，尤其是插入交易ID xmin 。它保存插入行的事务的ID（需要确定PostgreSQL MVCC 模型）。尝试（对于任何表）：

You may be able to deduce some information from the row headers (HeapTupleHeaderData), in particular from the insert transaction id xmin. It holds the ID of the transaction in which the row was inserted (needed to decide visibility in PostgreSQL's MVCC model). Try (for any table):

SELECT xmin, * FROM tbl LIMIT 10;

有一些限制：

• 如果数据库已转储并还原，则显然信息已经消失了-所有行都插入到同一事务中。


• 如果数据库很大/非常旧/写得很重，那么它可能已经经历了交易ID环绕，并且 xmin 中的数字顺序不明确。

• If the database was dumped and restored then, obviously, the information is gone - all rows are inserted in the same transaction.
• If the database is huge / very old / very heavily written, then it may have gone through transaction ID wraparound, and the order of numbers in xmin is ambiguous.

但是对于大多数数据库，您应该能够得出：

But for most databases you should be able to derive:

• INSERT的时间顺序


• 哪些行被插入在一起


• 何时（可能）两次插入之间的时间很长

不过没有时间戳。

这篇关于如何确定何时将数据插入Postgres？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！