Использование pg_repack в Managed Service for PostgreSQL

Статья создана

Обновлена 6 августа 2024 г.

Установить расширение pg_repack в кластер PostgreSQL
Установить клиент pg_repack
Запустить pg_repack
Пример использования

Таблицы и индексы PostgreSQL могут быть подвержены раздуванию (bloating). При выполнении транзакций, которые изменяют данные в таблицах и индексах, старая версия данных сохраняется, чтобы можно было откатить транзакции, если это потребуется (rollback). Это приводит к тому, что таблицы и индексы раздуваются в размере при массовом изменении данных. Оценить степень раздувания можно, например, с помощью расширения pgstattuple или набора запросов pgsql-bloat-estimation.

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

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

Установить расширение pg_repack в кластер PostgreSQL

Добавьте расширение pg_repack к выбранной базе данных.
Назначьте владельцу этой базы данных роль mdb_admin, если это еще не сделано.

Имя владельца можно запросить со списком баз данных в кластере.
Задайте нулевые значения для следующих настроек кластера PostgreSQL:
- statement_timeout;
- idle_in_transaction_session_timeout.
Важно

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

Установить клиент pg_repack

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

Чтобы установить клиент:

Определите версию расширения pg_repack, которая установлена в кластер PostgreSQL.
Установите клиент pg_repack.
Важно
Версии клиента и расширения должны совпадать, иначе подключение завершится с ошибкой:
```
ERROR: pg_repack failed with error: program 'pg_repack ...' does not match database library 'pg_repack ...'
```
В зависимости от операционной системы и подключенных репозиториев может быть доступна установка клиента с помощью пакетного менеджера, например apt или yum. Если требуемой версии клиента нет в репозиториях, соберите ее из исходных файлов самостоятельно.
Инструкция по сборке клиента pg_repack в Ubuntu 22.04 LTS
1. Подключите Apt-репозиторий PostgreSQL, который содержит часть зависимостей, необходимых для сборки:
  sudo apt install -y postgresql-common && \ sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y $(lsb_release -cs)
2. Установите зависимости.
  
  Выберите версию пакета postgresql-server-dev-*, совпадающую с версией кластера PostgreSQL, к которому планируется подключаться с помощью клиента. Это повысит стабильность работы собранного клиента.
  
  Пример команды, которая устанавливает упомянутый пакет для PostgreSQL 16, а также другие пакеты:
  sudo apt install git build-essential \ zlib1g-dev libzstd-dev liblz4-dev \ libreadline-dev \ postgresql-server-dev-16
3. Клонируйте Git-репозиторий pg_repack, выбрав тег для нужной версии, и перейдите в локальную директорию с репозиторием.
  
  Пример команды для тега ver_1.4.8:
  git clone https://github.com/reorg/pg_repack.git \ --branch ver_1.4.8 --depth 1 && \ cd pg_repack
4. Запустите сборку:
  make
  Дождитесь окончания процесса сборки.
5. Поместите исполняемый файл pg_repack в любую директорию, которая присутствует в переменной окружения PATH для текущего пользователя.
  
  Пример команды для перемещения в /usr/local/bin:
  sudo install bin/pg_repack /usr/local/bin
6. Проверьте версию клиента:
  pg_repack --version
  Должна быть выведена версия клиента, соответствующая выбранному ранее тегу, например:
  pg_repack 1.4.8

Запустить pg_repack

Совет

Запускайте pg_repack, когда нагрузка на кластер PostgreSQL минимальна: процесс перепаковки объектов базы данных создает дополнительную нагрузку на кластер.

Данные о состоянии кластера и его хостов доступны в консоли управления.

Чтобы запустить pg_repack для перепаковки объектов базы данных:

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

В ходе своей работы pg_repack оперирует копиями таблиц и индексов, поэтому ему необходимо дополнительное место для выполнения перепаковки.
Запустите клиент pg_repack с нужными параметрами.

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

Совет

Добавьте параметр --dry-run, чтобы запустить pg_repack в режиме пробного запуска и оценить планируемые изменения.

Будет выведен список объектов, которые будут перепакованы при запуске pg_repack в обычном режиме.

