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

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);

Categories

Contrib module, Data consistency and forensics, PostgreSQL internals, Storage

See also

pg_freespacemap, pgstattuple, pg_visibility