Backup и восстановление PostgreSQL

Август 2017. GitLab.com. DBA случайно выполняет `rm -rf` на production БД вместо staging. 300 ГБ данных. Оказывается: репликация не работала 6 часов, S3 бэкапы не работали, pg_dump не работал. Только один WAL-архив - но устаревший на 6 часов. 6 часов потерянных данных, 18 часов downtime, публичная постмортем. Эта история изменила индустрию: у GitLab теперь 5 независимых систем бэкапа. PostgreSQL имеет всё для надёжного бэкапа - нужно только настроить и проверить.

• **GitLab** после инцидента 2017: 5 уровней бэкапа включая pgBackRest на S3, ежедневная проверка восстановления на тестовом сервере, мониторинг каждого уровня
• **Supabase** - pgBackRest + S3 для всех 1M+ проектов: непрерывный WAL archiving, PITR с точностью до секунды, ежедневные full backup с недельным retention
• **Dropbox** - PITR для восстановления файлов пользователей: при случайном удалении БД восстанавливается на момент до ошибки, затем нужные строки мигрируют в production

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

**pg_dump создаёт логический бэкап** - экспортирует данные в SQL-команды или специальный формат. Работает на уровне таблиц и схем, не привязан к версии PostgreSQL или физической структуре файлов. Можно восстановить на другую версию, другую платформу, в другую схему.

**pg_dump не блокирует запросы**, но делает снимок данных на момент начала дампа (MVCC snapshot). Для консистентного бэкапа всего кластера нужен pg_dumpall или физический бэкап. pg_dump не сохраняет данные WAL - восстановление возможно только на момент дампа, не на произвольный момент времени.

pg_basebackup: физический бэкап

**pg_basebackup создаёт физическую копию всего кластера** - все файлы данных как есть на диске. В отличие от pg_dump, бэкап создаётся быстро (не зависит от числа строк), но привязан к major версии PostgreSQL и платформе. Основа для streaming replication и PITR.

**pg_basebackup vs pg_dump:** basebackup в 5-10 раз быстрее на больших БД (I/O bound, не CPU bound). Но basebackup нельзя восстановить на другую версию PostgreSQL и нельзя выбрать отдельные таблицы. В production нужны оба: basebackup для быстрого восстановления всей БД, dump для точечного восстановления таблиц.

WAL Archiving: непрерывный бэкап

**WAL archiving** - сохранение каждого WAL-сегмента в надёжное хранилище по мере его заполнения. В сочетании с базовым бэкапом даёт возможность восстановления на любой момент времени (PITR). PostgreSQL вызывает `archive_command` при закрытии каждого 16 МБ WAL-сегмента.

**Если archive_command возвращает ненулевой код - PostgreSQL повторяет попытку и не удаляет WAL-сегмент.** Это гарантирует что ни один WAL-сегмент не будет потерян. Но если архивирование сломано надолго - WAL накапливается на диске. Мониторинг failed_count обязателен.

PITR: восстановление на момент времени

**Point-In-Time Recovery (PITR) - восстановление БД на любой момент времени** в прошлом. Нужно: базовый бэкап (pg_basebackup) + все WAL-сегменты с момента бэкапа. PostgreSQL воспроизводит WAL до указанного времени, транзакции, LSN или transaction ID.

**Dropbox восстанавливал данные пользователей через PITR** при случайном удалении: тысячи файлов за секунды восстанавливались с точностью до секунды. PostgreSQL воспроизводит транзакции из WAL в точности в том порядке в котором они были зафиксированы.

pgBackRest и Barman: промышленные инструменты

**pgBackRest и Barman** - специализированные инструменты управления бэкапами PostgreSQL. Решают проблемы ванильного подхода: параллельный бэкап/восстановление, инкрементальные бэкапы, встроенное шифрование, интеграция с S3/GCS/Azure, управление retention policy, автоматическая верификация.

**pgBackRest delta restore** - восстановление только изменённых страниц, а не всего бэкапа. На БД 1 ТБ с небольшим повреждением delta restore занимает минуты, полное восстановление - часы. Supabase использует pgBackRest для бэкапов всех проектов на S3.

Итоги

• **pg_dump** - логический бэкап: MVCC snapshot, кросс-версионный, но медленный на больших БД и без PITR
• **pg_basebackup** - физический бэкап: быстро, полный кластер, нужен для PITR и репликации, привязан к major версии
• **WAL archiving** (archive_command) + basebackup = непрерывный бэкап. Каждый 16MB WAL-сегмент копируется в хранилище
• **PITR** восстанавливает БД на любой момент: basebackup + WAL-цепочка до recovery_target_time/lsn/xid
• **pgBackRest** - промышленный инструмент: параллельный бэкап, S3, шифрование, delta restore, автоверификация

Связанные темы

Бэкап и репликация дополняют друг друга в production:

• Streaming Replication — pg_basebackup - основа для создания standby. WAL archiving дополняет streaming replication для PITR
• WAL: Write-Ahead Log — WAL archiving строится поверх WAL-механизма. Понимание WAL сегментов и их жизненного цикла критично для правильной настройки бэкапа
• Обновление PostgreSQL — Полный бэкап перед major upgrade - обязательный шаг. pg_basebackup - точка возврата при проблемах с обновлением

Вопросы для размышления

• Команда делает pg_dump каждую ночь в 03:00. В 14:30 разработчик выполняет `DELETE FROM users WHERE 1=1`. Сколько данных потеряно? Как WAL archiving изменил бы ситуацию?
• Бэкап 500 ГБ БД занимает 2 часа. Восстановление - тоже 2 часа. RTO требование - 30 минут. Как pgBackRest delta restore решает эту проблему? Что нужно для его работы?
• archive_command копирует WAL на NFS. pg_stat_archiver показывает failed_count растёт 4 часа. Что происходит с диском под PGDATA? Как PostgreSQL справляется с ситуацией когда архивирование недоступно?

Связанные уроки

• db-03-acid