PostgreSQL備機是否做checkpoint

背景

我們知道為了縮短崩潰恢復時間三椿，PostgreSQL支持checkpoint功能箩兽，即異常重啟時津肛，PostgreSQL以最近的checkpoint為起點，不斷回放之后的WAL日志汗贫。PostgreSQL主機做checkpoint時身坐，產(chǎn)生一條CHECKPOINT WAL日志記錄。那么備機會做checkpoint嗎落包？如果可以做checkpoint部蛇，它是否也會產(chǎn)生一條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、備機手動執(zhí)行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、主機執(zhí)行一次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警绩、主機執(zhí)行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、備機執(zhí)行完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

可以看到備機可以執(zhí)行checkpoint盅称，但是并不會產(chǎn)生WAL日志肩祥。

具體過程

1后室、備機shutdown時，checkpoint調(diào)用流程

1）ShutdownXLOG函數(shù)調(diào)用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、回放函數(shù)

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記錄后俯逾，備機再次手動執(zhí)行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();

}

小結(jié)

PostgreSQL備庫也可以寫檢查點舅逸，目的是避免每次重啟備庫都需要從上一個檢查點（由主庫產(chǎn)生桌肴，在WAL中回放出來的）APPLY后面所有的WAL。但是他記錄的checkpoint位點是從主庫傳過來的琉历。這樣的話就有問題了坠七，如果主機很長時間都沒有做checkpoint了，備機即使正常關(guān)閉旗笔，重啟時彪置，也會從上一個checkpoint開始恢復，這樣也會恢復很長時間蝇恶；并且多次重啟也需要從上一次checkpoint開始重復恢復拳魁。