PG孤儿文件清理

原创岳麓丹枫 2025-11-28

413

背景
孤儿文件产生常见原因
分析

检查业务库占用
检查最大的表占用
从操作系统层面检查文件占用

pg_orphan 插件

编译安装插件
示例

会话 1
会话 2
会话 1
会话 2

背景

某局点嵌入式Linux环境, $PGDATA 占用空间占用较大，分析发现是孤儿文件导致空间占用

孤儿文件产生常见原因

孤儿文件通常产生于PG崩溃（OOM、或者pid被暴力kill -9 杀掉等）
https://cloud.tencent.com.cn/developer/article/2374846

分析

检查业务库占用

业务库 nddb 占用 200 多 M, 远不到 800 多 M

检查最大的表占用

最大的表占用也只有 180M 多

从操作系统层面检查文件占用

发现是 $PGDATA/base/ 下面存在几个 100 多 M 的文件(文件名是数字)
根据文件名去 pg_class 表中查询, 根据

select * from pg_class where relfilenode='文件名';

发现只有一张大文件可以过滤到, 其他几个大文件都过滤不到, 然后基于 GPT 写了两段脚本, 过滤
$PGDATA/base/业务库/目录下的大于 10M 的文件,并且只要纯数字的文件, 然后根据该文件名去 pg_class 中根据 relfilenode 进行过滤, 如果过滤不到则将其删除掉.
脚本内容如下:
● 工具性函数

-- 创建函数来查找未使用的relfilenode文件
CREATE OR REPLACE FUNCTION find_and_report_unused_relfilenodes(base_directory TEXT)
RETURNS TABLE(filename TEXT, is_used BOOLEAN, reference_count BIGINT) AS $$
DECLARE
    file_record RECORD;
    file_path TEXT;
    file_name TEXT;
    count_result BIGINT;
BEGIN
    -- 注意：这个函数需要配合shell脚本来实际删除文件
    -- 这里只是报告哪些文件是未使用的
    
    -- 在实际应用中，你可能需要使用外部工具或者pl/python来遍历文件系统
    -- 这里我们模拟一些常见的relfilenode文件名
    FOR file_record IN 
        SELECT pg_ls_dir(base_directory) AS filename
    LOOP
        file_name := file_record.filename;
        
        -- 检查文件名是否为数字（relfilenode格式）
        IF file_name ~ '^[0-9]+$' THEN
            -- 查询是否有表使用这个relfilenode
            SELECT COUNT(*) INTO count_result
            FROM pg_class
            WHERE relfilenode = file_name::oid;
            
            -- 返回结果
            filename := file_name;
            is_used := (count_result > 0);
            reference_count := count_result;
            
            RETURN NEXT;
        END IF;
    END LOOP;
    
    RETURN;
END;
$$ LANGUAGE plpgsql;

● 巡检脚本

vim /usr/local/bin/del_garbage_file.sh

#!/bin/sh

# 创建临时文件
TMP_FILE="/tmp/file_list.txt"
OUTPUT_FILE="/tmp/large_files.txt"

# 获取冗余的文件
>  /tmp/file_list.txt
export PGPASSWORD=123456
psql -U postgres -d nddb -qAtX <<EOF
\o /tmp/file_list.txt
with tmp as ( SELECT * FROM find_and_report_unused_relfilenodes('$PGDATA/base/108937')
) select 'stat -c "%s" $PGDATA/base/108937/'||filename  from tmp where not is_used;

\o
EOF



echo "正在处理文件大小检查..."
echo "========================================"
echo "大于1MB的文件:"
echo "========================================"

# 逐行执行命令并检查文件大小
while IFS= read -r cmd; do
    # 提取文件路径
    filepath=$(echo "$cmd" | sed 's/stat -c "%s" //')
    
    # 检查文件是否存在
    if [ -f "$filepath" ]; then
        lsof filepath >/dev/null 2>&1
        res=$?
        if [[ $res -eq 0 ]];then
            continue
        fi
        # 获取文件大小
        filesize=$(stat -c %s "$filepath" 2>/dev/null)
        
        # 检查是否获取到有效的文件大小
        if [ -n "$filesize" ] && [ "$filesize" -gt $((10*1024*1024)) ]; then
            # 计算大小（MB）
            echo "文件: $filepath 大小: $filesize 字节 "
            echo "`date +%F`: rm -f $filepath" >> /tmp/removed_file.txt
            rm -f "$filepath"

        fi
    # else
        # echo "文件不存在: $filepath" >> /tmp/null_file
    fi
done < $TMP_FILE

但是发现会有意外, 某些文件在查询的时候通过 relfilenode 过滤不到, 但是实际会被 postgres 访问,具体是做的啥, 环境不在了, 没法确认.

