pg_orphaned. Очистка неиспользуемых файлов в табличных пространствах

В случае сбоев в работе СУБД Pangolin после восстановления сервера иногда остаются файлы табличных страниц при отсутствии на них ссылок в системном каталоге, что приводит к накоплению устаревших данных. В промышленных масштабах оптимизация ресурсов для процесса очистки таких файлов должна производиться автоматизированно с учетом вводимых пользователем требований и ограничений.

Для этого расширение pg_orphaned работает с неиспользуемыми и временными файлами по следующему алгоритму:

• поиск файлов по числовым маскам (идентификаторам relfilenode) в каталоге табличного пространства;
• поиск этих файлов по соответствующим идентификаторам в представлении pg_class, аналогично pg_filenode_relation();
• если идентификатор на предыдущем шаге не был найден, файл считается неиспользуемым и временно помещается в другой каталог;
• при отсутствии обращений к отобранным «на карантин» файлам их можно удалить.

Примечание:

Расширение ищет файлы только в той БД, в которую установлено, за исключением файлов глобального каталога (так как глобальный каталог не привязан к конкретной БД).

Расширение может быть установлено в несколько БД. Добавление расширения происходит при выполнении администратором СУБД следующей команды:

CREATE EXTENSION pg_orphaned SCHEMA ext;

В случае если схема не указана, для установки будет взята первая схема из search_path. Так же с расширением в БД устанавливаются функции, указанные ниже.

Функции расширения

В функциях расширения реализован механизм dryrun, то есть все действия с файлами можно запустить без реального выполнения (что является поведением по умолчанию) для предварительного анализа работы с данными. Чтобы выполнить действие с файлами, необходимо передать параметр true в аргументе nodryrun (значение по умолчанию - FALSE). Все функции расширения доступны только суперпользователю, ограничение введено на уровне кода, выдать права на функции другим пользователям нельзя.

pg_list_orphaned([interval])

Функция осуществляет поиск неиспользуемых файлов и вывод в консоль их списка.

Пример использования:

SELECT * FROM pg_list_orphaned();
SELECT * FROM pg_list_orphaned('10 minutes');

На вход функция принимает временной интервал (значение по умолчанию - 1 сутки), файлы старше которого будут дополнительно отмечены, и возвращает следующий набор значений:

Имя поля	Описание	Тип
dbname	Имя БД, в которой выполнен поиск	text
path	Путь к найденному файлу	text
name	Имя найденного файла	text
size	Размер найденного файла	bigint
mod_time	Время последнего изменения найденного файла	timestamptz
relfilenode	Числовое имя отношения	bigint
reloid	oid отношения (для потерянных файлов 0)	bigint
older	Флаг, показывающий старше или нет файл переданного в функцию интервала	bool

pg_list_moved_orphaned([backup_dir])

Функция осуществляет поиск перемещенных файлов и вывод в консоль их списка.

Пример использования:

SELECT * FROM pg_list_moved_orphaned();
SELECT * FROM pg_list_moved_orphaned('/pgdata/05/backup');

На вход функция принимает путь до каталога, в котором будет выполнен поиск (необязательный параметр, по умолчанию поиск будет осуществлен в каталогах табличных пространств каталога orphand_backup).

Функция возвращает набор значений, идентичный набору значений, возвращаемых выполнением функции pg_list_orphaned, приведенный в предыдущем подразделе.

pg_move_orphaned([interval], [backup_dir], [nodryrun])

Функция выполняет перемещение найденных неиспользуемых файлов, старше значения interval (по умолчанию 1 сутки) в промежуточный карантинный каталог, переданный в параметре backup_dir (если параметр не передан, данные каталоги будут созданы в каталогах с табличными пространствами). В случае, если в каталоге назначения уже есть файл с таким именем, функция запишет WARNING и файл будет пропущен.

Функция возвращает результаты в виде таблицы:

Имя поля	Описание	Тип
dbname	Имя БД, в которой выполнен поиск	text
path	Путь к найденному файлу	text
name	Имя найденного файла	text
dest_path	Путь для перемещения файла	text
size	Размер найденного файла	bigint
mod_time	Время последнего изменения найденного файла	timestamptz
relfilenode	Числовое имя отношения	bigint
reloid	oid отношения (для потерянных файлов 0)	bigint
moved	Признак реального перемещения файла (false, если по каким-то причинам файл не был перемещен)	bool

Пример запуска функции:

