如何查找PG中的孤儿文件

=# 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 这个文件，就叫做孤儿文件

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 这个文件被找出来了。

-- 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的时候会自动清理掉。

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