在生产环境中我们尝试使用非排他备份的方式在从库进行备份,可以遇到经常备份超时的情况,看看这里的逻辑。
非排他备份在pg15之后是唯一的选项了,pg15中修改了基础备份函数名,将pg_start_bckup和pg_stop_backup改为了pg_backup_start以及pg_backup_stop,并且仅提供非排他备份,在老版本中,我们可以在pg_start_backup函数中指定备份方式:
pg_start_backup(`label` text , `fast` boolean , `exclusive` boolean ) 其中lable为备份标签; fast为是否立即执行checkpoint,这个我们在checkpoint章节有分析过,checkpoint的来源有备份函数pg_start_backup调用 exclusive为是否启用非排他备份,排他备份会将backup_lable写入到data目录下,而非排他备份会将backup_lable写入到备份目录下
而新函数pg_backup_start则只提供两个参数值:
pg_backup_start(`label` text , `fast` boolean default false)
当然这个函数的调用逻辑依然没有改变,步骤还是和老版本趋于一致,具体的pg_backup_start会进行哪些操作参见备份部分,这里不做展开。
回到具体的使用场景,在从库执行非排他备份,默认从库是开启归档的,由于生产环境中,需要一起备份从库的wal日志,所以就将对于pg_backup_start的第二个参数,设置为需要等待wal归档。我们看看这个场景下的pg_backup_start和pg_backup_stop
备份逻辑依然是:
-
调用pg_backup_start
-
备份
-
调用pg_backup_stop
看到在调用pg_backup_stop时,耗时非常久,等待wal日志归档。当数据库长期处于一个空闲状态时,那么wal日志是没有任何写入操作的,也就是lsn不会发生变化。同样再加上从库不会写wal日志,而从库又开启了归档。这就导致了pg_backup_stop迟迟无法完成的原因。
WARNING "still waiting for all required WAL segments to be archived (7680 seconds elapsed)",,"check that your archive_ command is executing properly. ... [local]",664007ca.d9C6,13,"SELECT",2024-85-12 88:05:30 CST.5/187946.6.L0G.0000,duration: 56954798.541 ms plan: Query Text: select lsn,labelfile from pg_stop_backup(false) Function Scan on pg_stop_backup (cost=0.00..10.08 rows=1000 width=40)",,,,,,,,"","client backend"
可是我们在主库上默认开启了archive_timeout为30min,理论上就应该30min切换一个wal日志吗? 实际不然,archive_timeout也是有跳过机制,当数据库没有任何的lsn变化时,那么archive_timeout则什么都不会做。(当然archive_timeout也会调用到pg_switch_wal操作)
直接尝试在主库触发一个pg_switch_wal操作,主动进行切换wal日志,可是发现还是存在wal日志不会切换的场景,这与官方的说明是一致的:PostgreSQL: Documentation: 16: 9.27. System Administration Functions
pg_switch_wal() -> pg_lsn Forces the server to switch to a new write-ahead log file, which allows the current file to be archived (assuming you are using continuous archiving). The result is the ending write-ahead log location plus 1 within the just-completed write-ahead log file. If there has been no write-ahead log activity since the last write-ahead log switch, pg_switch_wal does nothing and returns the start location of the write-ahead log file currently in use. This function is restricted to superusers by default, but other users can be granted EXECUTE to run the function.
可是实际操作中,在一个空闲的数据库上面做pg_switch_wal,可以观察到wal日志是会切换的,这是为什么? 看看代码逻辑(pg15)
pg_switch_wal函数的调用逻辑比较简单,直接梳理代码逻辑,从同名函数入口,直接调用 RequestXLogSwitch -> XLogInsert 传入的参数为XLOG_SWITCH,做switch操作 -> XLogInsertRecord 返回endpos
我们详细看看 XLogInsertRecord函数中,做实际的wal日志写入操作,是怎么返回endpos的,直接到switch逻辑中
/* * Reserve space for the record in the WAL. This also sets the xl_prev * pointer. */ if (isLogSwitch) inserted = ReserveXLogSwitch(&StartPos, &EndPos, &rechdr->xl_prev); /*返回是否需要inserted*/ else { ReserveXLogInsertLocation(rechdr->xl_tot_len, &StartPos, &EndPos, &rechdr->xl_prev); inserted = true; } ... /* * If this was an XLOG_SWITCH record, flush the record and the empty * padding space that fills the rest of the segment, and perform * end-of-segment actions (eg, notifying archiver). */ if (isLogSwitch) { TRACE_POSTGRESQL_WAL_SWITCH(); XLogFlush(EndPos); /*执行flush操作,将switch的这条wal日志刷到磁盘上*/ /* * Even though we reserved the rest of the segment for us, which is * reflected in EndPos, we return a pointer to just the end of the * xlog-switch record. */ if (inserted) /*判断是否需要做inserted,如果需要,则返回的endpos是加上页头的*/ { EndPos = StartPos + SizeOfXLogRecord; if (StartPos / XLOG_BLCKSZ != EndPos / XLOG_BLCKSZ) { uint64 offset = XLogSegmentOffset(EndPos, wal_segment_size); if (offset == EndPos % XLOG_BLCKSZ) EndPos += SizeOfXLogLongPHD; else EndPos += SizeOfXLogShortPHD; } } }
ReserveXLogSwitch函数中,返回inserted是否为真,只有如下条件时,才会返回inserted为假
ptr = XLogBytePosToEndRecPtr(startbytepos); if (XLogSegmentOffset(ptr, wal_segment_size) == 0) { SpinLockRelease(&Insert->insertpos_lck); *EndPos = *StartPos = ptr; return false; }
至此,我们梳理通了代码逻辑,自然也就解释了操作现象:为什么pg_switch_wal有时候会返回lsn变化,有时候不变化,是因为这个变量导致的,而当inserted为t时,表示需要endpos需要加上页头,而我们又写了switch的wal日志,所以自然endpos变化的这条wal日志就写入到新的wal日志中了,也就是我们看到的新产生一个wal。
当然还有一个现象,我们在pg_switch_wal中,频繁快速的调用时,偶尔会看到lsn是不变化的,这是为什么呢? 因为在XLogFlush函数中,存在sleep 200ms的睡眠
了解了代码逻辑后,解决方案也就非常简单了,保证wal有变化就可以了,比如做完pg_switch_wal之后再主库做一个写wal日志的操作即可