PostgreSQL备机是否做checkpoint

背景

我们知道为了缩短崩溃恢复时间，PostgreSQL支持checkpoint功能，即异常重启时，PostgreSQL以最近的checkpoint为起点，不断回放之后的WAL日志。PostgreSQL主机做checkpoint时，产生一条CHECKPOINT WAL日志记录。那么备机会做checkpoint吗？如果可以做checkpoint，它是否也会产生一条CHECKPOINT WAL日志记录并将其写入到WAL文件中？

实验

1、备机的日志位置

[yzs@bogon pg_wal]$ pg_waldump 00000001000000000000000C

...

rmgr: Transaction len (rec/tot):    405/   405, tx:        722, lsn: 0/0C019E18, prev 0/0C019DEC, desc: COMMIT 2019-05-02 00:49:43.519052 PDT; inval msgs: catcache 74 catcache 73 catcache 74 catcache 73 catcache 50 catcache 49 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot 2608 relcache 16447

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C019FB0, prev 0/0C019E18, desc: RUNNING_XACTS nextXid 723 latestCompletedXid 722 oldestRunningXid 723

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C019FE4, prev 0/0C019FB0, desc: RUNNING_XACTS nextXid 723 latestCompletedXid 722 oldestRunningXid 723

rmgr: XLOG        len (rec/tot):    102/   102, tx:          0, lsn: 0/0C01A02C, prev 0/0C019FE4, desc: CHECKPOINT_ONLINE redo 0/C019FE4; tli 1; prev tli 1; fpw true; xid 0:723; oid 24639; multi 1; offset 0; oldest xid 548 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 723; online

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C01A094, prev 0/0C01A02C, desc: RUNNING_XACTS nextXid 723 latestCompletedXid 722 oldestRunningXid 723

pg_waldump: FATAL:  error in WAL record at 0/C01A094: invalid record length at 0/C01A0C8: wanted 24, got 0

2、备机pg_control文件记录的checkpoint位置

[yzs@bogon pg_wal]$ pg_controldata -D /home/yzs/data

pg_control version number:            1002

Catalog version number:               201707211

Database system identifier:           6602905782398358082

Database cluster state:               in archive recovery

pg_control last modified:             Thu 02 May 2019 12:58:24 AM PDT

Latest checkpoint location:           0/C01A02C

Prior checkpoint location:            0/C005470

Latest checkpoint's REDO location:    0/C019FE4

Latest checkpoint's REDO WAL file:    00000001000000000000000C

3、备机手动执行checkpoint

[yzs@bogon pg_wal]$ psql

psql (10.5)

Type "help" for help.

yzs=# checkpoint;

CHECKPOINT

4、备机日志文件

[yzs@bogon pg_wal]$ pg_waldump 00000001000000000000000C

...

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C019FB0, prev 0/0C019E18, desc: RUNNING_XACTS nextXid 723 latestCompletedXid 722 oldestRunningXid 723

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C019FE4, prev 0/0C019FB0, desc: RUNNING_XACTS nextXid 723 latestCompletedXid 722 oldestRunningXid 723

rmgr: XLOG        len (rec/tot):    102/   102, tx:          0, lsn: 0/0C01A02C, prev 0/0C019FE4, desc: CHECKPOINT_ONLINE redo 0/C019FE4; tli 1; prev tli 1; fpw true; xid 0:723; oid 24639; multi 1; offset 0; oldest xid 548 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 723; online

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C01A094, prev 0/0C01A02C, desc: RUNNING_XACTS nextXid 723 latestCompletedXid 722 oldestRunningXid 723

pg_waldump: FATAL:  error in WAL record at 0/C01A094: invalid record length at 0/C01A0C8: wanted 24, got 0

5、此时备机pg_control文件

[yzs@bogon pg_wal]$ pg_controldata -D /home/yzs/data

pg_control version number:            1002

Catalog version number:               201707211

Database system identifier:           6602905782398358082

Database cluster state:               in archive recovery

pg_control last modified:             Thu 02 May 2019 12:58:24 AM PDT

Latest checkpoint location:           0/C01A02C

Prior checkpoint location:            0/C005470

Latest checkpoint's REDO location:    0/C019FE4

Latest checkpoint's REDO WAL file:    00000001000000000000000C

6、主机执行一次insert

yzs=# insert into t1 values(1,2);

INSERT 0 1

7、此时备机位置：

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C019FB0, prev 0/0C019E18, desc: RUNNING_XACTS nextXid 723 latestCompletedXid 722 oldestRunningXid 723

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C019FE4, prev 0/0C019FB0, desc: RUNNING_XACTS nextXid 723 latestCompletedXid 722 oldestRunningXid 723

