PostgreSQL 开启 track_commit_timestamp 记录数据最新更改时间

查看设置是否开启

show track_commit_timestamp;

开启设置

注意：需要重启

ALTER SYSTEM SET track_commit_timestamp = 'on';

查看设置

select * from pg_settings where name = 'track_commit_timestamp';

track_commit_timestamp的context是postmaster，需要重启才能生效。

对于postmaster解释如下：

These settings can only be applied when the server starts, so any change requires restarting the server. Values for these settings are typically stored in the postgresql.conf file, or passed on the command line when starting the server. Of course, settings with any of the lower context types can also be set at server start time.

查看最新变更时间

注意xmin是PostgreSQL的隐藏列。

select pg_xact_commit_timestamp(xmin), xmin from my_table;

输出示例

postgres=# select pg_xact_commit_timestamp(xmin), xmin from my_table;
   pg_xact_commit_timestamp    | xmin
-------------------------------+-------
                               |  8329
                               |  8331
 2025-01-15 14:08:48.491414+08 | 10248
(3 rows)

xmin 与写入时间的映射

SELECT * FROM pg_last_committed_xact();

输出示例

postgres=# SELECT * FROM pg_last_committed_xact();
  xid  |           timestamp           | roident
-------+-------------------------------+---------
 10248 | 2025-01-15 14:08:48.491414+08 |       0
(1 row)

pg_settings 文档

https://www.postgresql.org/docs/current/view-pg-settings.html