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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

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》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
2天前
|
JavaScript Java 关系型数据库
农业信息|基于SSM+vue的农业信息管理系统的设计与实现(源码+数据库+文档)
农业信息|基于SSM+vue的农业信息管理系统的设计与实现(源码+数据库+文档)
18 0
|
2天前
|
小程序 JavaScript Java
高校宿舍信息|基于Spring Boot的高校宿舍信息管理系统的设计与实现(源码+数据库+文档)
高校宿舍信息|基于Spring Boot的高校宿舍信息管理系统的设计与实现(源码+数据库+文档)
7 0
|
2天前
|
JavaScript Java 关系型数据库
医院门诊互联电子病历|基于SSM+vue的医院门诊互联电子病历管理信息系统的设计与实现(源码+数据库+文档)
医院门诊互联电子病历|基于SSM+vue的医院门诊互联电子病历管理信息系统的设计与实现(源码+数据库+文档)
29 11
|
2天前
|
JavaScript NoSQL Java
毕业就业信息|基于Springboot+vue的毕业就业信息管理系统的设计与实现(源码+数据库+文档)
毕业就业信息|基于Springboot+vue的毕业就业信息管理系统的设计与实现(源码+数据库+文档)
13 0
|
2天前
|
小程序 JavaScript Java
学生公寓电费信息|基于SprinBoot+vue微信小程序的学生公寓电费信息管理系统(源码+数据库+文档)
学生公寓电费信息|基于SprinBoot+vue微信小程序的学生公寓电费信息管理系统(源码+数据库+文档)
9 0
|
2天前
|
JavaScript Java 关系型数据库
校园社团信息|基于SprinBoot+vue的校园社团信息管理系统设计与实现(源码+数据库+文档)
校园社团信息|基于SprinBoot+vue的校园社团信息管理系统设计与实现(源码+数据库+文档)
8 0
|
3天前
|
小程序 JavaScript Java
就业|高校毕业生就业信息小程序|基于微信小程序的高校毕业生就业信息的设计与实现(源码+数据库+文档)
就业|高校毕业生就业信息小程序|基于微信小程序的高校毕业生就业信息的设计与实现(源码+数据库+文档)
11 1
|
3天前
|
小程序 JavaScript Java
学生信息|基于Springboot的学生信息管理系统设计与实现(源码+数据库+文档)
学生信息|基于Springboot的学生信息管理系统设计与实现(源码+数据库+文档)
9 0
|
7天前
|
存储 Oracle 关系型数据库
关系型数据库文件方式存储CONTROL FILE(控制文件)
【5月更文挑战第11天】关系型数据库文件方式存储CONTROL FILE(控制文件)
19 1
|
7天前
|
存储 关系型数据库 数据库
关系型数据库文件方式存储LOG FILE(日志文件)
【5月更文挑战第11天】关系型数据库文件方式存储LOG FILE(日志文件)
62 1

相关产品

  • 云原生数据库 PolarDB