PostgreSQL数据库WAL日志空间大小以及不清理的原因深入分析

1. 背景
很多初学者会对WAL日志占用多少空间比较疑惑，听网上的一些文章说是由max_wal_size来控制的，但发现很多时候WAL日志空间会超过这个设置的值，不知道为什么? 同时有时会发现WAL日志不清理了，占用空间在不停的增长，然后不知道为什么？看一些网上的文章，发现情况不是网上说的那种情况。中启乘数科技工程师在服务客户的工程师，遇到了导致WAL日志空间膨胀不清理的各种用分期，并进行了深入全面的分析，基本囊括了所有的导致WAL日志膨胀的各种原因。所以对于初学者来说不需要再看网上那些不全面的文章了，只看这篇文章就够了。

2. 决定WAL日志占用空间大小因素
控制WAL日志的数量由以下这三个参数控制：

• max_wal_size
• min_wal_size
• wal_keep_segments或wal_keep_size

注意：PostgreSQL13版本后，wal_keep_segments参数以及废弃了，由wal_keep_size替代此参数
很多人认为WAL占用的空间是由max_wal_size来控制的，这种认识是不全面的，下面我们详细讲解这几个参数的意思。
假设pg_wal下的文件为：

000000A7000000040000005A
000000A7000000040000005B
000000A7000000040000005C
000000A7000000040000005D
000000A7000000040000005E
000000A7000000040000005F
000000A70000000400000060
000000A70000000400000061
000000A70000000400000062
000000A70000000400000063
000000A70000000400000064

假设当前正在写的WAL文件为000000A70000000400000060，则wal_keep_segments控制000000A7000000040000005A到000000A70000000400000060的个数，
而min_wal_size控制000000A70000000400000060到000000A70000000400000064，即这一段至少要保留min_wal_size的WAL日志。
如果min_wal_size + wal_keep_segments 大于了max_wal_size，那么WAL日志空间至少也会占用：min_wal_size + wal_keep_segments。所以从这里可以看出，WAL占用的空间大小并不是完全由max_wal_size控制的，只有在min_wal_size + wal_keep_segments的值小于max_wal_size时，PostgreSQL才尽量保值WAL的空间不超过这个值。注意这里说的是尽量，原因是PostgreSQL是在做checkpoint时，把不需要的WAL日志给清理掉，但是如果数据库由很大的写，导致还没有来得及做checkpoint时，这时WAL日志占用的空间会超过max_wal_size设置的值。
如果min_wal_size + wal_keep_segments小于max_wal_size，那么WAL日志空间尽量保持不超过max_wal_size参数设置的值，当然每次checkpoint清理时，会保持WAL的日志空间不会低于min_wal_size + wal_keep_segments的值。
所以从这个原理来说：min_wal_size不需要设置太大，生产库只需要为1G左右大小时就够用了，不需要太大。
而为了防止备库同步失败，应该设置一个较大的wal_keep_segments，WAL文件为16M大小，可把wal_keep_segments设置为500或更大。
max_wal_size比 min_wal_size + wal_keep_segments略大一点就可以了。
实际上参数max_wal_size主要时为了控制checkpoint发生的频繁程度：

target = (double) ConvertToXSegs(max_wal_size_mb) / (2.0 + CheckPointCompletionTarget);

如果checkpoint_completion_target设置为0.5时，则每写了 max_wal_size/2.5 的WAL日志时，就会发送一次checkpoint。
checkpoint_completion_target的范围为0~1，那么结果就是写的WAL的日志量超过: max_wal_size的1/3～1/2时，就会发生一次checkpoint。

3. 导致WAL日志空间膨胀的原因
3.1 长事务
数据库中如果有长事务，PostgreSQL数据库对于这个长事务开始后产生的所有WAL日志都不会清理。
select pid,usename, xact_start from pg_stat_activity where now() - xact_start > interval ‘8 hours’;
下面时监控超过8个小时的长事务的SQL:

select pid,usename, xact_start from pg_stat_activity where now() - xact_start > interval '8 hours';

更甚的情况是用户有“Idle in transaction”的连接，即一个连接开启了事务，然后什么事情也不干，一直空闲着，用下面的SQL查询“Idle in transaction”的连接：

select pid,client_addr,usename,datname, xact_start,state from pg_stat_activity where state not in ('active','idle') order by  xact_start;

如果有长时间的“idle in transaction”的连接，需要kill掉，kill的方法是select pg_terminate_backend(3415)，其中3415是这个连接的pid。当然kill掉之前需要调查这中长时间的“idle in transaction”的连接是如何产生的。
对于一些应用产生的“idle in transaction”随便kill掉可能会导致应用出现问题，需要注意。

