Contents
pageinspect
A contrib module enabling low-level inspection of database pages
pageinspect
is a contrib module providing functions enabling direct inspection of database pages, including information about different kinds of index.
pageinspect
was added in PostgreSQL 8.3.
Change history
Work-in-progress
- PostgreSQL 16 (version 1.12)
- function
bt_multi_page_stats()
added (commit 1fd3dd20)
- function
- 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
- added, incorporating some functions previously part of
pgstattuple
(commit 64058429)
- added, incorporating some functions previously part of
Examples
General usage:
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)
Dump a page from a relation (using psql
):
\copy (SELECT get_raw_page(oid::regclass::text, 12345) FROM pg_class WHERE relfilenode = '678910') to '/tmp/raw.data' (FORMAT binary);
References
- PostgreSQL documentation: pageinspect