Dumping individual pages

Advanced users who really want to inspect their broken systems in detail can focus their attention on pageinspect. It allows them to inspect a single data or index page on the disk. This requires a fair amount of knowledge about the inner working of PostgreSQL, but it can give valuable insights into what might have gone wrong in a system.

To make use of the module, first it has to be installed. It works like this:

test=# CREATE EXTENSION pageinspect;
CREATE EXTENSION

Once the module is in place, a table can be inspected. To do so, a couple of functions are available. The most important function is get_raw_page. It will be needed for subsequent inspections:

get_raw_page(relname text, fork text, blkno int) 
  returns bytea

The get_raw_page function returns a bytea field containing the content of the page. The first parameter tells us the relation name we want to inspect. The second parameter needs the so-called relation fork. In PostgreSQL, a table does not only consist of data files. In all, there are three types of files belonging to a table: main (the table itself), fsm (information about free space in the so-called Free Space Map), and vm (the so-called Visibility Map). Depending on the branch of the table you want to inspect, this second parameter has to be set to the proper value. Finally, there is the last parameter. It tells PostgreSQL which block to dump.

If you want to extract the first page of pg_class (a system table), try the following:

test=# SELECT * FROM get_raw_page('pg_class', 
    'main', 0);

Of course, the output of this function is too long to list. It is an 8k block after all!

Extracting the page header

Once the page has been extracted, the page header can be inspected. To do so, just call page_header:

test=# x
Expanded display (expanded) is on.
test=# SELECT * FROM page_header(get_raw_page('pg_class', 'main', 0));
-[ RECORD 1 ]--------
 lsn       | 0/E6604D8 
 checksum  | 0         
 flags     | 1         
 lower     | 340       
 upper     | 904       
 special   | 8192      
 pagesize  | 8192      
 version   | 4         
 prune_xid | 0

What is the meaning of those fields? The following listing contains an overview:

  • pd_lsn: This identifies xlog record for the last change to this page
  • pd_checksum: This is the page checksum, if set
  • pd_flags: This is a set of flag bits
  • pd_lower: This is an offset to start of free space
  • pd_upper: This is an offset to end of free space
  • pd_special: This is an offset to start of a special space
  • pd_pagesize_version: This gives the size in bytes and the page layout version number
  • pd_prune_xid: This is the oldest XID among potentially prunable tuples on the page

Note

Keep in mind that not all of this information is there for every type of object; for example pg_special does not give anything useful for a normal data file because there is no special space there.

If those numbers show something odd, it is a good indicator that something has gone wrong.

In the next step, individual tuples (rows) can be inspected. To do this, heap_page_items is available:

test=# SELECT * 
  FROM heap_page_items(  
     get_raw_page('pg_class', 'main', 0)
       );
-[ RECORD1 ]------------------------------------
 lp          | 1   
 lp_off      | 7992 
 lp_flags    | 1     
 lp_len      | 197    
 t_xmin      | 931     
 t_xmax      | 0         
 t_field3    | 5            
 t_ctid      | (0,1)           
 t_infomask2 | 29                   
 t_infomask  | 11019                      
 t_hoff      | 32                                  
 t_bits      | 11111 … 0000 
 t_oid       | 2619

In this example, a couple of rows are returned.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.129.194.123