3.2 废弃的复制槽(replication slots)
复制槽是用来保证逻辑复制或物理复制需要的WAL日志不会被清理掉。如果使用了逻辑复制或物理复制使用的复制槽，而这些逻辑复制或物理因为某些原因停掉了，那么会导致这些复制槽会把WAL的日志保留着。如果是逻辑复制或物理复制停掉了，则需要尽快把这些逻辑复制或物理复制启动起来，否则很容易把主库的空间撑满。
用下面的SQL查询复制槽：

SELECT slot_name, slot_type, database, xmin,active,active_pid FROM pg_replication_slots ORDER BY age(xmin) DESC;

如果上面结果某一行中active为空，说明复制停掉了，需要检查。
如果逻辑复制或物理复制停掉了，但一时半会还启动不起来，而主库的空间又要慢了，这时可以强制把复制槽给删除掉，注意删除掉逻辑复制的复制槽后，逻辑复制的同步就废弃了,后续的恢复需要做全量的数据恢复。所以这是逻辑复制的一个大缺点。逻辑复制还有一个大缺点是主备库切换后，逻辑复制槽也废掉了。如果想避免这个问题，可以使用中启乘数科技的产品CMiner，具体请见CMiner介绍页面。

3.3 废弃的未提交两阶段事务(prepared transactions)
未提交的两阶段事务(prepared transactions)，会让数据库保留从这个事务开始时WAL日志，导致WAL日志空间膨胀。如果应用使用了两阶段事务，理论上两阶段事务的提交和回滚时需要由这个应用来提交或回滚的，而如果这个应用出现的问题，一直没有对其创建的两阶段事务进行提交或回滚，则会产生此问题。
查询两阶段事务的语句：

SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

如果发现某个两阶段事务长期存在（如数个小时），则可能出现了这个问题，如下所示：

postgres=# SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;
    gid     |           prepared            |  owner   | database | xmin
------------+-------------------------------+----------+----------+-------
 osdba_pxid | 2019-01-10 10:27:15.441513+08 | codetest | postgres | 13843
(1 row)

如果发现prepared列的时间是一个之前很久的时间，基本可以断定这是一个废弃的两阶端事务。这时我们可以手工提交或回滚这个事务：
提交的方法：

commit prepared 'osdba_pxid';

回滚的方法：

roback prepared 'osdba_pxid';

注意需要调查两阶端事务产生的原因以及确定应该是提交还是回滚，否则可能造出数据的丢失。

3.4 主库的WAL日志的归档未成功
主库不会清理未归档的WAL日志，从而导致了主库的WAL日志膨胀。
主库开启了归档，但是归档命令一直没有执行成功，或归档命令hang住，也可能是归档命令执行的太慢，来不及归档。
检查主库的日志，看看释放又归档失败的日志。也可以到pg_wal/archive_status目录下，看看是否大量的WAL日志未归档成功。

3.5 备库开启的HOT_STANDBY_FEEDBACK
如果只读备库开启了HOT_STANDBY_FEEDBACK，备库上如果有个长时间运行的查询正在执行，备库会通知主库这个备库上长时间查询开始启动后的WAL日志都不能被清理掉，从而导致主库的WAL日志膨胀。这种情况导致主库WAL日志膨胀出现的概率很低。
有人问为什么要有HOT_STANDBY_FEEDBACK这种机制呢？
原因是如果没有这种机制，主库执行UPDATE并VACUUM了，由于主库上已经不存在使用被更新元组的事务，VACUUM 会将这些元组清理掉，当 备库回放到 VACUUM 对应的日志时，检测到当前 VACUUM 清理的元组仍然被这个长时间的查询使用，则会阻塞备库的WAL日志应用，导致备库有很大的延迟。为了避免备库的延迟，PostgreSQL又提供了参数max_standby_streaming_delay(默认30s)，让应用WAL的进程在等待此参数指定的时间后后，若长时间SQL还没有执行完，则直接取消长时间SQL的运行，并在日志种打印如下异常信息：

FATAL: terminating connection due to conflict with recovery  
DETAIL: User query might have needed to see row versions that must be removed. 
HINT: In a moment you should be able to reconnect to the database and repeat your 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.

那么这样就导致了备库上无法运行长时间的SQL。为了解决此问题，备库把参数HOT_STANDBY_FEEDBACK设置为on后，就将 备库种长时间运行的SQL的最小活跃事务ID定期告知主库，使得主库在执行 VACUUM时对这些事务还需要的数据手下留情，不进行清理。

转载自:https://zhuanlan.zhihu.com/p/622585294