PostgreSQL 逻辑复制异常引发Pg_wal目录膨胀一例__前几天一位社区朋友咨询一个PostgreSQL的WAL文件膨胀案例，有个生产库最近几天pg_wal目录的WAL文件爆涨到了7万多个，把硬盘空间撑满，造成数据库故障。为了快速恢复，这位朋友删除了pg_wal目录下10天前的WAL文件，将硬盘空间使用率降下来，使得数据库恢复，但pg_wal目录下的WAL文件依然涨得很快_

PG中文社区 /

mdi-home

首页

社区新闻

中文文档

加入ACE

mdi-home

首页

mdi-chat-processing

社区新闻

mdi-book-open-variant

中文文档

mdi-account-multiple-check

加入ACE

mdi-file-multiple-outline

相关资料

mdi-blank

mdi-exit-to-app

退出账号

PostgreSQL 逻辑复制异常引发Pg_wal目录膨胀一例

原作者：谭峰　　创作时间：2020-09-07 21:49:25+08　　

wangliyun 发布于2020-09-08 08:00:00　　

评论: 2　　

顶: 434　

踩: 464　

### **作者简介**

#### 谭峰

#### 网名francs，PostgreSQL中文社区委员，《PostgreSQL实战》作者之一，《PostgreSQL 9 Administration Cookbook》译者之一，致力于PostgreSQL技术分享，博客 https://postgres.fun，三墩IT人。

### **故障现象**

#### 前几天一位社区朋友咨询一个PostgreSQL的WAL文件膨胀案例，有个生产库最近几天pg_wal目录的WAL文件爆涨到了7万多个，把硬盘空间撑满，造成数据库故障。
 
#### 为了快速恢复，这位朋友删除了pg_wal目录下10天前的WAL文件，将硬盘空间使用率降下来，使得数据库恢复，但pg_wal目录下的WAL文件依然涨得很快。

#### 数据库环境信息如下:
```
数据库版本: PostgreSQL 11.2
数据库大小: 大于1TB
操作系统: CentOS 7.4
硬件环境: HPE DL560 gen10 + 全闪存(3par 8440)
其它信息: 单实例，配置了逻辑复制
```

### **排查过程**

#### 首先，pg_wal目录下的wal文件为在线WAL日志，不能删除，删除后数据库大概率会故障，这位朋友删除了pg_wal目录10天前的WAL文件,数据库居然还活着，我表示非常吃惊。
 
#### 既然数据库还活着，已提醒他做好数据库备份，以防万一。
 
#### 关于WAL文件膨胀，我们的排查步骤如下:
#### 1、首先排查数据库pg_log日志文件，没有发现有价值的信息。
#### 2、查看数据库活动会话，排查是否有长事务和慢查询，没有发现长事务和慢查询。
#### 3、查看数据库主机性能，CPU、内存使用率正常，数据库负载正常。
#### 4、查看数据库归档情况，发现数据库归档正常，归档相关参数如下:
```
archive_mode = on    # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp --backup %p  /log/archive_log/%f'    # command to use to archive a logfile segment
```
#### 5、查看WAL相关参数设置，如下:
```
wal_keep_segments = 0  # in logfile segments; 0 disables
checkpoint_timeout = 5min    # range 30s-1d
```
#### 发现 wal_keep_segments 没有设置，并且checkpoint_timeout设置过小。于是建议将wal_keep_segments调整为2048，checkpoint_timeout调整为30分钟。
 
#### 这两个参数设置后只需执行 pg_ctl reload生效，不需要重启数据库，操作前做好数据库备份。
 
#### 当晚重设了这两个参数并执行checkpoint操作，pg_wal目录下的wal文件依然没有下降，还在上涨。
 
#### 6、由于部署了逻辑复制，查看复制槽情况，查询 pg_replication_slot 视图，如下:

![CENTER_PostgreSQL_Community]( /images/news/2020/20200907_2158_图片1--2020.9_.7_.png)

#### 发现有两个逻辑复制槽的active状态为f，并且active_pid为空，这位朋友怀疑与这两个复制槽有关，开始时我不确认是这个问题（事实证明我这个是很大的误判）。
 
#### 当天晚上，朋友申请了维护窗口，删除了这两个复制槽，大概半小时后，发现pg_wal目录下的文件数降下来了，从原来的7万多下降到了3052个，果然是由这两个复制槽引起！
 
#### 困扰了我俩几天的问题终于解决了，很是高兴，在这个故障的处理上我出现了很大的误判，逻辑复制的生产运维经验还需积累。
 
#### 接下来计划在测试环境模拟这个故障现象，加深理解。

### **模拟WAL目录膨胀**

#### 为了演示方便，在笔记本上的两台虚机上演示，数据库版本为 PostgreSQL 13 Beta1，生产库版本为 PostgreSQL 11.2，尽管版本不同，但并不影响这个模拟测试。
 
#### 思路如下:
#### 1、部署一套逻辑复制环境，同时把源库的wal_keep_segments参数设置得足够低。
#### 2、将目标库的订阅DISABLE。
#### 3、对源库上的逻辑复制表进行压力测试，观察源库上pg_wal目录下的WAL文件是否会膨胀。

### **环境规划**

#### 环境规划，如下:

![CENTER_PostgreSQL_Community]( /images/news/2020/20200907_2159_图片2--2020.9_.7_.png)

### **环境准备**