rmgr: XLOG        len (rec/tot):    102/   102, tx:          0, lsn: 0/0C01A02C, prev 0/0C019FE4, desc: CHECKPOINT_ONLINE redo 0/C019FE4; tli 1; prev tli 1; fpw true; xid 0:723; oid 24639; multi 1; offset 0; oldest xid 548 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 723; online

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C01A094, prev 0/0C01A02C, desc: RUNNING_XACTS nextXid 723 latestCompletedXid 722 oldestRunningXid 723

rmgr: Heap        len (rec/tot):     54/  6986, tx:        723, lsn: 0/0C01A0C8, prev 0/0C01A094, desc: INSERT off 192, blkref #0: rel 1663/16434/16443 blk 2899 FPW

rmgr: Transaction len (rec/tot):     34/    34, tx:        723, lsn: 0/0C01BC14, prev 0/0C01A0C8, desc: COMMIT 2019-05-02 01:01:07.715235 PDT

pg_waldump: FATAL:  error in WAL record at 0/C01BC14: invalid record length at 0/C01BC38: wanted 24, got 0

8、主机执行checkpoint

lti 1; offset 0; oldest xid 548 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 723; online

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C01A094, prev 0/0C01A02C, desc: RUNNING_XACTS nextXid 723 latestCompletedXid 722 oldestRunningXid 723

rmgr: Heap        len (rec/tot):     54/  6986, tx:        723, lsn: 0/0C01A0C8, prev 0/0C01A094, desc: INSERT off 192, blkref #0: rel 1663/16434/16443 blk 2899 FPW

rmgr: Transaction len (rec/tot):     34/    34, tx:        723, lsn: 0/0C01BC14, prev 0/0C01A0C8, desc: COMMIT 2019-05-02 01:01:07.715235 PDT

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C01BC38, prev 0/0C01BC14, desc: RUNNING_XACTS nextXid 724 latestCompletedXid 723 oldestRunningXid 724

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C01BC6C, prev 0/0C01BC38, desc: RUNNING_XACTS nextXid 724 latestCompletedXid 723 oldestRunningXid 724

rmgr: XLOG        len (rec/tot):    102/   102, tx:          0, lsn: 0/0C01BCA0, prev 0/0C01BC6C, desc: CHECKPOINT_ONLINE redo 0/C01BC6C; tli 1; prev tli 1; fpw true; xid 0:724; oid 24639; multi 1; offset 0; oldest xid 548 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 724; online

rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C01BD08, prev 0/0C01BCA0, desc: RUNNING_XACTS nextXid 724 latestCompletedXid 723 oldestRunningXid 724

pg_waldump: FATAL:  error in WAL record at 0/C01BD08: invalid record length at 0/C01BD3C: wanted 24, got 0

9、备机执行完checkpoint后

g_control version number:            1002

Catalog version number:               201707211

Database system identifier:           6602905782398358082

Database cluster state:               in archive recovery

pg_control last modified:             Thu 02 May 2019 01:03:16 AM PDT

Latest checkpoint location:           0/C01BCA0

Prior checkpoint location:            0/C01A02C

Latest checkpoint's REDO location:    0/C01BC6C

Latest checkpoint's REDO WAL file:    00000001000000000000000C

可以看到备机可以执行checkpoint，但是并不会产生WAL日志。

具体过程

1、备机shutdown时，checkpoint调用流程

1）ShutdownXLOG函数调用CreateRestartPoint做checkpoint，checkpoint位点来自XLogCtl->lastCheckPointRecPtr：

ShutdownXLOG->CreateRestartPoint:

lastCheckPointRecPtr = XLogCtl->lastCheckPointRecPtr;

ControlFile->checkPoint = lastCheckPointRecPtr;

2）恢复进程更新XLogCtl->lastCheckPointRecPtr值

StartupXLOG

do{

    ...

    RmgrTable[record->xl_rmid].rm_redo(xlogreader);

    |-- RecoveryRestartPoint(&checkPoint);

          |-- lastCheckPointRecPtr = XLogCtl->lastCheckPointRecPtr;

          |-- ControlFile->checkPoint = lastCheckPointRecPtr;

    ...

    record = ReadRecord(xlogreader, InvalidXLogRecPtr, LOG, false);

    |-- record = XLogReadRecord(xlogreader, RecPtr, &errormsg);

    |-- ReadRecPtr = xlogreader->ReadRecPtr;

} while (record != NULL);

ShutdownXLOG->CreateRestartPoint:

        lastCheckPointRecPtr = XLogCtl->lastCheckPointRecPtr;

        ControlFile->checkPoint = lastCheckPointRecPtr;

1、备机回放

StartupXLOG

    do{

        ...

        RmgrTable[record->xl_rmid].rm_redo(xlogreader);//回放

        ...

        record = ReadRecord(xlogreader, InvalidXLogRecPtr, LOG, false);//读取一个xlog

    } while (record != NULL);

2、回放函数

void

xlog_redo(XLogReaderState *record)

