pageinspect 是一个 contrib 模块，它提供了一系列函数，能够直接检查数据库页面，包括关于不同类型索引的信息。
pageinspect 添加于 PostgreSQL 8.3。
变更历史
进行中
- PostgreSQL 18 (version 1.13)
- 转换为使用 SQL 标准函数体（commit 68ff25ee）
- PostgreSQL 16 (version 1.12)
- PostgreSQL 14 (version 1.9)
- PostgreSQL 13 (version 1.8)
- PostgreSQL 10
- PostgreSQL 9.5 (version 1.3)
- PostgreSQL 9.4 (version 1.2)
- PostgreSQL 9.0
- PostgreSQL 8.4
- PostgreSQL 8.3
- 添加，并合并了之前部分属于
pgstattuple的函数（commit 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 documentation: pageinspect