#### 源库创建测试表并插入测试数据，如下:
```
mydb=> CREATE TABLE user1 (userid int4, username character varying(32),regtime timestamp without time zone);
CREATE TABLE

mydb=> INSERT INTO user1 (userid,username,regtime) SELECT n, 'user' || n, now() FROM generate_series(1,10000000) n;
INSERT 0 10000000

mydb=> ALTER TABLE user1 ADD PRIMARY KEY(userid);
ALTER TABLE
```
#### 源库设置wal_keep_segments参数值为4，如下:
```
mydb=> show wal_keep_segments ;
 wal_keep_segments
-------------------
 4
(1 row)
```
#### 查看pg_wal目录下的WAL文件数量，如下:
```
[pg13@ydtf01 pg_wal]$ ls $PGDATA/pg_wal | wc -l
57
```
#### 目标库上创建表结构，如下:
```
mydb=> CREATE TABLE user1 (userid int4, username character varying(32),regtime timestamp without time zone);
CREATE TABLE

mydb=> ALTER TABLE user1 ADD PRIMARY KEY(userid);
ALTER TABLE
```
#### 源库上创建发布，如下:
```
mydb=> CREATE PUBLICATION pub_user1 FOR TABLE user1 ;
CREATE PUBLICATION
```
#### 目标库上创建订阅，如下:
```
CREATE SUBSCRIPTION sub_user1 CONNECTION 'host=192.168.2.11 port=1922 dbname=mydb user=repuser' PUBLICATION pub_user1;
```
#### 注意配置好源库的pg_hba.conf和.pgpass文件，否则创建订阅会报相关的连接不上错误，发现user1表数据已同步到目标库。

### **源库压力测试**

UPDATE user1 SET username='updated'||:v_id  WHERE userid=:v_id
```
#### 执行pgbench压力测试前，查看pg_wal目录下的WAL文件数量，如下:
```
[pg13@ydtf01 pg_wal]$ ls $PGDATA/pg_wal | wc -l
57
```
#### 在源库上进行 pgbench 压力测试，如下:
```
pgbench -n -Mprepared -c 4 -j 2 -T 120 -U pguser mydb -f tran1.sql > tran1.out 2>&1 &
```
#### 源库监控 pg_wal目录WAL文件情况，如下:
```
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
57
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
57
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
69
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
69
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
73
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
80
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
81
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
86
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
88
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
91
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
91
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
95
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
96
[pg13@ydtf01 ~]$ ls $PGDATA/pg_wal| wc -l
```
#### pgbench执行过程中WAL文件一直在增长，pgbench停止后WAL文件不再增长，可以预见当目标库的订阅DISABLE掉后，若不干预，主库上的WAL文件将会一直增长下去。
 
#### 以上成功模拟了故障现象。

### **应对措施**

#### 1 、生产库上如果启用了逻辑复制或复制槽，需监控源库的复制槽运行状态，可监控源库的 `pg_replication_slots`视图，若出现active=f的复制槽需人工介入。
#### 2、监控生产库上 pg_wal目录的WAL文件增长情况，若短时间内增长较快，需人工介入，进行原因分析。
#### 3、监控生产库上的长事务和慢查询，通过查询`pg_stat_activity`视图获取。
#### 4、最重要的一条: 任何时候不要删除$PGDATA/pg_wal目录下的WAL日志文件。

#### 阅读原文
#### https://postgres.fun/20200628070600.html

![CENTER_PostgreSQL_Community]( /images/news/2020/20200907_2204_最新公众号底部图片.jpg)

评论:2　　

顶: 434　

踩: 464　

请在登录后发表评论，否则无法保存。

1# __ xiaowu 回答于 2024-04-24 10:45:32+08

泰戈尔诗集读后感:https://www.nanss.com/xuexi/20476.html 洗车店成本:https://www.nanss.com/gongzuo/20679.html 梦回大唐作文:https://www.nanss.com/xuexi/xiezuo/20735.html c语言学习心得:https://www.nanss.com/xuexi/18801.html 寒假总结:https://www.nanss.com/xuexi/20270.html 乡镇空缺生意:https://www.nanss.com/shenghuo/20675.html 4k分辨率是多少:https://www.nanss.com/wenti/19465.html 岗位调动申请书:https://www.nanss.com/gongzuo/20458.html 校长个人总结:https://www.nanss.com/gongzuo/20619.html 做家务的感受:https://www.nanss.com/xuexi/19669.html 表达方式有哪些:https://www.nanss.com/xuexi/20152.html 84的主要成分是什么:https://www.nanss.com/shenghuo/18329.html 中国三八妇女节的来历简介:https://www.nanss.com/shenghuo/19384.html 酒店工作总结:https://www.nanss.com/gongzuo/19338.html 小学教务工作总结:https://www.nanss.com/gongzuo/20624.html 游戏英语名字:https://www.nanss.com/mingcheng/20622.html 幼儿园教师工作计划:https://www.nanss.com/gongzuo/20341.html 放烟花文案发朋友圈:https://www.nanss.com/wenan/20230.html 有钱暴富发财好运名字:https://www.nanss.com/mingcheng/20261.html 送东阳马生序原文翻译:https://www.nanss.com/xuexi/20315.html 七夕节的来历和传说故事:https://www.nanss.com/shenghuo/18299.html 人事管理工作总结:https://www.nanss.com/gongzuo/20100.html 初二入团申请书:https://www.nanss.com/xuexi/18613.html 教研员工作总结:https://www.nanss.com/gongzuo/19326.html 师德工作总结:https://www.nanss.com/gongzuo/20559.html 励志电影排行榜前十名:https://www.nanss.com/shenghuo/20563.html 挑大梁什么意思:https://www.nanss.com/shenghuo/20163.html 高二语文教学总结:https://www.nanss.com/gongzuo/19258.html 东海龙王叫什么名字:https://www.nanss.com/wenti/18930.html 每日一签语录:https://www.nanss.com/yulu/20484.html

2# __ airtmis 回答于 2020-11-13 10:46:06+08

如何可以联系上谭峰先生