理解postgresql数据表与其对应的数据文件关系

时间:2022-06-01 12:37:16

方法一:通过查找表数据文件方式

     这种方法通过查找表的数据文件的方式从而确定表的创建时间,但是这种方法并不能准备查询表的创建
时间,而且有时候,这种方法得到的信息还有可能是错误的,下面大致演示下。

--1.1 创建表并插入数据

 

 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 定位表所在的表空间

 

 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 的默认表空间

 

 francs=> select oid,datname,dattablespace from pg_database where datname='francs';
  oid  | datname | dattablespace 
-------+---------+---------------
 16386 | francs  |         16385

   
 
  备注:上面查出数据库 francs 的默认表空间的 oid 为 16385。
 
 
--1.4 查找 oid 为 16385 的表空间

 

 

  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 对应的物理目录

 

 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 进入数据目录

 

 [postgres@redhat6 16386]$ cd /database/1922/pgdata1/pg_tbs/tbs_francs
[postgres@redhat6 tbs_francs]$ ll
total 4.0K
drwx------. 4 postgres postgres 4.0K 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 16K
drwx------. 2 postgres postgres  12K Jun 26 19:03 16386
drwx------. 2 postgres postgres 4.0K May 22 10:37 pgsql_tmp

   

 

 备注:根据前面的步骤 1.3  查询的信息知道 16386 为数据库 francs 的 oid。 再根据步骤 1.2  的信息知道
           表 test_ctime 的 relfilenode 值为 24650
 
--1.7 查找表 test_ctime 的数据文件

 

 [postgres@redhat6 16386]$ ll 24650
-rw-------. 1 postgres postgres 512K Jun 26 18:57 24650

   

 

    备注:根据数据文件 24650 知道表的创建时间为 2012-06-26 18:57。但这种方法并不准确,因为
              表上的操作可能导致表重新生成文件,接着演示。
         

--1.8 cluster 表

 

 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.00s/0.03u 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 系统上再次查询表数据文件

 

 [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 表

 

 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 系统上再次查询表数据文件

 

 [postgres@redhat6 16386]$ ll 24659
-rw-------. 1 postgres postgres 512K Jun 26 19:22 24659

   

 

 

方法二:通过查询数据库日志方式

  这里配置 postgresql.conf  配置文件,通过记录表的 DDL 信息,从而确定表的创建时间。
  
--2.1 配置 postgresql.conf

 

 log_destination = 'csvlog'  
logging_collector = on  
log_directory = '/var/applog/pg_log/1922/pg_log'   
log_rotation_age = 1d 
log_rotation_size = 10MB 
log_statement = 'ddl'                   # none, ddl, mod, all

   

 

 备注:最重要的参数配置 log_statement 值为 DDL,表示记录表上的所有 DDL 操作,其它的参数
           为日志格式的控制参数。
       
--2.2 创建测试表测试

 

 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 数据库日志
2012-06-26 19:31:10.657
 CST,"francs","francs",13753,"[local]",4fe99d61.35b9,2,"idle",2012-06-26 19:30:41CST,3/580,0,LOG,00000,"statement: create table test_ctime_bak as select * From test_ctime;",,,,,,,,,"psql"

    备注:上面的信息有两个时间,第一个为 log_time ,表示当前动作的执行时间,第二个时间为 
               session_start_time  表示会话开始时间,当然也可以将整个 csv 日志导到对应表中。如
               果数据库很多,为了管理方便,通常需要建立监控服务器,通过编写日志搜集脚本将所有
               数据库的日志导到监控数据库中,以便监控。