PostgreSQL获取对象的创建时间

创建测试数据

psql -U postgres -c "create user test with password 'test';"
psql -U postgres -c "create database testdb owner test"
psql -U test -d testdb -c "create schema authorization test;"

psql -U test -d testdb
create table test_t (id int, info text, c_time timestamp);
insert into test_t select generate_series(1,100000),md5(random()::text),clock_timestamp();

获取集群初始化时间

[postgres@centos7 ~]$ psql -U postgres -xc "select version(),to_timestamp (system_identifier >> 32) as clusterinit_timestamp from pg_control_system();"
-[ RECORD 1 ]---------+-------------------------------------------------------------------------------------------------------
version               | PostgreSQL 12.17 on x86_64-pc-linux-gnu,compiled by gcc (GCC)4.8.5 20150623(Red Hat 4.8.5-36),64-bit
clusterinit_timestamp | 2024-01-08 23:00:38+08

[postgres@centos7 ~]$

获取数据库创建时间

使用以下shell脚本

cat > getdb_createtime.sh << "EOF"
echo "===========获取数据库创建时间==========="
data_dir=$(psql -U postgres -d postgres -X -qAt -c "show data_directory" )
db_dirs=$(ls $data_dir/base |grep -v pgsql_tmp)
for db_oid in $db_dirs
do
  db_exists=$(psql -U postgres -d postgres -X -qAt -c "select count(*) from pg_database where oid=$db_oid and datname not in ('postgres','template0','template1')") 
  if [ $db_exists -eq 1 ];then
    db_name=$(psql -U postgres -d postgres -X -qAt -c "select datname from pg_database where oid=$db_oid and datname not in ('postgres','template0','template1')")
    echo $db_name
    /bin/stat -c "%y" `ls $data_dir/base/$db_oid/PG_VERSION`
  fi
done
EOF

执行脚本结果示例

[postgres@centos7 ~]$ sh getdb_createtime.sh 
===========获取数据库创建时间===========
testdb
2025-07-05 12:48:36.166028334 +0800
[postgres@centos7 ~]$

获取用户创建时间

需要修改track_commit_timestamp参数为on

track_commit_timestamp (boolean)

记录事务的提交时间。这个参数只能在postgresql.conf 文件中或在服务器命令行上设置。默认值是off。

[postgres@centos7 ~]$ psql -U postgres -c "select name,setting,unit,context from pg_settings where name ~* 'track_commit_timestamp';"
          name          | setting | unit |  context   
------------------------+---------+------+------------
 track_commit_timestamp | on      |      | postmaster
(1 row)

[postgres@centos7 ~]$ psql -U postgres -c "select rolname, pg_xact_commit_timestamp(xmin) from pg_authid where rolpassword is not null;"
 rolname  |   pg_xact_commit_timestamp    
----------+-------------------------------
 postgres | 
 test     | 2025-07-05 12:48:36.090816+08
(2 rows)

[postgres@centos7 ~]$

获取表的创建时间

log_statement (enum)

控制哪些 SQL 语句被记录。有效值是 none (off)、ddl、mod和 all（所有语句）。ddl记录所有数据定义语句，例如CREATE、ALTER和 DROP语句。mod记录所有ddl语句，外加数据修改语句例如INSERT, UPDATE、DELETE、TRUNCATE, 和COPY FROM。如果PREPARE、EXECUTE和 EXPLAIN ANALYZE包含合适类型的命令，它们也会被记录。对于使用扩展查询协议的客户端，当收到一个执行消息时会产生日志并且会包括绑定参数的值（任何内嵌的单引号会被双写）。默认值是none。只有超级用户可以改变这个设置。

修改日志参数

postgres=# select name,setting,unit,context from pg_settings where name = 'log_statement';
     name      | setting | unit |  context  
---------------+---------+------+-----------
 log_statement | ddl     |      | superuser
(1 row)

创建表测试

[postgres@centos7 ~]$ psql -U test -d testdb
psql (12.17)
Type "help" for help.

testdb=> create table test_t1 (id int,name varchar(100));
CREATE TABLE
testdb=> create index idx_id on test_t1(id);
CREATE INDEX
testdb=> select now();
              now              
-------------------------------
 2025-07-06 13:13:00.921743+08
(1 row)

查看后台日志

2025-07-06 13:12:54.201 CST [27554] LOG:  statement: create table test_t1 (id int,name varchar(100));
2025-07-06 13:12:56.089 CST [27554] LOG:  statement: create index idx_id on test_t1(id);

如上日志只有时间戳和pid，可以修改以下参数让信息更详细一点

postgres=# select name,setting,unit,context from pg_settings where name ~* 'log_line_prefix';
      name       | setting  | unit | context 
-----------------+----------+------+---------
 log_line_prefix | %m [%p]  |      | sighup