Выполните нужную команду:
- Команда для перепаковки указанных таблиц в базе данных:
  Перепаковка с подключением без SSL
  
  Перепаковка с подключением по SSL
  pg_repack -k -h c-<идентификатор_кластера>.rw.mdb.yandexcloud.net -p 6432 \ -U <имя_пользователя> \ -d <имя_базы_данных> \ -t <имя_таблицы>
  PGSSLMODE='verify-full' \ pg_repack -k -h c-<идентификатор_кластера>.rw.mdb.yandexcloud.net -p 6432 \ -U <имя_пользователя> \ -d <имя_базы_данных> \ -t <имя_таблицы>
  Если нужно перепаковать несколько таблиц, то передайте нужное количество параметров -t — по одному на таблицу.
- Команда для перепаковки указанных индексов в базе данных:
  Перепаковка с подключением без SSL
  
  Перепаковка с подключением по SSL
  pg_repack -k -h c-<идентификатор_кластера>.rw.mdb.yandexcloud.net -p 6432 \ -U <имя_пользователя> \ -d <имя_базы_данных> \ -i <имя_индекса>
  PGSSLMODE='verify-full' \ pg_repack -k -h c-<идентификатор_кластера>.rw.mdb.yandexcloud.net -p 6432 \ -U <имя_пользователя> \ -d <имя_базы_данных> \ -i <имя_индекса>
  Если нужно перепаковать несколько индексов, то передайте нужное количество параметров -i — по одному на индекс.
- Команда для перепаковки всех таблиц и индексов в базе данных:
  Перепаковка с подключением без SSL
  
  Перепаковка с подключением по SSL
  pg_repack -k -h c-<идентификатор_кластера>.rw.mdb.yandexcloud.net -p 6432 \ -U <имя_пользователя> \ -d <имя_базы_данных>
  PGSSLMODE='verify-full' \ pg_repack -k -h c-<идентификатор_кластера>.rw.mdb.yandexcloud.net -p 6432 \ -U <имя_пользователя> \ -d <имя_базы_данных>

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

Примечание

Пример проверялся в следующем окружении:

Кластер PostgreSQL версии 16, в котором:
- Создана база данных db1, владелец базы — user1.
- В базу данных db1 установлено расширение pg_repack версии 1.4.8.
Виртуальная машина Yandex Cloud с Ubuntu 22.04 LTS, в которой:
- Установлен собранный из исходных файлов клиент pg_repack версии 1.4.8.
- Есть возможность подключения к кластеру по SSL.

В этом примере используется инструмент pgbench, который предназначен для нагрузочного тестирования PostgreSQL и позволяет в автоматическом режиме выполнять следующие операции:

Создавать тестовые таблицы и индексы, наполнять таблицы тестовыми данными.
Выполнять множество запросов к тестовым таблицам, включая запросы INSERT и DELETE, которые изменяют содержимое таблицы.

После завершения работы pgbench тестовые таблицы и соответствующие им индексы находятся в раздутом состоянии из-за того, что выполнялось множество запросов. При этом команда VACUUM, которая устраняет раздувание таблиц и индексов, выполняется pgbench не в конце работы, а в начале. Поэтому pgbench будет использован для создания раздутых таблиц и индексов, чтобы продемонстрировать работу pg_repack.

Чтобы проверить работу pg_repack на тестовых таблицах и индексах pgbench:

Получите идентификатор кластера PostgreSQL.
Добавьте расширение pgstattuple к базе данных db1.

С помощью расширения можно оценить степень раздувания таблиц и индексов, что позволит наглядно продемонстрировать работу pg_repack.
Установите pgbench на виртуальную машину:
```
sudo apt install postgresql-contrib
```
Примечание

Пакет postgresql-contrib содержится в Apt-репозитории PostgreSQL.

Если вы удалили этот репозиторий из виртуальной машины после сборки клиента pg_repack в Ubuntu 22.04 LTS — снова подключите репозиторий.
Создайте тестовые таблицы и индексы в базе данных db1, подключившись к ней от имени владельца user1:
```
PGSSLMODE='verify-full' \
pgbench -h c-<идентификатор_кластера>.rw.mdb.yandexcloud.net -p 6432 \
        -U user1 -i -s 1 db1
```
Будут созданы следующие таблицы и индексы:

