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. Some functions were previously part of pgstattuple.
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
Time: 11.628 ms
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)
Time: 0.530 ms
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