Налаштування Point-in-Time Recovery (PITR) для баз даних
Point-in-Time Recovery дозволяє відновити базу даних на будь-який момент часу в минулому — не лише на точку останньої резервної копії. Критично для сценаріїв: «о 14:37 хтось видалив таблицю», «о 09:15 сталася неправильна UPDATE мільйона записів».
Як працює PITR
PITR вимагає двох компонентів:
- Базовий снапшот (повна резервна копія) — вихідна точка
- 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=300RPO ≤ 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 дні.