Восстановление PostgreSQL после сбоя в продакшне: полное руководство по WAL, PITR и автоматизации

Восстановление базы данных после сбоя - критическая задача для любого администратора. Стандартные бэкапы в формате pg_dump не защищают от потери данных между моментами их создания. Для полного восстановления на произвольный момент времени нужна комбинация полных физических бэкапов и непрерывной архивации журналов транзакций (WAL). Это руководство объясняет, как настроить надежную систему резервного копирования и восстановления для PostgreSQL, включая Point-in-Time Recovery (PITR), работу с pg_basebackup для больших баз и инструмент pg_rewind для быстрого исправления реплик.

Почему стандартные бэкапы не спасут от всех сбоев и что такое WAL

Ежедневный pg_dump создает снимок базы данных на конкретный момент времени. Если сбой произошел через 23 часа после бэкапа, вы потеряете все изменения за этот период. Механизм Write-Ahead Logging (WAL) решает эту проблему, записывая все изменения в журнал перед их фиксацией в основных файлах данных. Архивация этих журналов позволяет "проиграть" изменения поверх полного бэкапа, восстанавливая состояние базы на любой момент в прошлом.

Как работает механизм Write-Ahead Logging (WAL)

PostgreSQL записывает каждую транзакцию в сегменты WAL (файлы по 16 МБ по умолчанию) перед тем, как подтвердить ее (commit). Только после гарантированной записи в WAL изменения применяются к таблицам и индексам в data directory. Каждая запись в журнале имеет уникальный Log Sequence Number (LSN) - адрес, который определяет ее положение в общем потоке записей. При восстановлении PostgreSQL последовательно применяет записи из WAL, начиная с LSN, который был актуален на момент создания полного бэкапа, и может остановиться на любом последующем LSN или метке времени.

Сценарии сбоев, где только WAL и PITR спасут данные

Удаление данных пользователем в 14:00 при последнем полном бэкапе в 00:00. PITR позволит восстановить состояние базы на 13:59, откатив ошибочную команду DELETE.
Аппаратный сбой диска с data directory в 12:00. Полный бэкап с позавчера и архив WAL за последние 36 часов восстановят все данные вплоть до момента сбоя.
Логическая ошибка в приложении, постепенно портившая данные несколько часов. Определив точное время начала порчи, можно восстановить базу на момент непосредственно перед ним.

Настройка надёжной системы архивации WAL: archive_command и restore_command

Архивация WAL - это процесс непрерывного копирования заполненных сегментов журнала в надежное внешнее хранилище. Для этого PostgreSQL выполняет команду, заданную в параметре archive_command, для каждого завершенного WAL-сегмента. Команда restore_command, в свою очередь, указывает, как извлекать эти сегменты при восстановлении.

Конфигурация postgresql.conf для включения архивации WAL

В файле конфигурации postgresql.conf необходимо установить следующие параметры:

wal_level = replica             # Минимальный уровень для архивации и репликации
archive_mode = on               # Включение режима архивации
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'
# restore_command будет задан позже, при восстановлении

Параметр wal_level определяет объем информации, записываемой в WAL. Значение 'replica' достаточно для архивации и репликации. Параметр archive_command выполняется для каждого заполненного сегмента WAL. Символ %p заменяется на полный путь к сегменту, а %f - на его имя файла. Команда выше проверяет, не существует ли уже файл в архиве (во избежание перезаписи), и копирует его. После изменения конфигурации перезагрузите PostgreSQL: systemctl reload postgresql (или pg_ctl reload).

Практические примеры archive_command для NFS, S3 и локального диска

Выбор команды зависит от инфраструктуры. Вот проверенные примеры:

Для сетевой файловой системы (NFS): archive_command = 'cp %p /mnt/nfs_archive/%f'. Убедитесь, что каталог смонтирован и PostgreSQL имеет права на запись.
Для облачного хранилища AWS S3: archive_command = 'aws s3 cp %p s3://your-bucket/wal_archive/%f'. Требует установленного и настроенного AWS CLI с соответствующими правами IAM.

Надежный скрипт с логированием и обработкой ошибок: Укажите в archive_command путь к собственному скрипту, например, archive_command = '/usr/local/bin/archive_wal.sh %p %f'. Пример содержимого скрипта:

