Работа консультантом по базам: форматы, доступы и их отзыв, план изменений и runbook с проверенными командами

Через неделю после аудита заказчик спрашивает, почему «никто не посмотрел бэкапы», а консультант уверен, что бэкапы в задачу не входили. Второй вариант: роль, выданная на три дня, живёт в базе полгода. Оба случая — не про PostgreSQL, а про то, что проект без границ, доступа с датой окончания и артефактов на каждом шаге заканчивается спором. Техническая часть работы описана в методе аудита: три дня, роль только на чтение, девять снимков, отчёт. Здесь — рамка вокруг неё: форматы, доступы и их отзыв, план изменений, runbook и передача команде. Чем DBA занят в штате, а не на проекте, разобрано в статье о профессии. Всё, что можно выполнить, выполнено на учебной базе из статьи об аудите.

DBA / 01koddo

Проект от запроса до передачи: артефакт каждого этапа

Форматы и границы

Формат определяет, за что консультант отвечает, а за что нет. Таблица — шаблон раздела договора, а не рекомендация по ценам.

Три правила границы. Каждое изменение в проде выполняет и подтверждает письменно владелец системы, консультант готовит команды и откат. То, что не проверялось, записывается в отчёте («не смотрели») — это защищает обе стороны. Суперпользователя за три дня никто не выдаёт: если без него не обойтись, это находка отчёта, а не повод попросить пароль.

Доступ: выдача и отзыв командами

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

CREATE ROLE auditor LOGIN PASSWORD 'audit' IN ROLE pg_monitor;
GRANT CONNECT ON DATABASE postgres TO auditor;

Вход по паролю через TCP работает, а чтение таблицы закрыто:

auditor|t
ERROR:  permission denied for table orders

Срок действия роли не задан, и сама она не истечёт. Для этого у ALTER ROLE есть VALID UNTIL (документация); в этом опыте он не запускался, поведение входа после срока не проверено.

Отзыв — это четыре действия, и порядок важен. Перед ними у аудитора были две открытые сессии: длинный запрос (pg_sleep) и простаивающее соединение.

ALTER ROLE auditor NOLOGIN;
SELECT count(*) AS still_connected FROM pg_stat_activity WHERE usename = 'auditor';
SELECT count(*) FILTER (WHERE pg_terminate_backend(pid)) AS terminated FROM pg_stat_activity WHERE usename = 'auditor';
REVOKE pg_monitor FROM auditor;
REVOKE CONNECT ON DATABASE postgres FROM auditor;

вывод (шаги выполнялись по очереди; ALTER ROLE занял 0,971 мс, завершение сессий — 0,342 мс; сообщения о разрыве соединений в консолях самих сессий опущены):

 still_connected 
-----------------
               2
(1 row)

 terminated 
------------
          2
(1 row)

NOLOGIN не выгнал никого: обе сессии остались (still_connected = 2). Причина в коде: флаг rolcanlogin проверяется только при установке соединения (InitializeSessionUserId, L855-L859):

		if (!bypass_login_check && !rform->rolcanlogin)
			ereport(FATAL,
					(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),
					 errmsg("role \"%s\" is not permitted to log in",
							rname)));

Поэтому закрытие состоит из NOLOGIN и отдельного pg_terminate_backend. Проверка, что всё закрыто, и удаление роли:

SELECT count(*) FROM pg_stat_activity WHERE usename = 'auditor';
SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname = 'auditor';
SELECT count(*) AS memberships FROM pg_auth_members WHERE member = 'auditor'::regrole;

PGPASSWORD=audit psql -h 127.0.0.1 -U auditor postgres -Atc "select 1"

вывод:

0
 rolname | rolcanlogin 
---------+-------------
 auditor | f
(1 row)

 memberships 
-------------
           0
(1 row)

psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  role "auditor" is not permitted to log in

Затем DROP ROLE auditor отработал без ошибки, и SELECT count(*) FROM pg_roles WHERE rolname = 'auditor' вернул 0. Для отчёта запиши момент закрытия и выводы этих проверок: это единственное доказательство, что доступ больше не действует.

План изменений на находке аудита