Таблица Индекс

pgbench_accounts pgbench_accounts_pkey

pgbench_branches pgbench_branches_pkey

pgbench_tellers pgbench_tellers_pkey

pgbench_history —
Посмотрите статистику для таблиц и индексов pgbench_*:
1. Подключитесь к базе данных db1 от имени владельца user1. Для подключения используйте утилиту psql.
2. Запросите статистику по таблицам:
```
SELECT * FROM pgstattuple('pgbench_accounts');
SELECT * FROM pgstattuple('pgbench_branches');
SELECT * FROM pgstattuple('pgbench_tellers');
SELECT * FROM pgstattuple('pgbench_history');
```
3. Запросите статистику по индексам:
```
SELECT * FROM pgstattuple('pgbench_accounts_pkey');
SELECT * FROM pgstattuple('pgbench_branches_pkey');
SELECT * FROM pgstattuple('pgbench_tellers_pkey');
```
В столбцах dead_tuple_count должно быть нулевое значение для всех результатов запросов. Это означает, что таблицы и индексы не раздуты.
Пример части вывода для таблицы pgbench_accounts:
```
 table_len | tuple_count | ... | dead_tuple_count | ... | free_space | free_percent
-----------+-------------+-...-+------------------+-...-+------------+--------------
  13434880 |      100000 | ... |                0 | ... |     188960 |         1.41
```
Однократно запустите pgbench:
```
PGSSLMODE='verify-full' \
pgbench -h c-<идентификатор_кластера>.rw.mdb.yandexcloud.net -p 6432 \
        -U user1 -c 5 -j 2 -t 1000 db1
```
Дождитесь завершения работы pgbench, это может занять несколько минут.
Повторно посмотрите статистику для таблиц и индексов pgbench_*.

Ненулевые значения в столбцах dead_tuple_count свидетельствуют о раздувании таблиц и индексов. В случае тестовых таблиц и индексов, созданных pgbench, наибольшее раздувание будет наблюдаться у таблицы pgbench_tellers.
Запустите pg_repack в режиме пробного запуска (dry run), чтобы оценить планируемые изменения:
```
PGSSLMODE='verify-full' \
pg_repack -k -h c-<идентификатор_кластера>.rw.mdb.yandexcloud.net -p 6432 \
          -U user1 \
          -d db1 \
          --dry-run
```
Поскольку часть индексов и таблиц не раздуты, в выводе будут присутствовать не все объекты базы данных.
Пример вывода:
```
INFO: repacking table "public.pgbench_accounts"
INFO: repacking table "public.pgbench_branches"
INFO: repacking table "public.pgbench_tellers"
```
Запустите pg_repack в обычном режиме, чтобы перепаковать таблицы и индексы:
```
PGSSLMODE='verify-full' \
pg_repack -k -h c-<идентификатор_кластера>.rw.mdb.yandexcloud.net -p 6432 \
          -U user1 \
          -d db1
```
Дождитесь завершения работы pg_repack, это может занять несколько минут.
Повторно посмотрите статистику для таблиц и индексов pgbench_*.

В столбцах dead_tuple_count должно быть нулевое значение для всех результатов запросов. Это означает, что таблицы и индексы не раздуты, и pg_repack выполнил работу корректно.

Таблица	Индекс
`pgbench_accounts`	`pgbench_accounts_pkey`
`pgbench_branches`	`pgbench_branches_pkey`
`pgbench_tellers`	`pgbench_tellers_pkey`
`pgbench_history`	—

Использование pg_repack в Managed Service for PostgreSQL

Установить расширение pg_repack в кластер PostgreSQLУстановить расширение pg_repack в кластер PostgreSQL

Установить клиент pg_repackУстановить клиент pg_repack

Запустить pg_repackЗапустить pg_repack

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

Была ли статья полезна?

Установить расширение pg_repack в кластер PostgreSQL

Установить клиент pg_repack

Запустить pg_repack

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