openGauss/PostgreSQL中的预写式日志WAL（Write Ahead Log），又名Xlog或redo log,相当于oracle的online redo log, 不同的是oracle online redo log是提前创建几组滚动使用，但在opengauss中只需要本配置参数控制WAL日志的周期，数据库会一直的创建并自动清理，但存在一些情况WAL日志未清理导致目录空间耗尽，或目录空间紧张时手动删除wal日志时，比如如何确认在非归档模式下哪些WAL日志文件可以安全删除？
数据库参数
先看几个数据库实例控制的与wal日志相关的, 这里是这的测试环境openGauss 5.0，默认值。
openGauss=# \! sh show walenable_mix_replication|off|Allthereplicationlog sentbythe wal streaming.enable_wal_shipping_compression|off|enablecompress xlog during xlog shipping.hadr_max_size_for_xlog_receiver|256GB|This paramsetbyuserisusedforxlogtostop receivingwhenthe gapislarger than this parambetweenreplay xlog locationandwalreceiver r max_wal_senders|4|Sets the maximum numberofsimultaneously running WAL sender processes.wal_block_size|8192|Shows the block sizeinthewriteahead log.wal_buffers|16MB|Sets the numberofdisk-page buffersinshared memoryforWAL.wal_file_init_num|10|Sets the numberofxlog segment files that WAL writer auxiliary thread creates at onetime.wal_flush_delay|1|setdelaytimewheniteratortableentry.wal_flush_timeout|2|settimeoutwheniteratortableentry.wal_keep_segments|16|Sets the numberofWAL files heldforstandby servers.wal_level|hot_standby|Sets thelevelofinformation writtentothe WAL.wal_log_hints|on|WritesfullpagestoWALwhenfirstmodifiedafteracheckpoint,evenfora non-critical modifications.wal_receiver_buffer_size|64MB|Sets the buffer sizetoreceivedatafrommaster.wal_receiver_connect_retries|1|Sets the maximum retriestoconnectmaster.wal_receiver_connect_timeout|2s|Sets the maximum waittimetoconnectmaster.wal_receiver_status_interval|5s|Sets the maximumintervalbetweenWAL receiverstatusreportstotheprimary.wal_receiver_timeout|6s|Sets the maximum waittimetoreceivedatafrommaster.wal_segment_size|16MB|Shows the numberofpages perwriteahead log segment.wal_sender_timeout|6s|Sets the maximumtimetowaitforWALreplication.wal_sync_method|fdatasync|Selects the method usedforforcing WAL updatestodisk.wal_writer_delay|200ms|WAL writer sleeptimebetweenWAL flushes.walsender_max_send_size|8MB|Sizeofwalsender max send size.walwriter_cpu_bind|-1|Sets the binding CPU numberforthe WAL writer thread.walwriter_sleep_threshold|500|Numberofidle xlog flushes before xlog flusher goestosleep.openGauss=# \! sh show xlogadvance_xlog_file_num|0|Sets the numberofxlog filestobe initializedinadvance.archive_timeout|0|Forces a switchtothenextxlogfileifa newfilehasnotbeen startedwithinN seconds.enable_wal_shipping_compression|off|enablecompress xlog during xlog shipping.enable_xlog_prune|on|Enablexlog prunewhennotallstandys connectedandxlog sizeislargger than max_xlog_size hadr_max_size_for_xlog_receiver|256GB|This paramsetbyuserisusedforxlogtostop receivingwhenthe gapislarger than this parambetweenreplay xlog locationandwalreceiver r max_size_for_xlog_prune|2147483647kB|This paramsetbyuserisusedforxlogtobe recycledwhennotallare connectedandthe param enable_xlog_pruneison.recovery_parse_workers|1|The numberofrecovery threadstodoxlog parse.recovery_redo_workers|1|The number belongingtoone parse workertodoxlog redo.wal_file_init_num|10|Sets the numberofxlog segment files that WAL writer auxiliary thread creates at onetime.walwriter_sleep_threshold|500|Numberofidle xlog flushes before xlog flusher goestosleep.xlog_file_path||useonly one xlogfile,the pathofit xlog_file_size|549755813888|sharestorage xlogfilesize xlog_lock_file_path||usedtocontrolwritetoxlog_file_path xloginsert_locks|8|Sets the numberoflocks usedforconcurrent xlog insertions.openGauss=# show archive_mode;archive_mode--------------off(1row)自动清理WAL
WAL日志并不是一直存在，会自动的清理。wal_keep_segments参数控制wal日志保留的个数，默认保留最近16个。“pg_xlog”目录下保留事务日志文件的最小数目。
另一个参数max_size_for_xlog_prune参数，在enable_xlog_prune打开时生效，如果有备机断连且xlog日志大小大于此阈值，则回收日志。自动清理时会在pg_log中记录:
[BACKEND]LOG: attemptingtoremove WAL segments older than logfile000000010000000000000008[BACKEND]LOG: attemptingtoremove WAL segments older than logfile000000010000000000000009我们看一下是哪16个
[og@oel7db1pg_xlog]$ ls-lrt|cat-n1total4259882drwx------ 2 og og 4096 May 8 11:36 archive_status3-rw------- 1 og og 16777216 May 10 17:17 00000001000000000000001C4-rw------- 1 og og 16777216 May 27 15:58 00000001000000000000001D5-rw------- 1 og og 16777216 May 27 15:59 00000001000000000000001E6-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000001F7-rw------- 1 og og 16777216 May 27 16:00 0000000100000000000000208-rw------- 1 og og 16777216 May 27 16:00 0000000100000000000000219-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000002210-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000002311-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000A12-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000B13-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000C14-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000D15-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000E16-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000F17-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000001018-rw------- 1 og og 16777216 May 27 16:47 00000001000000000000001119-rw------- 1 og og 16777216 May 27 16:47 00000001000000000000001220-rw------- 1 og og 16777216 May 27 16:47 00000001000000000000001321-rw------- 1 og og 16777216 May 27 16:47 00000001000000000000001422-rw------- 1 og og 16777216 May 27 16:47 00000001000000000000001523-rw------- 1 og og 16777216 May 27 16:47 00000001000000000000001624-rw------- 1 og og 16777216 May 27 16:47 00000001000000000000001725-rw------- 1 og og 16777216 May 27 16:47 00000001000000000000001826-rw------- 1 og og 16777216 May 27 16:52 00000001000000000000001927-rw------- 1 og og 16777216 May 27 16:52 00000001000000000000001A28-rw------- 1 og og 16777216 May 27 16:53 00000001000000000000001Bnote:
减去当前和之后的。
手动日志切换
相当于Oracle的alter system switch logfile;
penGauss=# select pg_switch_xlog();pg_switch_xlog----------------0/F000168(1row)openGauss=# select pg_switch_xlog();pg_switch_xlog----------------0/10000168(1row)查看当前的WAL日志
# DB内部openGauss=# select * from pg_ls_waldir() order by modification asc;name|size|modification--------------------------+----------+------------------------000000010000000000000001|16777216|2023-05-0811:37:00+08000000010000000000000002|16777216|2023-05-1017:17:55+08000000010000000000000003|16777216|2023-05-2715:58:58+08000000010000000000000004|16777216|2023-05-2715:59:02+08000000010000000000000005|16777216|2023-05-2716:00:24+08000000010000000000000006|16777216|2023-05-2716:00:27+08000000010000000000000007|16777216|2023-05-2716:00:29+08000000010000000000000008|16777216|2023-05-2716:00:31+08000000010000000000000009|16777216|2023-05-2716:00:33+0800000001000000000000000A|16777216|2023-05-2716:00:34+0800000001000000000000000B|16777216|2023-05-2716:00:36+0800000001000000000000000C|16777216|2023-05-2716:00:38+0800000001000000000000000D|16777216|2023-05-2716:00:39+0800000001000000000000000E|16777216|2023-05-2716:00:41+0800000001000000000000000F|16777216|2023-05-2716:00:43+08000000010000000000000012|16777216|2023-05-2716:00:44+08000000010000000000000013|16777216|2023-05-2716:00:44+08000000010000000000000014|16777216|2023-05-2716:00:45+08000000010000000000000016|16777216|2023-05-2716:00:45+08000000010000000000000010|16777216|2023-05-2716:00:45+08000000010000000000000015|16777216|2023-05-2716:00:45+08000000010000000000000017|16777216|2023-05-2716:00:46+08000000010000000000000019|16777216|2023-05-2716:00:46+08000000010000000000000018|16777216|2023-05-2716:00:46+0800000001000000000000001A|16777216|2023-05-2716:00:47+08000000010000000000000011|16777216|2023-05-2716:03:45+08(26rows)# 操作系统[og@oel7db1pg_xlog]$ ls-lrt|cat-n1total4259882drwx------ 2 og og 4096 May 8 11:36 archive_status3-rw------- 1 og og 16777216 May 8 11:37 0000000100000000000000014-rw------- 1 og og 16777216 May 10 17:17 0000000100000000000000025-rw------- 1 og og 16777216 May 27 15:58 0000000100000000000000036-rw------- 1 og og 16777216 May 27 15:59 0000000100000000000000047-rw------- 1 og og 16777216 May 27 16:00 0000000100000000000000058-rw------- 1 og og 16777216 May 27 16:00 0000000100000000000000069-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000710-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000811-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000912-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000A13-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000B14-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000C15-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000D16-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000E17-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000000F18-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000001219-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000001320-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000001421-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000001522-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000001023-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000001624-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000001725-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000001826-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000001927-rw------- 1 og og 16777216 May 27 16:00 00000001000000000000001A28-rw------- 1 og og 16777216 May 27 16:06 000000010000000000000011日志文件命名格式
000000010000000000000011
WAL日志三个组成部分：
第一部分，叫做时间线，是从1开始递增地数字(类似ORACLE 的SCN的wrap), 当低位满时，前一段加1.
第二部分，叫做LogId，是LSN的高32位(从0开始)
第三部分，叫做LogSeg，是LSN的低32位除以WAL文件的大小，WAL文件的大小默认是16M
WAL日志手动清理
[og@oel7db1~]$ ps-ef|grep gauss og19891111:48pts/000:04:13gaussdb-D/opensource/opengauss/5.0/db--single_nodeog2608625871015:59pts/200:00:00grep--color=auto gauss[og@oel7db1pg_xlog]$ pg_controldata/opensource/opengauss/5.0/db/pg_control version number:923Catalog version number:201611171Databasesystem identifier:576503776236165954Databasecluster state:inproduction pg_controllastmodified: Sat27May202304:01:45PM CST Latestcheckpointlocation:0/110001C8 Priorcheckpointlocation:0/110000A8 Latestcheckpoint's REDO location: 0/11000148 Latest checkpoint's TimeLineID:1Latestcheckpoint's full_page_writes: off Latest checkpoint's NextXID:16617Latestcheckpoint's NextOID: 16431 Latest checkpoint's NextMultiXactId:2Latestcheckpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID:12664Latestcheckpoint's oldestXID's DB:15645Latestcheckpoint's oldestActiveXID: 16617 Latest checkpoint's remove lsn:0/1Timeoflatestcheckpoint: Sat27May202304:01:45PM CST Minimum recovery ending location:0/0Backupstartlocation:0/0Backupendlocation:0/0End-of-backuprecord required:noCurrentwal_level setting: hot_standbyCurrentmax_connections setting:250Currentmax_prepared_xacts setting:200Currentmax_locks_per_xact setting:256Maximumdataalignment:8Databaseblock size:8192Blocks per segmentoflarge relation:131072WAL block size:8192Bytes per WAL segment:16777216Maximum lengthofidentifiers:64Maximumcolumnsinanindex:32Maximum sizeofa TOAST chunk:1996Date/timetypestorage:64-bitintegers Float4 argument passing:byvalueFloat8 argument passing:byvalueDatabasesystem TimeLine:8openGauss=# select pg_xlogfile_name('0/110001C8');pg_xlogfile_name--------------------------000000010000000000000011(1row)Note:
说明000000010000000000000011(#11)之前的wal日志都可以清理。
可以手动rm 也可以使用pg_archivecleanup，如果当前opengauss中没有该工具，可以从postgresql中复制，目前在openGauss的商业发行版Mogdb中有该工具的说明。 pg_archivecleanup 是一个用于清理旧的归档日志的工具。
pg_archivecleanup[OPTION]...ARCHIVELOCATION OLDESTKEPTWALFILE e.g.pg_archivecleanup-d/opensource/opengauss/5.0/db/000000010000000000000011
