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

Write-Ahead Logging (WAL) is a standard method to ensure data integrity.

Write-Ahead Logging (WAL) is a standard method for ensuring data integrity. A detailed description can be found in most (if not all) books about transaction processing. Briefly, WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage. If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the log records. This is roll-forward recovery, also known as REDO.

Note: in PG, in PostgreSQL, WAL is an abbreviation for Write Ahead log (pre-write log), can be used as a synonym for transaction log, and is used to refer to the implementation mechanism associated with writing operations to the transaction log (WAL).

But here, for the sake of distinction, WAL generally refers to the mechanism for implementing WAL (Write-Ahead Logging) in PostgreSQL, which is a broad concept, not just transaction log (Write Ahead log).

I. terminology

Redo Log

Redo Log is often referred to as a redo log, and its purpose and significance are:

1.Redo Log saves all modification history of the database

2.Redo Log Files for recovery / incremental backup and PITR (Point In Time Recovery) / replication

3. Changes to each database are written to Redo Log File before being written to the data file

WAL segment file

Persist the Redo Log file on the storage device, and each WAL segment file size in PG is 16MB (default).

File name rules:

WAL segment file name=timelineId+ (uint32) (LSN − 1) / (16m ∗ 256m) + (uint32) ((LSN − 1) / 16m)% 256

XLOG Record (WAL data)

In PG, it is used to store historical modifications, which can be considered as Redo log.

WAL buffer

XLOG Record buffer, Redo Log is written to the buffer first, and then written to WAL segment file through WAL writer when appropriate.

LSN (Log Sequence Number)

The LSN in XLOG record indicates that the record is written to a location in the transaction log with a size of uint64.

In XLOG Record, LSN is unique.

Checkpointer

Checkpoint background process, execute checkpoint.

Redo point

The starting point of PostgreSQL when executing Crash Recover.

When the checkpointer background process starts, the Redo point is stored in memory.

During the PG run, the Redo point is adjusted to point to the write location of the XLOG Record in the WAL segment file when the latest checkpoint is started.

Checkpoint record

When executing checkpoint, the checkpoint-related XLOG Record is first written in the WAL buffer, which contains Redo point, and this type of XLOG Record is called checkpoint record.

Pg_control

Pg_control is a physical file that holds the basic information of checkpoints and is used in database recovery.

You can view the information about the file by using the command pg_controldata.

II. Reference materials

Write-Ahead Logging (WAL)

Separating bgwriter and checkpointer

Basics of Tuning Checkpoints

Write Ahead Logging-WAL

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.