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

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?

是否有方法获得给定数据库、模式或表的最后访问或修改日期?

5 个解决方案

#1

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.

最后一个命令意味着列出最后修改时间所订购的所有文件。

#2

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格式的数据库日志文件中收集这些信息。检测“修改”vs“未修改”要困难得多;考虑选择writes_to_some_table(……)。

If you don't need to detect old activity, 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 has been 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.

所以我可以看到自从上次的状态重置以来，这个DB上一直有活动。但是，我不知道统计重置之前发生了什么，所以如果我有一个DB显示自半个小时前统计重置以来没有任何活动，我就不知道什么有用的东西了。

#3

PostgreSQL 9.5 let us to track last modified commit.

PostgreSQL 9.5让我们跟踪最后修改的提交。

Check track commit is on or off using the following query

使用以下查询检查跟踪提交是否打开或关闭
```
show track_commit_timestamp;
```
If it return "ON" go to step 3 else modify postgresql.conf

如果返回“ON”，转到步骤3，修改postgresql.conf
```
cd /etc/postgresql/9.5/main/
vi postgresql.conf
```
Change

改变
```
track_commit_timestamp = off
```
to

来
```
track_commit_timestamp = on
```
Reboot the system

重新启动系统

Repeat step 1.

重复步骤1。

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;

#4

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

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

#5

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.

我不确定诸如吸尘器之类的东西是否会打乱这个进程，但在我的测试中，它是一种非常快速的方法，可以获得不再使用的表，至少在插入/更新操作中是如此。

#1