Shulou(Shulou.com)06/01 Report--

This article mainly shows you the "sample analysis of WAL mechanism in POSTGRESQL". The content is simple and easy to understand and clearly organized. I hope it can help you solve your doubts. Let Xiaobian lead you to study and learn the article "Sample analysis of WAL mechanism in POSTGRESQL".

POSTGRESQL does exist as a similar MYSQL BINLOG + UNDO LOG, ORACLE REDO LOG, is POSTGRESQL itself in preventing data loss, backup data, data replication, recovery data after database CRASH and so on.

In other words, if the three major parts of the car, engine, gearbox, chassis, then POSTGRESQL WAL wants to be the chassis of the car, which integrates the safety, performance, stability and other important characteristics of the database.

WAL, if explained in detail, is a history log that records all changes and operations in the database system to ensure that no data is lost due to failures, such as power failures or other server failures that cause server crashes. Because the log contains enough information about each transaction that has been executed, the database server should be able to recover the data in the database by replaying changes and operations in the transaction log.

We will roughly follow the WAL process to go over again (in fact, many databases are like this, MYSQL, SQL SERVER , ORACLE), but the form of generation, file format, and the way to use, etc. are different, but they have always been the goal is not to lose data.

The first thing to confirm is that any data operation is done in memory and has nothing to do with files on disk. To say that the relationship is the persistence of data.

The above rough description of a data write (write contains INSERT ,UPDATE, in fact Delete from a certain point of view is also a data write), that data is written to memory, and WAL LOG is a transaction,(in fact LOG is also a buffer, mainly for performance). Here we ignore LOG BUFFER and directly think that the above operation is a transaction. Then after the data falls into the log, this operation will return to success, followed by the dirty page brush into the disk process, this is actually not anxious,(here not anxious to say wait an hour), after the check point, the data will fall into the data PAGE, the data will persist, roughly such a process.

Then there is a problem here, that is, if the dirty page is not refreshed to DATA PAGE, and the machine is OVER, or OOM, then the next time the machine is started, the data page has no data, and the data is lost? No, because there is WAL, why there is no data on the data page, because there is no check point on the dirty page, and after the system is started, check the check point to find out the position of the check point, and the dirty page operations after this position are stored in WAL, so directly execute WAL after the check point and your data will come back, crash data recovery. You job is done.

This is one of the most important functions of WAL.

We can confirm that the current wal falls into that wal file by issuing the following command

In addition, versions prior to PG 11 store two checkpoint locations, one for the last checkpoint and one for the next checkpoint, while PG 11 records only the record number of the most recent checkpoint.

At the same time, we can get detailed information about the current PG in WAL through pg_controldata command, and even use it well to learn some things about relationship performance. (How to do it, find time to write an article)

DBAs can also manually generate new WAL logs, cut off existing WAL logs, and perform required maintenance.

PG is also similar to other databases, such as MYSQL mysqlbinlog to parse BINLOG files, PG pg_waldump can also translate wal files into human-readable format.

The above is "POSTGRESQL WAL mechanism in the sample analysis" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.