#!/bin/bash
WAL_PATH=$1
WAL_FILE=$2
ARCHIVE_DIR="/mnt/wal_archive"
LOG_FILE="/var/log/postgresql/wal_archive.log"

if cp "$WAL_PATH" "$ARCHIVE_DIR/$WAL_FILE"; then
    echo "$(date): $WAL_FILE archived successfully." >> "$LOG_FILE"
    exit 0
else
    echo "$(date): ERROR archiving $WAL_FILE. Retrying..." >> "$LOG_FILE"
    # Можно добавить логику повторных попыток
    exit 1
fi

Организуйте директорию архива по датам или таймлайнам для удобства управления. Например, /mnt/wal_archive/$(date +%Y-%m)/.

Настройка restore_command для будущего восстановления

Команда restore_command указывается в postgresql.conf на этапе восстановления. Она сообщает PostgreSQL, как получить архивированный WAL-сегмент. Пример для S3: restore_command = 'aws s3 cp s3://your-bucket/wal_archive/%f %p'. Здесь %f - имя требуемого файла, а %p - полный путь, по которому PostgreSQL ожидает его найти в директории восстановления (pg_wal). Подготовьте директорию восстановления, скопируйте туда базовый бэкап и настройте postgresql.conf с этим параметром перед запуском процесса восстановления.

Полное восстановление кластера с помощью pg_basebackup и WAL

Восстановление после полной потери основного сервера начинается с размещения полного физического бэкапа, созданного утилитой pg_basebackup, на новом хосте. Затем поверх него применяются архивированные WAL-сегменты, что приводит базу в актуальное состояние.

Создание базового бэкапа pg_basebackup: команды и оптимизация для больших баз

Базовая команда создания бэкапа с ведущего сервера (primary):

pg_basebackup -D /backup/base_backup -h primary_host -p 5432 -U replicator -v -P -Xs -R

-D: Целевая директория для бэкапа.
-Xs: Потоковая передача WAL во время создания бэкапа. Это обязательный параметр для создания согласованного бэкапа без остановки основной базы.
-R: Создает файлы standby.signal и postgresql.auto.conf с настройками для запуска в режиме реплики, что полезно и для восстановления.
-z: Сжатие передаваемых данных. Экономит время и место при работе с базами в 1+ ТБ.
--checkpoint=fast: Ускоряет начало бэкапа, заставляя PostgreSQL выполнить быструю контрольную точку.

Для очень больших баз (1+ ТБ) рассмотрите создание бэкапа на предварительно настроенной реплике, чтобы снять нагрузку с мастера. Используйте отдельный диск с высокой скоростью записи (например, NVMe) для директории /backup.

Пошаговый процесс восстановления из полного бэкапа и архива WAL

Остановите PostgreSQL на новом сервере, если он запущен: systemctl stop postgresql.
Очистите data directory кластера (например, /var/lib/postgresql/16/main/), оставив только необходимые конфигурационные файлы, если они есть.
Распакуйте или скопируйте содержимое базового бэкапа (из /backup/base_backup) в очищенный data directory.
Настройте postgresql.conf в data directory. Убедитесь, что параметры listen_addresses, port и другие соответствуют новой среде. Критически важно задать правильный restore_command, как описано выше.
Создайте пустой файл recovery.signal в data directory. Это сигнал для PostgreSQL запустить процесс восстановления при старте.
Запустите PostgreSQL: systemctl start postgresql. Сервер перейдет в режим восстановления, начнет применять WAL из архива и остановится, достигнув конца потока журналов.
Проверьте логи (например, journalctl -u postgresql -f). Вы должны увидеть сообщения о поиске и применении WAL-файлов.
После завершения восстановления сервер перейдет в режим standby (ожидания). Чтобы начать работу с базой, выполните promote, создав файл touch /var/lib/postgresql/16/main/standby.signal и перезапустив сервис, либо выполнив команду pg_ctl promote.

Point-in-Time Recovery (PITR): восстановление на момент до ошибки

PITR - это процедура восстановления базы не до последнего доступного состояния, а до произвольного момента в прошлом. Это главный инструмент для отката ошибочных транзакций, таких как случайное удаление данных.

