PostgreSQL備機(jī)是否做checkpoint

PostgreSQL備機(jī)是否做checkpoint

背景

我們知道為了縮短崩潰恢復(fù)時(shí)間，PostgreSQL支持checkpoint功能，即異常重啟時(shí)，PostgreSQL以最近的checkpoint為起點(diǎn)，不斷回放之后的WAL日志。PostgreSQL主機(jī)做checkpoint時(shí)，產(chǎn)生一條CHECKPOINT WAL日志記錄。那么備機(jī)會(huì)做checkpoint嗎？如果可以做checkpoint，它是否也會(huì)產(chǎn)生一條CHECKPOINT WAL日志記錄并將其寫入到WAL文件中？

實(shí)驗(yàn)

1、備機(jī)的日志位置

[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、備機(jī)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、備機(jī)手動(dòng)執(zhí)行checkpoint

[yzs@bogon pg_wal]$ psql

psql (10.5)

Type "help" for help.

yzs=# checkpoint;

CHECKPOINT

4、備機(jī)日志文件

[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、此時(shí)備機(jī)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、主機(jī)執(zhí)行一次insert

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

INSERT 0 1

7、此時(shí)備機(jī)位置：

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、主機(jī)執(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、備機(jī)執(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

可以看到備機(jī)可以執(zhí)行checkpoint，但是并不會(huì)產(chǎn)生WAL日志。

具體過程

1、備機(jī)shutdown時(shí)，checkpoint調(diào)用流程

1）ShutdownXLOG函數(shù)調(diào)用CreateRestartPoint做checkpoint，checkpoint位點(diǎn)來自XLogCtl->lastCheckPointRecPtr：

ShutdownXLOG->CreateRestartPoint:

lastCheckPointRecPtr = XLogCtl->lastCheckPointRecPtr;

ControlFile->checkPoint = lastCheckPointRecPtr;

2）恢復(fù)進(jìn)程更新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、備機(jī)回放

StartupXLOG

? ? do{

? ? ? ? ...

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

? ? ? ? ...

? ? ? ? record = ReadRecord(xlogreader, InvalidXLogRecPtr, LOG, false);//讀取一個(gè)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、備機(jī)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){

//回放了最后一個(gè)checkpoint記錄后，備機(jī)再次手動(dòng)執(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、備機(jī)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())//備機(jī)寫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備庫(kù)也可以寫檢查點(diǎn)，目的是避免每次重啟備庫(kù)都需要從上一個(gè)檢查點(diǎn)（由主庫(kù)產(chǎn)生，在WAL中回放出來的）APPLY后面所有的WAL。但是他記錄的checkpoint位點(diǎn)是從主庫(kù)傳過來的。這樣的話就有問題了，如果主機(jī)很長(zhǎng)時(shí)間都沒有做checkpoint了，備機(jī)即使正常關(guān)閉，重啟時(shí)，也會(huì)從上一個(gè)checkpoint開始恢復(fù)，這樣也會(huì)恢復(fù)很長(zhǎng)時(shí)間；并且多次重啟也需要從上一次checkpoint開始重復(fù)恢復(fù)。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容