vacuumlo

• PGHOST;
• PGPORT;
• PGUSER.

• строит временную таблицу, содержащую все OID больших объектов в выбранной базе данных;
• сканирует все столбцы в базе данных, имеющие тип oid или lo;
• удаляет соответствующие записи из временной таблицы, при этом рассматриваются только типы именно с такими именами, оставшиеся записи во временной таблице указывают на потерянные БО, которые затем и удаляются.

Использование модуля

1. Создайте простой файл lo_file.txt для импорта в базу данных:
   echo abcdefghqwerty > /tmp/lo_file.txt
2. Вызовите функцию lo_import и передайте имя файла, который требуется загрузить:
   SELECT lo_import('/tmp/lo_file.txt');
   Пример вывода результата выполнения запроса:

   Раскрыть type=sql
   lo_import
   -----------
       19014
   (1 row)
   

   В ответ СУБД выдает число – это идентификатор объекта.
3. Создайте таблицу и сохраните в ней полученный идентификатор, используйте тип данных OID для хранения идентификаторов объектов:

   CREATE TABLE lo_file (name text, oid_number oid);
   
   INSERT INTO lo_file VALUES ('/tmp/lo_file.txt', lo_import('/tmp/lo_file.txt')) RETURNING *;
   

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

   Раскрыть type=sql
       name          | oid_number
   ------------------+------------
   /tmp/lo_file.txt  |      19021
   (1 row)
   
   INSERT 0 1
   

   Большой объект полностью независим от файла в файловой системе.
4. Для проверки работы утилиты импортируйте указанный файл большое количество раз, в данном примере - 100 тысяч раз:

   INSERT INTO lo_file
           SELECT '/tmp/lo_file.txt', lo_import('/tmp/lo_file.txt')
           FROM generate_series(1, 100000);
   

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

   Раскрыть type=sql
   lo_tst=# SELECT * FROM lo_file LIMIT 10;
       name         | oid_number
   -----------------+------------
   /tmp/lo_file.txt |      19021
   /tmp/lo_file.txt |      19022
   /tmp/lo_file.txt |      19023
   /tmp/lo_file.txt |      19024
   /tmp/lo_file.txt |      19025
   /tmp/lo_file.txt |      19026
   /tmp/lo_file.txt |      19027
   /tmp/lo_file.txt |      19028
   /tmp/lo_file.txt |      19029
   /tmp/lo_file.txt |      19030
   (10 rows)
   

   Файл был импортирован. Каждый файл имеет новый идентификатор объекта, как показано в списке.
5. Просмотрите внутренние компоненты при помощи системного каталога pg_largeobject, чтобы понимать, как данные хранятся в СУБД:
   lo_tst=# \d pg_largeobject
   Вывод результата:

   Раскрыть type=sql
           Table "pg_catalog.pg_largeobject"
   Column |  Type   | Collation | Nullable | Default
   -------+---------+-----------+----------+---------
   loid   | oid     |           | not null |
   pageno | integer |           | not null |
   data   | bytea   |           | not null |
   Indexes:
       "pg_largeobject_loid_pn_index" UNIQUE, btree (loid, pageno)
   

   Подробное описание значений столбцов таблицы pg_largeobject в документации: https://www.postgresql.org/docs/13/catalog-pg-largeobject.html.
6. Отобразите массив байтов столбца data с типом bytea:
   SELECT * FROM pg_largeobject WHERE loid = 19029 ORDER BY pageno;
   Пример вывода результата выполнения запроса:

   Раскрыть type=sql
   loid  | pageno |               data
   ------+--------+----------------------------------
   19029 |      0 | \x61626364656667687177657274790a
   (1 row)
   

   Для удобства чтения переведите вывод двоичных данных в формат escape:

   SET bytea_output TO escape;
   SELECT * FROM pg_largeobject WHERE loid = 19029 ORDER BY pageno;
   

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

   Раскрыть type=sql
   loid  | pageno |        data
   ------+--------+--------------------
   19029 |      0 | abcdefghqwerty\012
   (1 row)
   

7. С помощью функции lo_unlink() удалите большой объект с OID 19029:
   SELECT lo_unlink(19029);
   Пример вывода результата выполнения запроса:

   Раскрыть type=sql
   lo_unlink
   -----------
           1
   (1 row)
   

   Подробное описание функции lo_unlink() в документации https://www.postgresql.org/docs/13/lo-funcs.html.
8. Запустите утилиту с аргументом --dry-run или -n для проверки количества больших объектов, подлежащих удалению:

   vacuumlo --dry-run --verbose lo_tst
   Connected to database "lo_tst"
   Test run: no large objects will be removed!
   Checking oid_number in ext.lo_file
   Would remove 1 large objects from database "lo_tst".
   

9. Удалите найденный потерянный большой объект:

   vacuumlo --verbose lo_tst
   Connected to database "lo_tst"
   Checking oid_number in ext.lo_file
   Successfully removed 1 large objects from database "lo_tst".
   

10. Убедитесь, что повторная проверка не находит потерянных больших объектов в выбранной базе данных:

    vacuumlo --dry-run --verbose lo_tst
    Connected to database "lo_tst"
    Test run: no large objects will be removed!
    Checking oid_number in ext.lo_file
    Would remove 0 large objects from database "lo_tst".