Настройка параметров восстановления: recovery_target_time, recovery_target_lsn

Точка восстановления задается в файле postgresql.conf кластера, который готовится к восстановлению. Основные параметры:

recovery_target_time = '2026-05-05 14:29:00 UTC': Восстановление до конкретного времени.
recovery_target_lsn = '0/7001D28': Восстановление до конкретного LSN (Log Sequence Number). LSN можно найти в логах PostgreSQL или с помощью утилиты pg_waldump.
recovery_target_name = 'before_critical_delete': Восстановление до заранее созданной именованной точки восстановления (created with pg_create_restore_point('before_critical_delete')).
recovery_target_timeline = 'latest': Указание таймлайна для восстановления (обычно 'latest').

Установите только один из параметров recovery_target_*. После завершения PITR будет создан новый таймлайн, что позволяет в будущем восстановиться до состояния после этого восстановления.

Практический пример: восстановление после случайного DELETE без WHERE

Сценарий: В 14:30 выполнен ошибочный DELETE FROM important_table. Полный бэкап есть на 00:00, архив WAL ведется.

Определите время до ошибки, например, 14:29:00.
Восстановите полный бэкап на новом сервере, как описано в предыдущем разделе.
В postgresql.conf восстановленного кластера задайте: recovery_target_time = '2026-05-05 14:29:00 UTC'.
Убедитесь, что restore_command настроен корректно и указывает на архив WAL.
Создайте файл recovery.signal в data directory.
Запустите PostgreSQL. Он применит WAL вплоть до указанного времени и остановится.
Проверьте, что данные в important_table присутствуют.
Для завершения восстановления и перевода кластера в режим записи выполните promote: pg_ctl promote -D /var/lib/postgresql/16/main/.

Управление таймлайнами после PITR

Каждое восстановление с использованием PITR создает новую ветку истории - таймлайн. Это предотвращает конфликты LSN при будущих восстановлениях. После PITR PostgreSQL создает файл .history в архиве WAL, который описывает, где началась новая ветка. При последующих восстановлениях можно указать recovery_target_timeline, чтобы выбрать нужную ветку истории. Важно архивировать WAL-файлы со всех таймлайнов для обеспечения гибкости восстановления в будущем.

Автоматизация бэкапов и мониторинг: готовые скрипты и интеграция

Ручное выполнение бэкапов ненадежно. Автоматизация гарантирует регулярность и позволяет интегрировать проверки в процессы мониторинга.

Shell-скрипт для ежедневного полного бэкапа и ротации

#!/bin/bash
# backup_postgres.sh
BACKUP_DIR="/backup/postgres/$(date +%Y-%m-%d)"
LOG_FILE="/var/log/postgresql/backup.log"
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"

echo "$(date): Starting base backup" >> "$LOG_FILE"

if pg_basebackup -D "$BACKUP_DIR" -h localhost -p 5432 -U backup_user -v -P -Xs -z --checkpoint=fast; then
    echo "$(date): Base backup successful" >> "$LOG_FILE"
    # Удаляем бэкапы старше RETENTION_DAYS
    find /backup/postgres/ -maxdepth 1 -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \; 2>/dev/null
    echo "$(date): Old backups cleaned" >> "$LOG_FILE"
else
    echo "$(date): ERROR: Base backup failed" >> "$LOG_FILE"
    exit 1
fi

Настройте запуск скрипта через cron: 0 2 * * * /usr/local/bin/backup_postgres.sh. Убедитесь, что пользователь, от имени которого запускается cron, имеет необходимые права, а для пользователя backup_user настроена репликация.

Как проверять целостность бэкапов с pg_verifybackup

Начиная с PostgreSQL 13, в поставку входит утилита pg_verifybackup. Она проверяет целостность бэкапа, созданного pg_basebackup:

pg_verifybackup -D /backup/postgres/2026-05-05/

Утилита проверит контрольные суммы файлов, наличие обязательных файлов и корректность структуры. Регулярная проверка (например, раз в неделю) позволяет выявить проблемы с хранилищем до момента аварии. Интегрируйте эту проверку в скрипт бэкапа или в отдельное задание cron.

Мониторинг архивации WAL и алертирование о проблемах

