Очистить базу postgresql linux

Обновлено: 08.07.2024

Познакомимся поближе с процессом VACUUM и теми задачами, которые он решает в PostgreSQL. Этот процесс чистит таблицы, обновляет статистику и обновляет карты видимости и свободного пространства. А также борется с переполнением счетчика транзакций.

Варианты запуска очистки

Очищать таблицы или базы данных от ненужных версий строк можно с помощью следующих команд:

Дополнительно к этому существует процесс автоматической очистки AUTOVACUUM:

Частота работы autovacuum worker зависит от частоты изменений таблицы. Чем активнее ведётся работа с таблицей, тем чаще туда приходит autovacuum. Autovacuum настраивается конфигурационными параметрами.

Как уже рассматривалось VACUUM и AUTOVACUUM не сжимает файл, а только очищает его образовывая в нем пустые пространства. Для полного перестроения файла, другими словами чтобы файл уменьшился, нужно использовать VACUUM FULL:

VACUUM FULL очищает таблицу и перезаписывает её в новый файл, при этом файл уменьшается в размере. Для этого на таблицу навешивают блокировку, поэтому на некоторое время таблица становится недоступной. Похожим образом работает TRUNCATE.

Команда TRUNCATE блокирует таблицу и очищает её, при этом старые версии строк не сохраняются и файл уменьшается физически. Другой способ очистить таблицу это выполнить DELETE всех строк в ней, а затем запустить по этой таблице VACUUM FULL для очистки. TRUNCATE это транзакционная команда, поэтому её можно отменить (ROLLBACK).

Обновление статистики

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

В ручную сбор статистику можно запустить с помощью следующих команд:

Существует много параметров конфигурации, которые управляют процессом autovacuum:

Обновление карт видимости и свободного пространства

Эти карты обновляет autovacuum:

после очистки, если на странице не осталось старых версий строк, autovacuum ставит галочку в карте видимости. Любой процесс, который запишет в эту страницу, эту галочку снимает.
когда autovacuum почистит страницу, в ней образуются дыры, и он обновляет карту свободного пространства.

Переполнение счётчика транзакций и заморозка

Почему не сделать счетчик 64 битным? В каждой версии строчки есть заголовок. Если счетчик будет 64 битным, то будет слишком много служебной информации на каждую версию строки.

Мы начали с вопросов, связанных с изоляцией, сделали отступление про организацию данных на низком уровне, затем подробно поговорили о версиях строк и о том, как из версий получаются снимки данных.

В прошлый раз мы поговорили о HOT-обновлениях и внутристраничной очистке, а сегодня займемся всем известной обычной очисткой, vacuum vulgaris. Да, про нее написано уже столько всего, что вряд ли я скажу что-то новое, но полнота картины требует жертв. Терпите.

Что делает очистка

Внутристраничная очистка выполняется быстро, но освобождает только часть места. Она работает в пределах одной табличной страницы и не затрагивает индексы.

Основная, «обычная» очистка выполняется командой VACUUM и ее мы будем называть просто очисткой (а про автоочистку мы будем говорить отдельно).

Итак, очистка обрабатывает таблицу полностью. Она вычищает не только ненужные версии строк, но и ссылки на них из всех индексов.

Обработка происходит параллельно с другой активностью в системе. Таблица и индексы при этом могут использоваться обычным образом и для чтения, и для изменения (однако одновременное выполнение таких команд, как CREATE INDEX, ALTER TABLE и некоторых других будет невозможно).

В таблице просматриваются только те страницы, в которых происходила какая-то активность. Для этого используется карта видимости (напомню, что в ней отмечены страницы, содержащие только достаточно старые версии строк, которые гарантированно видимы во всех снимках данных). Обрабатываются только страницы, не отмеченные в карте, а сама карта при этом обновляется.

В процессе работы обновляется и карта свободного пространства, чтобы отразить появившееся свободное места в страницах.

Как водится, создадим таблицу:

С помощью параметра autovacuum_enabled мы отключаем автоматическую очистку. Про нее мы будем говорить в следующий раз, а пока — для экспериментов — нам важно управлять очисткой вручную.

Сейчас в таблице три версии строки, и на каждую ведет ссылка из индекса:

После очистки «мертвые» версии пропадают и остается только одна, актуальная. И в индексе тоже остается одна ссылка:

Обратите внимание, что два первых указателя получили статус unused, а не dead, как было бы при внутристраничной очистке.

