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

This article introduces the relevant knowledge of "WAL file structure analysis in PostgreSQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

WAL segment file

The WAL segment file file is 24 in length and consists of 3 parts, each with 8 hexadecimal digits:

1. Part 1 is TimeLineID,0x00000000-> 0xFFFFFFFF

two。 Part 2 is the logical file ID,0x00000000-> 0xFFFFFFFF

3. The third part is the physical file ID,0x00000000-> 0x000000FF

Logical file ID occupies 32bit, physical file ID occupies 8 bit and 16m file occupies 24bit. Through the combination of these three parts, the total 64bit.PG achieves the maximum file addressing space of 64bit.

File storage directory

Transaction log files are stored in the $PGDATA/pg_wal directory

II. LSN

The LSN in LSN:Logical Sequence Number,XLOG record indicates that the record is written to a location in the transaction log with a size of uint64. In PG, it can be understood as the Offset in the transaction log file.

LSN consists of three parts: logical file ID, physical file ID and intra-file offset. For example, LSN number 1/4288E228, where 1 is the logical file ID,42 is the physical file ID,88E228 is the offset within the WAL segment file file.

According to this rule, given a LSN, it is easy to get its corresponding log file (assuming the timeline is 1):

LSN-1/4288E228-- > WAL segment file:00000001 00000001 00000042

PG also provides the corresponding function to obtain the log file name based on LSN:

Testdb=# SELECT pg_walfile_name ('1WAL 4288E228'); pg_walfile_name-00000001000000000042 (1 row) "WAL file structure analysis in PostgreSQL". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.