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

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

05 мая 2026 12 мин. чтения
Содержание статьи

Восстановление PostgreSQL после сбоя требует не только последнего бэкапа, но и последовательного применения WAL. В этой статье показано, как подготовить систему к аварийному восстановлению, восстановить кластер из физического бэкапа, выполнить PITR до ошибочной транзакции и быстро вернуть реплику в рабочее состояние с помощью pg_rewind.

Восстановление PostgreSQL после сбоя: краткий план (TL;DR)

  • WAL-архивация — обязательный компонент защиты от потери данных между полными бэкапами.
  • PITR позволяет восстановить базу на момент до ошибочной транзакции, например, случайного DELETE.
  • pg_basebackup создает согласованный физический бэкап без остановки основной базы.
  • Автоматизация бэкапов и мониторинг архивации WAL снижают риски незаметной потери данных.
  • pg_rewind восстанавливает реплику после расхождения за минуты вместо часов полной переинициализации.

Когда использовать WAL, PITR и pg_rewind

  • WAL-архивация: используйте для восстановления изменений между полными физическими бэкапами и снижения потенциальной потери данных при сбое.
  • PITR: используйте после случайного DELETE, ошибочного обновления или постепенной порчи данных, когда нужно вернуть базу к состоянию до конкретного момента.
  • Полное восстановление: используйте после потери сервера или data directory, когда кластер нужно собрать из pg_basebackup и архива WAL.
  • pg_rewind: используйте после расхождения primary и реплики, например после переключения ролей или записи на бывшей реплике.

Что подготовить до сбоя PostgreSQL

  • Регулярный полный физический бэкап кластера через pg_basebackup в отдельном хранилище.
  • Непрерывную WAL-архивацию во внешнее хранилище, доступное независимо от основного сервера.
  • Проверенные права доступа для записи WAL и чтения архивов во время восстановления.
  • Зафиксированные значения data directory, версии PostgreSQL, параметры restore_command и порядок запуска recovery.
  • Регулярный тест восстановления в изолированной среде. Наличие файла бэкапа само по себе не подтверждает, что из него можно запустить рабочий кластер.

Минимальные требования и совместимость версий PostgreSQL

Примеры в статье рассчитаны на PostgreSQL 12 и новее, где для запуска восстановления используются файлы recovery.signal и standby.signal. В PostgreSQL 11 и более ранних версиях применялся параметрический файл recovery.conf, поэтому перед выполнением команд проверьте версию сервера и расположение data directory.

Утилита pg_verifybackup входит в поставку начиная с PostgreSQL 13. Названия systemd-сервиса, путь к data directory, доступность AWS CLI и параметры подключения могут отличаться в зависимости от дистрибутива и способа установки PostgreSQL.

Содержание

Почему нужен WAL для восстановления PostgreSQL и что такое 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 часов восстановят все данные вплоть до момента сбоя.
  • Логическая ошибка в приложении, постепенно портившая данные несколько часов. Определив точное время начала порчи, можно восстановить базу на момент непосредственно перед ним.

Как восстановить PostgreSQL из 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 с этим параметром перед запуском процесса восстановления.

Как восстановить PostgreSQL из 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

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

Как сделать PITR в PostgreSQL: восстановление до ошибки

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

Как настроить recovery_target_time для отката на нужный момент?

Точка восстановления задается в файле 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': Восстановление до заранее созданной именованной точки восстановления, созданной командой SELECT 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 ведется.

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

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

Каждое восстановление с использованием PITR создает новую ветку истории — таймлайн. Это предотвращает конфликты LSN при будущих восстановлениях.

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

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

Как автоматизировать бэкапы PostgreSQL и мониторинг 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/ -mindepth 1 -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 настроена репликация. Для автоматического запуска без запроса пароля используйте защищенный файл .pgpass или PGPASSFILE.

Как проверить бэкап PostgreSQL с pg_verifybackup

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

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

Утилита проверит контрольные суммы файлов, наличие обязательных файлов и корректность структуры. Регулярная проверка (например, раз в неделю) позволяет выявить проблемы с хранилищем до момента аварии. Она не заменяет тестовое восстановление и отдельно не подтверждает доступность всех сегментов WAL. Интегрируйте эту проверку в скрипт бэкапа или в отдельное задание 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)
ARCHIVE_LAG_SECONDS=$(sudo -u postgres psql -t -c "SELECT COALESCE(EXTRACT(EPOCH FROM (now() - last_archived_time)), 999999) FROM pg_stat_archiver;" 2>/dev/null | awk '{printf "%d", $1}')

# Проверяем, не устарела ли последняя успешная архивация
if [[ -n "$ARCHIVE_LAG_SECONDS" && "$ARCHIVE_LAG_SECONDS" -gt 300 ]]; then
    # Отправка алерта (пример для Telegram через curl)
    curl -s -X POST "https://api.telegram.org/bot<YOUR_BOT_TOKEN>/sendMessage" \
         -d "chat_id=<YOUR_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

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

Как восстановить реплику PostgreSQL с помощью pg_rewind

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

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

pg_rewind применяется, когда реплика отключилась от мастера, но ее data directory осталась в относительно целостном состоянии.

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

Процесс:

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

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

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

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

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

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

Поделиться:
Сохранить гайд? В закладки браузера