И еще раз о горизонте транзакций

Как PostgreSQL определяет, какие версии строк можно считать «мертвыми»? Мы уже рассматривали понятие горизонта транзакций, когда говорили о снимках данных, но это настолько важная тема, что не грех и повторить.

Снова начнем предыдущий опыт.

Но перед тем, как обновлять строку еще раз, пусть начнется (но не закончится) еще одна транзакция. В нашем примере она будет работать на уровне Read Committed, но должна получить настоящий (не виртуальный) номер транзакции. Например, она может изменить или даже просто заблокировать какие-то строки в любой таблице, не обязательно в vac:

Сейчас в таблице три строки, а в индексе — три ссылки. Что произойдет после очистки?

В таблице осталось две версии строки: очистка решила, что версия (0,2) еще не может быть удалена. Причина, конечно, в горизонте транзакций базы данных, который в нашем примере определяется незавершенной транзакцией:

Можно попросить очистку рассказать о том, что происходит:

2 nonremovable row versions — в таблице найдено 2 версии, которые нельзя удалить,
1 dead row versions cannot be removed yet — из них 1 «мертвая»,
oldest xmin показывает текущий горизонт.

После завершения открытой транзакции горизонт сдвигается и ситуация исправляется:

Теперь в странице осталась только последняя актуальная версия строки:

В индексе также только одна запись:

Что происходит внутри

Очистка должна обрабатывать и таблицу, и индексы одновременно, и делать это так, чтобы не блокировать работу остальных процессов. Как ей это удается?

Все начинается со сканирования таблицы (с учетом карты видимости, как уже отмечалось). В прочитанных страницах определяются ненужные версии строк и их идентификаторы (tid) записываются в специальный массив. Массив располагается в локальной памяти процесса очистки; для него выделяется фрагмент размером maintenance_work_mem. Значение этого параметра по умолчанию — 64 МБ. Отметим, что это память выделяется сразу в полном объеме, а не по мере необходимости. Правда, если таблица небольшая, то и фрагмент выделяется поменьше.

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

В этом месте мы получаем такую картину: в индексах уже нет ссылок на ненужные версии строк, а в таблице они еще есть. Это ничему не противоречит: выполняя запрос мы либо вообще не попадем на мертвые версии строк (при индексном доступе), либо отметем их при проверке видимости (при сканировании таблицы).

После этого начинается фаза очистки таблицы. Таблица снова сканируется, чтобы прочитать нужные страницы, вычистить из них запомненные версии строк и освободить указатели. Мы можем это сделать, поскольку ссылок из индексов уже нет.

Если на первом проходе таблица не была прочитана полностью, то массив очищается и все повторяется с того места, на котором мы остановились.

таблица всегда сканируется два раза;
если при очистке удаляется так много версий строк, что все они не помещаются в память размером maintenance_work_mem, то все индексы будут полностью сканироваться столько раз, сколько потребуется.

Чтобы ускорить процесс, имеет смысл либо вызывать очистку чаще (чтобы за каждый раз очищалось не очень большое количество версий строк), либо выделить больше памяти.

Замечу в скобках, что, начиная с версии 11, PostgreSQL может пропускать сканирование индексов, если в этом нет насущной необходимости. Это должно облегчить жизнь владельцев больших таблиц, в которые строки только добавляются (но не изменяются).

Мониторинг

Как понять, что очистка не справляется с работой за один проход?

Первый способ мы уже видели: можно вызывать команду VACUUM с указанием VERBOSE. Тогда на консоль будет выводиться и информация о фазах выполнения работы.

Во-вторых, начиная с версии 9.6 имеется представление pg_stat_progress_vacuum, которое также содержит всю необходимую информацию.

Вставим в таблицу побольше строк, чтобы очистка выполнялась ощутимое время, и все их обновим, чтобы очистке было чем заняться.

Уменьшим размер памяти, выделенной под массив идентификаторов:

Запускаем очистку и, пока она работает, обратимся несколько раз к представлению pg_stat_progress_vacuum:

Тут мы в частности видим:

название текущей фазы (phase) — мы говорили о трех основных фазах, но вообще их больше;
общее число страниц таблицы (heap_blks_total);
число просканированных страниц (heap_blks_scanned);
число уже очищенных страниц (heap_blks_vacuumed);
количество проходов по индексам (index_vacuum_count).