{

...

else if (info == XLOG_CHECKPOINT_SHUTDOWN){

...

memcpy(&checkPoint, XLogRecGetData(record), sizeof(CheckPoint));

...

RecoveryRestartPoint(&checkPoint);

}else if (info == XLOG_CHECKPOINT_ONLINE){

...

memcpy(&checkPoint, XLogRecGetData(record), sizeof(CheckPoint));

...

RecoveryRestartPoint(&checkPoint);

}

...

}

3、RecoveryRestartPoint

static void

RecoveryRestartPoint(const CheckPoint *checkPoint)

{

...

SpinLockAcquire(&XLogCtl->info_lck);

XLogCtl->lastCheckPointRecPtr = ReadRecPtr;//ReadRecPtr为读取checkpoint记录后的位置

XLogCtl->lastCheckPointEndPtr = EndRecPtr;

XLogCtl->lastCheckPoint = *checkPoint;

SpinLockRelease(&XLogCtl->info_lck);

}

4、ReadRecPtr赋值

ReadRecord

for (;;)

{

char    *errormsg;

record = XLogReadRecord(xlogreader, RecPtr, &errormsg);

ReadRecPtr = xlogreader->ReadRecPtr;

EndRecPtr = xlogreader->EndRecPtr;

...

}

5、备机createcheckpoint

bool

CreateRestartPoint(int flags)

{

LWLockAcquire(CheckpointLock, LW_EXCLUSIVE);

/* Get a local copy of the last safe checkpoint record. */

SpinLockAcquire(&XLogCtl->info_lck);

lastCheckPointRecPtr = XLogCtl->lastCheckPointRecPtr;//checkpoint的位置来自XLogCtl->lastCheckPointRecPtr

lastCheckPointEndPtr = XLogCtl->lastCheckPointEndPtr;

lastCheckPoint = XLogCtl->lastCheckPoint;

SpinLockRelease(&XLogCtl->info_lck);

...

if (XLogRecPtrIsInvalid(lastCheckPointRecPtr) || lastCheckPoint.redo <= ControlFile->checkPointCopy.redo){

//回放了最后一个checkpoint记录后，备机再次手动执行checkpoint命令

UpdateMinRecoveryPoint(InvalidXLogRecPtr, true);

if (flags & CHECKPOINT_IS_SHUTDOWN){

LWLockAcquire(ControlFileLock, LW_EXCLUSIVE);

ControlFile->state = DB_SHUTDOWNED_IN_RECOVERY;

ControlFile->time = (pg_time_t) time(NULL);

UpdateControlFile();

LWLockRelease(ControlFileLock);

}

LWLockRelease(CheckpointLock);

return false;

}

...

LWLockAcquire(ControlFileLock, LW_EXCLUSIVE);

if (ControlFile->state == DB_IN_ARCHIVE_RECOVERY && ControlFile->checkPointCopy.redo < lastCheckPoint.redo){

ControlFile->prevCheckPoint = ControlFile->checkPoint;

ControlFile->checkPoint = lastCheckPointRecPtr;//checkpoint的位置

ControlFile->checkPointCopy = lastCheckPoint;

ControlFile->time = (pg_time_t) time(NULL);

...

if (flags & CHECKPOINT_IS_SHUTDOWN)

ControlFile->state = DB_SHUTDOWNED_IN_RECOVERY;

UpdateControlFile();

}

...

return true;

}

6、备机shutdown

void

ShutdownXLOG(int code, Datum arg)

{

/*

 * Signal walsenders to move to stopping state.

 */

WalSndInitStopping();

/*

 * Wait for WAL senders to be in stopping state.  This prevents commands

 * from writing new WAL.

 */

WalSndWaitStopping();

if (RecoveryInProgress())//备机写checkpoint

CreateRestartPoint(CHECKPOINT_IS_SHUTDOWN | CHECKPOINT_IMMEDIATE);

else

{

/*

 * If archiving is enabled, rotate the last XLOG file so that all the

 * remaining records are archived (postmaster wakes up the archiver

 * process one more time at the end of shutdown). The checkpoint

 * record will go to the next XLOG file and won't be archived (yet).

 */

if (XLogArchivingActive() && XLogArchiveCommandSet())

RequestXLogSwitch(false);

CreateCheckPoint(CHECKPOINT_IS_SHUTDOWN | CHECKPOINT_IMMEDIATE);

}

ShutdownCLOG();

ShutdownCommitTs();

ShutdownSUBTRANS();

ShutdownMultiXact();

}

小结

PostgreSQL备库也可以写检查点，目的是避免每次重启备库都需要从上一个检查点（由主库产生，在WAL中回放出来的）APPLY后面所有的WAL。但是他记录的checkpoint位点是从主库传过来的。这样的话就有问题了，如果主机很长时间都没有做checkpoint了，备机即使正常关闭，重启时，也会从上一个checkpoint开始恢复，这样也会恢复很长时间；并且多次重启也需要从上一次checkpoint开始重复恢复。