postgresql的数据文件_pg数据文件

PG的表文件在哪里

创建一个表

postgres=# create table t1(id int,info text);
CREATE TABLE
postgres=# insert into t1 values(1,'hello');
INSERT 0 1
1
2
3
4

获取该表的oid

postgres=# select relfilenode from pg_class where relname='t1';
 relfilenode 
-------------
       16387
(1 row)
1
2
3
4
5

获取该表的文件路径

postgres=# select pg_relation_filepath('t1');
 pg_relation_filepath 
----------------------
 base/13593/16387
(1 row)
1
2
3
4
5

可以看到文件路径中的13593指的是表空间

postgres=# select oid,datname from pg_database;       
  oid  |  datname  
-------+-----------
 13593 | postgres
     1 | template1
 13592 | template0
(3 rows)
1
2
3
4
5
6
7

根据路径进入

[postgres@hecs-161929 13593]$ ll 16387*
-rw------- 1 postgres dba     0 Feb 23 15:47 16387
-rw------- 1 postgres dba 16384 Feb 23 15:49 16387_fsm
-rw------- 1 postgres dba     0 Feb 23 15:47 16387_vm
[postgres@hecs-161929 13593]$ pwd
/opt/pgdata/base/13593
1
2
3
4
5
6

可以通过oid2name的方式查找对应的表名

[postgres@hecs-161929 13593]$ oid2name -d postgres -o 16387
From database "postgres":
  Filenode  Table Name
----------------------
     16387          t1
1
2
3
4
5

查看数据文件内容

[postgres@hecs-161929 13593]$ hexdump -C 16387
00000000  00 00 00 00 98 d6 6b 01  00 00 00 00 1c 00 d8 1f  |......k.........|
00000010  00 20 04 20 00 00 00 00  d8 9f 44 00 00 00 00 00  |. . ......D.....|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00001fd0  00 00 00 00 00 00 00 00  f1 01 00 00 00 00 00 00  |................|
00001fe0  00 00 00 00 00 00 00 00  01 00 02 00 02 08 18 00  |................|
00001ff0  01 00 00 00 0d 68 65 6c  6c 6f 00 00 00 00 00 00  |.....hello......|
00002000
1
2
3
4
5
6
7
8
9

查看可见性

如果数据被删除了，在物理文件是没有被清除的。只有当进行vacuum后才可见。

create extension pg_visibility;   
\dx+ pg_visibility;
postgres=# select * from pg_visibility_map('t1'::regclass);
 blkno | all_visible | all_frozen 
-------+-------------+------------
     0 | t           | f
(1 row)

postgres=# delete from t1;
DELETE 1
postgres=# select * from pg_visibility_map('t1'::regclass);
 blkno | all_visible | all_frozen 
-------+-------------+------------
     0 | f           | f
(1 row)
postgres=# vacuum t1;
VACUUM
postgres=# select * from pg_visibility_map('t1'::regclass);
 blkno | all_visible | all_frozen 
-------+-------------+------------
     0 | t           | f
(1 row)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

vacuum清理死元组

查看具体的表行存储

create extension pageinspect;   
postgres=# select * from heap_page_items(get_raw_page('t1',0)); 
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |         t_data         
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------
  1 |   8152 |        1 |     34 |    497 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | \x010000000d68656c6c6f
  2 |   8112 |        1 |     34 |    498 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x020000000d776f726c64
(2 rows)
1
2
3
4
5
6
7

当对表进行删除操作时，t_data 行的数据并没有删除

postgres=# select * from heap_page_items(get_raw_page('t1',0)); 
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |         t_data         
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------
  1 |   8152 |        1 |     34 |    497 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | \x010000000d68656c6c6f
  2 |   8112 |        1 |     34 |    498 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x020000000d776f726c64
(2 rows)

postgres=# delete t1 where id =1;
ERROR:  syntax error at or near "t1"
LINE 1: delete t1 where id =1;
               ^
postgres=# delete from t1 where id =1;
DELETE 1
postgres=# select * from heap_page_items(get_raw_page('t1',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |         t_data         
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------
  1 |   8152 |        1 |     34 |    497 |    499 |        0 | (0,1)  |        8194 |        258 |     24 |        |       | \x010000000d68656c6c6f
  2 |   8112 |        1 |     34 |    498 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x020000000d776f726c64
(2 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

如果进行vacuum ，才会清理掉底层的存储。

postgres=# vacuum t1;
VACUUM
postgres=# select * from heap_page_items(get_raw_page('t1',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |         t_data         
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------
  1 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       | 
  2 |   8152 |        1 |     34 |    498 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x020000000d776f726c64
(2 rows)
1
2
3
4
5
6
7
8