# Занятие 12: Задачи администрирования / Мониторинг > Исходная страница в нативном Markdown: Задачи администрирования / Мониторинг --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [dba1_12_admin_monitoring.html](https://edu.postgrespro.ru/16/dba1-16/dba1_12_admin_monitoring.html) > **Archive:** [DBA1-handouts-16.zip](https://edu.postgrespro.ru/16/DBA1-handouts-16.zip) > **Страниц PDF:** 30 ## Page 1: Задачи администрирования / Мониторинг {#source-page-001} ### Авторские права © Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Алексей Береснев Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Средства операционной системы - Накопительная статистика сервера - Журнал сообщений сервера - Внешние системы мониторинга ## Page 3: Средства ОС {#source-page-003} **Процессы** - ps, pgrep... - параметр update\_process\_title для обновления статуса процессов - параметр cluster\_name для установки имени кластера - Использование ресурсов - iostat, vmstat, sar, top... - Дисковое пространство - df, du, quota... ### Комментарии преподавателя PostgreSQL работает под управлением операционной системы и в известной степени зависит от ее настроек. Используя инструменты операционной системы, можно посмотреть информацию о процессах PostgreSQL. При включенном (по умолчанию) параметре сервера update\_process\_title в имени процесса отображается его текущее состояние. Параметр cluster\_name задает имя экземпляра, по которому его можно отличать в списке процессов. Использование системных ресурсов (процессора, ОЗУ, дисков) в Unix проверяют с помощью iostat, vmstat, sar, top и других инструментов. Необходимо следить и за размером дискового пространства. Место, занимаемое базой данных, можно смотреть как из самой БД (см. модуль «Организация данных»), так из ОС (команда du). Размер доступного дискового пространства надо смотреть в ОС (команда df). Дисковые квоты Unix ограничивают использование диска пользователями ОС. Так как процессы PostgreSQL принадлежат единственному пользователю postgres (по умолчанию), дисковая квота для него должна быть достаточной. PostgreSQL не позволяет квотировать использование ролями дискового пространства. В целом набор инструментов и подходы могут сильно различаться в зависимости от используемой ОС и файловой системы, поэтому подробно здесь не рассматриваются. ## Page 4: Накопительная статистика {#source-page-004} **Процесс сбора статистики** - Текущие активности системы - Отслеживание выполнения команд - Дополнительные расширения ### Комментарии преподавателя Существует два основных источника информации о происходящем в системе. Первый из них — статистическая информация, которая собирается PostgreSQL и хранится в кластере. ## Page 5: Сбор статистики {#source-page-005} **Настройки накопительной статистики** - параметр - действие - track\_activities - включает мониторинг текущих команд - track\_counts - сбор статистики по обращениям к таблицам - и индексам - track\_functions - отслеживание использования пользовательских - функций - выключен по умолчанию - track\_io\_timing - мониторинг времени чтения и записи блоков - выключен по умолчанию - track\_wal\_io\_timing - мониторинг времени записи WAL - выключен по умолчанию ### Комментарии преподавателя Система накопительной статистики в PostgreSQL собирает и позволяет получать информацию о работе сервера. Накопительная статистика отслеживает обращения к таблицам и индексам как на уровне блоков на диске, так и на уровне отдельных строк. Кроме того, для каждой таблицы собираются сведения о количестве строк и действиях по очистке и анализу. Можно также учитывать количество вызовов пользовательских функций и время, затраченное на их выполнение. Количеством собираемой информации управляют несколько параметров сервера, так как чем больше информации собирается, тем больше и накладные расходы. ## Page 6: Архитектура {#source-page-006} ![Source slide page 6: Архитектура](images/page-006.png){#source-figure-006 caption="Source slide page 6: Архитектура"} ### Комментарии преподавателя Обслуживающие процессы собирают статистику в рамках транзакций. Затем эта статистика самим процессом записывается в разделяемую память, но не чаще, чем раз в одну секунду (задано при компиляции). Накопительная статистика запоминается в PGDATA/pg\_stat/ при штатной остановке сервера и считывается при его запуске. При аварийной остановке все счетчики сбрасываются. Обслуживающий процесс может кешировать данные статистики при обращении к ней. Уровнем кеширования управляет параметр stats\_fetch\_consistency: ● none — без кеширования, статистика только в разделяемой памяти; ● cache — кешируется статистика по одному объекту; ● snapshot — кешируется вся статистика текущей базы данных. По умолчанию используется значение cache — это компромисс между согласованностью и эффективностью. Закешированная статистика не перечитывается и сбрасывается в конце транзакции или при вызове pg\_stat\_clear\_snapshot(). Из-за задержек и кеширования обслуживающий процесс использует не самую свежую статистику, но обычно это и не требуется. ## Page 7: Накопительная статистика {#source-page-007} ```sql => CREATE DATABASE admin_monitoring; ``` ```sql CREATE DATABASE ``` ```sql => \c admin_monitoring ``` ```text You are now connected to database "admin_monitoring" as user "student". ``` Вначале включим сбор статистики ввода-вывода: ```sql => ALTER SYSTEM SET track_io_timing=on; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` Смотреть на активности сервера имеет смысл, когда какие-то активности на самом деле есть. Чтобы сымитировать нагрузку, воспользуемся pgbench — штатной утилитой для запуска эталонных тестов. Сначала утилита создает набор таблиц и заполняет их данными. ```sh student$ pgbench -i admin_monitoring ``` ```sql dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... generating data (client-side)... 100000 of 100000 tuples (100%) done (elapsed 0.23 s, remaining 0.00 s) vacuuming... creating primary keys... done in 0.47 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 0.26 s, vacuum 0.11 s, primary keys 0.09 s). ``` Сбросим накопленную ранее статистику по базе данных: ```sql => SELECT pg_stat_reset(); ``` ```text pg_stat_reset --------------- (1 row) ``` А также статистику экземпляра по вводу-выводу: ```sql => SELECT pg_stat_reset_shared('io'); ``` ```text pg_stat_reset_shared ---------------------- (1 row) ``` Запускаем тест TPC-B на несколько секунд: ```sh student$ pgbench -T 10 admin_monitoring ``` ```sql pgbench (16.11 (Ubuntu 16.11-1.pgdg24.04+1)) starting vacuum...end. transaction type: scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 duration: 10 s number of transactions actually processed: 3250 number of failed transactions: 0 (0.000%) ``` ## Page 8: Page 8 {#source-page-008} ```text latency average = 3.075 ms initial connection time = 6.972 ms tps = 325.158222 (without initial connection time) ``` Теперь мы можем посмотреть статистику обращений к таблицам в терминах строк: ```sql => SELECT * FROM pg_stat_all_tables WHERE relid = 'pgbench_accounts'::regclass \gx ``` ```text -[ RECORD 1 ]-------+------------------------------ relid | 16393 schemaname | public relname | pgbench_accounts seq_scan | 0 last_seq_scan | seq_tup_read | 0 idx_scan | 6500 last_idx_scan | 2026-04-10 17:05:22.905171+03 idx_tup_fetch | 6500 n_tup_ins | 0 n_tup_upd | 3250 n_tup_del | 0 n_tup_hot_upd | 1805 n_tup_newpage_upd | 1445 n_live_tup | 0 n_dead_tup | 2409 n_mod_since_analyze | 3250 n_ins_since_vacuum | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 ``` И в терминах страниц: ```sql => SELECT * FROM pg_statio_all_tables WHERE relid = 'pgbench_accounts'::regclass \gx ``` ```text -[ RECORD 1 ]---+----------------- relid | 16393 schemaname | public relname | pgbench_accounts heap_blks_read | 0 heap_blks_hit | 18863 idx_blks_read | 276 idx_blks_hit | 15648 toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit | ``` Существуют аналогичные представления для индексов: ```sql => SELECT * FROM pg_stat_all_indexes WHERE relid = 'pgbench_accounts'::regclass \gx ``` ```text -[ RECORD 1 ]-+------------------------------ relid | 16393 indexrelid | 16407 schemaname | public relname | pgbench_accounts indexrelname | pgbench_accounts_pkey idx_scan | 6500 last_idx_scan | 2026-04-10 17:05:22.905171+03 idx_tup_read | 7978 idx_tup_fetch | 6500 ``` ```sql => SELECT * FROM pg_statio_all_indexes WHERE relid = 'pgbench_accounts'::regclass \gx ``` ## Page 9: Page 9 {#source-page-009} ```text -[ RECORD 1 ]-+---------------------- relid | 16393 indexrelid | 16407 schemaname | public relname | pgbench_accounts indexrelname | pgbench_accounts_pkey idx_blks_read | 276 idx_blks_hit | 15648 ``` Эти представления, в частности, могут помочь определить неиспользуемые индексы. Такие индексы не только бессмысленно занимают место на диске, но и тратят ресурсы на обновление при каждом изменении данных в таблице. Есть также представления для пользовательских и системных объектов (all, user, sys), для статистики текущей транзакции (pg\_stat\_xact\*) и другие. Можно посмотреть общую статистику по базе данных: ```sql => SELECT * FROM pg_stat_database WHERE datname = 'admin_monitoring' \gx ``` ```text -[ RECORD 1 ]------------+------------------------------ datid | 16386 datname | admin_monitoring numbackends | 1 xact_commit | 3270 xact_rollback | 0 blks_read | 278 blks_hit | 59340 tup_returned | 46592 tup_fetched | 7651 tup_inserted | 3250 tup_updated | 9751 tup_deleted | 0 conflicts | 0 temp_files | 0 temp_bytes | 0 deadlocks | 0 checksum_failures | checksum_last_failure | blk_read_time | 7.953 blk_write_time | 1.358 session_time | 22282.257 active_time | 8577.372 idle_in_transaction_time | 1207.837 sessions | 2 sessions_abandoned | 0 sessions_fatal | 0 sessions_killed | 0 stats_reset | 2026-04-10 17:05:12.581326+03 ``` Здесь есть много полезной информации о количестве произошедших взаимоблокировок, зафиксированных и отмененных транзакций, использовании временных файлов, ошибках подсчета контрольных сумм. Здесь же хранится статистика общего количества сеансов и количества прерванных по разным причинам сеансов. Столбец numbackends показывает текущее количество обслуживающих процессов, подключенных к базе данных. Статистика ввода-вывода на уровне сервера доступна в представлении pg\_stat\_io. Например, выполним контрольную точку и посмотрим количество операций чтения и записи страниц по типам процессов: ```text => CHECKPOINT; ``` ```text CHECKPOINT ``` ```sql => SELECT backend_type, sum(hits) hits, sum(reads) reads, sum(writes) writes FROM pg_stat_io GROUP BY backend_type; ``` ## Page 10: Page 10 {#source-page-010} ```text backend_type | hits | reads | writes ---------------------+-------+-------+-------- background worker | 0 | 0 | 0 client backend | 59487 | 278 | 0 walsender | 0 | 0 | 0 standalone backend | 0 | 0 | 0 autovacuum worker | 0 | 0 | 0 autovacuum launcher | 0 | 0 | 0 background writer | | | 0 startup | 0 | 0 | 0 checkpointer | | | 2901 (9 rows) ``` ## Page 11: Текущие активности {#source-page-011} **Настройка** - статистика - параметр - текущие активности - track\_activities - и ожидания обслуживающих - включен по умолчанию - и фоновых процессов ### Комментарии преподавателя Текущие активности всех обслуживающих и фоновых процессов отображаются в представлении pg\_stat\_activity. Подробнее на нем мы остановимся в демонстрации. Работа этого представления зависит от параметра track\_activities, включенного по умолчанию. ## Page 12: Текущие активности {#source-page-012} ![Source slide page 12: Текущие активности](images/page-012.png){#source-figure-012 caption="Source slide page 12: Текущие активности"} Воспроизведем сценарий, в котором один процесс блокирует выполнение другого, и попробуем разобраться в ситуации с помощью системных представлений. Создадим таблицу с одной строкой: ```sql => CREATE TABLE t(n integer); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO t VALUES(42); ``` ```sql INSERT 0 1 ``` Запустим два сеанса, один из которых изменяет таблицу и не завершает транзакцию: ```sh student$ psql -d admin_monitoring ``` ```text => BEGIN; ``` ```text BEGIN ``` ```sql => UPDATE t SET n = n + 1; ``` ```sql UPDATE 1 ``` А второй пытается изменить ту же строку и блокируется: ```sh student$ psql -d admin_monitoring ``` ```sql => UPDATE t SET n = n + 2; ``` Посмотрим информацию об обслуживающих процессах: ```sql => SELECT pid, query, state, wait_event, wait_event_type, pg_blocking_pids(pid) FROM pg_stat_activity WHERE backend_type = 'client backend' \gx ``` ```sql -[ RECORD 1 ]----+----------------------------------------------------------------------------- pid | 77591 query | UPDATE t SET n = n + 1; state | idle in transaction wait_event | ClientRead wait_event_type | Client pg_blocking_pids | {} -[ RECORD 2 ]----+----------------------------------------------------------------------------- pid | 76445 query | SELECT pid, query, state, wait_event, wait_event_type, pg_blocking_pids(pid)+ | FROM pg_stat_activity + | WHERE backend_type = 'client backend' state | active wait_event | wait_event_type | pg_blocking_pids | {} -[ RECORD 3 ]----+----------------------------------------------------------------------------- pid | 77683 query | UPDATE t SET n = n + 2; state | active wait_event | transactionid wait_event_type | Lock pg_blocking_pids | {77591} ``` Состояние «idle in transaction» означает, что сеанс начал транзакцию, но в настоящее время ничего не делает, а транзакция осталась незавершенной. Это может стать проблемой, если ситуация возникает систематически (например, из-за некорректной реализации приложения или из-за ошибок в драйвере), поскольку открытая транзакция удерживает снимок данных и таким образом препятствует очистке. В арсенале администратора имеется параметр idle\_in\_transaction\_session\_timeout, позволяющий принудительно завершать сеансы, в которых транзакция простаивает больше указанного времени. Также имеется параметр idle\_session\_timeout — принудительно завершает сеансы, простаивающие больше указанного времени вне транзакции. ## Page 13: Page 13 {#source-page-013} А мы покажем, как завершить блокирующий сеанс вручную. Сначала узнаем номер заблокированного процесса при помощи функции pg\_blocking\_pids: ```sql => SELECT pid AS blocked_pid FROM pg_stat_activity WHERE backend_type = 'client backend' AND cardinality(pg_blocking_pids(pid)) > 0; ``` ```text blocked_pid ------------- 77683 (1 row) ``` Блокирующий процесс можно вычислить и без функции pg\_blocking\_pids, используя запросы к таблице блокировок. Запрос покажет две строки: одна транзакция получила блокировку (granted), а другая ее ожидает. ```sql => SELECT locktype, transactionid, pid, mode, granted FROM pg_locks WHERE transactionid IN ( SELECT transactionid FROM pg_locks WHERE pid = 77683 AND NOT granted ); ``` ```text locktype | transactionid | pid | mode | granted ---------------+---------------+-------+---------------+--------- transactionid | 3999 | 77591 | ExclusiveLock | t transactionid | 3999 | 77683 | ShareLock | f (2 rows) ``` В общем случае нужно аккуратно учитывать тип блокировки. Выполнение запроса можно прервать функцией pg\_cancel\_backend. В нашем случае транзакция простаивает, так что просто прерываем сеанс, вызвав pg\_terminate\_backend: ```sql => SELECT pg_terminate_backend(b.pid) FROM unnest(pg_blocking_pids(77683)) AS b(pid); ``` ```text pg_terminate_backend ---------------------- t (1 row) ``` Функция unnest нужна, поскольку pg\_blocking\_pids возвращает массив идентификаторов процессов, блокирующих искомый обслуживающий процесс. В нашем примере блокирующий процесс один, но в общем случае их может быть несколько. Подробнее о блокировках рассказывается в курсе DBA2. Проверим состояние обслуживающих процессов. ```sql => SELECT pid, query, state, wait_event, wait_event_type FROM pg_stat_activity WHERE backend_type = 'client backend' \gx ``` ```sql -[ RECORD 1 ]---+------------------------------------------------------ pid | 76445 query | SELECT pid, query, state, wait_event, wait_event_type+ | FROM pg_stat_activity + | WHERE backend_type = 'client backend' state | active wait_event | wait_event_type | -[ RECORD 2 ]---+------------------------------------------------------ pid | 77683 query | UPDATE t SET n = n + 2; state | idle wait_event | ClientRead wait_event_type | Client ``` Осталось только два, причем заблокированный успешно завершил транзакцию. Представление pg\_stat\_activity показывает информацию не только про обслуживающие процессы, но и про служебные фоновые процессы экземпляра: ```sql => SELECT pid, backend_type, backend_start, state FROM pg_stat_activity; ``` ## Page 14: Page 14 {#source-page-014} ```text pid | backend_type | backend_start | state -------+------------------------------+-------------------------------+-------- 76348 | autovacuum launcher | 2026-04-10 17:05:07.647151+03 | 76349 | logical replication launcher | 2026-04-10 17:05:07.649822+03 | 76445 | client backend | 2026-04-10 17:05:11.564288+03 | active 77683 | client backend | 2026-04-10 17:05:26.907786+03 | idle 76345 | background writer | 2026-04-10 17:05:07.612697+03 | 76344 | checkpointer | 2026-04-10 17:05:07.612244+03 | 76347 | walwriter | 2026-04-10 17:05:07.647576+03 | (7 rows) ``` Сравним с тем, что показывает операционная система: ```sh student$ sudo head -n 1 /var/lib/postgresql/16/main/postmaster.pid ``` ```text 76343 ``` ```sh student$ ps -o pid,command --ppid 76343 ``` ```text PID COMMAND 76344 postgres: 16/main: checkpointer 76345 postgres: 16/main: background writer 76347 postgres: 16/main: walwriter 76348 postgres: 16/main: autovacuum launcher 76349 postgres: 16/main: logical replication launcher 76445 postgres: 16/main: student admin_monitoring [local] idle 77683 postgres: 16/main: student admin_monitoring [local] idle ``` ## Page 15: Выполнение команд {#source-page-015} **Представления для отслеживания выполнения** - команда - представление - ANALYZE - pg\_stat\_progress\_analyze - CREATE INDEX, REINDEX - pg\_stat\_progress\_create\_index - VACUUM - pg\_stat\_progress\_vacuum - включая процессы автоочистки - CLUSTER, VACUUM FULL - pg\_stat\_progress\_cluster - Создание базовой резервной копии - pg\_stat\_progress\_basebackup - COPY - pg\_stat\_progress\_copy ### Комментарии преподавателя Следить за ходом выполнения некоторых потенциально долгих команд можно, выполняя запросы к соответствующим представлениям. Структуры представлений описаны в документации: Создание резервных копий рассматривается в модуле «Резервное копирование». ## Page 16: Дополнительная статистика {#source-page-016} **Расширения в поставке** - pg\_stat\_statements - статистика по запросам - pgstattuple - статистика по версиям строк - pg\_buffercache - состояние буферного кеша - Другие расширения - pg\_wait\_sampling - статистика ожиданий - pg\_stat\_kcache - статистика по процессору и вводу-выводу - pg\_qualstats - статистика по предикатам - ... ### Комментарии преподавателя Существуют расширения, позволяющие собирать дополнительную статистику, как входящие в поставку, так и внешние. Например, расширение pg\_stat\_statements сохраняет информацию о запросах, выполняемых СУБД; pg\_buffercache позволяет заглянуть в содержимое буферного кеша и т. п. Многие важные расширения рассматриваются в курсах DBA2 и DEV2. ## Page 17: Журнал сообщений {#source-page-017} **Настройка журнальных записей** - Ротация файлов журнала - Анализ журнала ### Комментарии преподавателя Второй важный источник информации о происходящем на сервере — журнал сообщений. ## Page 18: Журнал сообщений {#source-page-018} **Приемник сообщений (log\\\_destination = список)** - stderr - поток ошибок - csvlog - формат CSV (только с коллектором) - jsonlog - формат JSON (только с коллектором) - syslog - демон syslog - eventlog - журнал событий Windows - Коллектор сообщений (logging\_collector = on) - позволяет собирать дополнительную информацию - никогда не теряет сообщения (в отличие от syslog) - записывает stderr, csvlog и jsonlog в файл log\_directory/log\_filename ### Комментарии преподавателя Журнал сообщений сервера можно направлять в разные приемники и выводить в разных форматах. Основной параметр, который определяет приемник и формат — log\_destination (можно указать один или несколько приемников через запятую). Значение stderr (установленное по умолчанию) выводит сообщения в стандартный поток ошибок в текстовом виде. Значение syslog направляет сообщения демону syslog в Unix-системах, а eventlog — в журнал событий Windows. Обычно дополнительно включают специальный процесс — коллектор сообщений. Он позволяет записать больше информации, поскольку собирает ее со всех процессов, составляющих PostgreSQL. Он спроектирован так, что никогда не теряет сообщения; как следствие, при большой нагрузке он может стать узким местом. Коллектор сообщений включается параметром logging\_collector. При значении stderr информация записывается в каталог, определяемый параметром log\_directory, в файл, определяемый параметром log\_filename. Включенный коллектор сообщений позволяет также указать приемник csvlog; в этом случае информация будет сбрасываться в формате CSV в файл log\_filename с расширением csv. При использовании приемника jsonlog содержимое файла отчета будет записываться в формате JSON, а имя файла будет иметь расширение json. ## Page 19: Информация в журнале {#source-page-019} **Настройки** - информация - параметр - сообщения определенного уровня - log\_min\_messages - время выполнения длинных команд - log\_min\_duration\_statement - время выполнения команд - log\_duration - имя приложения - application\_name - контрольные точки - log\_checkpoints - подключения и отключения - log\_(dis)connections - длинные ожидания - log\_lock\_waits - текст выполняемых команд - log\_statement - использование временных файлов - log\_temp\_files - ... ### Комментарии преподавателя В журнал сообщений сервера можно выводить множество полезной информации. По умолчанию почти весь вывод отключен, чтобы не превратить запись журнала в узкое место для подсистемы ввода-вывода. Администратор должен решить, какая информация важна, обеспечить необходимое место на диске для ее хранения и оценить влияние записи журнала на общую производительность системы. ## Page 20: Ротация файлов журнала {#source-page-020} **С помощью коллектора сообщений** - настройка - параметр - маска имени файла - log\_filename - время ротации, мин - log\_rotation\_age - размер файла для ротации, КБ - log\_rotation\_size - перезаписывать ли файлы - log\_truncate\_on\_rotation = on - комбинируя маску файла и время ротации, получаем разные схемы: - 'postgresql-%H.log', '1h' - 24 файла в сутки - 'postgresql-%a.log', '1d' - 7 файлов в неделю - Внешние средства - системная утилита logrotate ### Комментарии преподавателя Если записывать журнал в один файл, рано или поздно он вырастет до огромных размеров, что крайне неудобно для администрирования и анализа. Поэтому обычно используется та или иная схема ротации журналов. Коллектор сообщений имеет встроенные средства ротации, которые настраиваются несколькими параметрами, основные из которых приведены на слайде. Параметр log\_filename позволяет задавать не просто имя, а маску имени файла с помощью спецсимволов даты и времени. Параметр log\_rotation\_age задает время переключения на следующий файл в минутах (а log\_rotation\_size — размер файла, при котором надо переключаться на следующий). Включение log\_truncate\_on\_rotation перезаписывает уже существующие файлы. Таким образом, комбинируя маску и время переключения, можно получать разные схемы ротации. В качестве альтернативы можно воспользоваться внешними программами ротации, например пакетный дистрибутив для Ubuntu использует системную утилиту logrotate (ее настройки находятся в файле /etc/logrotate.d/postgresql-common). ## Page 21: Анализ журнала {#source-page-021} **Средства операционной системы** - grep, awk... - Специальные средства анализа - pgBadger — требует определенных настроек журнала ### Комментарии преподавателя Анализировать журналы можно по-разному. Можно искать определенную информацию средствами ОС или специально разработанными скриптами. Стандартом де-факто для анализа является программа PgBadger , но надо иметь в виду, что она накладывает определенные ограничения на содержимое журнала. В частности, допускаются сообщения только на английском языке. ## Page 22: Анализ журнала {#source-page-022} Посмотрим самый простой случай. Например, нас интересуют сообщения FATAL: ```sh student$ sudo grep FATAL /var/log/postgresql/postgresql-16-main.log | tail -n 10 ``` ```text 2026-04-10 12:23:48.292 MSK [45370] bob@postgres FATAL: no pg_hba.conf entry for host "[local]", user "bob", database "postgres", no encryption 2026-04-10 12:23:48.887 MSK [45554] alice@postgres FATAL: Peer authentication failed for user "alice" 2026-04-10 12:23:49.061 MSK [45580] bob@postgres FATAL: Peer authentication failed for user "bob" 2026-04-10 12:23:49.977 MSK [45822] bob@student FATAL: Peer authentication failed for user "bob" 2026-04-10 12:25:19.077 MSK [50193] FATAL: terminating logical replication worker due to administrator command 2026-04-10 12:25:19.167 MSK [50083] student@replica_overview_logical_dba2 FATAL: terminating connection due to administrator command 2026-04-10 17:01:57.270 MSK [50752] FATAL: terminating logical replication worker due to administrator command 2026-04-10 17:01:57.316 MSK [57977] student@replica_overview_logical_dba FATAL: the database system is shutting down 2026-04-10 17:02:07.201 MSK [58117] student@student FATAL: terminating connection due to administrator command 2026-04-10 17:05:28.677 MSK [77591] student@admin_monitoring FATAL: terminating connection due to administrator command ``` Сообщение «terminating connection» вызвано тем, что мы завершали блокирующий процесс. Обычное применение журнала — анализ наиболее продолжительных запросов. Включим вывод всех команд и времени их выполнения: ```sql => ALTER SYSTEM SET log_min_duration_statement=0; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` Теперь выполним какую-нибудь команду: ```sql => SELECT sum(random()) FROM generate_series(1,1_000_000); ``` ```text sum -------------------- 499660.39133418293 (1 row) ``` И посмотрим журнал: ```sh student$ sudo tail -n 1 /var/log/postgresql/postgresql-16-main.log ``` ```sql 2026-04-10 17:05:30.303 MSK [76445] student@admin_monitoring LOG: duration: 348.094 ms statement: SELECT sum(random()) FROM generate_series(1,1_000_000); ``` ## Page 23: Внешний мониторинг {#source-page-023} **Универсальные системы мониторинга** - Zabbix, Munin, Cacti... - в облаке: Okmeter, NewRelic, Datadog... - Системы мониторинга PostgreSQL - pg\_profile, pgpro\_pwr - PGObserver - PostgreSQL Workload Analyzer (PoWA) - Open PostgreSQL Monitoring (OPM) - ... ### Комментарии преподавателя На практике требуется полноценная система мониторинга, которая собирает различные метрики как с PostgreSQL, так и с операционной системы, хранит историю этих метрик, отображает их в виде понятных графиков, имеет средства оповещения при выходе определенных метрик за установленные границы и т. д. Собственно PostgreSQL не располагает такой системой; он только предоставляет средства для получения информации о себе (которые мы рассмотрели). Поэтому для полноценного мониторинга нужно выбрать внешнюю систему. Таких систем существует довольно много. Есть универсальные системы, имеющие плагины или агенты для PostgreSQL. К ним относятся Zabbix, Munin, Cacti, облачные сервисы Okmeter, NewRelic, Datadog и другие. Есть и системы, ориентированные специально на PostgreSQL, такие, как PGObserver, PoWA, OPM и т. д. Расширение pg\_profile позволяет строить снимки статических данных и сравнивать их, выявляя ресурсоемкие операции и их динамику. Расширенная коммерческая версия этого расширения — pgpro\_pwr. Неполный, но представительный список систем мониторинга можно посмотреть на странице Для более глубокого погружения в эту тему можно прочитать книгу Алексея Лесовского «Мониторинг PostgreSQL»: ## Page 24: Итоги {#source-page-024} **Мониторинг заключается в контроле работы сервера как со стороны операционной системы, так и со стороны самого сервера** - PostgreSQL предоставляет накопительную статистику и журнал сообщений сервера - Для полноценного мониторинга требуется внешняя система ## Page 25: Практика {#source-page-025} 1. В новой базе данных создайте таблицу, выполните вставку нескольких строк, а затем удалите все строки. - Посмотрите статистику обращений к таблице и сопоставьте - цифры (n\_tup\_ins, n\_tup\_del, n\_live\_tup, n\_dead\_tup) - с вашей активностью. - Выполните очистку (vacuum), снова проверьте статистику и сравните с предыдущими цифрами. 2. Создайте ситуацию взаимоблокировки двух транзакций. - Посмотрите, какая информация записывается при этом - в журнал сообщений сервера. ### Комментарии преподавателя 2. Взаимоблокировка (deadlock) — ситуация, в которой две (или больше) транзакций ожидают друг друга. В отличие от обычной блокировки при взаимоблокировке у транзакций нет возможности выйти из этого «тупика» и СУБД вынуждена принимать меры — одна из транзакций будет принудительно прервана, чтобы остальные могли продолжить выполнение. Проще всего воспроизвести взаимоблокировку на таблице с двумя строками. Первая транзакция меняет (и, соответственно, блокирует) первую строку, а вторая — вторую. Затем первая транзакция пытается изменить вторую строку и «повисает» на блокировке. А потом вторая транзакция пытается изменить первую строку — и тоже ждет освобождения блокировки. ## Page 26: Статистика обращений к таблице {#source-page-026} Создаем базу данных и таблицу: ```sql => CREATE DATABASE admin_monitoring; ``` ```sql CREATE DATABASE ``` ```sql => \c admin_monitoring ``` ```text You are now connected to database "admin_monitoring" as user "student". ``` ```sql => CREATE TABLE t(n numeric); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO t SELECT 1 FROM generate_series(1,1000); ``` ```sql INSERT 0 1000 ``` ```sql => DELETE FROM t; ``` ```sql DELETE 1000 ``` Проверяем статистику обращений. ```sql => SELECT * FROM pg_stat_all_tables WHERE relid = 't'::regclass \gx ``` ```text -[ RECORD 1 ]-------+------------------------------ relid | 16387 schemaname | public relname | t seq_scan | 1 last_seq_scan | 2026-04-10 17:10:45.442005+03 seq_tup_read | 1000 idx_scan | last_idx_scan | idx_tup_fetch | n_tup_ins | 1000 n_tup_upd | 0 n_tup_del | 1000 n_tup_hot_upd | 0 n_tup_newpage_upd | 0 n_live_tup | 0 n_dead_tup | 1000 n_mod_since_analyze | 2000 n_ins_since_vacuum | 1000 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 ``` Мы вставили 1000 строк (n\_tup\_ins = 1000), удалили 1000 строк (n\_tup\_del = 1000). После этого не осталось активных версий строк (n\_live\_tup = 0), все 1000 строк не актуальны на текущий момент (n\_dead\_tup = 1000). Выполним очистку. ```sql => VACUUM; ``` ```sql VACUUM ``` ```sql => SELECT * FROM pg_stat_all_tables WHERE relid = 't'::regclass \gx ``` ## Page 27: Page 27 {#source-page-027} ```text -[ RECORD 1 ]-------+------------------------------ relid | 16387 schemaname | public relname | t seq_scan | 1 last_seq_scan | 2026-04-10 17:10:45.442005+03 seq_tup_read | 1000 idx_scan | last_idx_scan | idx_tup_fetch | n_tup_ins | 1000 n_tup_upd | 0 n_tup_del | 1000 n_tup_hot_upd | 0 n_tup_newpage_upd | 0 n_live_tup | 0 n_dead_tup | 0 n_mod_since_analyze | 2000 n_ins_since_vacuum | 0 last_vacuum | 2026-04-10 17:10:46.860029+03 last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 1 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0 ``` Неактуальные версии строк убраны при очистке (n\_dead\_tup = 0), очистка обрабатывала таблицу один раз (vacuum\_count = 1). ### 2. Взаимоблокировка ```sql => INSERT INTO t VALUES (1),(2); ``` ```sql INSERT 0 2 ``` Одна транзакция блокирует первую строку таблицы... ```sh student$ psql ``` ```sql => \c admin_monitoring ``` ```text You are now connected to database "admin_monitoring" as user "student". ``` ```text => BEGIN; ``` ```text BEGIN ``` ```sql => UPDATE t SET n = 10 WHERE n = 1; ``` ```sql UPDATE 1 ``` Затем другая транзакция блокирует вторую строку... ```sh student$ psql ``` ```sql => \c admin_monitoring ``` ```text You are now connected to database "admin_monitoring" as user "student". ``` ```text => BEGIN; ``` ```text BEGIN ``` ```sql => UPDATE t SET n = 200 WHERE n = 2; ``` ```sql UPDATE 1 ``` Теперь первая транзакция пытается изменить вторую строку и ждет ее освобождения... ```sql => UPDATE t SET n = 20 WHERE n = 2; ``` А вторая транзакция пытается изменить первую строку... ```sql => UPDATE t SET n = 100 WHERE n = 1; ``` ...и происходит взаимоблокировка. Сервер обрывает одну из транзакций: ## Page 28: Page 28 {#source-page-028} ```text ERROR: deadlock detected DETAIL: Process 98731 waits for ShareLock on transaction 739; blocked by process 98599. Process 98599 waits for ShareLock on transaction 740; blocked by process 98731. HINT: See server log for query details. ``` ```text CONTEXT: while updating tuple (0,1) in relation "t" ``` Другая транзакция разблокируется: ```sql UPDATE 1 ``` Проверим информацию в журнале сообщений: ```sh student$ sudo tail -n 8 /var/log/postgresql/postgresql-16-main.log ``` ```sql 2026-04-10 17:10:50.363 MSK [98731] student@admin_monitoring ERROR: deadlock detected 2026-04-10 17:10:50.363 MSK [98731] student@admin_monitoring DETAIL: Process 98731 waits for ShareLock on transaction 739; blocked by process 98599. Process 98599 waits for ShareLock on transaction 740; blocked by process 98731. Process 98731: UPDATE t SET n = 100 WHERE n = 1; Process 98599: UPDATE t SET n = 20 WHERE n = 2; 2026-04-10 17:10:50.363 MSK [98731] student@admin_monitoring HINT: See server log for query details. 2026-04-10 17:10:50.363 MSK [98731] student@admin_monitoring CONTEXT: while updating tuple (0,1) in relation "t" 2026-04-10 17:10:50.363 MSK [98731] student@admin_monitoring STATEMENT: UPDATE t SET n = 100 WHERE n = 1; ``` ## Page 29: Практика+ {#source-page-029} 1. Установите расширение pg\_stat\_statements. - Выполните несколько произвольных запросов. - Посмотрите, какую информацию показывает представление - pg\_stat\_statements. ### Комментарии преподавателя 1. Для установки расширения потребуется перед выполнением команды CREATE EXTENSION изменить значение параметра shared\_preload\_libraries с последующей перезагрузкой сервера. ## Page 30: 1. Расширение pg_stat_statements {#source-page-030} Расширение собирает статистику планирования и выполнения всех запросов. Для работы расширения требуется загрузить одноименный модуль. Для этого имя модуля нужно прописать в параметре shared\_preload\_libraries и перезагрузить сервер. Изменять этот параметр лучше в файле postgresql.conf, но для целей демонстрации установим параметр с помощью команды ALTER SYSTEM. ```sql => ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements'; ``` ```sql ALTER SYSTEM ``` ```sql => \q ``` ```sh student$ sudo pg_ctlcluster 16 main restart ``` ```sh student$ psql ``` ```sql => CREATE DATABASE admin_monitoring; ``` ```sql CREATE DATABASE ``` ```sql => \c admin_monitoring ``` ```text You are now connected to database "admin_monitoring" as user "student". ``` ```sql => CREATE EXTENSION pg_stat_statements; ``` ```sql CREATE EXTENSION ``` Теперь выполним несколько запросов. ```sql => CREATE TABLE t(n numeric); ``` ```sql CREATE TABLE ``` ```sql => SELECT format('INSERT INTO t VALUES (%L)', x) FROM generate_series(1,5) AS x \gexec ``` ```sql INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 ``` ```sql => DELETE FROM t; ``` ```sql DELETE 5 ``` ```sql => DROP TABLE t; ``` ```sql DROP TABLE ``` Посмотрим на статистику запроса, который выполнялся чаще всего. ```sql => SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY calls DESC LIMIT 1; ``` ```sql query | calls | total_exec_time ---------------------------+-------+--------------------- INSERT INTO t VALUES ($1) | 5 | 0.13486299999999998 (1 row) ``` Разделяемая библиотека больше не требуется, восстановим исходное значение параметра: ```sql => ALTER SYSTEM RESET shared_preload_libraries; ``` ```sql ALTER SYSTEM ``` ```sql => \q ``` ```sh student$ sudo pg_ctlcluster 16 main restart ```