直播+吐槽: 看看你的PG有没有被注水? 聊聊孤儿文件

本期有直播:

文中参考文档点击阅读原文打开, 同时推荐2个学习环境: 

1、懒人Docker镜像, 已打包200+插件:《最好的PostgreSQL学习镜像》

2、有web浏览器就能用的云起实验室: 《免费体验PolarDB开源数据库》

3、PolarDB开源数据库内核、最佳实践等学习图谱:  https://www.aliyun.com/database/openpolardb/activity 

第100期吐槽:PG数据库被注水了?孤儿文件的形成、查找和清理

之前吐槽过PG的某些文件删了还不报错, 或者重启后会回放checkpoint以来被修改的page, 而不是整个数据文件, 导致数据库出现错乱. 详见: 《DB吐槽大会,第98期 - 文件删了不报错还能启动、能查询! PG有隐患》

今天这篇我想吐槽的是PG也可能会多出一些孤儿文件. 放着占用空间, 浪费宝贵的存储资源, 同时全量备份时还浪费备份时间、备份带宽消耗、备份的存储空间等.

孤儿文件的产生

内存泄露是使用内存过程中忘记释放不再需要的内存空间造成没有被引用的内存片段的占用, 导致内存浪费. 孤儿文件, 有点像内存泄露, 但是产生孤儿文件的原因比较多, 大多数可能是因为数据库崩溃时有未完成的事务, 事务中包括DDL以及DDL对应有大量的导入. 当数据库重启后, pg_class等原数据会自动会滚, 但是对应的数据文件确没有被清理.

例如:

• pg_dump 逻辑数据导入过程中, 数据库崩溃.

• 在一个事务中创建一张新表, 并写入大量数据, 然后在提交这样的用法. 但是在事务提交前, 数据库崩溃了.

其实数据库崩溃再启动时, PG会自动释放临时空间、unlogged table 的文件也会自动清理. 原因是这些清理比较简单, 临时空间位置固定, unlogged table的数据文件则有_init后缀标记.

但是持久化数据表, 崩溃后, pg_class.oid 对应的记录其实是未提交状态, 不容易查询并清理.

复现方法

会话1:

postgres=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
            383  
(1 row)  
  postgres=# begin;  
BEGIN  
postgres=*# create table b(id int);  
CREATE TABLE  
postgres=*# insert into b select generate_series(1,30000000);  
INSERT 0 30000000

另外开启一个shell去kill

 kill -n 9 383

此时b表的数据库文件就是孤儿文件, 重启数据库也存在. 但是b表其实不存在了.

postgres=# \dt  
        List of relations  
 Schema | Name | Type  |  Owner     
--------+------+-------+----------  
 public | a    | table | postgres  
 public | tbl1 | table | postgres  
(2 rows)

重启数据库这些文件依旧存在, 成为孤儿文件/失联文件.

发现和清理方法

网上也有热心网友写过孤儿文件发现方法, 如下文, 但是这篇文章存在一些问题, 作者写的时候可能没有展开, 只是给出了一个简单示例.

https://www.cnblogs.com/abclife/p/13948101.html

这篇文章提供的获得孤儿文件的SQL如下:

postgres=# select * from pg_ls_dir ( '/data/pgdata/11/data/base/75062' ) as file where file ~ '^[0-9]*$' and file::text not in (select oid::text from pg_class );

以上获得孤儿文件的SQL有如下问题:

1、千万不要直接用pg_class.oid来对照文件名是否存在, 因为在进行table rewrite后(例如vacuum full, cluster, 以及某些导致表重写的alter table操作), filenode会变, 变更后那就匹配不上oid了, 按以上SQL是可以被查出来的, 导致文件被误删. 正确的方法是使用 pg_relation_filenode(pg_class.oid)

例如:

postgres=# select oid from pg_class where relname='a';
  oid  
-------
 16738
(1 row)
postgres=# select pg_relation_filenode(16738) ;
 pg_relation_filenode 
----------------------
                16738
(1 row)
-- vacuum full 后 表a的oid没有变化, 但是文件名已经变了.   
postgres=# vacuum full a;
VACUUM
postgres=# select oid from pg_class where relname='a';
  oid  
-------
 16738
(1 row)
postgres=# select pg_relation_filenode(16738) ;
 pg_relation_filenode 
----------------------
                24925
(1 row)

2、当数据文件大于1G时会新增 .N 后缀的文件, 也需要找出来. 以上SQL没有考虑这个.

3、孤儿数据文件还有对应的_init, _fsm, _vm后缀的文件, 也需要清理. 以上SQL没有考虑这个.

最后补充一下, 为了防止误删, 还可以再用文件的修改和访问时间作为辅助判断, 一般孤儿文件可能是许久没有访问、修改过的.