SELECT pg_move_orphaned();
SELECT pg_move_orphaned('1 minute', '/pgdata/05/backup', true);
SELECT pg_move_orphaned('/pgdata/05/backup', true);
SELECT pg_move_orphaned('1 minute'::interval, true);
SELECT pg_move_orphaned('1 minute', '/pgdata/05/backup');

pg_remove_orphaned([backup_dir, [relfilenode]], [dryrun])

Функция выполняет окончательное удаление ранее перемещенных в карантинный каталог неиспользуемых файлов. Есть возможность передать путь к конкретному каталогу и числовой идентификатор (relfilenode) определенного файла (по умолчанию будет произведен поиск в каталогах табличных пространств).

Внимание!

При передаче relfilenode указание пути к каталогу является обязательным. По умолчанию функция не выполнит реальное удаление, а только вернет консольный лог предполагаемых действий. Для реального удаления файлов нужно передать true для аргумента nodryrun.

Результат в виде таблицы включает в себя следующие столбцы:

Имя поля	Описание	Тип
dbname	Имя БД, в которой выполнен поиск	text
path	Путь к найденному файлу	text
name	Имя найденного файла	text
size	Размер найденного файла	bigint
mod_time	Время последнего изменения найденного файла	timestamptz
relfilenode	Числовое имя отношения	bigint
removed	Признак реального удаления файла (false, если по каким-то причинам файл не был перемещен)	bool

Пример запуска функции:

SELECT pg_remove_orphaned();
SELECT pg_remove_orphaned(true);
SELECT pg_remove_orphaned('/pgdata/05/backup');
SELECT pg_remove_orphaned('/pgdata/05/backup', true);
SELECT pg_remove_orphaned('/pgdata/05/backup', '12345');
SELECT pg_remove_orphaned('/pgdata/05/backup', '12345', false);

pg_rollback_orphaned([backup_dir, [relfilenode]], [dryrun])

Функция позволяет осуществить восстановление ранее перемещенных в карантинный каталог неиспользуемых файлов. Есть возможность передать путь к конкретному каталогу и числовой идентификатор (relfilenode) определенного файла (по умолчанию будет произведен поиск в каталогах табличных пространств).

Внимание!

При передаче relfilenode указание пути к каталогу является обязательным. По умолчанию функция не выполнит реальное восстановление файлов, а только вернет консольный лог предполагаемых действий. Для реального восстановления файлов нужно передать true для аргумента nodryrun.

Функция возвращает результаты предполагаемого или реального выполнения в виде таблицы:

Имя поля	Описание	Тип
dbname	Имя БД, в которой выполнен поиск	text
path	Путь к найденному файлу	text
name	Имя найденного файла	text
dest_path	Путь для перемещения файла	text
size	Размер найденного файла	bigint
mod_time	Время последнего изменения найденного файла	timestamptz
relfilenode	Числовое имя отношения	bigint
reloid	oid отношения (для потерянных файлов 0)	bigint
moved	Признак реального перемещения файла (false, если по каким-то причинам файл не был перемещен)	bool

Пример запуска функции:

SELECT pg_rollback_orphaned();
SELECT pg_rollback_orphaned(true);
SELECT pg_rollback_orphaned('/pgdata/05/backup');
SELECT pg_rollback_orphaned('/pgdata/05/backup', true);
SELECT pg_rollback_orphaned('/pgdata/05/backup', '12345');
SELECT pg_rollback_orphaned('/pgdata/05/backup', '12345', false);

pg_remove_temp_orphaned([dryrun])

Функция выполняет поиск временных файлов в каталогах /base/pgtblspc и /pg_tblspc/tblspc_oid/pgsql_tmp (где tblspc_oid - это oid всех добавленных табличных пространств) и удаление «потерянных». Под «потерянными» понимаются файлы, предназначенные для размещения оперативных данных под операции, которые не поместились в оперативной памяти. Резервные копии для таких файлов не создаются. По умолчанию функция не выполнит реальное удаление, а только вернет консольный лог предполагаемых действий (список неиспользуемых временных файлов для удаления временных файлов). Для реального удаления файлов нужно передать true в аргументе nodryrun.

Функция возвращает результаты предполагаемого или реального выполнения (в зависимости от параметра nodryrun) в виде таблицы:

Имя поля	Описание	Тип
path	Путь к найденному файлу	text
name	Имя найденного файла	text
size	Размер найденного файла	bigint
created_at	Время создания найденного файла	timestamptz
removed	Признак реального удаления файла (false, если по каким-то причинам файл не был перемещен)	bool

