经常有人问这么个问题，” 在 PostgreSQL 中如何查找表的创建时间？” ，因为在其它关系型数据库中这个信息很容易得到，例如在 oracel中，可以通过查询 dba_objects 数据字典得到表的创建时间，如下所示。
Oracle 查询表创建时间
1 2 3 4
SQL> select owner ,object_name,CREATED from dba_objects where object_name='TBL_1' ; OWNER OBJECT_NAME CREATED ---------- -------------------- ------------------- SKYAAA TBL_1 2010 -10 -18 08 :24 :40
那么在 PostgreSQL 中如何查找表的创建时间呢？GOOGLE 了不少网页，大致的说法是在 PostgreSQL 中没有系统表存储表的创建时间信息，因此不能直接在 PostgreSQL 库中找到这方面的信息，然而可以通过其它方法实现，我这里总结了两种方法。
方法一: 通过查找表数据文件方式
这种方法通过查找表的数据文件的方式从而确定表的创建时间，但是这种方法并不能准备查询表的创建时间，而且有时候，这种方法得到的信息还有可能是错误的，下面大致演示下。
1.1 创建表并插入数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
francs=> create table test_ctime (id int4 primary key ,name varchar(32 )); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_ctime_pkey" for table "test_ctime" CREATE TABLE francs=> insert into test_ctime select generate_series(1 ,10000 ),'create_time test'; INSERT 0 10000 francs=> /d test_ctime; Table "francs.test_ctime" Column | Type | Modifiers --------+-----------------------+----------- id | integer | not null name | character varying(32 ) | Indexes : "test_ctime_pkey" PRIMARY KEY, btree (id) francs=> /dt+ test_ctime; List of relations Schema | Name | Type | Owner | Size | Description --------+------------+-------+--------+--------+------------- francs | test_ctime | table | francs | 536 kB | (1 row)
备注：表创建好了，接下来演示如何定位表的物理文件。
1.2 定位表所在的表空间
1 2 3 4 5
francs=> select relname,relfilenode,reltablespace from pg_class where relname='test_ctime'; relname | relfilenode | reltablespace ------------+-------------+--------------- test_ctime | 24650 | 0 (1 row)
备注：在 PostgreSQL 的逻辑结构体系中，表位于数据库中，同时表位于表空间上，面表空间对应系统上一个文件目录，每个表由一个或者多个文件组成； 根据上面的结果，表 test_ctime 的 reltablespace 值为 0,表示位于所属数据库的默认表空间，注意 relfilenode 值为 24650。
1.3 查询数据库 francs 的默认表空间
1 2 3 4
francs=> select oid,datname,dattablespace from pg_database where datname='francs'; oid | datname | dattablespace -------+---------+--------------- 16386 | francs | 16385
备注：上面查出数据库 francs 的默认表空间的 oid 为 16385。
1.4 查找 oid 为 16385 的表空间
1 2 3 4 5
francs=> select oid,* from pg_tablespace where oid=16385 ; oid | spcname | spcowner | spcacl | spcoptions -------+------------+----------+-----------------------------------------+------------ 16385 | tbs_francs | 10 | {postgres =C/postgres,francs=C/postgres} | (1 row)
备注：查了半天才查到表 test_ctime 的默认表空间为 tbs_francs，这里之所以饶这么大圈，是为了展示 postgresql 中的一些逻辑结构关系，如果自己对环境比较熟悉，可以直接定位到哪个表空间。
1.5 查询表空间 tbs_francs 对应的物理目录
1 2 3 4 5 6 7 8
francs=> /db List of tablespaces Name | Owner | Location ------------+----------+------------------------------------------ pg_default | postgres | pg_global | postgres | tbs_francs | postgres | /database /1922 /pgdata1/pg_tbs/tbs_francs (3 rows)
备注：表空间 tbs_francs 的数据目录为 /database/1922/pgdata1/pg_tbs/tbs_francs。
1.6 进入数据目录
1 2 3 4 5 6 7 8 9
[postgres@redhat6 16386 ]$ cd /database/1922 /pgdata1/pg_tbs/tbs_francs [postgres@redhat6 tbs_francs]$ ll total 4.0 K drwx------. 4 postgres postgres 4.0 K May 22 10 : 35 PG_9.2_201204301 [postgres@redhat6 tbs_francs]$ cd PG_9.2_201204301 / [postgres@redhat6 PG_9.2_201204301 ]$ ll total 16 K drwx------. 2 postgres postgres 12 K Jun 26 19 : 03 16386 drwx------. 2 postgres postgres 4.0 K May 22 10 : 37 pgsql_tmp
备注：根据前面的步骤1.3查询的信息知道 16386 为数据库 francs 的 oid。 再根据步骤1.2 的信息知道表 test_ctime 的 relfilenode 值为 24650
1.7 查找表 test_ctime 的数据文件
1 2
[postgres@redhat6 16386]$ ll 24650 -rw-------. 1 postgres postgres 512K Jun 26 18:57 24650
备注：根据数据文件 24650 知道表的创建时间为 2012-06-26 18:57。但这种方法并不准确，因为表上的操作可能导致表重新生成文件，接着演示。
1.8 cluster 表
1 2 3 4 5 6 7 8 9 10 11 12
francs=> cluster verbose test_ctime using test_ctime_pkey; INFO: clustering "francs.test_ctime" using index scan on "test_ctime_pkey" INFO: "test_ctime" : found 0 removable, 10000 nonremovable row versions in 64 pages DETAIL: 0 dead row versions cannot be removed yet. CPU 0.00 s/0.03 u sec elapsed 0.08 sec. CLUSTER francs=> select relname,relfilenode,reltablespace from pg_class where relname='test_ctime'; relname | relfilenode | reltablespace ------------+-------------+--------------- test_ctime | 24655 | 0 (1 row)
备注：表 test_ctime 经过 cluster 操作后，重新生成了数据文件，文件号由原来的 24650 变成了 24655
1.9 系统上再次查询表数据文件
1 2 3 4
[postgres@redhat6 16386]$ ll 24650 -rw-------. 1 postgres postgres 0 Jun 26 19:19 24650 [postgres@redhat6 16386]$ ll 24655 -rw-------. 1 postgres postgres 512K Jun 26 19:19 24655
备注：显然新文件的时间 24655 并不是表 test_ctime 的初始创建时间。
1.10 vacuum full 表
1 2 3 4 5 6
francs => vacuum full test_ctime;VACUUM francs => select relname,relfilenode,reltablespace from pg_class where relname ='test_ctime' ; relname | relfilenode | reltablespace ------------+-------------+--------------- test_ctime | 24659 | 0 (1 row)
备注： vacuum full 操作后，同样产生了新文件，新文件号为 24659
1.11 系统上再次查询表数据文件
1 2
[postgres@redhat6 16386]$ ll 24659 -rw-------. 1 postgres postgres 512K Jun 26 19:22 24659
方法二: 通过查询数据库日志方式
这里配置 postgresql.conf 配置文件，通过记录表的 DDL 信息，从而确定表的创建时间。
2.1 配置 postgresql.conf
1 2 3 4 5 6
log_destination = 'csvlog' logging_collector = on log_directory = '/var/applog/pg_log/1922/pg_log' log_rotation_age = 1 d log_rotation_size = 10 MB log_statement = 'ddl' # none, ddl, mod, all
备注：最重要的参数配置 log_statement 值为 DDL，表示记录表上的所有 DDL 操作，其它的参数为日志格式的控制参数。
2.2 创建测试表测试
1 2 3 4 5 6 7
francs=> select now(); now ------------------------------- 2012-06-26 19:31:05.900842+08 (1 row) francs=> create table test_ctime_bak as select * From test_ctime; SELECT 9000
2.3 查看 csv 数据库日志
1
2012 -06 -26 19 :31 :10.657 CST,"francs" ,"francs" ,13753 ,"[local]" ,4 fe99d61.35 b9,2 ,"idle" ,2012 -06 -26 19 :30 :41 CST,3 /580 ,0 ,LOG,00000 ,"statement: create table test_ctime_bak as select * From test_ctime;" ,,,,,,,,,"psql"
备注：上面的信息有两个时间，第一个为 log_time ，表示当前动作的执行时间，第二个时间为 session_start_time 表示会话开始时间，当然也可以将整个 csv 日志导到对应表中。如果数据库很多，为了管理方便，通常需要建立监控服务器，通过编写日志搜集脚本将所有数据库的日志导到监控数据库中，以便监控。
2.4 附: csv 日志表结构
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
CREATE TABLE postgres_log ( log_time timestamp (3 ) with time zone, user_name text , database_name text , process_id integer , connection_from text , session_id text , session_line_num bigint , command_tag text , session_start_time timestamp with time zone, virtual_transaction_id text , transaction_id bigint , error_severity text , sql_state_code text , message text , detail text , hint text , internal_query text , internal_query_pos integer , context text , query text , query_pos integer , location text , application_name text , PRIMARY KEY (session_id, session_line_num) );
总结
根据上面两种方法演示情况来看，方法一由于表上的操作可能产生新的数据文件，所以不太靠谱，而方法二通过数据库日志的方法比较准确。
原创文章，作者：kepupublish，如若转载，请注明出处：https://blog.ytso.com/237875.html
