一、介绍
虽然可以通过查询获得孤儿文件列表，但由于存在正在创建文件(如CREATE TABLE、Relation重写等)的事务(意味着已启动且尚未提交或回滚)，因此可能会得到不正确的结果。
pg_orphaned扩展通过在pg_class中查找relfilnode(s)时使用脏快照来处理此类情况。
有关孤独文件，可参考 PostgreSQL孤儿文件 。
二、安装
$ git clone https://github.com/bdrouvot/pg_orphaned.git $ cd pg_orphaned $ make$ make install $ psql DB -c"CREATE EXTENSION pg_orphaned;"
三、功能
pg_orphaned扩展提供了以下函数去维护孤独文件：
pg_list_orphaned(interval)
列出孤独文件。当"older"字段为true时，仅列出比interval参数（缺省1天）老的孤儿文件。
pg_move_orphaned(interval)
移动孤儿文件到"orphaned_backup" 目录。 当"older"字段为true时，仅移动比interval参数（缺省1天）老的孤儿文件。
pg_list_orphaned_moved()
列出已经被移动到"orphaned_backup" 目录的孤儿文件。
pg_move_back_orphaned()
将已经被移到 "orphaned_backup" 目录的孤儿文件移回其原始位置。
pg_remove_moved_orphaned()
删除"orphaned_backup" 目录中的孤独文件。
四、用法举例
例一：
postgres=# create database test;CREATE DATABASE postgres=# \c test You are now connected to database "test"as user "postgres". test=# createtable bdt asselect*from generate_series(1,40000000);SELECT40000000test=# select*from pg_list_orphaned()orderby relfilenode; dbname | path | name | size | mod_time | relfilenode | reloid --------+------+------+------+----------+-------------+--------(0 rows test=# begin;BEGINtest=# createtable bdtorph asselect*from generate_series(1,40000000);SELECT40000000test=# create index orphidx on bdtorph(generate_series);CREATE INDEX test=# select pg_relation_filepath ('bdtorph'); pg_relation_filepath ---------------------- base/294991/294997(1 row)test=# select pg_relation_filepath ('orphidx'); pg_relation_filepath ---------------------- base/294991/295000(1 row)$ backend has been killed -9test=# select pg_relation_filepath ('orphidx');server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. $ reconnect and search for orphaned files test=# select pg_relation_filepath ('orphidx');ERROR: relation "orphidx" does not exist LINE 1:select pg_relation_filepath ('orphidx');test=# test=# select pg_relation_filepath ('bdtorph');ERROR: relation "bdtorph" does not exist LINE 1:select pg_relation_filepath ('bdtorph');test=# select*from pg_list_orphaned()orderby relfilenode; dbname | path | name | size | mod_time | relfilenode | reloid --------+-------------+----------+------------+------------------------+-------------+-------- test | base/294991|294997.1|376176640|2020-05-0316:18:36+00|294997|0 test | base/294991|294997|1073741824|2020-05-0316:16:03+00|294997|0 test | base/294991|295000|898490368|2020-05-0316:20:16+00|295000|0(3 rows)
例二：
test=# CREATE TABLESPACE bdttbs location '/usr/local/pgsql12.2-orphaned/bdttbs';CREATE TABLESPACE test=# begin;BEGINtest=# createtable bdtorph tablespace bdttbs asselect*from generate_series(1,40000000);SELECT40000000test=# select pg_relation_filepath ('bdtorph'); pg_relation_filepath ------------------------------------------------ pg_tblspc/303184/PG_12_201909212/303183/303185(1 row)$ backend has been killed -9test=# select pg_relation_filepath ('bdtorph');server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. $ reconnect and search for orphaned files test=# select pg_relation_filepath ('bdtorph');ERROR: relation "bdtorph" does not exist LINE 1:select pg_relation_filepath ('bdtorph');test=# select*from pg_list_orphaned()orderby relfilenode; dbname | path | name | size | mod_time | relfilenode | reloid --------+-----------------------------------------+----------+------------+------------------------+-------------+-------- test | pg_tblspc/303184/PG_12_201909212/303183|303185|1073741824|2020-05-0317:28:49+00|303185|0 test | pg_tblspc/303184/PG_12_201909212/303183|303185.1|376176640|2020-05-0317:30:18+00|303185|0(2 rows)
例三：
test=# begin;BEGINtest=# create temp table bdtorphtemp asselect*from generate_series(1,40000000);SELECT40000000test=# select pg_relation_filepath ('bdtorphtemp'); pg_relation_filepath ----------------------- base/311377/t4_311380 (1 row)$ backend has been killed -9test=# select pg_relation_filepath ('bdtorphtemp');server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. $ 重连查找孤独文件 test=# select pg_relation_filepath ('bdtorphtemp');ERROR: relation "bdtorphtemp" does not exist LINE 1:select pg_relation_filepath ('bdtorphtemp');test=# select*from pg_list_orphaned()orderby relfilenode; dbname | path | name | size | mod_time | relfilenode | reloid --------+-------------+-------------+------------+------------------------+-------------+-------- test | base/311377| t4_311380.1|376176640|2020-05-0317:35:03+00|311380|0 test | base/311377| t4_311380 |1073741824|2020-05-0317:34:59+00|311380|0(2 rows)
例四（处理进行中的交易）：
Session 1:postgres=# begin;BEGINpostgres=*# createtable bdtinpgro (a int);CREATETABLE如果使用类似下面的查询，Session 2 会列出一个假的孤儿文件:postgres=# select*from pg_ls_dir ('/home/postgres/pgorph/pg_installed/data/base/13580')as file where file ~'^[0-9]*$'and file::textnotin(select oid::textfrom pg_class ); file -------16408(1 row)使用扩展不会列出这个假孤儿文件:postgres=# select*from pg_list_orphaned(); dbname | path | name | size | mod_time | relfilenode | reloid --------+------+------+------+----------+-------------+--------(0 rows)
例五（从10/28/2021开始）：
- pg_list_orphaned() 增加interval参数 (缺省 1 天)。
- 它是个布尔值，用于填充新增加的“older”列，以指示孤儿文件是否比interval参数指定的值旧。
postgres=# select now(); now -------------------------------2021-10-2813:20:24.734192+00(1 row)postgres=# select*from pg_list_orphaned(); dbname | path | name | size | mod_time | relfilenode | reloid | older ----------+------------+-------+--------+------------------------+-------------+--------+------- postgres | base/13214|16391|106496|2021-10-2813:19:56+00|16391|0| f postgres | base/13214|16388|147456|2021-10-2813:19:56+00|16388|0| f (2 rows)postgres=# select*from pg_list_orphaned('10 seconds'); dbname | path | name | size | mod_time | relfilenode | reloid | older ----------+------------+-------+--------+------------------------+-------------+--------+------- postgres | base/13214|16391|106496|2021-10-2813:19:56+00|16391|0| t postgres | base/13214|16388|147456|2021-10-2813:19:56+00|16388|0| t (2 rows)
例六（从11/26/2021开始）：
列出1分钟前的孤儿文件 postgres=# select*from pg_list_orphaned('1 minute'); dbname | path | name | size | mod_time | relfilenode | reloid | older ----------+------------+----------+---------+------------------------+-------------+--------+------- postgres | base/13892|987654|8192000|2021-11-2615:01:46+00|987654|0| t postgres | base/13892|145676.2|8192000|2021-11-2614:54:44+00|145676|0| t postgres | base/13892|145676|8192000|2021-11-2614:54:30+00|145676|0| t postgres | base/13892|145676.1|8192000|2021-11-2614:54:40+00|145676|0| t (4 rows)移动1分钟前的孤儿文件到备份目录 postgres=# select pg_move_orphaned('1 minute'); pg_move_orphaned ------------------4(1 row)列出备份目录中的孤儿文件 postgres=# select*from pg_list_orphaned_moved(); dbname | path | name | size | mod_time | relfilenode | reloid ----------+----------------------------------+----------+---------+------------------------+-------------+-------- postgres | orphaned_backup/13892/base/13892|987654|8192000|2021-11-2615:01:46+00|987654|0 postgres | orphaned_backup/13892/base/13892|145676.2|8192000|2021-11-2614:54:44+00|145676|0 postgres | orphaned_backup/13892/base/13892|145676|8192000|2021-11-2614:54:30+00|145676|0 postgres | orphaned_backup/13892/base/13892|145676.1|8192000|2021-11-2614:54:40+00|145676|0(4 rows)移除备份目录中的孤独文件 postgres=# select pg_remove_moved_orphaned(); pg_remove_moved_orphaned --------------------------(1 row)列出备份目录中的孤独文件 postgres=# select*from pg_list_orphaned_moved(); dbname | path | name | size | mod_time | relfilenode | reloid --------+------+------+------+----------+-------------+--------(0 rows)列出1分钟前的孤独文件 postgres=# select*from pg_list_orphaned('1 minute'); dbname | path | name | size | mod_time | relfilenode | reloid | older --------+------+------+------+----------+-------------+--------+-------(0 rows)
