如何列出最近24小时内数据变化的所有表？ [英] How list all tables with data changes in the last 24 hours?

问题描述

我们遇到了一个丑陋的问题，错误的是，一个平衡器将一些请求重定向到一个与生产数据非常相似的测试实例，现在我知道测试Postgres中记录的数据属于生产

We had an ugly problem, by mistake, a balancer redirect some requests to a test instance with pretty similar data than production, now I know that there are data recorded in the test Postgres that belongs to production

在Postgres中是否可以列出最近24小时内所有具有数据更改的表？

Is there a way to list all the tables with data changes in the last 24 hours in Postgres?

Postgres版本是9.3，我大约有250张桌子。

Postgres version is 9.3 and I have around 250 tables.

推荐答案

首先考虑我的评论。

9.4及以下的Postgres本身不会记录插入或更新行时的时间戳。

Postgres up to and including 9.4 does not by itself record timestamps when rows were inserted or updated.

行标题中有一些系统列可以帮助进行取证工作。如果此后表没有其他任何变化，则行的物理顺序（ ctid ）可以作为指示符。在简单情况下，新行在插入时会附加到表的物理末尾，因此 ctid 指示最后插入的内容-直到表中的任何内容更改为止。 Postgres可以随意重新排列行的物理顺序，例如使用 VACUUM 。任何 UPDATE 还会写入一个新的行版本，该版本可以更改物理位置。新版本不必在表格末尾。 Postgres尝试尽可能在同一数据页面上保留新的行版本（ HOT更新）...

There are some system columns in the row headers that can help in the forensic work. The physical order of rows (ctid) can be an indicator if nothing else has happened to the table since. In simple cases new rows are appended to the physical end of a table when inserted, so the ctid indicates what was inserted last - until anything changes in the table. Postgres is free to rearrange the physical order of rows at will, for instance with VACUUM. Any UPDATE also writes a new row version, which can change the physical position. The new version does not have to be at the end of the table. Postgres tries to keep new row version on the same data page if possible (HOT update) ...

这是一个简单的查询，用于以物理方式获取给定表的最后一行：

That said, here is a simple query to get the physically last rows for a given table:

SELECT ctid, *
FROM   big
ORDER  BY ctid DESC
LIMIT  10;

有关dba.SE的相关答案，并提供详细信息：

Related answers on dba.SE with detailed information:

• VACUUM将磁盘空间返回给操作系统


• 如何将ctid分解为页码和行号？

• VACUUM returning disk space to operating system
• How do I decompose ctid into page and row numbers?

插入交易ID xmin 可能有用：

The insert transaction id xmin can be useful:

• 如何确定何时将数据插入Postgres？

• How to find out when data was inserted to Postgres?

如果您碰巧从事件发生之前就对测试数据库进行备份，这将对 有所帮助。将旧状态还原到测试数据库的单独架构并比较表...

If you happen to have a backup for the test DB from right before the incident, that would be helpful. Restore the old state to a separate schema of the test DB and compare tables ...

通常，我添加一个或两个 timestamptz 列到重要表中，以了解何时插入行和/或何时更新该行。 将会 现在对您非常有用...

Typically, I add one or two timestamptz columns to important tables for when the row was inserted, and / or when it was updated the last time. That would be tremendously useful for you right now ...

将会 也非常适合您：引入的时间功能SQL标准与SQL：2011 。但这尚未在Postgres中实现。

Postgres Wiki中有一个页面。

PGXN上还有一个非正式的扩展名。我还没有测试它，所以不能说它有多远。

What would also be great for you: the "temporal" features introduced in the SQL standard with SQL:2011. But that's not implemented in Postgres, yet.
There's a page in the Postgres Wiki.
There is also an unofficial extension on PGXN. I have not tested it and can't say how far it is.

Postgres 9.5 引入了一项功能，用于记录提交时间戳（例如 @ Craig评论了）。开始录制之前需要手动启用。
手册：

Postgres 9.5 introduces a feature to record commit timestamps (like @Craig commented). Needs to be enabled manually before it starts recording. The manual:

track_commit_timestamp （ bool ）

记录事务的提交时间。该参数只能在
postgresql.conf 文件或服务器命令行中设置。默认值
是 off 。

Record commit time of transactions. This parameter can only be set in postgresql.conf file or on the server command line. The default value is off.

以及一些与之配合使用的功能。

这篇关于如何列出最近24小时内数据变化的所有表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！