Находка 2 из отчёта: внешний ключ order_items.order_id без индекса. План — документ на одну страницу; поля идут в том порядке, в каком их читает владелец системы, который подписывает выполнение.

Откат проверен до применения: сборка запущена с потолком statement_timeout = 150ms, оборвана, а затем откатана тем же способом, каким откатывают настоящий запуск.

SET lock_timeout = '2s';
SET statement_timeout = '150ms';
CREATE INDEX CONCURRENTLY order_items_order_id_idx ON order_items (order_id);
RESET statement_timeout;
SELECT indexrelid::regclass AS idx, indisvalid, indisready FROM pg_index WHERE indrelid = 'order_items'::regclass;
DROP INDEX CONCURRENTLY order_items_order_id_idx;
SELECT count(*) AS left_indexes FROM pg_index WHERE indrelid = 'order_items'::regclass AND NOT indisprimary;

вывод:

ERROR:  canceling statement due to statement timeout
           idx            | indisvalid | indisready 
--------------------------+------------+------------
 order_items_pkey         | t          | t
 order_items_order_id_idx | f          | t
(2 rows)

 left_indexes 
--------------
            0
(1 row)

Оборванная сборка оставила order_items_order_id_idx с indisvalid = f: это именно тот «невалидный индекс», о котором предупреждает документация, а DROP INDEX CONCURRENTLY убрал его (5,209 мс). Настоящий запуск после этого:

           idx            | indisvalid | size  
--------------------------+------------+-------
 order_items_order_id_idx | t          | 11 MB
(1 row)

Создание заняло 258,099 мс на 1 млн строк. План «после»: Index Scan using order_items_order_id_idx, буферы shared hit=6 read=5 вместо shared read=6370, время выполнения 0,051 мс вместо 11,579 мс. Это один запрос на тёплой учебной базе; для решения владельца важен порядок, а не само число. Как вести индексы в боевой базе, описано в статье об индексах без остановки записи.

Runbook «таблица разбухла»

Runbook отличается от статьи тем, что его читают ночью, когда звонит дежурный. Поэтому у каждого шага есть команда, ожидаемый вывод, время и условие остановки. Шаблон — таблица; ниже все шаги выполнены на таблице events после того, как приложение открыло транзакцию и забыло про неё, а затем удалило ещё 250 тыс. строк.

SELECT pg_size_pretty(pg_table_size('events')) AS table_size, (pgstattuple('events')).dead_tuple_percent AS dead_pct, (pgstattuple('events')).free_percent AS free_pct;
SELECT reloptions, last_autovacuum, n_dead_tup FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid WHERE s.relname = 'events';
SELECT pid, usename, state, now() - xact_start AS age, left(query, 40) AS query FROM pg_stat_activity WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL ORDER BY xact_start;
SELECT count(*) AS slots FROM pg_replication_slots;
SELECT count(*) AS prepared FROM pg_prepared_xacts;
VACUUM (VERBOSE) events;

вывод (у VACUUM (VERBOSE) оставлена строка tuples:, остальные строки вывода опущены):

 table_size | dead_pct | free_pct 
------------+----------+----------
 112 MB     |    47.85 |    23.66
(1 row)

         reloptions         | last_autovacuum | n_dead_tup 
----------------------------+-----------------+------------
 {autovacuum_enabled=false} |                 |    1000000
(1 row)

 pid | usename  |        state        |       age       |                  query                   
-----+----------+---------------------+-----------------+------------------------------------------
 201 | app      | idle in transaction | 00:00:02.440422 | UPDATE orders SET amount = amount WHERE 
 215 | postgres | active              | 00:00:00        | SELECT pid, usename, state, now() - xact
(2 rows)

 slots 
-------
     0
(1 row)

 prepared 
----------
        0
(1 row)

tuples: 250025 removed, 500000 remain, 250000 are dead but not yet removable