Вывод завершившейся к этому времени команды VACUUM VERBOSE покажет общую картину:

Здесь видно, что всего было выполнено три прохода по индексам, на каждом из которых очищалось 174480 указателей на мертвые версии строк. Откуда такое число? Одна ссылка (tid) занимает 6 байтов, а 1024*1024/6 = 174762 — это число, которое мы видим в pg_stat_progress_vacuum.max_dead_tuples. Реально может использоваться чуть меньше: так гарантируется, что при чтении очередной страницы все указатели на «мертвые» версии точно поместятся в память.

Анализ

Анализ, или, иными словами, сбор статистической информации для планировщика запросов, формально никак с очисткой не связан. Тем не менее мы можем выполнять анализ не только командой ANALYZE, но и совмещать очистку с анализом: VACUUM ANALYZE. При этом сначала выполняется очистка, а затем анализ — никакой экономии не происходит.

Но, как мы увидим позже, автоматическая очистка и автоматический анализ выполняются одним процессом и управляются схожим образом.

Как мы видели, обычная очистка освобождает больше места, чем внутристраничная, но и она не всегда решает задачу полностью.

Если таблица или индекс по каким-то причинам сильно выросли в размерах, то обычная очистка освободит место внутри существующих страниц: в них появятся «дыры», которые затем будут использованы для вставки новых версий строк. Но число страниц не изменится, и, следовательно, с точки зрения операционной системы файлы будут занимать ровно столько же места, сколько занимали и до очистки. А это плохо, потому что:

замедляется полное сканирование таблицы (или индекса);
может потребоваться больший буферный кэш (ведь хранятся страницы, а плотность полезной информации падает);
в дереве индекса может появиться “лишний” уровень, который будет замедлять индексный доступ;
файлы занимают лишнее место на диске и в резервных копиях.

Если доля полезной информации в файлах опустилась ниже некоторого разумного предела, администратор может выполнить полную очистку таблицы. При этом таблица и все ее индексы перестраиваются полностью с нуля, а данные упаковываются максимально компактно (разумеется, с учетом параметра fillfactor). При перестройке PostgreSQL последовательно перестраивает сначала таблицу, а затем и каждый из ее индексов. Для каждого объекта создаются новые файлы, а в конце перестройки старые файлы удаляются. Следует учитывать, что в процессе работы на диске потребуется дополнительное место.

Для иллюстрации снова вставим в таблицу некоторое количество строк:

Как оценить плотность информации? Для этого удобно воспользоваться специальным расширением:

Функция читает полность всю таблицу и показывает статистику по тому, сколько места какими данными занято в файлах. Основная информация, которая нам сейчас интересна — поле tuple_percent: процент, занятый полезными данными. Он меньше 100 из-за неизбежных накладных расходов на служебную информацию внутри страницы, но тем не менее довольно высок.

Для индекса выводится другая информация, но поле avg_leaf_density имеет тот же смысл: процент полезной информации (в листовых страницах).

А вот какой размер занимают таблица и индекс:

Теперь удалим 90% всех строк. Строки для удаления выбираем случайно, чтобы в каждой странице с большой вероятностью хоть одна строка, да осталась:

Какой размер будут иметь объекты после обычной очистки?

Мы видим, что размер не изменился: обычная очистка никак не может уменьшить размер файлов. Хотя плотность информации, очевидно, уменьшилась примерно в 10 раз:

Теперь проверим, что получится после полной очистки. Вот какие файлы используются сейчас таблицей и индексами:

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

Обратите внимание, что плотность информации в индексе даже увеличилась по сравнению с первоначальной. Заново создать индекс (B-дерево) по имеющимся данным выгоднее, чем вставлять данные в уже имеющийся индекс строка за строкой.

Функции расширения pgstattuple, которые мы использовали, читают полностью всю таблицу. Если таблица большая, то это неудобно, и поэтому там же есть функция pgstattuple_approx, которая пропускает страницы, отмеченные в карте видимости, и показывает примерные цифры.

Еще более быстрый, но и еще менее точный способ — прикинуть отношение объема данных к размеру файла по системному каталогу. Варианты таких запросов можно найти в вики.

Полная очистка не предполагает регулярного использования, так как полностью блокирует всякую работу с таблицей (включая и выполнение запросов к ней) на все время своей работы. Понятно, что на активно используемой системе это может оказаться неприемлемым. Блокировки будут рассмотрены отдельно, а пока ограничимся упоминанием расширения pg_repack, которое блокирует таблицу только на короткое время в конце работы.