Пример запуска функции:

SELECT pg_remove_temp_orphaned();
SELECT pg_remove_temp_orphaned(true);

pg_list_global_orphaned()

Функция поиска неиспользуемых файлов в системном табличном пространстве pg_global. Внешние операции с файлами в данном табличном пространстве несут гораздо более высокие риски, поэтому функции для работы с файлами в pg_global не создаются стандартным образом. Появление файлов в результате вывода данной функции является сигналом для администратора, а все остальные действия должны выполняться в ручном режиме и с большой осторожностью.

Пример вызова:

SELECT pg_list_global_orphaned();

Имя поля	Описание	Тип
dbname	Имя БД, в которой выполнен поиск	text
path	Путь к найденному файлу	text
name	Имя найденного файла	text
size	Размер найденного файла	bigint
mod_time	Время последнего изменения найденного файла	timestamptz
relfilenode	Числовое имя отношения	bigint
reloid	oid отношения (для потерянных файлов 0)	bigint

Примеры

Имитация наличия неиспользуемых файлов

Для имитации ситуации, когда в каталоге табличного пространства появились неиспользуемые файлы, использовались две консоли (далее консоль_1 и консоль_2), в которых были выполнены следующие действия:

1. Получение PID процесса в консоли_1:

   SELECT pg_list_global_orphaned();

2. Запуск в консоли_1 транзакции, которая создает и наполняет таблицы, с дальнейшим поиском путей до файлов табличных страниц:

   First_db=# begin;
   BEGIN
   First_db=*# create table bdtorph tablespace "Tbl_t" as select * from generate_series(1,40000);
   SELECT 40000
   First_db=*# create index orphidx on bdtorph(generate_series) tablespace "Tbl_t";
   CREATE INDEX
   First_db=*# create temp table bdtorphtemp as select * from generate_series(1,40000000);
   SELECT 40000000
   First_db=*# select pg_relation_filepath ('bdtorph');
   pg_relation_filepath
   ----------------------------------------------
   pg_tblspc/16800/PG_13_202305111/16801/658459
   (1 row)
   
   First_db=*# select pg_relation_filepath ('orphidx');
   pg_relation_filepath
   ----------------------------------------------
   pg_tblspc/16800/PG_13_202305111/16801/658462
   (1 row)
   
   First_db=*# select pg_relation_filepath ('bdtorphtemp');
   pg_relation_filepath
   -------------------------------------------------
   pg_tblspc/16800/PG_13_202305111/16801/t8_658465
   (1 row)

3. Прекращение в консоли_2 процесса, в котором выполняется транзакция из предыдущего шага (PID процесса был получен на первом шаге):

   kill -9 28618

4. Проверка остановки процесса выполнения транзакции:

   First_db=*# commit;
   server closed the connection unexpectedly
      This probably means the server terminated abnormally
      before or while processing the request.
   The connection to the server was lost. Attempting reset: Succeeded.
   SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

5. Проверка в psql консоли_2 отсутствия таблиц:

   First_db=# select pg_relation_filepath ('bdtorph');
   ERROR:  relation "bdtorph" does not exist
   LINE 1: select pg_relation_filepath ('bdtorph');
                                        ^
   First_db=# select pg_relation_filepath ('orphidx');
   ERROR:  relation "orphidx" does not exist
   LINE 1: select pg_relation_filepath ('orphidx');
                                        ^
   First_db=# select pg_relation_filepath ('bdtorphtemp');
   ERROR:  relation "bdtorphtemp" does not exist
   LINE 1: select pg_relation_filepath ('bdtorphtemp');

Поиск неиспользуемых файлов

Поиск неиспользуемых файлов:

First_db=# select * from pg_list_orphaned();
dbname   |                 path                  |    name     |    size    |        mod_time        | relfilenode | reloid | older
---------+---------------------------------------+-------------+------------+------------------------+-------------+--------+-------
First_db | pg_tblspc/16800/PG_13_202305111/16801 | 658459      |    1449984 | 2023-08-14 12:02:06+03 |      658459 |      0 | f
First_db | pg_tblspc/16800/PG_13_202305111/16801 | 658459_fsm  |      24576 | 2023-08-14 12:02:06+03 |      658459 |      0 | f
First_db | pg_tblspc/16800/PG_13_202305111/16801 | 658462      |     917504 | 2023-08-14 12:02:06+03 |      658462 |      0 | f
First_db | pg_tblspc/16800/PG_13_202305111/16801 | t8_658465   | 1073741824 | 2023-08-14 12:00:15+03 |      658465 |      0 | f
First_db | pg_tblspc/16800/PG_13_202305111/16801 | t8_658465.1 |  376176640 | 2023-08-14 12:00:18+03 |      658465 |      0 | f
(5 rows)

