PostgreSQL 从文件时间戳获悉一些信息(如数据库创建时间)

目录例子：

/data01/pg/pg_root4000/pg_tblspc/28704/PG_11_201804061/28705  
  
/data01/pg/pg_root4000/base/13220  

使用SQL查询所有数据库的创建时间，取modification。

select   
datname,  
(pg_stat_file(format(‘%s/%s/PG_VERSION‘,  
case   
  when spcname=‘pg_default‘ then ‘base‘   
  else ‘pg_tblspc/‘||t2.oid||‘/PG_11_201804061/‘    
end,  
t1.oid))).*    
from   
pg_database t1,    
pg_tablespace t2   
where t1.dattablespace=t2.oid;   
  
  datname  | size |         access         |      modification      |         change         | creation | isdir   
-----------+------+------------------------+------------------------+------------------------+----------+-------  
 postgres  |    3 | 2018-04-07 17:07:05+08 | 2018-04-07 17:07:05+08 | 2018-04-07 17:07:05+08 |          | f  
 test      |    3 | 2018-04-13 16:30:08+08 | 2018-04-13 16:30:08+08 | 2018-04-13 16:30:08+08 |          | f  
 template1 |    3 | 2018-04-07 17:07:02+08 | 2018-04-07 17:07:02+08 | 2018-04-07 17:07:02+08 |          | f  
 template0 |    3 | 2018-04-07 17:07:05+08 | 2018-04-07 17:07:05+08 | 2018-04-07 17:07:05+08 |          | f  
(4 rows)  

背景知识

Linux offers three timestamps for files:

time of last access of contents (atime),
time of last modification of contents (mtime),
and time of last modification of the inode (metadata, ctime). 当文件大小发生变化时，会修改INODE导致ctime变化

The directory‘s mtime corresponds to the last file creation or deletion that happened, though.

The stat command may output this - (dash).
I guess it depends on the filesystem you are using.
stat calls it the "Birth time". On my ext4 fs it is empty, though.

%w Time of file birth, human-readable; - if unknown  
  
%W Time of file birth, seconds since Epoch; 0 if unknown  
  
stat foo.txt  
  File: `foo.txt‘  
  Size: 239             Blocks: 8          IO Block: 4096   regular file  
Device: 900h/2304d      Inode: 121037111   Links: 1  
Access: (0644/-rw-r--r--)  Uid: ( 1000/  adrian)   Gid: (  100/   users)  
Access: 2011-10-26 13:57:15.000000000 -0600  
Modify: 2011-10-26 13:57:15.000000000 -0600  
Change: 2011-10-26 13:57:15.000000000 -0600  
 Birth: -  

但是，它对表的创建时间是无法准确获得的，原因如下：

1、access time，mount atime=off 的话，不记录atime

2、注意表在rewrite(例如truncate, 导致rewrite的alter)后，会产生新的filenode，所以即使access time不变，也不完全准确。

3、touch文件，会改变access time

PostgreSQL 从文件时间戳获悉一些信息(如数据库创建时间)

标签：may   user   space   alt   时间   bsp   ruby   空间   使用