pg_orphan 插件

经过网络搜索, 发现存在一个插件 pg_orphaned 可以比较保险的发现孤儿文件, 避免删除错误(多删除了)

https://github.com/bdrouvot/pg_orphaned/tree/master
https://cloud.tencent.com.cn/developer/article/2374846

编译安装插件

export PGCONFIG=$PGHOME/bin/pg_config
export PATH=$PGHOME/bin:$PATH
make
make install
psql -c "create extension pg_orphaned ;"

示例

会话 1

postgres=# begin;
BEGIN
postgres=*# create table test2(id int);
CREATE TABLE
postgres=*# insert into test2 select n from generate_series(1,10000)n;
INSERT 0 10000
postgres=*# 
postgres=*# 
postgres=*# 
postgres=*# 
postgres=*# select pg_backend_pid();
 pg_backend_pid 
----------------
         164651
(1 row)

postgres=*# select pg_relation_filepath('test2');
 pg_relation_filepath 
----------------------
 base/5/32768
(1 row)

会话 2

[root@openeuler22 data]# ll  base/5/24576 -h
-rw------- 1 postgres postgres 360K Nov 27 14:06 base/5/24576
[root@openeuler22 data]# ll -h  base/5/32768
-rw------- 1 postgres postgres 360K Nov 27 14:07 base/5/32768
[root@openeuler22 data]# 
[root@openeuler22 data]# 
[root@openeuler22 data]# 
[root@openeuler22 data]# ps -ef | grep 164651
postgres  164651  164461  0 14:06 ?        00:00:00 postgres(5432): postgres postgres 127.0.0.1(53728) idle in transaction
root      164656  164480  0 14:08 pts/22   00:00:00 grep --color=auto 164651
[root@openeuler22 data]# kill -9 164651
[root@openeuler22 data]# ps -ef | grep 164651
root      164665  164480  0 14:08 pts/22   00:00:00 grep --color=auto 164651
[root@openeuler22 data]# ll -h  base/5/32768
-rw------- 1 postgres postgres 360K Nov 27 14:08 base/5/32768
[root@openeuler22 data]# 
[root@openeuler22 data]# ll -h  base/5/32768
-rw------- 1 postgres postgres 360K Nov 27 14:08 base/5/32768

会话 1

-- 获取孤儿文件
postgres=*# select * from pg_list_orphaned() ;
  dbname  |  path  |   name    |  size  |        mod_time        | relfilenode | reloid | older 
----------+--------+-----------+--------+------------------------+-------------+--------+-------
 postgres | base/5 | 16396     |   8192 | 2025-11-27 14:04:36+08 |       16396 |      0 | f
 postgres | base/5 | 24576     | 368640 | 2025-11-27 14:06:14+08 |       24576 |      0 | f
 postgres | base/5 | 24576_fsm |  24576 | 2025-11-27 14:06:14+08 |       24576 |      0 | f
(3 rows)


-- 移动孤儿文件
postgres=# select pg_move_orphaned(interval '1 second');
 pg_move_orphaned 
------------------
                5
(1 row)

postgres=# 

-- 查看移动的孤儿文件
postgres=# select * from pg_list_orphaned_moved();
  dbname  |           path           |   name    |  size  |        mod_time        | relfilenode | reloid 
----------+--------------------------+-----------+--------+------------------------+-------------+--------
 postgres | orphaned_backup/5/base/5 | 24576     | 368640 | 2025-11-27 14:06:14+08 |       24576 |      0
 postgres | orphaned_backup/5/base/5 | 24576_fsm |  24576 | 2025-11-27 14:06:14+08 |       24576 |      0
 postgres | orphaned_backup/5/base/5 | 32768     | 368640 | 2025-11-27 14:08:10+08 |       32768 |      0
 postgres | orphaned_backup/5/base/5 | 32768_fsm |  24576 | 2025-11-27 14:08:10+08 |       32768 |      0


-- 真正删除移动过的孤儿文件
postgres=# select * from pg_list_orphaned_moved();
 dbname | path | name | size | mod_time | relfilenode | reloid 
--------+------+------+------+----------+-------------+--------
(0 rows)

会话 2

-- 发现文件孤儿文件确实被删除掉了
[root@openeuler22 data]# ll -h  base/5/32768
ls: cannot access 'base/5/32768': No such file or directory
[root@openeuler22 data]# ll -h  base/5/24576
ls: cannot access 'base/5/24576': No such file or directory

墨力计划 postgresql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

PG孤儿文件清理

Table of Contents

背景

孤儿文件产生常见原因

分析

检查业务库占用

检查最大的表占用

从操作系统层面检查文件占用

pg_orphan 插件

编译安装插件

示例

会话 1

会话 2

会话 1

会话 2

评论