PostgreSQL, как и все СУБД, основанные на его открытом коде, устроен так, что всю информацию хранит в большом количестве отдельных файлов. И они при разных обстоятельствах могут потеряться. Например, бывает так, что при заполнении таблицы или обработке транзакции происходит сбой. Процесс, породивший файл, прерывается, не удалив результаты своей работы. СУБД про этот файл ещё ничего не знает, поскольку транзакция, создавшая его, не успела закоммититься. Часть таких файлов удалится при перезапуске СУБД, а часть — нет. Так неиспользуемые файлы копятся и занимают всё больше места. Иногда их объём исчисляется терабайтами.
Меня зовут Роман Дягелев, я инженер в СберТехе, сопровождаю и разрабатываю СУБД Platform V Pangolin. Наш продукт основан на открытых решениях PostgreSQL и включает в себя собственные доработки в области безопасности, отказоустойчивости и удобства эксплуатации. Я расскажу о том, почему нам не хватило готового инструмента PostgreSQL для очистки файлового мусора и как я вместе с коллегами дорабатывал его. Надеюсь, наш опыт станет полезен тем, кто работает с инструментарием PostgreSQL и ищет решения для работы с ненужными файлами.
Поиск и удаление потерянных файлов в больших системах — изрядно трудоёмкое дело. Для автоматизации этой задачи существует расширение pg_orphaned, которое ищет неиспользуемые файлы в табличных пространствах базы данных, где оно запускается, и перемещает их в карантинный каталог. После чего с помощью этого же расширения файлы можно восстановить, если произошла ошибка, либо удалить окончательно.
Детально это устроено следующим образом. Находим мусорные файлы с помощью функции pg_list_orphaned(interval). В качестве аргумента передаем ей временной диапазон, файлы старше которого мы ищем. Это позволяет отсеять файлы, создаваемые прямо сейчас. Перемещаем мусорные в карантин с помощью функции pg_move_orphaned(interval), удаляем с помощью pg_remove_moved_orphaned(), или же восстанавливаем с помощью pg_move_back_orphaned().
Это хорошее, простое решение. Но нас не устраивало несколько моментов.
Высокая нагрузка на диск. Как сказано выше, перед удалением файлы перемещаются в карантин. Это промежуточная директория, которая создаётся в корневом каталоге данных кластера с именем backup_dir и сохраняет внутри него путь к файлам, начиная с каталога табличного пространства. Такой подход может сильно нагрузить дисковую систему, если файлов накопилось много и табличные пространства созданы на отдельных, выделенных специально под них физических дисках. Да, и возможна ситуация, когда в корневом каталоге не хватит места под все файлы табличных пространств.
Расширение не позволяет выбирать место переноса файлов. Этот подход исключает любую вариативность в выборе карантинного каталога, путь до него всегда один.
Не удаляются временные файлы. Расширение не работает с ними. Временные файлы автоматически удаляются при следующем запуске СУБД. Однако, как мы знаем, промышленные системы могут работать без перезапуска по несколько месяцев. Поэтому администраторам СУБД было бы полезно иметь такую возможность.
Расширение переносит все файлы сразу. Функции восстановления и удаления затрагивают все файлы в карантине. На первый взгляд эта функция выглядит довольно странной: зачем восстанавливать файлы, о которых СУБД ничего не знает? Однако нельзя исключать того, что отдельные файлы положил администратор. Неизвестно зачем, но мы не вправе ему это запрещать. Поэтому была необходима возможность дополнительно остановиться, осмотреть всё, что лежит в карантине, а затем восстановить или удалить конкретные файлы.
Итак, мы поняли, что без доработки этого расширения не обойтись.
Какие функции были нужны нашим командам
Мы пошли к командам в Сбере, которые сопровождают Platform V Pangolin, и опросили их, чтобы понять, какие функции им важны. Получился такой набор пожеланий.
Оставить возможность использовать путь по умолчанию, но сделать так, чтобы по умолчанию файлы перемещались в карантин на тех же дисках, где они лежат (для снижения нагрузки на диски). Если администратору понадобится переместить файлы в другое место, например, на конкретный диск, то предусмотреть возможность задать свой путь.
Добавить опцию dry-run, которая позволит запустить функцию без реальных действий с файлами. Это нужно для предварительной проверки, какие действия реально будут выполнены с конкретным набором параметров.
Предусмотреть очистку потерянных временных таблиц и временных файлов. Сделать так, чтобы можно было очищать дисковую систему без перезапуска СУБД.
Реализовать возможность окончательного удаления или восстановления конкретных табличных страниц. Ванильная версия расширения просто искала все файлы. Нам было важно разрешить удалять или восстанавливать файлы, относящиеся к конкретным таблицам.
Как мы дорабатывали pg_orphaned. Пошагово и с примерами
В общем-то, мы пошли по порядку. Сначала реализовали возможность указывать путь, куда сохранять файлы. Ванильная версия кладет всё, что нашла, в корень data- директории. Проблема в том, что если директории табличных пространств (а зачастую это именно так), расположены на других дисках, а в pg_tblspc лежат только симлинки на них, то, когда мы вытащим довольно большой объём данных в корень диска, то можем невзначай исчерпать всё свободное место. Это приведёт, во-первых, к остановке СУБД, во-вторых, к междисковому взаимодействию и создаст ненужную нагрузку.
Однако администратор — существо свободолюбивое, и мы не хотели совсем забирать у него возможность выбора места для перемещения файлов. В результате мы добавили к оригинальной реализации функции pg_move_orphaned() необязательный аргумент backup_dir. Если он задан, то именно туда будут перемещены наши мусорные файлы. А если нет — резервная копия директории будет создана в корне папки с соответствующим табличным пространством (тем, где и лежат файлы). Аналогичные доработки потребовались и в функциях, работающих с перемещенными файлами, ведь теперь они не могут знать, куда те были перемещены.
Далее сделали доработки, чтобы позволить администраторам обрабатывать отдельные файлы. Поторговавшись с ними, остановились на relfilenode. Таким образом мы могли бы восстанавливать или удалять после очистки файлы, относящиеся к конкретной таблице. А сам процесс очистки решили не трогать. Ввели ещё один необязательный параметр — relfilenode, который работает, если мы указываем backup_dir. Сделали это с помощью создания SQL-функций с разным набором аргументов, ссылающихся на разные методы в коде библиотеки на Cи.
Например, вот так стал выглядеть код создания функции удаления:
CREATE FUNCTION pg_remove_orphaned(
custom_backup_path text,
custom_relfilenode text,
nodryrun boolean default false,
OUT dbname text,
OUT path text,
OUT name text,
OUT size bigint,
OUT mod_time timestamptz,
OUT relfilenode bigint,
OUT removed boolean)
RETURNS SETOF RECORD
AS 'MODULE_PATHNAME', 'pg_remove_moved_orphaned_file'
LANGUAGE C VOLATILE;
CREATE FUNCTION pg_remove_orphaned(
custom_backup_path text,
nodryrun boolean default false,
OUT dbname text,
OUT path text,
OUT name text,
OUT size bigint,
OUT mod_time timestamptz,
OUT relfilenode bigint,
OUT removed boolean)
RETURNS SETOF RECORD
AS 'MODULE_PATHNAME', 'pg_remove_moved_orphaned_path'
LANGUAGE C VOLATILE;
CREATE FUNCTION pg_remove_orphaned(
nodryrun boolean default false,
OUT dbname text,
OUT path text,
OUT name text,
OUT size bigint,
OUT mod_time timestamptz,
OUT relfilenode bigint,
OUT removed boolean)
RETURNS SETOF RECORD
AS 'MODULE_PATHNAME', 'pg_remove_moved_orphaned_wo_params'
LANGUAGE C VOLATILE;Механизм dry-run реализовали так: обернули в if-else строки с реальными действиями с файлами и добавили в результирующую таблицу столбец с результатом. В коде выше — это столбец removed: там будет True, если мы передали в функцию true и файлы реально удалены.
Итоговая доработка — создание функции для работы со временными файлами. Она позволяет находить и удалять временные файлы без перезагрузки сервера. Механизм прост: получаем PID из имени временного файла, и если в системе такого процесса нет, то удаляем его. Конечно, для этой функции также реализован механизм dry-run, чтобы просто посмотреть, есть ли у нас такие файлы и насколько их много.
Под спойлер я положил полный список функций и примеры использования
pg_list_orphaned([interval]) — ищет неиспользуемые файлы и выводит их списком на консоль. Примеры использования:
SELECT * FROM pg_list_orphaned();
SELECT * FROM pg_list_orphaned('10 minutes');На вход функция принимает временной интервал (значение по умолчанию — одни сутки). Файлы старше этого интервала будут дополнительно отмечены.
pg_list_moved_orphaned([backup_dir]) — ищет перемещенные файлы и также выводит их списком на консоль. Примеры использования:
SELECT * FROM pg_list_moved_orphaned();
SELECT * FROM pg_list_moved_orphaned('/pgdata/05/backup');На вход функция принимает путь до каталога, в котором будет выполняться поиск. Это необязательный параметр, по умолчанию поиск осуществляется в каталогах табличных пространств, в каталоге orphaned_backup.
pg_move_orphaned([interval], [backup_dir], [nodryrun]) — перемещает найденные неиспользуемые файлы старше значения interval (по умолчанию — сутки) в промежуточный карантинный каталог, переданный в параметре backup_dir. Если параметр не передан, то эти каталоги будут созданы в каталогах с табличными пространствами. Параметр nodryrun (boolean) определяет, выполнит ли функция реальные действия или только напишет, что выполнит (значение по умолчанию — false). А если в каталоге назначения уже есть файл с таким именем, то функция запишет WARNING и пропустит этот файл. Примеры запуска:
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]], [nodryrun]) — окончательно удаляет неиспользуемые файлы, перемещённые в карантинный каталог. Можно передать путь до каталога (по умолчанию ищет в каталогах табличных пространств). Также можно передать конкретный числовой идентификатор (relfilenode) таблицы для удаления файлов конкретно в ней. Важно, что при передаче relfilenode надо обязательно указывать путь до каталога. По умолчанию функция не выполнит реальное удаление, а только вернёт консольный лог предполагаемых действий. Для реального удаления файлов нам следует передать true для аргумента nodryrun (значение по умолчанию — false). Примеры запуска функции:
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]], [nodryrun]) — восстанавливает неиспользуемые файлы, ранее перемещенные в карантинный каталог. Можно передать путь до каталога (по умолчанию будет произведен поиск в каталогах табличных пространств). Также можно передать конкретный числовой идентификатор (relfilenode) таблицы для восстановления файлов конкретной таблицы. При передаче relfilenode обязательно нужно указать путь до каталога. По умолчанию функция не выполняет реальное восстановление, а только возвращает консольный лог предполагаемых действий. Для реального восстановления файлов нужно передать true для аргумента nodryrun (значение по умолчанию — false). Примеры запуска функции:
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([nodryrun]) — ищет и удаляет потерянные временные файлы. Речь о файлах для размещения оперативных данных для операций, которые не влезли в оперативную память. Такие файлы используются в моменте — их резервное копирование лишено смысла, поэтому здесь нет промежуточных функций, как в случае с файлами табличных страниц. По умолчанию функция не выполняет реальное удаление, а только возвращает консольный лог предполагаемых действий (список неиспользуемых временных файлов для их удаления). Чтобы реально удалить файлы, нужно передать true для аргумента nodryrun (значение по умолчанию — false). Функция ищет файлы в каталогах /base/pgtblspc и /pg_tblspc/tblspc_oid/pgsql_tmp, где tblspc_oid — OID всех добавленных в pg табличных пространств. Примеры запуска функции:
select pg_remove_temp_orphaned();
select pg_remove_temp_orphaned(true);pg_list_global_orphaned() — ищет неиспользуемые файлы в системном табличном пространстве pg_global. В нём нельзя создать таблицу простым способом, и внешние операции с файлами несут высокие риски, поэтому функции для работы с файлами в pg_global не создаются. Появление файлов в результате вывода этой функции — сигнал для администратора. Все остальные действия должны выполняться вручную и с большой осторожностью. Пример использования:
select pg_list_global_orphaned();Разбираем функции нашего расширения в деле
Похожим образом работает и оригинальная реализация расширения. Сама его идея полностью сохранилась.
Итак, имитируем ситуацию с потерей файлов таблицы. Откроем две консоли для одного и того же хоста. В первой консоли начнём создавать и наполнять тестовые таблицы и индексы, предварительно получив PID этого процесса. Убедимся, что таблицы созданы, и найдём их файловые идентификаторы:
first_db=# SELECT pg_backend_pid();
pg_backend_pid
----------------
3443588
(1 row)
first_db=# begin;
BEGIN
first_db=*# create table bdtorph(id, description)
first_db-*# tablespace "tbl_t"
first_db-*# as
first_db-*# select id, 'asknvclagnciaslgoseihgcoalugmlaegchaxgblacyelcmacgahgcla'
first_db-*# from
first_db-*# generate_series(1,30000000) as t(id);
SELECT 30000000
first_db=*# create index orphidx on bdtorph(id) tablespace "tbl_t";
CREATE INDEX
first_db=*# create temp table bdtorphtemp as select * from generate_series(1,40000000);
SELECT 40000000
first_db=*#
first_db=*#
first_db=*#
first_db=*# select pg_relation_filepath ('bdtorph');
pg_relation_filepath
---------------------------------------------
pg_tblspc/16799/PG_13_202306131/16800/18262
(1 row)
first_db=*# select pg_relation_filepath ('orphidx');
pg_relation_filepath
---------------------------------------------
pg_tblspc/16799/PG_13_202306131/16800/18268
(1 row)
first_db=*# select pg_relation_filepath ('bdtorphtemp');
pg_relation_filepath
------------------------------------------------
pg_tblspc/16799/PG_13_202306131/16800/t6_18271
(1 row)
first_db=*# Во второй консоли имитируем аварию. Например, так:
$ kill -11 3443588В первой консоли имитируем продолжение транзакции. Но видим, что процесс был аварийно завершен:
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.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
first_db=# Знает ли теперь СУБД что-либо об этих таблицах?
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=# Как видим, нет. Однако если мы посмотрим в каталог табличного пространства, то увидим, что в действительности эти файлы никуда не делись:
[postgres@srv-64-200 ~]$ ls -la $PGDATA/pg_tblspc/16799/PG_13_202306131/16800/18262*
-rw------- 1 postgres postgres 1073741824 Jul 9 14:47 /pgdata/05/data/pg_tblspc/16799/PG_13_202306131/16800/18262
-rw------- 1 postgres postgres 1073741824 Jul 9 14:55 /pgdata/05/data/pg_tblspc/16799/PG_13_202306131/16800/18262.1
-rw------- 1 postgres postgres 645251072 Jul 9 14:55 /pgdata/05/data/pg_tblspc/16799/PG_13_202306131/16800/18262.2
-rw------- 1 postgres postgres 704512 Jul 9 14:55 /pgdata/05/data/pg_tblspc/16799/PG_13_202306131/16800/18262_fsm
[postgres@srv-64-200 ~]$ ls -la $PGDATA/pg_tblspc/16799/PG_13_202306131/16800/18268*
-rw------- 1 postgres postgres 673882112 Jul 9 14:55 /pgdata/05/data/pg_tblspc/16799/PG_13_202306131/16800/18268
[postgres@srv-64-200 ~]$ ls -la $PGDATA/pg_tblspc/16799/PG_13_202306131/16800/t6_18271*
-rw------- 1 postgres postgres 1073741824 Jul 9 14:48 /pgdata/05/data/pg_tblspc/16799/PG_13_202306131/16800/t6_18271
-rw------- 1 postgres postgres 376176640 Jul 9 14:48 /pgdata/05/data/pg_tblspc/16799/PG_13_202306131/16800/t6_18271.1
[postgres@srv-64-200 ~]$ Теперь мы знаем об этих файлах. Можем удалить их вручную. Самый простой вариант для поиска — пройтись скриптом по каталогам табличных пространств, получить идентификаторы таблиц и поискать их с помощью pg_filenode_relation(). На примере созданных нами таблиц:
first_db=# select oid,spcname from pg_tablespace;
oid | spcname
-------+------------
1663 | pg_default
1664 | pg_global
16799 | tbl_t
(3 rows)
first_db=# select * from pg_filenode_relation(16799, 18262);
pg_filenode_relation
----------------------
(1 row)
first_db=# select * from pg_filenode_relation(16799, 18268);
pg_filenode_relation
----------------------
(1 row)
first_db=# select * from pg_filenode_relation(16799, 18271);
pg_filenode_relation
----------------------
(1 row)
first_db=#Таблиц, найденных по этим идентификаторам, не существует — такие файлы можно удалить. Для сравнения посмотрим, что возвращает функция с существующей таблицей:
first_db=# create temp table bdtorphtemp as select * from generate_series(1,40000000);
SELECT 40000000
first_db=# select pg_relation_filepath ('bdtorphtemp');
pg_relation_filepath
------------------------------------------------
pg_tblspc/16799/PG_13_202306131/16800/t4_24587
(1 row)
first_db=# select * from pg_filenode_relation(16799, 24587);
pg_filenode_relation
----------------------
bdtorphtemp
(1 row)
first_db=# Временные таблицы в версиях старше 14-й будут удалены при перезапуске СУБД. В 14-й версии ядра и в последующих версиях поведение изменилось, таких файлов уже оставаться не должно, но мало ли что. Раз у нас есть расширение — воспользуемся им. Найдём наши файлы:
first_db=# select * from pg_list_orphaned() order by name;
dbname | path | name | size | mod_time | relfilenode | reloid | older
----------+---------------------------------------+------------+------------+------------------------+-------------+--------+-------
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262 | 1073741824 | 2024-07-09 14:47:09+03 | 18262 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262.1 | 1073741824 | 2024-07-09 14:55:07+03 | 18262 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262.2 | 645251072 | 2024-07-09 14:55:07+03 | 18262 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262_fsm | 704512 | 2024-07-09 14:55:05+03 | 18262 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18268 | 673882112 | 2024-07-09 14:55:08+03 | 18268 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | t6_18271 | 1073741824 | 2024-07-09 14:48:23+03 | 18271 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | t6_18271.1 | 376176640 | 2024-07-09 14:48:25+03 | 18271 | 0 | f
(7 rows)Поле older говорит о том, что файлы не достигли возраста, начиная с которого считаются потерянными. Чтобы их переместить, нужно передать аргумент с пониженным временным интервалом. Дополнительно зададим путь, в который хотим убрать эти файлы:
first_db=# select * from pg_move_orphaned('1 minute'::interval, '/pgdata/05/backup');
dbname | path | name | dest_path | size | mod_time | relfilenode | reloid | moved
----------+---------------------------------------+------------+-------------------------------------------------------------------------+------------+------------------------+-------------+--------+-------
first_db | pg_tblspc/16799/PG_13_202306131/16800 | t6_18271 | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 1073741824 | 2024-07-09 14:48:23+03 | 18271 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18268 | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 673882112 | 2024-07-09 14:55:08+03 | 18268 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262.1 | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 1073741824 | 2024-07-09 14:55:07+03 | 18262 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | t6_18271.1 | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 376176640 | 2024-07-09 14:48:25+03 | 18271 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262 | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 1073741824 | 2024-07-09 14:47:09+03 | 18262 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262_fsm | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 704512 | 2024-07-09 14:55:05+03 | 18262 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262.2 | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 645251072 | 2024-07-09 14:55:07+03 | 18262 | 0 | f
(7 rows)
first_db=# Мы видим в колонке moved, что файлы не перемещены. Это та самая реализация режима dry-run. Наконец, переместим файлы, передав последний аргумент:
first_db=# select * from pg_move_orphaned('1 minute'::interval, '/pgdata/05/backup', true);
dbname | path | name | dest_path | size | mod_time | relfilenode | reloid | moved
----------+---------------------------------------+------------+-------------------------------------------------------------------------+------------+------------------------+-------------+--------+-------
first_db | pg_tblspc/16799/PG_13_202306131/16800 | t6_18271 | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 1073741824 | 2024-07-09 14:48:23+03 | 18271 | 0 | t
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18268 | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 673882112 | 2024-07-09 14:55:08+03 | 18268 | 0 | t
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262.1 | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 1073741824 | 2024-07-09 14:55:07+03 | 18262 | 0 | t
first_db | pg_tblspc/16799/PG_13_202306131/16800 | t6_18271.1 | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 376176640 | 2024-07-09 14:48:25+03 | 18271 | 0 | t
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262 | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 1073741824 | 2024-07-09 14:47:09+03 | 18262 | 0 | t
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262_fsm | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 704512 | 2024-07-09 14:55:05+03 | 18262 | 0 | t
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262.2 | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 645251072 | 2024-07-09 14:55:07+03 | 18262 | 0 | t
(7 rows)
first_db=# Проверим. Для поиска потерянных файлов существует две функции: pg_list_orphaned и pg_list_moved_orphaned. Первая ищет потерянные файлы в каталогах табличных пространств, вторая — в карантине:
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)
first_db=# select * from pg_list_moved_orphaned('/pgdata/05/backup');
dbname | path | name | size | mod_time | relfilenode | reloid
----------+-------------------------------------------------------------------------+------------+------------+------------------------+-------------+--------
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | t6_18271 | 1073741824 | 2024-07-09 14:48:23+03 | 18271 | 0
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18268 | 673882112 | 2024-07-09 14:55:08+03 | 18268 | 0
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262.1 | 1073741824 | 2024-07-09 14:55:07+03 | 18262 | 0
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | t6_18271.1 | 376176640 | 2024-07-09 14:48:25+03 | 18271 | 0
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262 | 1073741824 | 2024-07-09 14:47:09+03 | 18262 | 0
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262_fsm | 704512 | 2024-07-09 14:55:05+03 | 18262 | 0
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262.2 | 645251072 | 2024-07-09 14:55:07+03 | 18262 | 0
(7 rows)
first_db=#Теперь наши табличные пространства чисты. Дальше мы можем удалить эти файлы окончательно. А можем их восстановить. Здесь стоит отметить, что расширение написано таким образом, чтобы пропускать файлы со случайными именами, оно работает лишь с файлами таблиц. Также нет смысла разделять группу файлов, относящихся к одной таблице. Расширение проверяет только, связана ли таблица с определённой группой файлов или нет. Поэтому в качестве аргумента будет задан relfilenode и обработаны все файлы, относящиеся к нему (все табличные страницы, а также fsm- и vm-файлы). В рамках демонстрации восстановим один из них (значение relfilenode возьмём из соответствующей колонки) и снова выполним поиск:
first_db=# select * from pg_rollback_orphaned('/pgdata/05/backup', '18262', true);
dbname | path | name | dest_path | size | mod_time | relfilenode | reloid | moved
----------+-------------------------------------------------------------------------+-----------+---------------------------------------+------------+------------------------+-------------+--------+-------
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262.1 | pg_tblspc/16799/PG_13_202306131/16800 | 1073741824 | 2024-07-09 14:55:07+03 | 18262 | 0 | t
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262 | pg_tblspc/16799/PG_13_202306131/16800 | 1073741824 | 2024-07-09 14:47:09+03 | 18262 | 0 | t
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262_fsm | pg_tblspc/16799/PG_13_202306131/16800 | 704512 | 2024-07-09 14:55:05+03 | 18262 | 0 | t
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262.2 | pg_tblspc/16799/PG_13_202306131/16800 | 645251072 | 2024-07-09 14:55:07+03 | 18262 | 0 | t
(4 rows)
first_db=# select * from pg_list_orphaned();
dbname | path | name | size | mod_time | relfilenode | reloid | older
----------+---------------------------------------+-----------+------------+------------------------+-------------+--------+-------
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262.1 | 1073741824 | 2024-07-09 14:55:07+03 | 18262 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262 | 1073741824 | 2024-07-09 14:47:09+03 | 18262 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262_fsm | 704512 | 2024-07-09 14:55:05+03 | 18262 | 0 | f
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262.2 | 645251072 | 2024-07-09 14:55:07+03 | 18262 | 0 | f
(4 rows)
first_db=# select * from pg_list_moved_orphaned('/pgdata/05/backup');
dbname | path | name | size | mod_time | relfilenode | reloid
----------+-------------------------------------------------------------------------+------------+------------+------------------------+-------------+--------
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | t6_18271 | 1073741824 | 2024-07-09 14:48:23+03 | 18271 | 0
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18268 | 673882112 | 2024-07-09 14:55:08+03 | 18268 | 0
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | t6_18271.1 | 376176640 | 2024-07-09 14:48:25+03 | 18271 | 0
(3 rows)
first_db=# Ещё раз очистим, на этот раз без указания путей. Посмотрим, что получилось:
first_db=# select * from pg_move_orphaned('1 minute'::interval, true);
dbname | path | name | dest_path | size | mod_time | relfilenode | reloid | moved
----------+---------------------------------------+-----------+-----------------------------------------------------------------------+------------+------------------------+-------------+--------+-------
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262.1 | pg_tblspc/16799/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 1073741824 | 2024-07-09 14:55:07+03 | 18262 | 0 | t
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262 | pg_tblspc/16799/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 1073741824 | 2024-07-09 14:47:09+03 | 18262 | 0 | t
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262_fsm | pg_tblspc/16799/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 704512 | 2024-07-09 14:55:05+03 | 18262 | 0 | t
first_db | pg_tblspc/16799/PG_13_202306131/16800 | 18262.2 | pg_tblspc/16799/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 645251072 | 2024-07-09 14:55:07+03 | 18262 | 0 | t
(4 rows)
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
----------+-----------------------------------------------------------------------+-----------+------------+------------------------+-------------+--------
first_db | pg_tblspc/16799/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262.1 | 1073741824 | 2024-07-09 14:55:07+03 | 18262 | 0
first_db | pg_tblspc/16799/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262 | 1073741824 | 2024-07-09 14:47:09+03 | 18262 | 0
first_db | pg_tblspc/16799/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262_fsm | 704512 | 2024-07-09 14:55:05+03 | 18262 | 0
first_db | pg_tblspc/16799/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262.2 | 645251072 | 2024-07-09 14:55:07+03 | 18262 | 0
(4 rows)
first_db=# select * from pg_list_moved_orphaned('/pgdata/05/backup');
dbname | path | name | size | mod_time | relfilenode | reloid
----------+-------------------------------------------------------------------------+------------+------------+------------------------+-------------+--------
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | t6_18271 | 1073741824 | 2024-07-09 14:48:23+03 | 18271 | 0
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18268 | 673882112 | 2024-07-09 14:55:08+03 | 18268 | 0
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | t6_18271.1 | 376176640 | 2024-07-09 14:48:25+03 | 18271 | 0
(3 rows)
first_db=# Окончательно удалим все файлы. Выполним итоговую проверку.
first_db=# select * from pg_remove_orphaned(true);
dbname | path | name | size | mod_time | relfilenode | removed
----------+-----------------------------------------------------------------------+-----------+------------+------------------------+-------------+---------
first_db | pg_tblspc/16799/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262.1 | 1073741824 | 2024-07-09 14:55:07+03 | 18262 | t
first_db | pg_tblspc/16799/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262 | 1073741824 | 2024-07-09 14:47:09+03 | 18262 | t
first_db | pg_tblspc/16799/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262_fsm | 704512 | 2024-07-09 14:55:05+03 | 18262 | t
first_db | pg_tblspc/16799/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18262.2 | 645251072 | 2024-07-09 14:55:07+03 | 18262 | t
(4 rows)
first_db=# select * from pg_remove_orphaned('/pgdata/05/backup', true);
dbname | path | name | size | mod_time | relfilenode | removed
----------+-------------------------------------------------------------------------+------------+------------+------------------------+-------------+---------
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | t6_18271 | 1073741824 | 2024-07-09 14:48:23+03 | 18271 | t
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | 18268 | 673882112 | 2024-07-09 14:55:08+03 | 18268 | t
first_db | /pgdata/05/backup/orphaned_backup/pg_tblspc/16799/PG_13_202306131/16800 | t6_18271.1 | 376176640 | 2024-07-09 14:48:25+03 | 18271 | t
(3 rows)
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)
first_db=# select * from pg_list_moved_orphaned('/pgdata/05/backup');
dbname | path | name | size | mod_time | relfilenode | reloid
--------+------+------+------+----------+-------------+--------
(0 rows)
first_db=# На этом всё. Неиспользуемые файлы удалены. Немного (а, может быть, и много) места на диске мы освободили.
Очистка временных файлов (плюс ещё одна доработка)
Очень коротко расскажу ещё о том, как мы доработали эту функцию. Почему важно очищать временные файлы, что за проблема здесь кроется и как её воспроизвести, подробно написано в этой статье.
Для простоты создадим временный файл вручную:
$ echo '11111' > $PGDATA/base/pgsql_tmp/pgsql_tmp1234.1Перейдём в psql и поищем неиспользуемые файлы:
first_db=# select * from pg_remove_temp_orphaned();
path | name | size | created_at | removed
----------------+-----------------+------+------------------------+---------
base/pgsql_tmp | pgsql_tmp1234.1 | 6 | 2024-07-09 15:36:04+03 | f
(1 row)
first_db=# Видим созданный нами файл. Передав функции аргумент true, выполним реальное удаление файла:
first_db=# select * from pg_remove_temp_orphaned(true);
path | name | size | created_at | removed
----------------+-----------------+------+------------------------+---------
base/pgsql_tmp | pgsql_tmp1234.1 | 6 | 2024-07-09 15:36:04+03 | t
(1 row)
first_db=# select * from pg_remove_temp_orphaned();
path | name | size | created_at | removed
------+------+------+------------+---------
(0 rows)
first_db=# Вместо заключения
Это не окончательная версия нашего расширения. Наверняка мы будем находить баги и оптимизировать его. Например, уже после реализации появилась идея отказаться от функции поиска потерянных файлов pg_list_orphaned(), оставив только функцию перемещения (в режиме dry-run она всё равно найдёт файлы и покажет их список). Именно так мы и сделали в функции pg_remove_temp_orphaned().
Спасибо за внимание! Надеюсь, статья была вам полезна, буду рад ответить на все возникшие вопросы о нашей доработке. Прошу в комментарии.
P.S. О новых доработках (и не только) мы рассказываем в сообществе команды. Там это происходит почаще, чем на Хабре, нам можно задать любые вопросы, приходите!
Sleuthhound
А сделать пулл-реквест в проект pg_orphaned слабо?
Я конечно понимаю что лицензия этого не обязывает, но что толку от такого пиара? Ну сделали свои хотелки, ну молодцы, возьмите с полки пирожок...
boomster Автор
Всему свое время, возможно однажды и сделаем ПР в комьюнити версию. В этой статье не было задачи пиариться, простите, если обидел. Хотелось в первую очередь подсветить проблему потери файлов в постгресе и показать как их искать. Пусть даже вручную. И как можно автоматизировать этот процесс. Ну и каким путем пошли мы, конечно же.