Настройка Point-in-Time Recovery (PITR) базы данных

Настройка Point-in-Time Recovery (PITR) базы данных

Point-in-Time Recovery позволяет восстановить базу данных на любой момент времени в прошлом — не только на точку последнего бэкапа. Критически важно для сценариев: «в 14:37 кто-то удалил таблицу», «в 09:15 произошёл неверный UPDATE миллиона записей».

Как работает PITR

PITR требует двух компонентов:

1. Базовый снапшот (полный бэкап) — отправная точка
2. WAL/binlog-архив — непрерывный поток транзакционных логов от момента снапшота до настоящего времени

Восстановление = применение базового снапшота + воспроизведение WAL-логов до нужного момента.

PostgreSQL PITR

Настройка WAL-архивации

В postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=myapp archive-push %p'
archive_timeout = 300  # архивировать WAL не реже раз в 5 минут

Перезапуск PostgreSQL обязателен после изменения wal_level.

pgBackRest: полная конфигурация PITR

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/mnt/backup-storage/pgbackrest
repo1-retention-full=3
repo1-retention-archive=14

# Репликация в S3
repo2-type=s3
repo2-path=/pgbackrest
repo2-s3-bucket=company-db-backups
repo2-s3-region=eu-west-1
repo2-retention-full=2

[myapp]
pg1-path=/var/lib/postgresql/14/main
pg1-port=5432

Базовый бэкап (выполнять раз в неделю):

pgbackrest --stanza=myapp --type=full backup

Восстановление на конкретный момент

# Остановка PostgreSQL
systemctl stop postgresql

# Восстановление на точный timestamp
pgbackrest --stanza=myapp restore \
  --target="2024-03-15 14:30:00" \
  --target-action=promote \
  --delta

# Запуск PostgreSQL
systemctl start postgresql

Опция --delta восстанавливает только изменившиеся файлы — значительно ускоряет процесс.

Восстановление на LSN (Log Sequence Number):

pgbackrest --stanza=myapp restore \
  --target="0/5000000" \
  --target-action=promote

Проверка доступного диапазона PITR

-- Минимальная точка восстановления
SELECT pg_walfile_name(pg_current_wal_lsn());

-- Время первой доступной WAL-записи
SELECT min(last_modified) FROM pgbackrest.archive_list;

MySQL PITR через binlog

Настройка бинарных логов

# /etc/mysql/mysql.conf.d/mysqld.cnf
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 14
max_binlog_size = 500M
binlog_row_image = FULL

Восстановление через mysqlbinlog

# Сначала восстанавливаем полный бэкап
mysql -u root myapp < full_backup_20240310.sql

# Находим нужный момент в binlog
mysqlbinlog --base64-output=DECODE-ROWS -v \
  /var/log/mysql/mysql-bin.000042 | grep -A 5 "14:37"

# Применяем binlog до нужного момента
mysqlbinlog \
  --stop-datetime="2024-03-15 14:36:59" \
  /var/log/mysql/mysql-bin.000040 \
  /var/log/mysql/mysql-bin.000041 \
  /var/log/mysql/mysql-bin.000042 | mysql -u root myapp

Пропуск проблемной транзакции (не весь диапазон):

mysqlbinlog \
  --start-position=4 --stop-position=1234 \
  mysql-bin.000042 | mysql -u root myapp

# Пропускаем позиции 1234-5678 (DROP TABLE)

mysqlbinlog \
  --start-position=5679 \
  mysql-bin.000042 | mysql -u root myapp

Валидация PITR

Регулярные учения по восстановлению — минимум раз в квартал:

#!/bin/bash
# Тест PITR в изолированном окружении
TARGET_TIME=$(date -d "2 hours ago" "+%Y-%m-%d %H:%M:%S")

# Восстановление в тестовую инсталляцию
pgbackrest --stanza=myapp restore \
  --target="$TARGET_TIME" \
  --target-action=promote \
  --pg1-path=/var/lib/postgresql/14/pitr-test \
  --pg1-port=5434

# Проверка: данные на нужный момент присутствуют
psql -p 5434 -U postgres myapp -c \
  "SELECT COUNT(*) FROM orders WHERE created_at < '${TARGET_TIME}';"

Метрики и мониторинг

• RPO (Recovery Point Objective) — насколько свежие данные можно восстановить. С archive_timeout=300 RPO ≤ 5 минут.
• RTO (Recovery Time Objective) — время восстановления. Для базы 100GB с pgBackRest — 15–40 минут.

Мониторинг лага архивации:

SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

Алерт при лаге WAL-архивации более 15 минут означает проблему с доставкой логов.

Срок выполнения

Настройка PITR для PostgreSQL с pgBackRest (включая S3-репозиторий и тест восстановления) — 2–3 рабочих дня. MySQL binlog PITR — 2 дня.