postgres 的WAL日志膨胀的几种原因
一.长事务
数据库中如果有长事务，PostgreSQL数据库对于这个长事务开始后产生的所有WAL日志都不会清理。
二.废弃的复制槽(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为空，说明复制停掉了，需要检查。
如果逻辑复制或物理复制停掉了，但一时半会还启动不起来，而主库的空间又要慢了，这时可以强制把复制槽给删除掉，注意删除掉逻辑复制的复制槽后，逻辑复制的同步就废弃了,后续的恢复需要做全量的数据恢复。所以这是逻辑复制的一个大缺点。逻辑复制还有一个大缺点是主备库切换后，逻辑复制槽也废掉了。
三.废弃的未提交两阶段事务(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';
注意需要调查两阶端事务产生的原因以及确定应该是提交还是回滚，否则可能造出数据的丢失。
四.主库的WAL日志的归档未成功
主库不会清理未归档的WAL日志，从而导致了主库的WAL日志膨胀。
主库开启了归档，但是归档命令一直没有执行成功，或归档命令hang住，也可能是归档命令执行的太慢，来不及归档。
检查主库的日志，看看释放又归档失败的日志。也可以到pg_wal/archive_status目录下，看看是否大量的WAL日志未归档成功。
五. 备库开启的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时对这些事务还需要的数据手下留情，不进行清理。
参考资料: PostgreSQL数据库WAL日志空间大小以及不清理的原因深入分析
原文地址：https://blog.csdn.net/qq_33253054/article/details/140561849
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！