В поле older стоит значение false. Это означает, что файлы не старше заданного временного диапазона. По умолчанию данный интервал составляет 1 сутки, но можно передать аргументом любое значение:

First_db=# select * from pg_list_orphaned('1 minute'::interval);
dbname  |                 path                  |    name     |    size    |        mod_time        | relfilenode | reloid | older
----------+---------------------------------------+-------------+------------+------------------------+-------------+--------+-------
First_db | pg_tblspc/16800/PG_13_202305111/16801 | 658459      |    1449984 | 2023-08-14 12:02:06+03 |      658459 |      0 | t
First_db | pg_tblspc/16800/PG_13_202305111/16801 | 658459_fsm  |      24576 | 2023-08-14 12:02:06+03 |      658459 |      0 | t
First_db | pg_tblspc/16800/PG_13_202305111/16801 | 658462      |     917504 | 2023-08-14 12:02:06+03 |      658462 |      0 | t
First_db | pg_tblspc/16800/PG_13_202305111/16801 | t8_658465   | 1073741824 | 2023-08-14 12:00:15+03 |      658465 |      0 | t
First_db | pg_tblspc/16800/PG_13_202305111/16801 | t8_658465.1 |  376176640 | 2023-08-14 12:00:18+03 |      658465 |      0 | t
(5 rows)

Теперь эти файлы можно убрать из каталога табличного пространства:

