PostgreSQL 从文件时间戳获悉一些信息(如数据库创建时间)-阿里云开发者社区

开发者社区> 德哥> 正文

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

简介:
+关注继续查看

标签

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


背景

PG的一些元数据没有时间字段,例如对象的创建时间、DDL的修改时间。

如果要获得这个时间信息,我们可以

1、通过事件触发器,将DDL事件记录到某个元数据表中,来获得。

2、或者打开DDL审计日志,从审计日志中获得。

3、与事件触发器类似,不过是通过PG的HOOK来实现的,在执行DDL时,通过代码中对应的HOOK来跟踪记录DDL的时间。

还有没有其他方法呢?

人民群众的智慧是无限的,我收集了一个方法,来自PGer(智宏):

通过查看文件的创建时间,获得一些信息

例如数据库的创建时间。

在创建数据库时,会在“表空间/数据库”或“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)  

背景知识

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://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》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PostgreSQL pg_rewind,时间线修复,脑裂修复,flashback - 从库开启读写后,回退为只读从库。异步主从发生角色切换后,主库rewind为新主库的从库
PostgreSQL pg_rewind,时间线修复,脑裂修复,flashback - 从库开启读写后,回退为只读从库。异步主从发生角色切换后,主库rewind为新主库的从库
670 0
获取数据库服务器日期、时间
一、获取数据库服务器日期(格式:yyyymmdd) sql server版本 string vsSql = "select convert(varchar(10),getdate(),112) as snTime"; oracle 版本 string vsSql = "select to_char(sysdate,'yyyymmdd') as snDate from d
1442 0
PostgreSQL 使用逻辑decode实现异步主从切换后,时间线分歧变化量补齐、修复
PostgreSQL 使用逻辑decode实现异步主从切换后,时间线分歧变化量补齐、修复
406 0
数据库5.7 日期有时间类型
简介 日期与时间类型 mysql中有多种表示日期的数据类型,主要有:DATETIME DATE TIMESTAMP,DATE .每一个类型都有合法的取值范围,当指定确实不合法的值时系统将“零”值插入到数据库中。
585 0
ApiPost(中文版postman)如何发送一个随机数或者时间戳?
当我们的请求参数是一个时间戳或者随机数时,我们可以用ApiPost的内建变量
2239 0
创建门店信息(alipay.offline.market.shop.create)java版
准入条件:  1.经营范围:证照内的经营范围需与商户实际经营内容、口碑平台展示的门店类目/经营内容相符。 2.证照主体:开店提供的证照主体需与支付宝签约当面付的认证主体保持一致; 3.执照提交要求:证照需彩色版;真实、完整、清晰无水印、无PS(扫描或拍照均可);复印件需加盖红色公章。
518 0
数据库存储时间的时区问题
先说一下mysql中DATETIME和TIMESTAMP的区别 TIMESTAMP是标准的unix timestamp,它存储的是1970-1-1到现在经过的秒数,4字节存储。mysql用这个类型还蛮方便的,一个是有很多内置的函数和trigger来处理它,比如CURRENT_TIMESTAMP宏,最关键的是在取数据的时候mysql会自动帮你处理DST和时区的问题。 DATETIME的范围更
1827 0
PostgreSQL · 特性分析 · 统计信息计算方法
一条SQL在PG中的执行过程是: ----> SQL输入 ----> 解析SQL,获取解析后的语法树 ----> 分析、重写语法树,获取查询树 ----> 根据重写、分析后的查询树计算各路径代价,从而选择一条成本最优的执行树 ----> 根据执行树进行执行 ----> 获取结果并返回
1577 0
linux创建随机数并写文件处理
一、案例代码 /**************************************************************** * Name   : rondom_create_score.
775 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载