PG WAL日志详解
预写式日志（WAL）
预写式日志（Write Ahead Log，WAL）是保证数据完整性的一种标准方法。对其详尽的描述几乎可以在所有（如果不是全部）有关事务处理的书中找到。简单来说，WAL的中心概念是数据文件（存储着表和索引）的修改必须在这些动作被日志记录之后才被写入，即在描述这些改变的日志记录被刷到持久存储以后。如果我们遵循这种过程，我们不需要在每个事务提交时刷写数据页面到磁盘，因为我们知道在发生崩溃时可以使用日志来恢复数据库：任何还没有被应用到数据页面的改变可以根据其日志记录重做（这是前滚恢复，也被称为REDO）。
因为WAL在崩溃后恢复数据库文件内容，不需要日志化文件系统作为数据文件或WAL文件的可靠存储。实际上，日志会降低性能，特别是如果日志导致文件系统数据被刷写到磁盘。幸运地是，日志期间的数据刷写常常可以在文件系统挂载选项中被禁用，例如在Linux ext3文件系统中可以使用data=writeback。在崩溃后日志化文件系统确实可以提高启动速度。
使用WAL可以显著降低磁盘的写次数，因为只有日志文件需要被刷出到磁盘以保证事务被提交，而被事务改变的每一个数据文件则不必被刷出。日志文件被按照顺序写入，因此同步日志的代价要远低于刷写数据页面的代价。在处理很多影响数据存储不同部分的小事务的服务器上这一点尤其明显。此外，当服务器在处理很多小的并行事务时，日志文件的一个fsync可以提交很多事务。
WAL也使得在线备份和时间点恢复能被支持，如第 25.3 节所述。通过归档WAL数据，我们可以支持回转到被可用WAL数据覆盖的任何时间：我们简单地安装数据库的一个较早的物理备份，并且重放WAL日志一直到所期望的时间。另外，该物理备份不需要是数据库状态的一个一致的快照 — 如果它的制作经过了一段时间，则重放这一段时间的WAL日志将会修复任何内部不一致性。
类似于Oracle的redo，PostgreSQL的redo文件被称为WAL文件或XLOG文件，存放在 $PGDATA/pg_xlog或 $PGDATA/pg_wal目录中（PostgreSQL从10版本开始，将所用xlog相关的全部用wal替换了）。任何试图修改数据库数据的操作都会写一份日志到磁盘。
wal命名格式文件名称为16进制的24个字符组成，每8个字符一组，每组的意义如下：
1 2 3 4 5 6 7 8 | 00000001 00000000 000000E0 -------- -------- -------- 时间线 LogId LogSeg 时间线：英文为timeline，是以1开始的递增数字，如1,2,3... LogId：32bit长的一个数字，是以0开始递增的数字，如0,1,2,3... LogSeg：32bit长的一个数字，是以0开始递增的数字，如0,1,2,3... |
通过select pg_switch_xlog()或select pg_switch_wal()；可以手动切换xlog/wal日志。
PG10之前版本：select pg_switch_xlog();
PG10开始版本：select pg_switch_wal();
wal日志即write ahead log预写式日志，简称wal日志。wal日志可以说是PostgreSQL中十分重要的部分，相当于oracle中的redo日志。
当数据库中数据发生变更时：
change发生时：先要将变更后内容计入wal buffer中，再将变更后的数据写入data buffer；
commit发生时：wal buffer中数据刷新到磁盘；
checkpoint发生时：将所有data buffer刷新的磁盘。
可以想象，如果没有wal日志，那么数据库中将会发生什么？
首先，当我们在数据库中更新数据时，如果没有wal日志，那么每次更新都会将数据刷到磁盘上，并且这个动作是随机i/o，性能可想而知。并且没有wal日志，关系型数据库中事务的ACID如何保证呢？
因此wal日志重要性可想而知。其中心思想就是：先写入日志文件，再写入数据。
说到checkpoint，我们再来看看哪些情况会触发数据库的checkpoing：
1.手动执行CHECKPOINT命令；
2.执行需要检查点的命令（例如pg_start_backup 或pg_ctl stop|restart等等）；
3.达到检查点配置时间(checkpoint_timeout)；
4.max_wal_size已满。
其中1和2两点都和数据库的配置无关，我们暂时先不看，这里先介绍下checkpoint_timeout和max_wal_size两个参数。
checkpoint_timeout： 自动 WAL 检查点之间的最长时间，以秒计。合理的范围在 30 秒到 1 天之间。默认是 5 分钟(5min)。增加这个参数的值会增加崩溃恢复所需的时间。
1 2 3 4 5 | bill@bill=>show checkpoint_timeout ; checkpoint_timeout -------------------- 30min (1 row) |
max_wal_size： 在自动 WAL检查点之间允许WAL 增长到的最大尺寸。这是一个软限制，在特殊的情况 下 WAL 尺寸可能会超过max_wal_size， 例如在重度负荷下、archive_command失败或者高的 wal_keep_segments设置。默认为 1 GB。增加这个参数可能导致崩溃恢复所需的时间。
1 2 3 4 5 | bill@bill=>show max_wal_size ; max_wal_size -------------- 2GB (1 row) |
和max_wal_size相对应的还有个min_wal_size，这里简单介绍下：
只要 WAL 磁盘用量保持在这个设置之下，在检查点时旧的 WAL文件总是被回收以便未来使用，而不是直接被删除。
可能对oracle熟悉的人会觉得wal日志和redo还是有些不同，没错，oracle中redo是固定几个redo日志文件，然后轮着切换去写入，因此我们常常会在io高的数据库中看到redo切换相关的等待事件。
而在pg中wal日志是动态切换，从pg9.6开始采用这种模式。和oracle不同的是，pg中这种动态wal切换步骤是这样的：单个wal日志写满(默认大小16MB，编译数据库时指定)继续写下一个wal日志，直到磁盘剩余空间不足min_wal_size时才会将旧的 WAL文件回收以便继续使用。
但是这种模式有一个弊端就是如果在checkpoint之前产生了大量的wal日志就会导致发生checkpoint时对性能的影响巨大，因此pg中还有一个参数checkpoint_completion_target
来进行调整。
checkpoint_completion_target： 指定检查点完成的目标，作为检查点之间总时间的一部分。默认是 0.5。
什么意思呢，假如我的checkpoint_timeout设置是30分钟，而wal生成了10G，那么设置成0.5就允许我在15分钟内完成checkpoint，调大这个值就可以降低checkpoint对性能的影响，但是万一数据库出现故障，那么这个值设置越大数据就越危险。
当数据库中数据发生变更时： change发生时：先要将变更后内容计入wal buffer中，再将变更后的数据写入data buffer； commit发生时：wal buffer中数据刷新到磁盘； checkpoint发生时：将所有data buffer刷新的磁盘。
如果没有wal日志，那么数据库中将会发生什么？ 首先，当我们在数据库中更新数据时，如果没有wal日志，那么每次更新都会将数据刷到磁盘上，并且这个动作是随机i/o，性能可想而知。并且没有wal日志，关系型数据库中事务的ACID如何保证呢？ 因此wal日志重要性可想而知。其中心思想就是：先写入日志文件，再写入数据。
说到checkpoint，我们再来看看哪些情况会触发数据库的checkpoing：
1.手动执行CHECKPOINT命令；
2.执行需要检查点的命令（例如pg_start_backup 或pg_ctl stop|restart等等）；
3.达到检查点配置时间(checkpoint_timeout)；
4.max_wal_size已满。
checkpoint_timeout： 自动 WAL 检查点之间的最长时间，以秒计。合理的范围在 30 秒到 1 天之间。默认是 5 分钟(5min)。增加这个参数的值会增加崩溃恢复所需的时间。
max_wal_size： 在自动 WAL检查点之间允许WAL 增长到的最大尺寸。这是一个软限制，在特殊的情况 下 WAL 尺寸可能会超过max_wal_size， 例如在重度负荷下、archive_command失败或者高的 wal_keep_segments设置。默认为 1 GB。增加这个参数可能导致崩溃恢复所需的时间。（ wal_keep_segments用于指定pg_wal目录中保存的过去的wal文件（wal 段）的最小数量，以防备用服务器在进行流复制时需要。）
和max_wal_size相对应的还有个min_wal_size，只要 WAL 磁盘用量保持在这个设置之下，在检查点时旧的 WAL文件总是被回收以便未来使用，而不是直接被删除。
wal切换步骤是这样的：单个wal日志写满(默认大小16MB，编译数据库时指定)继续写下一个wal日志，直到磁盘剩余空间不足min_wal_size时才会将旧的 WAL文件回收以便继续使用。但是这种模式有一个弊端就是如果在checkpoint之前产生了大量的wal日志就会导致发生checkpoint时对性能的影响巨大，因此pg中还有一个参数checkpoint_completion_target 来进行调整。
checkpoint_completion_target： 指定检查点完成的目标，作为检查点之间总时间的一部分。默认是 0.5。假如我的checkpoint_timeout设置是30分钟，而wal生成了10G，那么设置成0.5就允许我在15分钟内完成checkpoint，调大这个值就可以降低checkpoint对性能的影响，但是万一数据库出现故障，那么这个值设置越大数据就越危险。
总结：大多数检查点应该是基于时间的,即由checkpoint_timeout触发。 性能（不频繁检查点）与恢复所需时间（频繁检查点）之间需要抉择： 值在15-30分钟之间是比例合适的,但到1小时不是什么坏事。 在决定checkpoint_timeout后,通过估计WAL的数量选择max_wal_size。 设置checkpoint_completion_target以便内核将数据刷新到磁盘的时间足够（但不是太多）
总结：大多数检查点应该是基于时间的,即由checkpoint_timeout触发。
性能（不频繁检查点）与恢复所需时间（频繁检查点）之间需要抉择：
值在15-30分钟之间是比例合适的,但到1小时不是什么坏事。
在决定checkpoint_timeout后,通过估计WAL的数量选择max_wal_size。
设置checkpoint_completion_target以便内核将数据刷新到磁盘的时间足够（但不是太多）。
切换日志
pg_switch_wal()或pg_switch_xlog()强制服务器切换到一个新的预写式日志文件，这允许对当前文件进行归档(假设你正在使用连续归档)。 其结果是在刚刚完成的预写式日志文件中结束预写式日志位置加1。 如果自从上次预写式日志切换以来没有WAL日志活动，则pg_switch_wal将不做任何操作，并返回当前正在使用的WAL日志文件的起始位置。默认情况下该函数仅限超级用户使用，但可以授权其他用户执行该函数。
pg_switch_xlog()用于PG 10之前，从PG 10开始切换归档日志使用pg_switch_wal()。
另外，PG也提供了相应的函数根据LSN获取日志文件名：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/F000000 (1 row) postgres=# SELECT pg_walfile_name(pg_current_wal_lsn()); pg_walfile_name -------------------------- 00000001000000000000000E (1 row) postgres=# SELECT pg_walfile_name('0/F000000'); pg_walfile_name -------------------------- 00000001000000000000000E (1 row) postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/10000000 (1 row) postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/10000000 (1 row) postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/10000000 (1 row) postgres=# SELECT pg_walfile_name(pg_current_wal_lsn()); pg_walfile_name -------------------------- 00000001000000000000000F (1 row) postgres=# SELECT pg_walfile_name('0/10000000'); pg_walfile_name -------------------------- 00000001000000000000000F (1 row) |
配置归档
必须重启：
1 2 3 4 5 6 7 8 | wal_level='replica' archive_mode='on' archive_command='test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f' restore_command='cp /pg13/archive/%f %p' archive_timeout=10 select * from pg_settings where name in ('archive_mode','archive_command'); |
在生产环境，为了保证数据高可用性，通常需要设置归档，所谓的归档，其实就是把pg_wal里面的日志备份出来，当系统故障后可以通过归档的日志文件对数据进行恢复：
配置归档需要开启如下参数：
wal_level = replica （pg13默认已经开启replica）
该参数的可选的值有minimal，replica和logical，wal的级别依次增高，在wal的信息也越多。由于minimal这一级别的wal不包含从基础的备份和wal日志重建数据的足够信息，在该模式下，无法开启wal日志归档archive_mode = on
上述参数为on，表示打开归档备份,可选的参数为on,off,always 默认值为off，所以要手动打开archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
该参数的默认值是一个空字符串，他的值可以是一条shell命令或者一个复杂的shell脚本。在shell脚本或命令中可以用 “%p” 表示将要归档的wal文件包含完整路径的信息的文件名，用“%f” 代表不包含路径信息的wal文件的文件名archive_timeout表示只对完整的WAL段调用archive_command。因此，如果你的服务器只产生很少的WAL流量（或者它产生的空闲时间很短），则在事务完成和将其安全记录到归档存储之间可能会有很长的延迟。要限制可以保存的未归档数据的数量，可以设置archive_timeout强制服务器定期切换到新的WAL段文件。
请注意，由于强制切换而提前关闭的归档文件的长度仍然与完全完整的文件的长度相同。因此，使用非常短的archive_timeout是不明智的-它将使您的存档存储空间过大。一分钟左右的archive_timeout设置通常是合理的。如果你希望数据能被更快地从主服务器上复制下来，你应该考虑使用流复制而不是归档。如果指定值时没有单位，则以秒为单位。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。
该参数只能在postgresql.conf文件或服务器命令行中设置。
注意：wal_level和archive_mode参数修改都需要重新启动数据库才可以生效。而修改archive_command则不需要。所以一般配置新系统时，无论当时是否需要归档，这要建议将这两个参数开启
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | postgres=# select * from pg_settings where name in ('archive_mode','archive_command'); name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart -----------------+------------+------+-----------------------------+-------------------------------------------------------------------+------------+------------+---------+---------+---------+---------+-----------------+----------+-----------+------------+------------+----------------- archive_command | (disabled) | | Write-Ahead Log / Archiving | Sets the shell command that will be called to archive a WAL file. | | sighup | string | default | | | | | | | | f archive_mode | off | | Write-Ahead Log / Archiving | Allows archiving of WAL files using archive_command. | | postmaster | enum | default | | | {always,on,off} | off | off | | | f (2 rows) postgres=# alter system set archive_command='test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f'; ALTER SYSTEM postgres=# alter system set archive_mode='on'; ALTER SYSTEM postgres=# select * from pg_settings where name in ('archive_mode','archive_command'); name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart -----------------+------------+------+-----------------------------+-------------------------------------------------------------------+------------+------------+---------+---------+---------+---------+-----------------+----------+-----------+------------+------------+----------------- archive_command | (disabled) | | Write-Ahead Log / Archiving | Sets the shell command that will be called to archive a WAL file. | | sighup | string | default | | | | | | | | f archive_mode | off | | Write-Ahead Log / Archiving | Allows archiving of WAL files using archive_command. | | postmaster | enum | default | | | {always,on,off} | off | off | | | f (2 rows) postgres=# select * from pg_settings where name in ('archive_mode','archive_command'); FATAL: terminating connection due to administrator command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. psql (13.3, server 13.4 (Debian 13.4-1.pgdg100+1)) postgres=# select * from pg_settings where name in ('archive_mode','archive_command'); name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart -----------------+------------------------------------------------------------------+------+-----------------------------+-------------------------------------------------------------------+------------+------------+---------+--------------------+---------+---------+-----------------+----------+------------------------------------------------------------------+-----------------------------------------------+------------+----------------- archive_command | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f | | Write-Ahead Log / Archiving | Sets the shell command that will be called to archive a WAL file. | | sighup | string | configuration file | | | | | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f | /var/lib/postgresql/data/postgresql.auto.conf | 4 | f archive_mode | on | | Write-Ahead Log / Archiving | Allows archiving of WAL files using archive_command. | | postmaster | enum | configuration file | | | {always,on,off} | off | on | /var/lib/postgresql/data/postgresql.auto.conf | 5 | f (2 rows) postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/15EDA18 (1 row) root@lhrpg134:/var/lib/postgresql/data/pg_wal# ls -l total 32772 -rw------- 1 postgres postgres 16777216 Sep 23 11:14 000000010000000000000001 -rw------- 1 postgres postgres 16777216 Sep 23 11:14 000000010000000000000002 drwx------ 2 postgres postgres 4096 Sep 23 11:14 archive_status |
如果开启了归档，则在归档路径下的archive_status目录里，会有类似000000010000000000000002.ready和000000010000000000000003.done的文件。
.ready表示XLOG文件已写满，可以调用归档命令了，.done表示已归档完成。开启了归档后，只有归档成功的pg_xlog文件才会被清除。在每次归档命令被执行后，会触发清除标签的动作，在执行检查点时，也会触发清除归档标签文件的动作。
影响wal保存的最大个数的参数
checkpoint_segments和wal_keep_segments,checkpoint_completion_target
1 2 3 4 5 | 通常地说，WAL最大个数不超过： ( 2 + checkpoint_completion_target ) * checkpoint_segments + 1 在流复制环境下，WAL最大数不超过： wal_keep_segments + checkpoint_segments + 1 |
超过的话会自动清理。
默认的WAL segments为16M，这个参数在PG编译执行 ./configure 时，指定“–with-wal-segsize=target_value”参数设置。从PG 11开始，可以通过initdb 和 pg_resetwal 调整WAL文件大小，参考：https://www.xmmup.com/postgresql11xiugaiwal-segsizedaxiao.html
自动清理wal日志
一般来说，设置自动清理archive_log 可以在配置文件中添加：
1 | archive_cleanup_command = 'pg_archivecleanup archivelocation %r' |
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | postgres=# select * from pg_settings where name in ('archive_mode','archive_command','archive_cleanup_command'); name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart -------------------------+------------------------------------------------------------------+------+------------------------------------+----------------------------------------------------------------------+------------+------------+---------+--------------------+---------+---------+-----------------+----------+------------------------------------------------------------------+-----------------------------------------------+------------+----------------- archive_cleanup_command | | | Write-Ahead Log / Archive Recovery | Sets the shell command that will be executed at every restart point. | | sighup | string | default | | | | | | | | f archive_command | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f | | Write-Ahead Log / Archiving | Sets the shell command that will be called to archive a WAL file. | | sighup | string | configuration file | | | | | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f | /var/lib/postgresql/data/postgresql.auto.conf | 4 | f archive_mode | on | | Write-Ahead Log / Archiving | Allows archiving of WAL files using archive_command. | | postmaster | enum | configuration file | | | {always,on,off} | off | on | /var/lib/postgresql/data/postgresql.auto.conf | 5 | f (3 rows) postgres=# alter system set archive_cleanup_command='pg_archivecleanup /var/lib/postgresql/data/pg_wal %r'; ALTER SYSTEM postgres=# postgres=# select * from pg_settings where name in ('archive_mode','archive_command','archive_cleanup_command'); name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart -------------------------+------------------------------------------------------------------+------+------------------------------------+----------------------------------------------------------------------+------------+------------+---------+--------------------+---------+---------+-----------------+----------+------------------------------------------------------------------+-----------------------------------------------+------------+----------------- archive_cleanup_command | | | Write-Ahead Log / Archive Recovery | Sets the shell command that will be executed at every restart point. | | sighup | string | default | | | | | | | | f archive_command | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f | | Write-Ahead Log / Archiving | Sets the shell command that will be called to archive a WAL file. | | sighup | string | configuration file | | | | | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f | /var/lib/postgresql/data/postgresql.auto.conf | 4 | f archive_mode | on | | Write-Ahead Log / Archiving | Allows archiving of WAL files using archive_command. | | postmaster | enum | configuration file | | | {always,on,off} | off | on | /var/lib/postgresql/data/postgresql.auto.conf | 5 | f (3 rows) postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# select * from pg_settings where name in ('archive_mode','archive_command','archive_cleanup_command'); name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart -------------------------+------------------------------------------------------------------+------+------------------------------------+----------------------------------------------------------------------+------------+------------+---------+--------------------+---------+---------+-----------------+----------+------------------------------------------------------------------+-----------------------------------------------+------------+----------------- archive_cleanup_command | pg_archivecleanup /var/lib/postgresql/data/pg_wal %r | | Write-Ahead Log / Archive Recovery | Sets the shell command that will be executed at every restart point. | | sighup | string | configuration file | | | | | pg_archivecleanup /var/lib/postgresql/data/pg_wal %r | /var/lib/postgresql/data/postgresql.auto.conf | 6 | f archive_command | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f | | Write-Ahead Log / Archiving | Sets the shell command that will be called to archive a WAL file. | | sighup | string | configuration file | | | | | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f | /var/lib/postgresql/data/postgresql.auto.conf | 4 | f archive_mode | on | | Write-Ahead Log / Archiving | Allows archiving of WAL files using archive_command. | | postmaster | enum | configuration file | | | {always,on,off} | off | on | /var/lib/postgresql/data/postgresql.auto.conf | 5 | f (3 rows) postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/7A4B4900 (1 row) |
手动清理wal
通常情况下尽量让数据库自动清理，避免错误操作！！！
如果配置了archive_mode=on，但是没有配置archive_command，那么xlog文件会一直堆积（xlog写完后，会写.ready，但是由于没有配置archive_command，也就是说不会触发归档命令，所以一直都不会写 .done），所以xlog会一直不清理。
1)读取控制文件，找到能清理的xlog范围
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | root@lhrpg134:/var/lib/postgresql/data/pg_wal# pg_controldata $PGDATA pg_control version number: 1300 Catalog version number: 202007201 Database system identifier: 7010921335449399333 Database cluster state: in production pg_control last modified: Thu 23 Sep 2021 11:23:37 AM CST Latest checkpoint location: 0/5000098 Latest checkpoint's REDO location: 0/5000060 Latest checkpoint's REDO WAL file: 000000010000000000000005 Latest checkpoint's TimeLineID: 1 Latest checkpoint's PrevTimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0:492 Latest checkpoint's NextOID: 24600 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID: 479 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 492 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 1 Latest checkpoint's oldestCommitTsXid:0 Latest checkpoint's newestCommitTsXid:0 Time of latest checkpoint: Thu 23 Sep 2021 11:23:37 AM CST Fake LSN counter for unlogged rels: 0/3E8 Minimum recovery ending location: 0/0 Min recovery ending loc's timeline: 0 Backup start location: 0/0 Backup end location: 0/0 End-of-backup record required: no wal_level setting: replica wal_log_hints setting: off max_connections setting: 100 max_worker_processes setting: 8 max_wal_senders setting: 10 max_prepared_xacts setting: 0 max_locks_per_xact setting: 64 track_commit_timestamp setting: off Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Size of a large-object chunk: 2048 Date/time type storage: 64-bit integers Float8 argument passing: by value Data page checksum version: 0 Mock authentication nonce: b58d0eceb6deeda37ed13e7433ff4b50e9682139b47ba1c9d4567b97435fbc80 |
其中，“Latest checkpoint's REDO WAL file: 000000010000000000000005”之前的文件都可以清理。
2)通过pg_archivecleanup清理
1 | pg_archivecleanup -d $PGDATA/pg_wal 000000010000000000000005 |
脚本
1 2 3 4 5 6 7 8 | #!/bin/bash ARCHIVEDIR='/pgdata/archive' CHK_SAFE=$(find $ARCHIVEDIR -type f -mtime +3 -name '*backup' -printf '%f\n' | sort -r | head -1) cd $ARCHIVEDIR /usr/local/postgres/bin/pg_archivecleanup $ARCHIVEDIR $CHK_SAFE find $ARCHIVEDIR -type f -mtime +3 -a -name '*backup' -a ! -newer $CHKPOINT -delete |
pg的wal日志保存在数据库目录下的pg_wal/子目录
这个日志存在的目的是为了保证崩溃后的安全，如果系统崩溃，可以“重放”从最后一次检查点以来的日志项来恢复数据库的一致性。
但是也存在日志膨胀的问题
pg提供如下参数控制wal日志的大小
1 | max_wal_size = 1GB``min_wal_size = 80MB |
测试一下
1、查看当前pg_wal目录大小
1 2 3 4 | [postgres@mingfan pg_wal]$ pwd /usr/local/pgsql/data/pg_wal [postgres@mingfan pg_wal]$ du -sh 16M . |
2、插入可产生约2Gwal日志的数据
1 2 3 4 5 | create table t1(a int); insert into t1 values (generate_series(1,10000000)); insert into t1 values (generate_series(1,10000000)); insert into t1 values (generate_series(1,10000000)); insert into t1 values (generate_series(1,10000000)); |
3、查看当前pg_wal目录大小
1 2 | [postgres@mingfan pg_wal]$ du -sh 1.1G . |
基本符合预期，看下官方的介绍：
- max_wal_size (integer)
在自动 WAL 检查点之间允许 WAL 增长到的最大尺寸。这是一个软限制， 在特殊的情况下 WAL 尺寸可能会超过max_wal_size， 例如在重度负荷下、archive_command失败或者高的 wal_keep_segments设置。如果指定值时没有单位，则以兆字节为单位。默认为 1GB。增加这个参数 可能导致崩溃恢复所需的时间。这个参数只能在postgresql.conf 或者服务器命令行中设置。
- min_wal_size (integer)
只要 WAL 磁盘用量保持在这个设置之下，在检查点时旧的 WAL 文件总是 被回收以便未来使用，而不是直接被删除。这可以被用来确保有足够的 WAL 空间被保留来应付 WAL 使用的高峰，例如运行大型的批处理任务。 如果指定值时没有单位，则以兆字节为单位。默认是 80 MB。这个参数只能在postgresql.conf 或者服务器命令行中设置。
如何计算WAL文件名
我们知道由三部分组成，那么又是如何计算呢？公式如下:
1 | WAL segment file name = timelineId +(uint32)LSN−1 / (16M ∗ 256） + (uint32)(LSN − 1 / 16M) % 256 |
我们算一个试一试.查看当前LSN位置
1 2 3 4 5 | postgres=# select pg_current_wal_lsn(); pg_current_wal_lsn -------------------- 1/C469AA30 (1 row) |
这里的LSN是’ 1/C469AA30’ 我们转换为十进制数:
1 2 3 4 5 | postgres=# select x'1C469AA30'::bigint; int8 ------------ 7590226480 (1 row) |
利用公式计算:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | logSeg: postgres=# select ((7590226480 - 1) / (16 * 1024 * 1024 )) % 256 ; ?column? ---------- 196 (1 row) 196是十进制数 转换为16 进制为 c4 postgres=# select to_hex(196); to_hex -------- c4 (1 row) LogId: postgres=# select ((7590226480 - 1) / (16::bigint * 1024::bigint * 1024::bigint * 256::bigint) :: int8); ?column? ---------- 1 (1 row) 得出的LogId等于1 时间线ID: postgres@coredumped ~ pg_controldata|grep TimeLine Latest checkpoint's TimeLineID: 1 Latest checkpoint's PrevTimeLineID: 1 |
算出来的值与通过函数查询的一致:
1 2 3 4 5 | postgres=# select pg_walfile_name('1/C469AA30'); pg_walfile_name -------------------------- 0000000100000001000000C4 (1 row) |
计算WAL日志的大小
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | postgres=# select sum(size) from pg_ls_waldir(); sum ---------- 67108864 (1 row) postgres=# select * from pg_ls_waldir(); name | size | modification --------------------------+----------+------------------------ 0000000100000002000000B1 | 16777216 | 2021-09-23 15:58:28+08 0000000100000002000000E1 | 16777216 | 2021-07-27 17:34:26+08 0000000100000002000000AF | 16777216 | 2021-09-23 16:05:50+08 0000000100000002000000B0 | 16777216 | 2021-09-23 15:52:43+08 (4 rows) postgres=# select pg_ls_dir('/pg13/pgdata/pg_wal'); pg_ls_dir -------------------------- archive_status 0000000100000002000000B1 0000000100000002000000E1 0000000100000002000000AF 0000000100000002000000B0 (5 rows) |
查看wal生成频率和大小
–wal 文件生成数量
–linux ls --full-time stat filename
–pg_stat_file返回一个记录，其中包含
– 1 size 文件尺寸
– 2 access 最后访问时间戳(linux:最近访问) 、
– 3 modification 最后修改时间戳(linux:最近更改–) 、
– 4 change 最后文件状态改变时间戳（只支持 Unix 平台）(linux:最近改动) 、
– 5 creation 文件创建时间戳（只支持 Windows）
– 6 isdir 一个boolean指示它是否为目录 isdir
1 2 3 4 | – select * from pg_stat_file('/var/lib/postgresql/9.1/main/pg_xlog/0000000200000BBB000000A9'); – /var/lib/postgresql/9.1/main/pg_xlog – /var/log/postgresql – /mnt/nas_dbbackup/archivelog |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | with tmp_file as ( select t1.file, t1.file_ls, (pg_stat_file(t1.file)).size as size, (pg_stat_file(t1.file)).access as access, (pg_stat_file(t1.file)).modification as last_update_time, (pg_stat_file(t1.file)).change as change, (pg_stat_file(t1.file)).creation as creation, (pg_stat_file(t1.file)).isdir as isdir from (select dir||'/'||pg_ls_dir(t0.dir) as file, pg_ls_dir(t0.dir) as file_ls from ( select '/pg13/pgdata/pg_wal'::text as dir -- 需要修改这个物理路径 -- select '/mnt/nas_dbbackup/archivelog'::text as dir -- select setting as dir from pg_settings where name='log_directory' ) t0 ) t1 where 1=1 order by (pg_stat_file(file)).modification desc ) select to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd') as day_id, sum(case when date_part('hour',tf0.last_update_time) >=0 and date_part('hour',tf0.last_update_time) <24 then 1 else 0 end) as wal_num_all, sum(case when date_part('hour',tf0.last_update_time) >=0 and date_part('hour',tf0.last_update_time) <1 then 1 else 0 end) as wal_num_00_01, sum(case when date_part('hour',tf0.last_update_time) >=1 and date_part('hour',tf0.last_update_time) <2 then 1 else 0 end) as wal_num_01_02, sum(case when date_part('hour',tf0.last_update_time) >=2 and date_part('hour',tf0.last_update_time) <3 then 1 else 0 end) as wal_num_02_03, sum(case when date_part('hour',tf0.last_update_time) >=3 and date_part('hour',tf0.last_update_time) <4 then 1 else 0 end) as wal_num_03_04, sum(case when date_part('hour',tf0.last_update_time) >=4 and date_part('hour',tf0.last_update_time) <5 then 1 else 0 end) as wal_num_04_05, sum(case when date_part('hour',tf0.last_update_time) >=5 and date_part('hour',tf0.last_update_time) <6 then 1 else 0 end) as wal_num_05_06, sum(case when date_part('hour',tf0.last_update_time) >=6 and date_part('hour',tf0.last_update_time) <7 then 1 else 0 end) as wal_num_06_07, sum(case when date_part('hour',tf0.last_update_time) >=7 and date_part('hour',tf0.last_update_time) <8 then 1 else 0 end) as wal_num_07_08, sum(case when date_part('hour',tf0.last_update_time) >=8 and date_part('hour',tf0.last_update_time) <9 then 1 else 0 end) as wal_num_08_09, sum(case when date_part('hour',tf0.last_update_time) >=9 and date_part('hour',tf0.last_update_time) <10 then 1 else 0 end) as wal_num_09_10, sum(case when date_part('hour',tf0.last_update_time) >=10 and date_part('hour',tf0.last_update_time) <11 then 1 else 0 end) as wal_num_10_11, sum(case when date_part('hour',tf0.last_update_time) >=11 and date_part('hour',tf0.last_update_time) <12 then 1 else 0 end) as wal_num_11_12, sum(case when date_part('hour',tf0.last_update_time) >=12 and date_part('hour',tf0.last_update_time) <13 then 1 else 0 end) as wal_num_12_13, sum(case when date_part('hour',tf0.last_update_time) >=13 and date_part('hour',tf0.last_update_time) <14 then 1 else 0 end) as wal_num_13_14, sum(case when date_part('hour',tf0.last_update_time) >=14 and date_part('hour',tf0.last_update_time) <15 then 1 else 0 end) as wal_num_14_15, sum(case when date_part('hour',tf0.last_update_time) >=15 and date_part('hour',tf0.last_update_time) <16 then 1 else 0 end) as wal_num_15_16, sum(case when date_part('hour',tf0.last_update_time) >=16 and date_part('hour',tf0.last_update_time) <17 then 1 else 0 end) as wal_num_16_17, sum(case when date_part('hour',tf0.last_update_time) >=17 and date_part('hour',tf0.last_update_time) <18 then 1 else 0 end) as wal_num_17_18, sum(case when date_part('hour',tf0.last_update_time) >=18 and date_part('hour',tf0.last_update_time) <19 then 1 else 0 end) as wal_num_18_19, sum(case when date_part('hour',tf0.last_update_time) >=19 and date_part('hour',tf0.last_update_time) <20 then 1 else 0 end) as wal_num_19_20, sum(case when date_part('hour',tf0.last_update_time) >=20 and date_part('hour',tf0.last_update_time) <21 then 1 else 0 end) as wal_num_20_21, sum(case when date_part('hour',tf0.last_update_time) >=21 and date_part('hour',tf0.last_update_time) <22 then 1 else 0 end) as wal_num_21_22, sum(case when date_part('hour',tf0.last_update_time) >=22 and date_part('hour',tf0.last_update_time) <23 then 1 else 0 end) as wal_num_22_23, sum(case when date_part('hour',tf0.last_update_time) >=23 and date_part('hour',tf0.last_update_time) <24 then 1 else 0 end) as wal_num_23_24 from tmp_file tf0 where 1=1 and tf0.file_ls not in ('archive_status') group by to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd') order by to_char(date_trunc('day',tf0.last_update_time),'yyyymmdd') desc ; |
pg_archivecleanup
pg_archivecleanup — 清理PostgreSQL WAL 归档文件
大纲
pg_archivecleanup [option...] archivelocation oldestkeptwalfile
简介
pg_archivecleanup被设计用作 archive_cleanup_command在作为后备服务器运行（ 第 26.2 节）时来清理 WAL 文件归档。 pg_archivecleanup也可以被用作一个单独的程序来清理 WAL 文件归档。
要配置一个后备服务器以使用pg_archivecleanup，把下面 的内容放在postgresql.conf配置文件中：
1 | archive_cleanup_command = 'pg_archivecleanup archivelocation %r' |
其中archivelocation是要从中移除 WAL 段文件的目录。
当被用在archive_cleanup_command中时，所有逻辑上在 %r参数的值之前的 WAL 文件都将被从 archivelocation移除。这能最小化需要被保留的文件数量， 同时能保留崩溃后重启的能力。如果对于这台特定的后备服务器，archivelocation是一个短暂需要的区域，使用这个参数就是 合适的，但是当archivelocation要用作一个长期的 WAL 归档 区域或者当多个后备服务器正在从这个归档位置恢复时，使用这个参数就 不\合适。
当被用作一个单独的程序时，所有逻辑上在oldestkeptwalfile 之前的 WAL 文件将被从archivelocation中移除。在这种模式 中，如果指定了.partial或者.backup文件名，则 只有该文件前缀将被用作oldestkeptwalfile。这种对 .backup文件名的处理允许你移除所有在一个特定基础备份之前归 档的 WAL 文件而不出错。例如，下面的例子将移除所有比 WAL 文件名 000000010000003700000010老的文件：
1 2 3 4 5 | pg_archivecleanup -d archive 000000010000003700000010.00000020.backup pg_archivecleanup: keep WAL file "archive/000000010000003700000010" and later pg_archivecleanup: removing file "archive/00000001000000370000000F" pg_archivecleanup: removing file "archive/00000001000000370000000E" |
pg_archivecleanup假定 archivelocation是一个可读的目录并且对于服务器拥有者是可写的。
选项
pg_archivecleanup接受下列命令行参数：
-d在
stderr上打印很多调试日志输出。-n在
stdout上打印将被移除的文件的名字（执行一次演习）。-V--version打印pg_archivecleanup版本并退出。
-xextension提供一个扩展名，在决定所有的文件 是否应该被删除之前，将从文件名中剥离这个扩展名。这通常有助于清理已经 存储期间被压缩过并且被压缩程序增加了一个扩展名的归档。例如：
-x .gz。-?--help显示pg_archivecleanup命令行参数的帮助并退出。
环境
环境变量 PG_COLOR 指定是否在诊断消息中使用颜色。 可能的值是 always, auto 和 never。
注解
在作为一个单独的工具时，pg_archivecleanup 被设计为与PostgreSQL 8.0 及其后的版本一起工作。如果 作为一个归档清理命令使用，则需要和PostgreSQL 9.0 及 其后的版本一起工作。
pg_archivecleanup以 C 写成并且具有很容易修改的 源代码，其中有特别指定的区域用于修改以符合你的需要
示例
在 Linux 或者 Unix 系统上，你可能会用：
1 | archive_cleanup_command = 'pg_archivecleanup -d /mnt/standby/archive %r 2>>cleanup.log' |
其中归档目录位于后备服务器上，这样archive_command通过 NFS 来访问它，但是文件对于后备服务器来说是本地的。这将会
- 在
cleanup.log中产生调试输出 - 从归档目录中移除不再需要的文件
