database 如何获取 PostgreSQL 数据库的最后访问/修改日期？

How to get last access/modification date of a PostgreSQL database?

提问by Jakub Zalas

On development server I'd like to remove unused databases. To realize that I need to know if database is still used by someone or not.

在开发服务器上，我想删除未使用的数据库。要意识到我需要知道数据库是否仍然被某人使用。

Is there a way to get last access or modification date of given database, schema or table?

有没有办法获取给定数据库、模式或表的最后访问或修改日期？

采纳答案by tinychen

You can do it via checking last modification time of table's file. In postgresql,every table correspond one or more os files,like this:

您可以通过检查表文件的最后修改时间来完成。在 postgresql 中，每个表对应一个或多个 os 文件，如下所示：

select relfilenode from pg_class where relname = 'test';

the relfilenode is the file name of table "test".Then you could find the file in the database's directory.

relfilenode 是表“test”的文件名。然后你可以在数据库的目录中找到该文件。

in my test environment:

在我的测试环境中：

cd /data/pgdata/base/18976

ls -l -t | head

the last command means listing all files ordered by last modification time.

last 命令表示列出按上次修改时间排序的所有文件。

回答by Craig Ringer

There is no built-in way to do this - and all the approaches that check the file mtime described in other answers here are wrong. The only reliable option is to add triggers to every table that record a change to a single change-history table, which is horribly inefficient and can't be done retroactively.

没有内置的方法可以做到这一点 -此处其他答案中描述的检查文件 mtime 的所有方法都是错误的。唯一可靠的选择是向每个记录更改历史记录表的表添加触发器，这是非常低效的，并且无法追溯。

If you only care about "database used" vs "database not used" you can potentially collect this information from the CSV-format database log files. Detecting "modified" vs "not modified" is a lot harder; consider SELECT writes_to_some_table(...).

如果您只关心“使用的数据库”与“未使用的数据库”，您可能会从 CSV 格式的数据库日志文件中收集此信息。检测“修改”与“未修改”要困难得多；考虑SELECT writes_to_some_table(...)。

If you don't need to detect oldactivity, you can use pg_stat_database, which records activity since the last stats reset. e.g.:

如果您不需要检测旧活动，则可以使用pg_stat_database，它记录自上次统计信息重置以来的活动。例如：

-[ RECORD 6 ]--+------------------------------
datid          | 51160
datname        | regress
numbackends    | 0
xact_commit    | 54224
xact_rollback  | 157
blks_read      | 2591
blks_hit       | 1592931
tup_returned   | 26658392
tup_fetched    | 327541
tup_inserted   | 1664
tup_updated    | 1371
tup_deleted    | 246
conflicts      | 0
temp_files     | 0
temp_bytes     | 0
deadlocks      | 0
blk_read_time  | 0
blk_write_time | 0
stats_reset    | 2013-12-13 18:51:26.650521+08

so I can see that there hasbeen activity on this DB since the last stats reset. However, I don't know anything about what happened before the stats reset, so if I had a DB showing zero activity since a stats reset half an hour ago, I'd know nothing useful.

这样我就可以看到，已经从上次统计复位一直在这个数据库活动。但是，我对统计信息重置之前发生的事情一无所知，因此，如果自半小时前统计信息重置以来我的数据库显示为零活动，我将一无所知。

回答by Thirumal

PostgreSQL 9.5 let us to track last modified commit.

PostgreSQL 9.5 让我们可以跟踪上次修改的提交。

1. Check track commit is on or off using the following query

   show track_commit_timestamp;
   

2. If it return "ON" go to step 3 else modify postgresql.conf

   cd /etc/postgresql/9.5/main/
   vi postgresql.conf
   

   Change

   track_commit_timestamp = off
   

   to

   track_commit_timestamp = on
   

   Restart the postgres / system

   Repeat step 1.

3. Use the following query to track last commit

   SELECT pg_xact_commit_timestamp(xmin), * FROM  YOUR_TABLE_NAME;
   
   SELECT pg_xact_commit_timestamp(xmin), * FROM YOUR_TABLE_NAME where COLUMN_NAME=VALUE;
   

1. 使用以下查询检查轨道提交是打开还是关闭

   show track_commit_timestamp;
   

2. 如果它返回“ON”，请转到第 3 步，否则修改 postgresql.conf

   cd /etc/postgresql/9.5/main/
   vi postgresql.conf
   

   改变

   track_commit_timestamp = off
   

   到

   track_commit_timestamp = on
   

   重启postgres/系统

   重复步骤 1。

3. 使用以下查询来跟踪上次提交

   SELECT pg_xact_commit_timestamp(xmin), * FROM  YOUR_TABLE_NAME;
   
   SELECT pg_xact_commit_timestamp(xmin), * FROM YOUR_TABLE_NAME where COLUMN_NAME=VALUE;
   

回答by Falco

My way to get the modification date of my tables:

我获取表格修改日期的方法：

Python Function

Python 函数

CREATE OR REPLACE FUNCTION py_get_file_modification_timestamp(afilename text)
  RETURNS timestamp without time zone AS
$BODY$
    import os
    import datetime
    return datetime.datetime.fromtimestamp(os.path.getmtime(afilename))
$BODY$
  LANGUAGE plpythonu VOLATILE
  COST 100;

SQL Query

SQL查询

SELECT
    schemaname,
    tablename,
    py_get_file_modification_timestamp('*postgresql_data_dir*/*tablespace_folder*/'||relfilenode)
FROM
    pg_class
INNER JOIN
    pg_catalog.pg_tables ON (tablename = relname)
WHERE
    schemaname = 'public'

I'm not sure if things like vacuum can mess this aproach, but in my tests it's a pretty acurrate way to get tables that are no longer used, at least, on INSERT/UPDATE operations.

我不确定像真空这样的东西是否会破坏这种方法，但在我的测试中，这是一种非常准确的方法来获取不再使用的表，至少在 INSERT/UPDATE 操作中是这样。

回答by pcent

I guess you should activate some log options. You can get information about logging on postgreSQL here.

我想你应该激活一些日志选项。您可以在此处获取有关登录 postgreSQL 的信息。