pageinspect 是一个提供函数的 contrib 模块，这些函数可以启用对数据库页面的直接检查，包括有关不同类型索引的信息。
pageinspect 在 PostgreSQL 8.3 中添加。
更改历史记录
开发中
- PostgreSQL 16 (版本 1.12)
- PostgreSQL 14 (版本 1.9)
- PostgreSQL 13 (版本 1.8)
- PostgreSQL 10
- PostgreSQL 9.5 (版本 1.3)
- PostgreSQL 9.4 (版本 1.2)
- PostgreSQL 9.0
- PostgreSQL 8.4
- PostgreSQL 8.3
- 添加，合并了以前是
pgstattuple部分的一些函数 (提交 64058429)
- 添加，合并了以前是
示例
一般用法
postgres=# CREATE TABLE sometable(
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
val TEXT
);
CREATE TABLE
postgres=# INSERT INTO sometable VALUES(default,'foo');
INSERT 0 1
postgres=# SELECT * FROM page_header(get_raw_page('sometable',0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
0/30833B8 | 0 | 0 | 28 | 8160 | 8192 | 8192 | 4 | 0
(1 row)
postgres=# VACUUM sometable ;
VACUUM
postgres=# SELECT * FROM page_header(get_raw_page('sometable',0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
0/3085510 | 0 | 4 | 28 | 8160 | 8192 | 8192 | 4 | 0
(1 row)
postgres=# UPDATE sometable SET val='bar';
UPDATE 1
postgres=# SELECT * FROM page_header(get_raw_page('sometable',0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
0/308BAA8 | 0 | 0 | 32 | 8128 | 8192 | 8192 | 4 | 627
(1 row)
postgres=# SELECT * FROM heap_page_items(get_raw_page('sometable',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 | 8160 | 1 | 32 | 626 | 627 | 0 | (0,2) | 16386 | 258 | 24 | | | \x0500000009666f6f
2 | 8128 | 1 | 32 | 627 | 0 | 0 | (0,2) | 32770 | 10242 | 24 | | | \x0500000009626172
(2 rows)
postgres=# SELECT ctid, * FROM sometable;
ctid | id | val
-------+----+-----
(0,2) | 5 | bar
(1 row)
postgres=# SELECT tuple_data_split('sometable'::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page('sometable', 0));
tuple_data_split
-------------------------------
{"\\x05000000","\\x09666f6f"}
{"\\x05000000","\\x09626172"}
(2 rows)
postgres=# VACUUM sometable ;
VACUUM
postgres=# SELECT * FROM heap_page_items(get_raw_page('sometable',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 | 2 | 2 | 0 | | | | | | | | | |
2 | 8160 | 1 | 32 | 627 | 0 | 0 | (0,2) | 32770 | 10498 | 24 | | | \x0500000009626172
(2 rows)
postgres=# SELECT * FROM page_header(get_raw_page('sometable',0));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
0/3091F50 | 0 | 4 | 32 | 8160 | 8192 | 8192 | 4 | 0
(1 row)
转储关系中的一个页面（使用 psql)
\copy (SELECT get_raw_page(oid::regclass::text, 12345) FROM pg_class WHERE relfilenode = '678910') to '/tmp/raw.data' (FORMAT binary);
参考
- PostgreSQL 文档： pageinspect