Установка PostgreSQL

PostgreSQL есть в репозитории Ubuntu, поэтому установка выполняется одной командой. Но сначала нужно проверить обновления самой системы:

Для выполнения команды нужны права суперпользователя. Так что придется вспомнить пароль.

После установки апдейтов инсталлируем PostgreSQL:

PostgreSQL установится вместе с пакетом contrib, в котором содержится дополнительная функциональность, а также утилиты для работы СУБД.

Чтобы убедиться, что все работает, проверим версию:

При установке автоматически создается роль и пользователь postgres.

Настройка PostgreSQL

Работать с PostgreSQL мы будем через терминал с помощью встроенной утилиты psql. Запускаем ее следующей командой:

Можно установить сторонние инструменты для администрирования PostgreSQL, но в этом мало смысла — psql справляется со всеми основными задачами.

Чтобы получить поддержку, вводим в терминале команду:

Если нужна справка по конкретной команде, пишем:

Выйти из psql можно командой \q.

Управление пользователями

В PostgreSQL используется концепция ролей. Одну роль можно рассматривать как отдельного пользователя или как группу пользователей. Роли могут владеть объектами БД и выдавать разрешения другим ролям.

По умолчанию была создана роль postgres. Давайте создадим еще одну роль. Для этого и з консоли системы выполняем команду:

Система запросит имя для новой роли, пароль, а также позволит настроить привилегии — например, нужно ли давать права суперпользователя или разрешать создавать другие роли и базы данных.

Если вы уже зашли в psql, то создать новую роль можно командой:

Затем задаем пароль:

Вывести список всех ролей можно командой /du. Кроме имен отобразятся привилегии каждого роли.

Чтобы закрыть список ролей, выполняем команду q.

Для удаления пользователя выполняем команду:

Это можно также сделать из консоли системы с помощью команды:

Чтоб сменить пароль пользователя, подключаемся к psql с правами суперпользователя. Затем выполняем следующую команду:

Эта операция сохраняется в файле .psql_history вместе с паролем, который не будет зашифрован. В качестве дополнительной меры безопасности эту запись рекомендуется удалить. Файл обычно находится в директории /var/lib/postgresql.

Работа с базами данных в PostgreSQL

Создать базу данных из консоли можно следующей командой:

Если вы используете терминальный клиент psql, то команда будет немного отличаться:

Чтобы посмотреть список всех БД, выполняем команду \l.

Для удаления базы данных используется та же команда, что и для удаления роли — drop. В терминале системы синтаксис будет таким:

В клиенте psql синтаксис похожий:

Подключение к базе данных

По умолчанию psql подключается от имени текущего пользователя Linux к БД с таким же названием. Если эти данные совпадают, достаточно выполнить запуск самого терминального клиента:

Чтобы вывести информацию о текущем соединении, выполняем команду:

Если имя базы данных отличается от имени пользователя, нужно указать его явно:

Если имя роли не совпадает с именем пользователя в Linux, прописываем дополнительные параметры.

Имя роли и название БД совпадают:

Название базы данных отличается от имени роли:

Чтобы переключиться на другую базу данных внутри psql используем команду:

Создание резервной копии и восстановление из бэкапа

Для создания резервной копии базы данных используется сложная команда:

Чтобы было проще разобраться, рассмотрим каждый параметр:

хост – сервер, на котором располагается БД. Например, можно указать localhost, домен, IP-адрес.
имя_роли – имя пользователя PostgreSQL, под которым мы работаем с базой данных.
формат_дампа – формат, в котором дамп сохранится на сервере. Доступны следующие форматы: c (custom) – архив .tar.gz, t (tar) – архив .tar, p (plain) – текст без сжатия, обычно .sql.
путь_к_дампу – путь, по которому будет сохранена резервная копия.
имя_БД – название БД, для которой будет создана резервная копия.

Выглядит это примерно так:

Для выполнения этой команды нужно ввести пароль, который используется при входе в psql от имени указанной роли (mybase в приведенном примере).

Восстановление из резервной копии выполняется аналогичным образом:

Параметры похожие, отличия минимальные. Важно знать хост, помнить формат и путь к бэкапу.

Мы разобрались с основными действиями и настройками PostgreSQL. На этом все!

Читайте также: