ref: https://zhuanlan.zhihu.com/p/665042157 系列
孤儿文件 通常产生于PG崩溃（OOM、或者pid被暴力kill -9 杀掉等）
孤儿文件，如何不处理，会造成磁盘空间的浪费。
孤儿文件的产生模拟
代码语言：sql复制=# BEGIN;
BEGIN
Time: 0.842 ms
22:08:47 db: postgres@postgres, pid:
*=# select pg_backend_pid();
pg_backend_pid
----------------
11776
(1 row)
Time: 0.865 ms
22:08:50 db: postgres@postgres, pid:
*=# create table t3233(a int);
CREATE TABLE
Time: 2.438 ms
22:08:54 db: postgres@postgres, pid:
*=# select pg_relation_filepath('t3233');
pg_relation_filepath
----------------------
base/5/216777
(1 row)
Time: 0.946 ms
22:09:06 db: postgres@postgres, pid:
*=#
另开一个会话，可以看磁盘上216777文件是存在的。
# postgres @ centos7-3 in /var/lib/pgsql/15/data [22:09:13]
$ l base/5/216777
-rw------- 1 postgres postgres 0 2023-12-26 22:08 base/5/216777
然后，另开一个会话，执行 kill -9 11670 杀掉PG
再次拉起PG进程后，可以 select * from t3233; 可以看到提示表不存在
=# select pg_relation_filepath('t3233');
ERROR: relation "t3233" does not exist
LINE 1: select pg_relation_filepath('t3233');
^
Time: 1.012 ms
22:10:25 db: postgres@postgres, pid:
但是，在linux上，可以看到216777 这个文件还是存在的
# postgres @ centos7-3 in /var/lib/pgsql/15/data [22:10:15]
$ l base/5/216777
-rw------- 1 postgres postgres 0 2023-12-26 22:08 base/5/216777
216777 这个文件，就叫做孤儿文件网上常见的孤儿文件的查找方法
代码语言：sql复制22:10:25 db: postgres@postgres, pid:
=# select oid,datname from pg_database ;
oid | datname
-------- -----------
5 | postgres
1 | template1
4 | template0
200399 | test
208591 | sbtest
(5 rows)
上面演示的时候，是在postgres库下面执行的，因此这里的查询语法也要在postgres库对应的oid目录下进行
22:12:56 db: postgres@postgres, pid:
=# SELECT * FROM pg_ls_dir('/var/lib/pgsql/15/data/base/5') as file WHERE file ~ '^[0-9]*$' AND file::text NOT IN (SELECT oid::text FROM pg_class);
file
--------
216777
(1 row)
可以看到 216777 这个文件被找出来了。但是！！ 上面的这个方法局限性很大。
在本机测试都是没问题的，发到生产去执行，发现结果会有很多的误报（文章后面有例子）。
思索了下，发现可能是之前有些表执行过vacuum full ，造成oid和relfilenode不一致，导致上述查询语句结果不正确。
PG群里问了下， 灿灿给了个他之前的文章，推荐使用pg_orphaned这个扩展，试了下的确很好用。
项目地址：https://github.com/bdrouvot/pg_orphaned/
需要注意的是：
1、需要提前安装PG的devel包
（我这里是percona pg rpm包安装，执行下yum install -y percona-postgresql15-devel即可）
2、如果make编译报错，可能需要安装高版本的gcc
（我这里用的是devtoolset-11套件，自带了很多高版本的编译工具包，devtoolset的安装可以自行搜索）
pg_orphaned扩展的安装
代码语言：sql复制-- 1 建个表，然后执行下vacuum full操作，用于下面演示
=# create table t3233(a int);
=# vacuum full t3233;
VACUUM
-- 2 使用pg_orphaned扩展查看（需要在待查看的库里都执行create extension操作）
=# create extension pg_orphaned;
22:18:50 db: postgres@postgres, pid:
=# select * from pg_list_orphaned() ;
dbname | path | name | size | mod_time | relfilenode | reloid | older
---------- -------- -------- ------ ------------------------ ------------- -------- -------
postgres | base/5 | 216777 | 0 | 2023-12-26 22:08:54 08 | 216777 | 0 | f
(1 row)
-- 3 使用传统方式查看（可以看到出现误报了，实际上224966这个不是孤儿文件，而是表t3233的文件）
=# SELECT * FROM pg_ls_dir('/var/lib/pgsql/15/data/base/5') as file WHERE file ~ '^[0-9]*$' AND file::text NOT IN (SELECT oid::text FROM pg_class);
file
--------
224966
216777
(2 rows)
TIPS： 对于正常的回滚的建表操作，如果我们立即到磁盘查看可能会发现文件居然还在，等PG下一次做checkpoint的时候会自动清理掉。pg_orphaned常用命令示例
代码语言：sql复制1 列出早于1小时(默认1天)前的孤儿文件,并将“older”字段设置为 true。
db: postgres@sbtest, pid:
=# select * from pg_list_orphaned('1 hour');
dbname | path | name | size | mod_time | relfilenode | reloid | older
-------- ------------- -------- ------ ------------------------ ------------- -------- -------
sbtest | base/208591 | 208592 | 0 | 2023-12-26 21:52:25 08 | 208592 | 0 | t
(1 row)
Time: 2.091 ms
不带参数，默认参数为1天
db: postgres@sbtest, pid:
=# select * from pg_list_orphaned(); 可以看到这个older值没有被设置为true（因为默认间隔是1天，我执行这个命令的时候，这个孤儿文件的创建时间还不到1天）
dbname | path | name | size | mod_time | relfilenode | reloid | older
-------- ------------- -------- ------ ------------------------ ------------- -------- -------
sbtest | base/208591 | 208592 | 0 | 2023-12-26 21:52:25 08 | 208592 | 0 | f
(1 row)
2 将孤立文件移动到“orphaned_backup”目录。仅移动早于1hour的孤立文件。
db: postgres@sbtest, pid:
=# select pg_move_orphaned('1 hour');
pg_move_orphaned
------------------
1 -- 这里的1表示移动了一个文件
(1 row)
可以看到磁盘上的文件已经被移动过来了
# root @ centos7-3 in /var/lib/pgsql/15/data/orphaned_backup
$ tree
.
└── 208591
└── base
└── 208591
└── 208592
3 directories, 1 file
(base)
# root @ centos7-3 in /var/lib/pgsql/15/data/orphaned_backup
$ l 208591/base/208591/208592
-rw------- 1 postgres postgres 0 2023-12-26 21:52 208591/base/208591/208592
3 pg_list_orphaned_moved 列出已移至“orphaned_backup”目录的孤立文件
db: postgres@sbtest, pid:
=# select * from pg_list_orphaned_moved();
dbname | path | name | size | mod_time | relfilenode | reloid
-------- ------------------------------------ -------- ------ ------------------------ ------------- --------
sbtest | orphaned_backup/208591/base/208591 | 208592 | 0 | 2023-12-26 21:52:25 08 | 208592 | 0
(1 row)
4 pg_move_back_orphaned() 将孤立文件从 orphaned_backup 目录移回其原始位置（如果仍然孤立）
db: postgres@sbtest, pid:
=# select * from pg_move_back_orphaned();
pg_move_back_orphaned
-----------------------
1 -- 1表示从orphaned_backup目录下移回了1个文件
(1 row)
然后，我们看下磁盘上的文件情况
# root @ centos7-3 in /var/lib/pgsql/15/data/orphaned_backup
$ tree
.
└── 208591
└── base
└── 208591
208592 这个文件没有了，但是目录还是存在的
看下数据库层面的pg_list_orphaned检查结果
db: postgres@sbtest, pid:
=# select * from pg_list_orphaned('1 hour');
dbname | path | name | size | mod_time | relfilenode | reloid | older
-------- ------------- -------- ------ ------------------------ ------------- -------- -------
sbtest | base/208591 | 208592 | 0 | 2023-12-26 21:52:25 08 | 208592 | 0 | t
(1 row)
5 再次执行 pg_move_orphaned 将孤立文件移动到 orphaned_backup 目录
db: postgres@sbtest, pid:
=# select pg_move_orphaned('1 hour'); -- 仅移动早于1hour的孤立文件
ERROR: directory "orphaned_backup/208591" exists but is not empty
HINT: please check no files exist with pg_list_orphaned_moved(), move them back (if any) with pg_move_back_orphaned() and then clean "orphaned_backup/208591" up with pg_remove_moved_orphaned()
Time: 1.725 ms
18:45:38 db: postgres@sbtest, pid:
可以看到这次报错了。
它要求orphaned_backup下面的208591这个目录必须是不存在的。 给了我们2个选择：
1、使用 pg_list_orphaned_moved() 命令，把移动到orphaned_backup目录下的文件再移回pg数据库中
2、执行 pg_remove_moved_orphaned() 删掉 orphaned_backup 下面208591 这个目录
这里，我们执行下 pg_remove_moved_orphaned
18:49:26 db: postgres@sbtest, pid:
=# select pg_remove_moved_orphaned();
pg_remove_moved_orphaned
--------------------------
(1 row)
再次查看磁盘上文件情况
# root @ centos7-3 in /var/lib/pgsql/15/data/orphaned_backup
$ tree
.
0 directories, 0 files
可以看到 目录下的208591文件已经被删除了
6 orphaned_backup目录下清理干净后，再次执行 pg_move_orphaned 就可以将孤立文件移动到 orphaned_backup 目录
db: postgres@sbtest, pid:
=# select pg_move_orphaned('1 hour');
pg_move_orphaned
------------------
1
(1 row)
# root @ centos7-3 in /var/lib/pgsql/15/data/orphaned_backup
$ tree
.
└── 208591
└── base
└── 208591
└── 208592
3 directories, 1 file
7 最后，再演示下 pg_remove_moved_orphaned() 的操作
【删除孤立文件的功能，实际上就是删除了该数据库的整个备份目录】
db: postgres@sbtest, pid:
=# select * from pg_list_orphaned_moved();
dbname | path | name | size | mod_time | relfilenode | reloid
-------- ------------------------------------ -------- ------ ------------------------ ------------- --------
sbtest | orphaned_backup/208591/base/208591 | 208592 | 0 | 2023-12-26 21:52:25 08 | 208592 | 0
(1 row)
db: postgres@sbtest, pid:
=# select * from pg_remove_moved_orphaned();
pg_remove_moved_orphaned
--------------------------
(1 row)
磁盘上文件：
# root @ centos7-3 in /var/lib/pgsql/15/data/orphaned_backup
$ tree
.
0 directories, 0 files
更多使用方式和说明，请参考 https://github.com/bdrouvot/pg_orphaned
TIPS：
1、RDS是否支持pg_orphaned扩展，我没有去验证。这个相对小众的扩展，我觉的云RDS大概率不支持。
2、在没发现pg_orphaned这个扩展前，我用传统sql方式在阿里云PG RDS 试了下，发现它不支持执行pg_ls_dir命令（可能是出于安全考虑？）
