PostgreSQL — pg_wal переполняется: диагностика и безопасная очистка (Runbook)

Приветcтвую 👋!

Цель: быстро выяснить, почему pg_wal занял много места, безопасно освободить диск и настроить профилактику.

---

1) Симптом и критичность

Проверка на Linux:

du -hd1 /data/main | sort -hr

3) Диагностика (psql)

Как зайти в psql:

sudo -u postgres psql

Если у тебя data dir нестандартный (как /data/main), psql все равно подключится. Путь проверяй через SHOW data_directory;.

3.1 Узнать каталог данных

SHOW data_directory;

3.2 Размер WAL через SQL (PostgreSQL 10+)

SELECT pg_size_pretty(SUM(size)) AS wal_dir_size
FROM pg_ls_waldir();

3.3 Ключевые параметры

SHOW wal_keep_size;
SHOW max_wal_size;
SHOW archive_mode;
SHOW archive_command;

3.4 Репликация и слоты

Активные реплики:

SELECT * FROM pg_stat_replication;

Слоты:

SELECT * FROM pg_replication_slots;

Сколько WAL удерживает каждый слот:

SELECT
  slot_name,
  active,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

Если есть inactive слот и он больше не нужен, именно он часто удерживает сотни гигабайт WAL.

3.5 Долгие транзакции

SELECT pid,
       age(backend_xmin) AS xmin_age,
       state,
       wait_event_type,
       wait_event,
       query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

---

4) Экстренные действия (безопасный минимум)

4.1 CHECKPOINT (перед любыми ручными действиями)

CHECKPOINT;

Нельзя вручную удалять файлы из pg_wal. Это может сломать кластер. PostgreSQL должен управлять pg_wal сам.

5) Очистка архива WAL (как сделал коллега)

Команда вида:

/usr/bin/pg_archivecleanup /path/to/archive 00000001000010E000000049

Что делает:

• удаляет из архива все WAL-сегменты старше указанного

pg_archivecleanup предназначен для каталога архивов WAL (куда пишет archive_command), а не для pg_wal.

Рекомендованный порядок:

1. CHECKPOINT;
2. убедиться, что целевой сегмент корректный (обычно берут “последний нужный” по логам/процессу архивации)
3. выполнить pg_archivecleanup

---

6) Профилактика (production defaults)

Рекомендации (подбирай под нагрузку):

wal_keep_size = 1GB
max_wal_size = 4GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

Применение (пример):

ALTER SYSTEM SET wal_keep_size = '1GB';
ALTER SYSTEM SET max_wal_size = '4GB';
SELECT pg_reload_conf();

Мониторинг: добавь контроль размера WAL
SELECT pg_size_pretty(SUM(size)) FROM pg_ls_waldir();

---

7) Чек-лист дежурного

• du -sh .../pg_wal показал рост
• CHECKPOINT;
• Проверил pg_replication_slots и retained_wal
• Проверил pg_stat_replication
• Проверил archive_mode/archive_command и каталог архива
• Очистил архив через pg_archivecleanup (если актуально)
• Настроил wal_keep_size/max_wal_size и алерты

8) FAQ

Почему нужен CHECKPOINT перед очисткой?
Чтобы PostgreSQL “закрыл” старые страницы и обновил состояние, после чего старые WAL становятся не нужны.

Почему pg_wal может быть огромным?
Чаще всего: слоты удерживают WAL, реплика отвалилась, или архивирование сломано/не чистится архив.