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

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

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

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

Примечание

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

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

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

Перечень функций:

pg_list_orphaned() – поиск неиспользуемых файлов;
pg_list_moved_orphaned() – поиск перемещенных файлов;
pg_move_orphaned() – перемещение найденных неиспользуемых файлов, старше значения interval в промежуточный карантинный каталог;
pg_remove_orphaned() – удаление ранее перемещенных в карантинный каталог неиспользуемых файлов;
pg_rollback_orphaned() – восстановление ранее перемещенных в карантинный каталог неиспользуемых файлов;
pg_remove_temp_orphaned() – поиск временных файлов в каталогах /base/pgtblspc и /pg_tblspc/tblspc_oid/pgsql_tmp и удаление «потерянных»;
pg_list_global_orphaned() – поиск неиспользуемых файлов в системном табличном пространстве pg_global.

Подробнее о данных функциях в разделе «Функции статистики планов выполнения запросов» документа «Справочная информация».

Установка

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

CREATE EXTENSION pg_orphaned SCHEMA ext;

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

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

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

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

Получение PID процесса в консоли_1:
```
SELECT pg_list_global_orphaned();
```

Запуск в консоли_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)

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

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

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)

Проверка в 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@<server_name> ~]$ 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@<server_name> ~]$ 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@<server_name> ~]$ kill -9 1476
[postgres@<server_name> ~]$ 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@<server_name> ~]$ 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@<server_name> ~]$

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

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

DROP EXTENSION pg_orphaned;

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

Установка​

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

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

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

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

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