(1 row)

postgres=# alter system set log_line_prefix = '%a %m %u@%r/%d[%p] ';
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

postgres=# select name,setting,unit,context from pg_settings where name ~* 'log_line_prefix';
      name       |       setting       | unit | context 
-----------------+---------------------+------+---------
 log_line_prefix | %a %m %u@%r/%d[%p]  |      | sighup
(1 row)

参数说明：

log_line_prefix (string)

这是一个printf风格的字符串，它在每个日志行的开头输出。%字符开始“转义序列”，它将被按照下文描述的替换成状态信息。未识别的转义被忽略。其他字符被直接复制到日志行。某些转义只被会话进程识别并且被主服务器进程等后台进程当作空。通过指定一个在%之后和该选项之前的数字可以让状态信息左对齐或右对齐。负值将导致在右边用空格填充状态信息已达到最小宽度，而正值则在左边填充。填充对于日志文件的人类可读性大有帮助。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。默认值是'%m [%p] '，它记录时间戳和进程ID。

转义	效果
%a	应用名
%m	带毫秒的时间戳
%u	用户名
%r	远程主机名或 IP 地址，以及远程端口
%d	数据库名
%p	进程 ID

继续创建测试表看看效果

testdb=> create table test_t2 (id int,name varchar(100));
CREATE TABLE
testdb=> create index idx_id1 on test_t2(id);
CREATE INDEX
testdb=> select now();
              now              
-------------------------------
 2025-07-06 13:24:07.305996+08
(1 row)

后台日志

psql 2025-07-06 13:26:17.706 CST test@[local]/testdb[27613] LOG:  statement: create table test_t2 (id int,name varchar(100));
psql 2025-07-06 13:26:18.187 CST test@[local]/testdb[27613] LOG:  statement: create index idx_id1 on test_t2(id);

继续使用navicat客户端test用户连接到testdb数据库创建测试数据，后台日志如下

Navicat 2025-07-06 13:34:29.714 CST test@192.168.1.99(49213)/testdb[27638] LOG:  statement: create table test_t3(id int)
Navicat 2025-07-06 13:34:29.717 CST test@192.168.1.99(49213)/testdb[27638] LOG:  statement: create index idx_id3 on test_t3(id)

通过表数据文件的创建时间

PostgreSQL的pg_stat_file()函数可以查看数据文件的access、modification、change时间，但没有文件的创建时间。

可以使用OS命令 stat | grep birth 查看数据文件的创建时间。但需要注意的是：vacuum full、cluster、truncate等命令会重写表，所以会改变数据文件的创建时间。 这种情况可以通过业务方面去判断，比如通过查看同一批同时创建的表的数据文件创建时间来对比。

Linux系统中不同的文件系统使用不同的命令查看文件创建时间

ext4文件系统通过stat可以查看文件的birth时间，如果为空可以通过以下命令

#debugfs -R 'stat filename' DEVICE

#示例
debugfs -R "stat '/data/postgres/13/data/base/13580/41034'" /dev/mapper/vol_slta340ispl_01-volume

如果是xfs文件系统，stat命令查看时birth时间也可能为空，如果为空可以通过以下命令

xfs_db -r -c "inode $(stat -c %i /data/postgres/13/data/base/13580/41034)" -c "p v3.crtime.sec" /dev/mapper/vol_slta340ispl_01-volume

示例：

使用SQL查询表对应的数据文件，参考：PostgreSQL根据表名查询出服务器上的数据文件

#test_t1
file_path         | /postgresql/pgdata/base/24753/24762
tablename         | test_t1

#test_t2
file_path         | /postgresql/pgdata/base/24753/24770
tablename         | test_t2

#test_t3
file_path         | /postgresql/pgdata/base/24753/24774
tablename         | test_t3

直接使用stat命令查看数据文件的时间戳

[postgres@centos7 ~]$ stat -c "%y" /postgresql/pgdata/base/24753/24762
2025-07-06 13:12:54.201376564 +0800
[postgres@centos7 ~]$ 
[postgres@centos7 ~]$ stat -c "%y" /postgresql/pgdata/base/24753/24770
2025-07-06 13:26:17.706407185 +0800
[postgres@centos7 ~]$ 
[postgres@centos7 ~]$ stat -c "%y" /postgresql/pgdata/base/24753/24774
2025-07-06 13:34:29.714425935 +0800
[postgres@centos7 ~]$

参考资料

https://blog.csdn.net/yueludanfeng/article/details/135308524

https://www.modb.pro/issue/27023

https://www.cnblogs.com/shuaixf/archive/2012/09/21/2696822.html

posted @ 2025-07-06 14:01 kahnyao 阅读(96) 评论(0) 收藏举报

刷新页面返回顶部

knlbase