select * from pg_move_orphaned('1 minute'::interval)\gx
-[ RECORD 1 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/PG_13_202305111/16801
name        | 658459
dest_path   | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
size        | 1449984
mod_time    | 2023-08-14 12:02:06+03
relfilenode | 658459
reloid      | 0
moved       | f
-[ RECORD 2 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/PG_13_202305111/16801
name        | 658459_fsm
dest_path   | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
size        | 24576
mod_time    | 2023-08-14 12:02:06+03
relfilenode | 658459
reloid      | 0
moved       | f
-[ RECORD 3 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/PG_13_202305111/16801
name        | 658462
dest_path   | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
size        | 917504
mod_time    | 2023-08-14 12:02:06+03
relfilenode | 658462
reloid      | 0
moved       | f
-[ RECORD 4 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/PG_13_202305111/16801
name        | t8_658465
dest_path   | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
size        | 1073741824
mod_time    | 2023-08-14 12:00:15+03
relfilenode | 658465
reloid      | 0
moved       | f
-[ RECORD 5 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/PG_13_202305111/16801
name        | t8_658465.1
dest_path   | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
size        | 376176640
mod_time    | 2023-08-14 12:00:18+03
relfilenode | 658465
reloid      | 0
moved       | f

В поле moved стоит значение false. По умолчанию расширение не выполняет действий с файлами, а только показывает, что будет сделано. Чтобы переместить файлы фактически, необходимо передать аргумент nodryrun:

select * from pg_move_orphaned('1 minute'::interval, true)\gx
-[ RECORD 1 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/PG_13_202305111/16801
name        | 658459
dest_path   | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
size        | 1449984
mod_time    | 2023-08-14 12:02:06+03
relfilenode | 658459
reloid      | 0
moved       | t
-[ RECORD 2 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/PG_13_202305111/16801
name        | 658459_fsm
dest_path   | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
size        | 24576
mod_time    | 2023-08-14 12:02:06+03
relfilenode | 658459
reloid      | 0
moved       | t
-[ RECORD 3 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/PG_13_202305111/16801
name        | 658462
dest_path   | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
size        | 917504
mod_time    | 2023-08-14 12:02:06+03
relfilenode | 658462
reloid      | 0
moved       | t
-[ RECORD 4 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/PG_13_202305111/16801
name        | t8_658465
dest_path   | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
size        | 1073741824
mod_time    | 2023-08-14 12:00:15+03
relfilenode | 658465
reloid      | 0
moved       | t
-[ RECORD 5 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/PG_13_202305111/16801
name        | t8_658465.1
dest_path   | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
size        | 376176640
mod_time    | 2023-08-14 12:00:18+03
relfilenode | 658465
reloid      | 0
moved       | t

Файлы были перемещены, и функция поиска их больше не находит, однако они не удалены навсегда, а перемещены в карантинный каталог. Для поиска перемещенных файлов используется другая функция:

First_db=# select * from pg_list_moved_orphaned();
  dbname  |                                 path                                  |    name     |    size    |        mod_time        | relfilenode | reloid
----------+-----------------------------------------------------------------------+-------------+------------+------------------------+-------------+--------
 First_db | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801 | 658459      |    1449984 | 2023-08-14 12:02:06+03 |      658459 |      0
 First_db | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801 | 658459_fsm  |      24576 | 2023-08-14 12:02:06+03 |      658459 |      0
 First_db | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801 | 658462      |     917504 | 2023-08-14 12:02:06+03 |      658462 |      0
 First_db | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801 | t8_658465   | 1073741824 | 2023-08-14 12:00:15+03 |      658465 |      0
 First_db | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801 | t8_658465.1 |  376176640 | 2023-08-14 12:00:18+03 |      658465 |      0
(5 rows)

В случае необходимости, файлы можно восстановить:

select * from pg_rollback_orphaned()\gx
-[ RECORD 1 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
name        | 658459
dest_path   | pg_tblspc/16800/PG_13_202305111/16801
size        | 1449984
mod_time    | 2023-08-14 12:02:06+03
relfilenode | 658459
reloid      | 0
moved       | f
-[ RECORD 2 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
name        | 658459_fsm
dest_path   | pg_tblspc/16800/PG_13_202305111/16801
size        | 24576
mod_time    | 2023-08-14 12:02:06+03
relfilenode | 658459
reloid      | 0
moved       | f
-[ RECORD 3 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
name        | 658462
dest_path   | pg_tblspc/16800/PG_13_202305111/16801
size        | 917504
mod_time    | 2023-08-14 12:02:06+03
relfilenode | 658462
reloid      | 0
moved       | f
-[ RECORD 4 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
name        | t8_658465
dest_path   | pg_tblspc/16800/PG_13_202305111/16801
size        | 1073741824
mod_time    | 2023-08-14 12:00:15+03
relfilenode | 658465
reloid      | 0
moved       | f
-[ RECORD 5 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
name        | t8_658465.1
dest_path   | pg_tblspc/16800/PG_13_202305111/16801
size        | 376176640
mod_time    | 2023-08-14 12:00:18+03
relfilenode | 658465
reloid      | 0
moved       | f

В поле moved стоит значение false. Данная функция также работает в режиме dryrun. Помимо восстановления, файлы можно удалить (установкой значения параметра true для фактического удаления):

First_db=# select * from pg_remove_orphaned(true)\gx
-[ RECORD 1 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
name        | 658459
size        | 1449984
mod_time    | 2023-08-14 12:02:06+03
relfilenode | 658459
removed     | t
-[ RECORD 2 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
name        | 658459_fsm
size        | 24576
mod_time    | 2023-08-14 12:02:06+03
relfilenode | 658459
removed     | t
-[ RECORD 3 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
name        | 658462
size        | 917504
mod_time    | 2023-08-14 12:02:06+03
relfilenode | 658462
removed     | t
-[ RECORD 4 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
name        | t8_658465
size        | 1073741824
mod_time    | 2023-08-14 12:00:15+03
relfilenode | 658465
removed     | t
-[ RECORD 5 ]----------------------------------------------------------------------
dbname      | First_db
path        | pg_tblspc/16800/orphaned_backup/pg_tblspc/16800/PG_13_202305111/16801
name        | t8_658465.1
size        | 376176640
mod_time    | 2023-08-14 12:00:18+03
relfilenode | 658465
removed     | t

Файлы удалены, их нет в каталоге табличных пространств. Если выполнить поиск файлов, то удаление будет отражено в выводе:

First_db=# select * from pg_list_orphaned();
 dbname | path | name | size | mod_time | relfilenode | reloid | older
--------+------+------+------+----------+-------------+--------+-------
(0 rows)
 
First_db=# select * from pg_list_moved_orphaned();
 dbname | path | name | size | mod_time | relfilenode | reloid
--------+------+------+------+----------+-------------+--------
(0 rows)

Удаление неиспользуемых временных файлов

Расширение позволяет удалить неиспользуемые временные файлы (в том числе те, которые могут оставаться в каталогах при аварийном завершении процесса и удаляться только при рестарте СУБД). Для имитации подобного сбоя в консоли_1 была запущена транзакция, активно пишущая временные файлы (с предварительным получением pid процесса):

First_db=# SELECT pg_backend_pid();
 pg_backend_pid
----------------
           1476
(1 row)
First_db=# explain (analyze, buffers)
First_db-# WITH RECURSIVE T AS (
First_db(#   SELECT
First_db(#     0 i
First_db(#   , '' s
First_db(# UNION ALL
First_db(#   SELECT
First_db(#     i + 1
First_db(#   , repeat('a', i + 1)
First_db(#   FROM
First_db(#     T
First_db(#   WHERE
First_db(#     i < 1e5 -- 100k итераций
First_db(# )
First_db-# TABLE T ORDER BY s DESC LIMIT 1;

После того как процесс запущен, в консоли_2 были созданы временные файлы. Необходимо убедиться в том, что они доступны расширению:

[postgres@srv-0-211.db.dev ~]$ ls -la $PGDATA/pg_tblspc/16800/PG_13_202305111/pgsql_tmp
total 2621312
drwx------ 2 postgres postgres         52 Aug 14 12:19 .
drwx------ 5 postgres postgres         49 Jul 13 17:12 ..
-rw------- 1 postgres postgres 1073741824 Aug 14 12:19 pgsql_tmp1476.5
-rw------- 1 postgres postgres  469073920 Aug 14 12:19 pgsql_tmp1476.6
[postgres@srv-0-211.db.dev ~]$ psql -d First_db
psql (13.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
 
First_db=# select * from pg_remove_temp_orphaned();
 path | name | size | created_at | removed
------+------+------+------------+---------
(0 rows)

После приостановки процесса можно убедиться, что файлы остались:

[postgres@srv-0-211.db.dev ~]$ kill -9 1476
[postgres@srv-0-211.db.dev ~]$ ls -la $PGDATA/pg_tblspc/16800/PG_13_202305111/pgsql_tmp
total 4581692
drwx------ 2 postgres postgres        121 Aug 14 12:21 .
drwx------ 5 postgres postgres         49 Jul 13 17:12 ..
-rw------- 1 postgres postgres 1073741824 Aug 14 12:19 pgsql_tmp1476.5
-rw------- 1 postgres postgres 1073741824 Aug 14 12:19 pgsql_tmp1476.6
-rw------- 1 postgres postgres 1073741824 Aug 14 12:20 pgsql_tmp1476.7
-rw------- 1 postgres postgres 1073741824 Aug 14 12:21 pgsql_tmp1476.8
-rw------- 1 postgres postgres  396681216 Aug 14 12:21 pgsql_tmp1476.9

Используя возможности pg_orphaned возможна очистка пространства от неиспользуемых файлов:

First_db=# select * from pg_remove_temp_orphaned(true);
                   path                    |      name       |    size    |       created_at       | removed
-------------------------------------------+-----------------+------------+------------------------+---------
 pg_tblspc/16800/PG_13_202305111/pgsql_tmp | pgsql_tmp1476.5 | 1073741824 | 2023-08-14 12:19:14+03 | t
 pg_tblspc/16800/PG_13_202305111/pgsql_tmp | pgsql_tmp1476.6 | 1073741824 | 2023-08-14 12:19:50+03 | t
 pg_tblspc/16800/PG_13_202305111/pgsql_tmp | pgsql_tmp1476.7 | 1073741824 | 2023-08-14 12:20:25+03 | t
 pg_tblspc/16800/PG_13_202305111/pgsql_tmp | pgsql_tmp1476.8 | 1073741824 | 2023-08-14 12:21:02+03 | t
 pg_tblspc/16800/PG_13_202305111/pgsql_tmp | pgsql_tmp1476.9 |  396681216 | 2023-08-14 12:21:15+03 | t
(5 rows)
 
First_db=# \q
[postgres@srv-0-211.db.dev ~]$ ls -la $PGDATA/pg_tblspc/16800/PG_13_202305111/pgsql_tmp
total 0
drwx------ 2 postgres postgres  6 Aug 14 12:26 .
drwx------ 5 postgres postgres 49 Jul 13 17:12 ..
[postgres@srv-0-211.db.dev ~]$

Отключение функциональности

Для отключения функциональности достаточно удалить расширение pg_orphaned:

DROP EXTENSION pg_orphaned;