Диагноз ставит сочетание двух строк: в reloptions стоит autovacuum_enabled=false, а VACUUM не может убрать 250 000 свежих мёртвых версий, потому что транзакция с pid 201 началась раньше удаления. Старые 250 025 версий она не держала, поэтому они ушли. Механизм горизонта разобран в статье о держателях xmin. Строка про n_dead_tup = 1000000 при 47,85 % мёртвых по pgstattuple напоминает: счётчик статистики не измерение, решение принимают по pgstattuple.

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'app' AND state = 'idle in transaction';
ALTER TABLE events RESET (autovacuum_enabled);
VACUUM (VERBOSE) events;
SELECT pg_size_pretty(pg_table_size('events')) AS table_size, (pgstattuple('events')).dead_tuple_percent AS dead_pct, (pgstattuple('events')).free_percent AS free_pct;
SET lock_timeout = '3s';
VACUUM FULL events;
SELECT pg_size_pretty(pg_table_size('events')) AS table_size, (pgstattuple('events')).dead_tuple_percent AS dead_pct, (pgstattuple('events')).free_percent AS free_pct;

вывод (у VACUUM (VERBOSE) оставлена строка tuples: основной таблицы):

 pg_terminate_backend 
----------------------
 t
(1 row)

tuples: 250000 removed, 250000 remain, 0 are dead but not yet removable

 table_size | dead_pct | free_pct 
------------+----------+----------
 112 MB     |        0 |    71.51
(1 row)

 table_size | dead_pct | free_pct 
------------+----------+----------
 28 MB      |        0 |      0.6
(1 row)

После обычного VACUUM мёртвых версий 0, но файл остался 112 МБ, а свободного места в нём 71,51 %: VACUUM отдал место таблице, а не операционной системе. Файл сократился до 28 МБ только после VACUUM FULL, который по документации берёт исключительную блокировку таблицы; поэтому шаг 7 всегда идёт под lock_timeout и по согласованию с владельцем. Что в этом runbook не проверено: pg_repack (не запускался, он описан в статье о раздутии), поведение шагов на реплике, время VACUUM FULL на таблице, которая не помещается в память, и то, как автовакуум поведёт себя под настоящей нагрузкой. Эти строки в боевой версии runbook остаются с пометкой «не проверено» до первой репетиции на копии.

Для других инцидентов runbook пишется по тому же шаблону; опора — переполнение XID и стратегия RPO и RTO.

Разговор с бизнесом

Руководитель не читает reloptions: находка переводится в потерю или риск и в решение. Три примера из этого кейса, числа — из выводов выше.

• «Нет архива WAL, нет реплик» → «если диск потеряется, база вернётся только до последней копии, а сейчас копий нет». Решение: выделить день на архив и пробное восстановление.
• «Внешний ключ без индекса» → «страница заказа читает всю таблицу позиций: 6 370 буферов вместо 11». Решение: подписать окно на создание индекса.
• «autovacuum_enabled=false и забытая транзакция» → «таблица занимает 112 МБ, а после уплотнения — 28 МБ, и это будет расти, пока приложение забывает закрывать транзакции». Решение: поручить команде найти место в коде и поставить таймаут.

Разговор строится так: что видно, что это значит для денег или риска, что предлагается, что будет без решения. Параметры и названия функций остаются в приложении к отчёту.

Типовые ошибки первого года

• Работа начата без списка «не смотрели»: через месяц заказчик считает, что аудит покрыл бэкапы и безопасность.
• Роль выдана без срока и не закрыта: NOLOGIN без завершения сессий оставляет их работающими, как показал опыт выше.
• Проект закрыт отчётом, без разбора с командой: через квартал те же находки возвращаются.

Чек-лист старта и завершения

Старт:

1. Бриф на страницу: что болит, кто принимает работу, какой формат из таблицы.
2. Список «не смотрели» согласован до начала, а не дописан в конце.
3. Роль только на чтение выдана с проверкой границы, срок записан.

Завершение:

1. Отчёт и план с откатом переданы, каждое изменение имеет проверку «до» и «после».
2. Runbook выполнен командой заказчика хотя бы раз, непроверенные строки помечены.
3. Доступ закрыт: NOLOGIN, завершение сессий, REVOKE, проверка входа и счёт сессий записаны в отчёт.

Стенд убирается командой docker rm -f -v dba-consult: без -v том с данными остаётся на диске.