PostgreSQL checkpoint Principle

PostgreSQL checkpoint Principle

Let's talk about PostgreSQL's checkpoint principle today. The checkpoint function is available in popular databases. Such as Oracle and MySQL, especially the implementation of the checkpoint function in Oracle. Oracle not only has global checkpoints, but also Incremental checkpoints, that is, the well-known "Incremental checkpoint ". Although the implementation methods of various major databases are different, the main objective is the same, which is to shorten the database recovery time. In fact, PG also has its own checkpoint implementation.

1. PG checkpoint type

Shutdown checkpoint: the checkpoint made when the PG instance is shut down.

Recovery End checkpoint: the checkpoint at the End of the recovery, similar to the shutdown checkpoint, but starts at the End of the WAL restoration.

Immediate checkpoint: not only create a checkpoint, but also do it immediately. These types of requests are usually urgent and need to immediately obtain the Database Consistency status.

Force checkpoint: this operation is performed even if no xlog change occurs. To request such checkpoints, you only need to obtain the latest checkpoint location.

The preceding checkpoints directly affect the creation of checkpoints and the completion time of checkpoints.

Wait checkpoint: the checkpoint will not be done immediately, but will Wait until the checkpoint is completed.

Usually important operations, but not very urgent. You can request such checkpoints. Especially for some DDL operations, the data consistency requirements are higher than the response time.

In addition, there is a kind of checkpoint, which is only used as the logging identifier:

Xlog checkpoint: A New xlog file is generated because of the xlog consumption.

Time checkpoint: caused by time elapse.

Flush checkpoint: It is triggered when all pages are flush, including tables without logging.

PG requests the corresponding checkpoint based on the purpose and time.

2. PG checkpoint Mechanism

The postmaster is responsible for creating the checkpoint process. As a sub-process of postmaster, it is one of several important background processes.

We can see that the postmaster process number is 2694. The checkpoint process is 2696, and its parent process is 2694, that is, postmaster.

After the checkpoint process fails, postmaster will kill all backend processes and restore the background processes one by one, a bit similar to the system after initialization. We can see the importance of this process for data consistency protection.

Because the database system has to achieve one goal: that is, any checkpoint changes have been made and do not need to be recovered from the WAL log. This greatly accelerates the recovery speed after the database system crash.

In the source code, checkpoint-related information is recorded by a struct and stored in the shared memory segment:

It stores the pid of the current checkpoint, the start position of the checkpoint, the completion position of the checkpoint, and the type of the checkpoint. A Checkpoint queue is also maintained. Generally, a checkpoint request only creates a checkpoint location and stores it in the queue. It is not executed immediately. The checkpoint scheduling is controlled by another logic.

The checkpoint position is strongly related to the xlog position, which is actually the position of the WAL log.

Each time the check is complete, you must require that the data changes before the Check Point or dirty pages be written to the physical disk for persistence.

When performing the check, there are roughly two processes:

1) traverse all the buffers and change the block status of all DIRTY to CHECKPOINT_NEEDED to indicate that these DIRTY blocks need to be written to the disk.

Note that this step is still completed in the memory and does not involve disk operations.

2) Click the physical file to sync the dirty block fsync from the cache to the disk.

This step involves disk operations. Write the block marked as CHECKPOINT_NEEDED to the disk.

3). The Checkpoint itself will also be recorded in XLOG.

The content and length of the checkpoint struct described above will be flushed out to xlog.

4). Update the control file

Update the checkpoint information in the control file to the current position. The bold section below shows the checkpoint content:

[S @ db1 ~] $ Pg_controldata/opt/pgdata
Pg_control version number: 937
Catalog version number: 201306121
Database system identifier: 6123041408807693241
Database cluster state: shut down
Pg_control last modified: Sun 17 May 2015 06:36:25 PM CST
Latest checkpoint location: 1F/9B9B2E20
Prior checkpoint location: 1F/9B9B2DB8
Latest checkpoint's REDO location: 1F/9B9B2E20
Latest checkpoint's redo wal file: 00000000000001f0000009b
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/15331854
Latest checkpoint's NextOID: 91378
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1799
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint: Sun 17 May 2015 06:36:25 PM CST
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
Current wal_level setting: minimal
Current max_connections setting: 100
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 64
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relationship: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0

------------------------------------ Lili split line ------------------------------------

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: