PostgreSQL 從檔案時間戳獲悉一些資訊(如資料庫建立時間)

标簽

PostgreSQL , Linux , stat , file , atime , mtime , ctime

https://github.com/digoal/blog/blob/master/201804/20180413_01.md#%E8%83%8C%E6%99%AF 背景

PG的一些中繼資料沒有時間字段，例如對象的建立時間、DDL的修改時間。

如果要獲得這個時間資訊，我們可以

1、通過事件觸發器，将DDL事件記錄到某個中繼資料表中，來獲得。

2、或者打開DDL審計日志，從審計日志中獲得。

3、與事件觸發器類似，不過是通過PG的HOOK來實作的，在執行DDL時，通過代碼中對應的HOOK來跟蹤記錄DDL的時間。

還有沒有其他方法呢？

人民群衆的智慧是無限的，我收集了一個方法，來自PGer（智宏）：

https://github.com/digoal/blog/blob/master/201804/20180413_01.md#%E9%80%9A%E8%BF%87%E6%9F%A5%E7%9C%8B%E6%96%87%E4%BB%B6%E7%9A%84%E5%88%9B%E5%BB%BA%E6%97%B6%E9%97%B4%E8%8E%B7%E5%BE%97%E4%B8%80%E4%BA%9B%E4%BF%A1%E6%81%AF 通過檢視檔案的建立時間，獲得一些資訊

例如資料庫的建立時間。

在建立資料庫時，會在“表空間/資料庫”或“pg_tblspc/表空間/版本/資料庫”目錄中新增一個PG_VERSION檔案，新增後就不會變化。是以通過檢視這個檔案的建立時間，我們可以知道資料庫的建立時間。

stat PG_VERSION

File: ‘PG_VERSION’  
  Size: 3               Blocks: 8          IO Block: 4096   regular file  
Device: fd11h/64785d    Inode: 1315077     Links: 1  
Access: (0600/-rw-------)  Uid: ( 1000/  digoal)   Gid: ( 1000/  digoal)  
Access: 2018-04-07 17:07:02.431482742 +0800  
Modify: 2018-04-07 17:07:02.431482742 +0800  
Change: 2018-04-07 17:07:02.431482742 +0800  
 Birth: -  
           

目錄例子：

/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)  
           

https://github.com/digoal/blog/blob/master/201804/20180413_01.md#%E8%83%8C%E6%99%AF%E7%9F%A5%E8%AF%86 背景知識

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

https://github.com/digoal/blog/blob/master/201804/20180413_01.md#%E5%8F%82%E8%80%83 參考

https://unix.stackexchange.com/questions/24441/get-file-created-creation-time 《PostgreSQL 事件觸發器應用 - DDL審計記錄 + 異步通知(notify)》 《use event trigger function record user who alter table's SQL》 《PostgreSQL 事件觸發器 - DDL審計 , DDL邏輯複制 , 打造DDL統一管理入》 《PostgreSQL 事件觸發器 - PostgreSQL 9.3 Event Trigger》