Shulou(Shulou.com)05/31 Report--

This article focuses on "how to install and use the pageinspcet plug-in in PostgreSQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to install and use the pageinspcet plug-in in PostgreSQL.

Storage basis of PG data table

Generally speaking, datasheet data is physically stored on non-volatile storage devices, and PG is no exception. As shown in the following figure, the data in the datasheet is stored in N data files, each consisting of N Page (the default size is 8K, which can be specified during compilation and installation). Page is the minimum access unit of PG.

General data table storage structure

Data Page (Page)

The data page Page consists of a header, an array of data pointers (ItemIdData), available free space (Free Space), actual data (Items), and special space (Special Space).

A, the page header stores metadata information such as LSN number and check bit, and occupies 24Bytes

B. The data pointer array stores the pointer to the actual data. The element ItemId in the array can be understood as the actual start offset of the corresponding data row in Page. The data row pointer ItemID consists of three parts. The first 15 bits are offset within Page, the middle 2 bits are marks, and the last 15 bits are length. For more information, please see Appendix.

C. Free space is unused allocable space. ItemID is allocated from the head of free space, and Item (data row) starts at the tail of free space.

D. the actual data is the row data of the data (the storage format of each data type will be parsed later)

E. the special space is used to store the data used for index access, and the data is different for different access methods

II. Pageinspect plug-in

How to simply, quickly and easily view the contents of Page? Unlike all kinds of dump in Oracle, you can easily use various functions provided by the pageinspect plug-in to view the contents of Page in PG.

Installation

Thanks to the good scalability of PG, the installation is simple:

# cd $PGSRC/contrib/pageinspect

# make

# make install

Easy to use

# psql-d testdb

Testdb#create extension pageinspect;-you need to create Extension for the first time

-- create a test table

Drop table if exists t_new

Create table t_new (id char (4), C1 varchar (20))

Insert into t_new values ('1m')

-- View page header&item

SELECT * FROM page_header (get_raw_page ('tasking newcomers, 0))

Select * from heap_page_items (get_raw_page ('tweeted newcomer no. 0))

Page Header&Item

-- View the raw content in Page

\ x

Select * from get_raw_page ('tasking newcomers, 0)

Page quality content

Inside the tail of Page

At this point, I believe you have a deeper understanding of "how to install and use the pageinspcet plug-in in PostgreSQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.