Бэкапы, расширения и мониторинг

Бэкапы, расширения и мониторинг — три кита эксплуатации: восстановиться после катастрофы, нарастить возможности базы и понимать, что внутри неё происходит.

Бэкап — копия данных, из которой можно восстановиться. PostgreSQL даёт два подхода: логический (выгрузка структуры и данных командами SQL) и физический (копия файлов кластера плюс WAL для восстановления на точку во времени).

Репликация защищает от отказа сервера, но не от ошибки человека или приложения: ошибочную DROP TABLE реплика честно повторит у себя тоже. Поэтому нужны бэкапы — независимый способ откатиться к прошлому состоянию. Завершаем раздел тремя темами эксплуатации: бэкапы и восстановление, расширение PostgreSQL готовыми модулями и наблюдение за здоровьем базы через системные представления.

Зачем это на практике

Эти три навыка отличают «база просто работает» от «базой управляют»:

• Бэкапы — единственная защита от логических катастроф (ошибочный DELETE, баг в миграции, повреждение данных) и обязательное требование любой серьёзной эксплуатации.
• Расширения превращают PostgreSQL в платформу: гео-данные, полнотекстовый поиск, статистика запросов, криптография — всё подключается одной командой.
• Мониторинг даёт раннее предупреждение: видно медленные запросы, раздувание таблиц, лаг репликации, нехватку соединений — до того, как сбой заметят пользователи.

Логический бэкап: pg_dump и pg_restore

pg_dump выгружает базу как набор команд, способных её воссоздать: CREATE TABLE, INSERT/COPY, индексы, ограничения. Это удобно для переноса между серверами, выборочного восстановления и смены версии PostgreSQL.

# текстовый дамп всей базы в SQL-файл
pg_dump -h localhost -U app_user shop > shop.sql

# восстановить текстовый дамп — это обычный SQL-скрипт
psql -h localhost -U app_user -d shop_restored < shop.sql

# дамп в custom-формате (-Fc): сжатый, для гибкого восстановления через pg_restore
pg_dump -Fc -h localhost -U app_user shop > shop.dump

Custom-формат (-Fc) восстанавливают утилитой pg_restore — она умеет выбирать отдельные объекты и распараллеливать загрузку:

# восстановить всё из custom-дампа, 4 параллельных потока
pg_restore -j 4 -h localhost -U app_user -d shop_restored shop.dump

# восстановить ТОЛЬКО одну таблицу из дампа
pg_restore -h localhost -U app_user -d shop -t orders shop.dump

Ключевое свойство pg_dump — благодаря MVCC он делает согласованный снимок на момент запуска: дамп отражает базу как единое целое, даже если во время выгрузки шли изменения. Минус — полная пересборка: для терабайтной базы дамп и восстановление идут часами.

Физический бэкап и PITR через WAL

Для больших баз и точного восстановления используют физический бэкап — копию файлов кластера от pg_basebackup плюс непрерывный архив WAL. Связка базовой копии и WAL даёт PITR (Point-In-Time Recovery).

PITR — восстановление базы на конкретную точку во времени: берём базовый бэкап и «доигрываем» поверх него заархивированные WAL-записи до нужного мгновения — например, до секунды перед ошибочным DELETE.

# на сервере: включить непрерывное архивирование WAL
archive_mode = on
archive_command = 'cp %p /backup/wal_archive/%f'   # куда складывать WAL-сегменты

# %p — путь к готовому сегменту WAL, %f — его имя

# снять базовый физический бэкап
pg_basebackup -h localhost -U repl_user -D /backup/base -Ft -X stream

Чтобы восстановиться на точку во времени, разворачивают базовый бэкап и задают целевой момент — PostgreSQL проигрывает WAL ровно до него:

# при восстановлении: postgresql.conf
restore_command = 'cp /backup/wal_archive/%f %p'   # откуда брать WAL
recovery_target_time = '2026-06-27 14:29:59+00'    # до какого момента доигрывать

