Хабр
Как мы автоматизировали обновление, развертывание и настройку Postgres-like СУБД для пользователей
Статьи●23.06.2025
Статьи
Как найти и удалить 2 Тб ненужных файлов, которые не видит СУБД? Дорабатываем pg_orphaned
16.07.2024
PostgreSQL, как и все СУБД, основанные на его открытом коде, устроен так, что всю информацию хранит в большом количестве отдельных файлов. И они при разных обстоятельствах могут потеряться. Например, бывает так, что при заполнении таблицы или обработке транзакции происходит сбой. Процесс, породивший файл, прерывается, не удалив результаты своей работы. СУБД про этот файл ещё ничего не знает, поскольку транзакция, создавшая его, не успела закоммититься. Часть таких файлов удалится при перезапуске СУБД, а часть — нет. Так неиспользуемые файлы копятся и занимают всё больше места. Иногда их объём исчисляется терабайтами.
Меня зовут Роман Дягелев, я инженер в СберТехе, сопровождаю и разрабатываю СУБД Platform V Pangolin. Наш продукт основан на открытых решениях 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;SQL
Механизм dry-run реализовали так: обернули в if-else строки с реальными действиями с файлами и добавили в результирующую таблицу столбец с результатом. В коде выше — это столбец removed: там будет True, если мы передали в функцию true и файлы реально удалены.
Итоговая доработка — создание функции для работы со временными файлами. Она позволяет находить и удалять временные файлы без перезагрузки сервера. Механизм прост: получаем PID из имени временного файла, и если в системе такого процесса нет, то удаляем его. Конечно, для этой функции также реализован механизм dry-run, чтобы просто посмотреть, есть ли у нас такие файлы и насколько их много.
Разбираем функции нашего расширения в деле
Похожим образом работает и оригинальная реализация расширения. Сама его идея полностью сохранилась.
Итак, имитируем ситуацию с потерей файлов таблицы. Откроем две консоли для одного и того же хоста. В первой консоли начнём создавать и наполнять тестовые таблицы и индексы, предварительно получив 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=*# SQL
Во второй консоли имитируем аварию. Например, так:
$ kill -11 3443588Bash
В первой консоли имитируем продолжение транзакции. Но видим, что процесс был аварийно завершен:
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=# Bash
Знает ли теперь СУБД что-либо об этих таблицах?
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=# Bash
Как видим, нет. Однако если мы посмотрим в каталог табличного пространства, то увидим, что в действительности эти файлы никуда не делись:
[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 ~]$ Bash
Теперь мы знаем об этих файлах. Можем удалить их вручную. Самый простой вариант для поиска — пройтись скриптом по каталогам табличных пространств, получить идентификаторы таблиц и поискать их с помощью 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=#Bash
Таблиц, найденных по этим идентификаторам, не существует — такие файлы можно удалить. Для сравнения посмотрим, что возвращает функция с существующей таблицей:
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=# Bash
Временные таблицы в версиях старше 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)Bash
Поле 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=# Bash
Мы видим в колонке 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=# Bash
Проверим. Для поиска потерянных файлов существует две функции: 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=#Bash
Теперь наши табличные пространства чисты. Дальше мы можем удалить эти файлы окончательно. А можем их восстановить. Здесь стоит отметить, что расширение написано таким образом, чтобы пропускать файлы со случайными именами, оно работает лишь с файлами таблиц. Также нет смысла разделять группу файлов, относящихся к одной таблице. Расширение проверяет только, связана ли таблица с определённой группой файлов или нет. Поэтому в качестве аргумента будет задан 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=# Bash
Ещё раз очистим, на этот раз без указания путей. Посмотрим, что получилось:
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=# Bash
Окончательно удалим все файлы. Выполним итоговую проверку.
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=# Bash
На этом всё. Неиспользуемые файлы удалены. Немного (а, может быть, и много) места на диске мы освободили.
Очистка временных файлов (плюс ещё одна доработка)
Очень коротко расскажу ещё о том, как мы доработали эту функцию. Почему важно очищать временные файлы, что за проблема здесь кроется и как её воспроизвести, подробно написано в этой статье.
Для простоты создадим временный файл вручную:
$ echo '11111' > $PGDATA/base/pgsql_tmp/pgsql_tmp1234.1Bash
Перейдём в 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=# Bash
Видим созданный нами файл. Передав функции аргумент 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=# Bash
Это не окончательная версия нашего расширения. Наверняка мы будем находить баги и оптимизировать его. Например, уже после реализации появилась идея отказаться от функции поиска потерянных файлов pg_list_orphaned(), оставив только функцию перемещения (в режиме dry-run она всё равно найдёт файлы и покажет их список). Именно так мы и сделали в функции pg_remove_temp_orphaned().