开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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》

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

相关文章
RDS数据库和ECS自建数据库主从复制,数据同步
设置阿里云RDS和ECS上自建的数据库达成主从分离。
17177 0
MSSQL-最佳实践-实例级别数据库上云RDS SQL Server
--- title: MSSQL-最佳实践-实例级别数据库上云RDS SQL Server author: 风移 --- # 摘要 到目前,我们完成了SQL Server备份还原专题系列八篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案、如何监控备份还原进度、阿里云RDS SQL自动化迁移上云的一种
1598 0
免费享受同城双可用区高可用容错能力!阿里云云数据库RDS新增可用区6月汇总(内含福利)
6月份,阿里云云数据库 MySQL 版,云数据库 PPAS 版,云数据库 SQL Server 版,云数据库 PostgreSQL 版均宣布新增可用区,用户在控制台上按需求创建实例,即可享受同城双可用区高可用容错能力。接下来小编将为大家详细列出新增可用区。
2729 0
MSSQL · 最佳实践 · 实例级别数据库上云RDS SQL Server
摘要 到目前,我们完成了SQL Server备份还原专题系列八篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案、如何监控备份还原进度、阿里云RDS SQL自动化迁移上云的一种解决方案以及上个月分享的RDS SDK实现数据库迁移上阿里云,本期我们分享如何将用户线下或者ECS上自建实例级别数据库一键迁移上阿里云RDS SQL Server。
1675 0
阿里云云数据库RDS秒级监控功能解锁,通宵加班找故障将成为过去式
每一个奋斗在前线的数据库管理员和运维人员似乎运气都不太好,这些人都绝对经历过的诡异事件就是:逢年过节必出故障,明明眼看着要休假了,又接到故障通知,只好通宵加班找问题。没问题的时候可能大家都不会想到你,一出问题就先拿运维试问,于是每逢佳节便出现拜数据库的戏谑图片。
2716 0
重磅干货免费下载!阿里云RDS团队论文被数据库顶会SIGMOD 2018收录
来自阿里云RDS团队的论文“**TcpRT: Instrument and Diagnostic Analysis System for Service Quality of Cloud Databases at Massive Scale in Real-time” (TcpRT:面向大规模海量云数据库的服务质量实时采集与诊断系统)**被数据库顶会SIGMOD 2018收录。
10381 0
参与 API 创新应用大赛,体验RDS CloudDBA数据库性能优化 API
阿里云的RDS数据库,有开发者所需要的一系列的功能,但很多功能很多开发者可能并没有使用过。这里,介绍一个RDS比较有用的功能:CloudDBA数据库性能优化 API。
12948 0
从运维的角度分析使用阿里云数据库RDS的必要性--你不应该在阿里云上使用自建的MySQL/SQL Server/Oracle/PostgreSQL数据库
开宗明义,你不应该在阿里云上使用自建的MySQL or SQL Server数据库,对了,还有Oracle or PostgreSQL数据库。 云数据库 RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务。
4454 0
【元气云妹】数据库RDS的连接方式
如何通过DMS和MySQL-Front客户端连接RDS实例
2164 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
国产化浪潮之上的PostgreSQL-删时间
立即下载
PostgreSQL Greemplum 数据上云
立即下载
PolarDB for PostgreSQL三节点功能介绍
立即下载