Неудачная архивация WAL означает потенциальную потерю данных. Простой скрипт для проверки и алертирования:

#!/bin/bash
# check_wal_archive.sh
LAST_WAL=$(sudo -u postgres psql -t -c "SELECT pg_walfile_name(pg_current_wal_lsn())" 2>/dev/null | tr -d ' ')
LAST_ARCHIVED=$(ls -Art /mnt/wal_archive/ | tail -n 1 2>/dev/null)

# Если последний WAL на сервере и последний в архиве отличаются более чем на 1 файл
if [[ "$LAST_ARCHIVED" < "$LAST_WAL" ]]; then
    # Отправка алерта (пример для Telegram через curl)
    curl -s -X POST "https://api.telegram.org/bot/sendMessage" \
         -d "chat_id=&text=WAL archive lag detected. Last on server: $LAST_WAL. Last archived: $LAST_ARCHIVED"
    echo "$(date): ALERT: WAL archive lag" >> /var/log/postgresql/wal_monitor.log
fi

Также регулярно проверяйте логи PostgreSQL (grep -i "archive command failed" /var/log/postgresql/postgresql-16-main.log) и рост директории с архивами. Для комплексного мониторинга инфраструктуры, включая базы данных, полезно иметь централизованную систему автоматизации и мониторинга.

Восстановление реплики после расхождения: быстрый pg_rewind вместо полной переинициализации

Если реплика отстала от мастера или на ней были произведены записи (например, при временном переводе в режим чтения-записи), ее традиционно переинициализируют заново с помощью pg_basebackup. Для больших баз это долгая операция. Утилита pg_rewind анализирует WAL обоих серверов и переписывает только те данные в data directory реплики, которые различаются, приводя ее в актуальное состояние за минуты вместо часов.

Когда и как использовать pg_rewind для восстановления реплики

pg_rewind применяется, когда реплика отключилась от мастера, но ее data directory осталась в относительно целостном состоянии. Обязательное условие: на мастере должен быть включен параметр wal_log_hints = on или использоваться полные page writes. Это позволяет отслеживать изменения на уровне блоков данных.

Процесс:

Остановите PostgreSQL на реплике: systemctl stop postgresql.
Выполните pg_rewind, указав data directory реплики и параметры подключения к мастеру:
```
pg_rewind -D /var/lib/postgresql/16/main/ \
          --source-server="host=primary_host port=5432 user=postgres dbname=postgres"
```
После успешного выполнения pg_rewind в data directory реплики появится файл standby.signal, а в postgresql.auto.conf добавятся параметры подключения к мастеру.
Запустите PostgreSQL на реплике: systemctl start postgresql. Она подключится к мастеру и начнет потоковую репликацию с текущей позиции.

Сравнение pg_rewind и полной переинициализации для больших баз

Рассмотрим кластер с базой данных объемом 1 ТБ.

Полная переинициализация через pg_basebackup: Требует передачи всего 1 ТБ данных по сети. Это создает высокую нагрузку на сеть и диск мастера на несколько часов. Реплика недоступна все это время.
Восстановление через pg_rewind: Анализирует WAL-журналы и передает только измененные за период расхождения блоки данных. Если реплика отстала на 1 час при средней нагрузке в 100 ГБ изменений, pg_rewind передаст примерно 100 ГБ. Время восстановления сокращается до десятков минут, нагрузка на инфраструктуру минимальна.

Вывод: pg_rewind - предпочтительный метод для быстрого восстановления реплик в высоконагруженных средах. Однако для его работы требуется заранее выполнить настройку (wal_log_hints = on). Проектирование отказоустойчивой инфраструктуры с четким разделением ролей - ключевая задача, которую помогает решить понимание взаимодействия между DBA, DevOps и сисадминами.

Настройка надежной стратегии резервного копирования и восстановления PostgreSQL - не разовая задача, а процесс. Начните с включения архивации WAL, автоматизируйте создание полных бэкапов и регулярно проводите учебные восстановления в изолированной среде. Это единственный способ гарантировать, что в момент реального сбоя ваши процедуры сработают. Для управления подобными сложными процессами и документацией эффективно использовать структурированную базу знаний, где будут храниться все скрипты, конфигурации и отчеты о проведенных тестах восстановления.