dump的文件查看pg_PostgreSQL 逻辑复制异常引发Pg_wal目录WAL文件膨胀一例

拼搏现实的明天。 2023-01-01 09:49 214阅读 0赞

![cb88cc2627dbdf50b6925e400aba2598.png][]

原文: https://postgres.fun/20200628070600.html

## 故障现象 ##

前几天一位社区朋友咨询一个PostgreSQL的WAL文件膨胀案例，他们有个生产库最近几天`pg_wal`目录的WAL文件爆涨到了7万多个，把硬盘空间撑满，造成数据库故障无法访问。

为了应急，这位朋友删除了`pg_wal`目录下十天前的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 视图，如下:

![e5433073e27843c17b2fd1ba2de73c7d.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文件是否会膨胀。

## 环境规划 ##

环境规划，如下:

![2c3e332bd619896c3cb4dc98de1dd0f2.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表数据已同步到目标库。

## 源库压力测试 ##

对源库进行压力测试，并将目标库的sub\_user1订阅DISABLE掉，看看是否能模拟源库WAL膨胀。

源库查询逻辑复制槽 sub\_user1 的初始状态，如下:

注意 active=t, active\_pid=84420。

目标库上将 sub\_user1 订阅DISABLE，如下:

mydb=# ALTER SUBSCRIPTION sub_user1 DISABLE;
    ALTER SUBSCRIPTION

再次在源库上查询 pg\_replication\_slots 视图，验证下:

此时active=f,active\_pid为空。

在源库上编写 tran1.sql 脚本，如下:

set v_id random(1,10000000)
    
    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
    97

`pgbench`执行过程中WAL文件一直在增长，`pgbench`停止后WAL文件不再增长，可以预见当目标库的订阅DISABLE掉后，若不干预，主库上的WAL文件将会一直增长下去。

以上成功模拟了故障现象。

## 应对措施 ##

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

## 参考 ##

*  pg\_wal is too big… what’s going on?
 *  PostgreSQL 13: 逻辑复制支持分区表
 *  pg\_replication\_slots
 *  pg\_subscription

[cb88cc2627dbdf50b6925e400aba2598.png]: /images/20221120/d7e41f6b1f4e44049848ec5936a80798.png
[e5433073e27843c17b2fd1ba2de73c7d.png]: /images/20221120/e4cda9457d1b440a93fb998312fe402e.png
[2c3e332bd619896c3cb4dc98de1dd0f2.png]: https://img-blog.csdnimg.cn/img_convert/2c3e332bd619896c3cb4dc98de1dd0f2.png