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

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

Представьте: в 14:37 кто-то случайно удалил таблицу orders, или в 09:15 произошёл массовый неверный UPDATE на миллион строк. Без PITR восстановление — только до последнего бэкапа, и все данные после него потеряны. Мы решаем эту проблему, настраивая Point-in-Time Recovery для PostgreSQL и MySQL, чтобы вы могли откатиться на любую секунду. Стандартные бэкапы спасают от полного отказа, но не от логических ошибок. PITR даёт возможность восстановить данные с точностью до транзакции. Ключевые метрики: RPO (Recovery Point Objective) — максимальные потери данных. При archive_timeout=300 RPO ≤ 5 минут. RTO (Recovery Time Objective) — время восстановления. Для базы 100 ГБ — 15–40 минут. Без PITR восстановление может занять дни вместо часов, а потерянные данные часто невосстановимы. Согласно документации PostgreSQL, WAL-архивация является основой PITR. Экономия на одном инциденте с удалением данных может превышать $5000.

Почему PITR — не опция, а необходимость?

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

Какие базы данных мы поддерживаем?

• PostgreSQL — pgBackRest с репликацией в S3.
• MySQL — бинарные логи с binlog_format=ROW.

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

Требует двух компонентов: базовый снапшот (полный бэкап) и непрерывный поток транзакционных логов от снапшота до текущего времени (WAL для PostgreSQL, binlog для MySQL). Восстановление = базовый снапшот + воспроизведение логов до нужного момента. Стоимость хранения WAL-архивов в S3 для базы 100 ГБ — около $15/мес.

Настройка PITR для PostgreSQL

Конфигурация WAL-архивации

В postgresql.conf:

wal_level = replica archive_mode = on archive_command = 'pgbackrest --stanza=myapp archive-push %p' archive_timeout = 300 

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

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

# /etc/pgbackrest/pgbackrest.conf [global] repo1-path=/mnt/backup-storage/pgbackrest repo1-retention-full=3 repo1-retention-archive=14 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 

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

systemctl stop postgresql pgbackrest --stanza=myapp restore \ --target="YYYY-MM-DD HH:MM:SS" \ --target-action=promote \ --delta systemctl start postgresql 

Замените --target на нужный момент. --delta восстанавливает только изменившиеся файлы, ускоряя процесс.

Настройка PITR для MySQL через 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_YYYYMMDD.sql mysqlbinlog \ --stop-datetime="YYYY-MM-DD HH:MM:SS" \ /var/log/mysql/mysql-bin.000040 \ /var/log/mysql/mysql-bin.000041 \ /var/log/mysql/mysql-bin.000042 | mysql -u root myapp 

Пропуск проблемной транзакции: укажите --start-position и --stop-position в mysqlbinlog для исключения повреждённого события.

Как выбрать между PITR на PostgreSQL и MySQL?

PostgreSQL даёт больше гибкости (восстановление по LSN, параллельное применение), но требует более тщательной настройки. MySQL проще в конфигурации, но binlog-файлы могут занимать много места при ROW формате.

Сравнение локального и S3-хранилища для архива WAL

Процесс настройки PITR в нашей компании

1. Аудит текущей инфраструктуры — определяем объём базы, частоту изменений, требования к RTO/RPO.
2. Конфигурация архивации — настраиваем WAL/binlog, выбираем репозиторий (локальный диск + S3).
3. Тестовое восстановление — проверяем восстановление на случайный момент в изолированном окружении.
4. Документирование — фиксируем процедуру, создаём runbook для дежурных инженеров.
5. Обучение — проводим воркшоп для вашей команды по ручному и автоматическому восстановлению.