# плюс файл recovery.signal в каталоге данных — сигнал «стартуй в режиме восстановления»

Так из бэкапа недельной давности и архива WAL базу можно «отмотать» к состоянию за секунду до фатальной ошибки — чего суточный логический дамп дать не может.

Расширения: CREATE EXTENSION

PostgreSQL спроектирован расширяемым: готовые модули добавляют типы, функции и индексы одной командой CREATE EXTENSION. Это переносимый SQL:

-- посмотреть, какие расширения доступны к установке
SELECT name, default_version, comment
FROM pg_available_extensions
WHERE name IN ('postgis', 'pg_stat_statements', 'pgcrypto')
ORDER BY name;

-- установить расширение в текущую базу
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Полезные расширения, которые стоит знать:

Некоторые расширения (как pg_stat_statements) требуют предварительной загрузки библиотеки через shared_preload_libraries в postgresql.conf и перезапуска сервера — об этом пишут в их документации.

Мониторинг через pg_stat_*

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

Найти самые тяжёлые запросы — классическая задача, которую решает pg_stat_statements:

-- топ-5 запросов по суммарному времени выполнения
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

А вот как увидеть зависшие или долго идущие запросы прямо сейчас:

-- активные запросы дольше 30 секунд
SELECT pid, now() - query_start AS running_for, state, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '30 seconds'
ORDER BY running_for DESC;

Как это работает под капотом

Оба вида бэкапа опираются на знакомые механизмы. pg_dump открывает транзакцию и читает согласованный снимок MVCC, поэтому видит базу как единое целое на момент старта, не блокируя писателей. PITR стоит на журнале упреждающей записи: WAL фиксирует каждое изменение, и, проиграв архив WAL поверх базовой копии, PostgreSQL воспроизводит историю до заданного момента — тот же механизм, что и в репликации, только источник WAL не сеть, а архив.

Статистика pg_stat_* собирается фоново: сборщик статистики накапливает счётчики по таблицам, индексам и активности, а представления отдают их в виде таблиц. pg_stat_statements вдобавок нормализует тексты запросов (заменяет литералы на параметры), чтобы SELECT ... WHERE id = 1 и ... id = 2 схлопнулись в одну запись — поэтому видно нагрузку по видам запросов, а не по конкретным значениям.

Частые ошибки

• Бэкап без проверки восстановления. Непроверенный бэкап — это не бэкап. Регулярно разворачивайте дамп на тестовом сервере, иначе повреждённый файл выяснится в самый неподходящий момент.
• Только суточный pg_dump для критичной базы. Между дампами можно потерять до суток данных; где это недопустимо — нужен физический бэкап с архивом WAL и PITR.
• archive_command, который молча падает. Если архивирование WAL ломается (нет места, недоступен путь), PostgreSQL копит WAL и диск переполняется — команда архивации обязана сообщать об ошибках.
• pg_stat_statements без shared_preload_libraries. Установить расширение мало — без предзагрузки библиотеки и перезапуска оно не собирает статистику.
• Смотреть только в pg_stat_activity. Это срез «сейчас»; для поиска систематически медленных мест нужен pg_stat_statements с агрегированной историей.

Итоги

• Логический бэкап (pg_dump/pg_restore) выгружает базу командами SQL — удобно для переноса и выборочного восстановления, но это полная пересборка.
• Физический бэкап (pg_basebackup) плюс архив WAL дают PITR — восстановление на любую точку во времени, например за секунду до ошибки.
• Расширения подключаются командой CREATE EXTENSION: PostGIS, pg_stat_statements, pgcrypto, pg_trgm и другие; часть требует shared_preload_libraries.
• Мониторинг — это представления pg_stat_*: pg_stat_activity (что сейчас), pg_stat_statements (медленные запросы), pg_stat_user_tables (таблицы и VACUUM).
• Главное правило бэкапов: регулярно проверяйте восстановление, иначе бэкап существует только на бумаге.