我写了一个比较严谨的找出所有孤儿文件的inline code:

连到实例的每个数据库去执行如下SQL: ( 除了 template0 库 )

do language plpgsql $$  
declare  
  loc text;  
  dbo text;  
  ofile text;  
  ofiles text;  
  const text := '/PG_14_202107181';   -- 这个值和版本有关, 包括 大版本, catalog version.  
begin  
  select oid::text into dbo from pg_database where datname = current_database() ;  
    for loc in select case spcname when 'pg_default' then './base' else pg_tablespace_location(oid)||const end case from pg_tablespace where spcname <> 'pg_global' and oid in (select oid from pg_tablespace)   
  loop  
    for ofile in select file from pg_ls_dir(format('%s/%s', loc, dbo)) as file where file ~ '^[0-9]*$' and file::text not in ( select pg_relation_filenode(oid)::text from pg_class where pg_relation_filenode(oid)::text is not null )   
    loop  
      for ofiles in select file from pg_ls_dir(format('%s/%s', loc, dbo)) as file where file = ofile or file ~ ('^'||ofile||'_') or file ~ ('^'||ofile||'\.')    
      loop    
        raise notice 'orphaned file: %/%/%', loc, dbo, ofiles ;   
        -- datafile 以及带后缀的: 当大于segemnt_size(默认1GB)时加后缀的文件 .N 、freespace map文件 _fsm, visibility map文件 _vm, 和unlogged table对应的文件 _init     
      end loop;  
    end loop;  
  end loop;  
end;  
$$;

返回类似这样的结果

NOTICE:  orphaned file: ./base/13757/123_init
NOTICE:  orphaned file: ./base/13757/123
NOTICE:  orphaned file: ./base/13757/16741_fsm
NOTICE:  orphaned file: ./base/13757/16741
NOTICE:  orphaned file: ./base/13757/16741.1
NOTICE:  orphaned file: /data/tbs1/PG_14_202107181/13757/123.1
NOTICE:  orphaned file: /data/tbs1/PG_14_202107181/13757/123_fsm
NOTICE:  orphaned file: /data/tbs1/PG_14_202107181/13757/123
DO

注意:

即使使用这个inline code的方法, 依旧存在风险: 例如 正在创建(事务中)的对象, 文件存在了, 但是别人pg_class表里还看不到记录, 所以这些文件也会被当成孤儿文件被打印出来, 导致误清理. 此时可以使用lsof再看看孤儿文件是不是有被进程打开?

lsof |grep 孤儿文件

查看所有postgres及子进程打开的文件:

lsof -p PID,$(pgrep -P PID | tr '\n' ',')     
  其中PID为postgres主进程PID      
  查询postgres主进程PID:  
head -n 1 $PGDATA/postmaster.pid  
7  
  OR  
  ps -ewf|grep postgres  
root         1     0  0 08:28 pts/0    00:00:00 su - postgres -c /usr/lib/postgresql/14/bin/postgres -D "/var/lib/postgresql/14/pgdata"  
postgres     7     1  0 08:28 ?        00:00:00 /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresql/14/pgdata  
postgres     9     7  0 08:28 ?        00:00:00 postgres: logger   
postgres    11     7  0 08:28 ?        00:00:00 postgres: checkpointer   
postgres    12     7  0 08:28 ?        00:00:00 postgres: background writer   
postgres    13     7  0 08:28 ?        00:00:00 postgres: walwriter   
postgres    14     7  0 08:28 ?        00:00:00 postgres: autovacuum launcher   
postgres    15     7  0 08:28 ?        00:00:00 postgres: stats collector   
postgres    16     7  0 08:28 ?        00:00:00 postgres: logical replication launcher   
postgres    70     7  0 08:36 ?        00:00:00 postgres: postgres postgres [local] idle  
root       124    17  0 08:41 pts/1    00:00:00 grep postgres  
    lsof -p 7,$(pgrep -P 7 | tr '\n' ',')  
...

所以最好在没有业务连在上面时进行孤儿文件的查找, 或者 先堵塞所有DDL和DCL, 然后进行孤儿文件的查找.

最后, D-Smart也提供了工具清理孤儿文件, 参考:

• https://www.modb.pro/db/1737302971619303424

再次提醒: 删除数据文件一定要谨慎.

本文实验可以在我提供的两个docker image中完成:

• 《2023-PostgreSQL Docker镜像学习环境 ARM64版, 已集成热门插件和工具》

• 《2023-PostgreSQL Docker镜像学习环境 AMD64版, 已集成热门插件和工具》

本期彩蛋-下周六长沙有数据库沙龙，欢迎来现场参加

文章中的参考文档请点击阅读原文获得. 

欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.  
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号: