PostgreSQL и MariaDB резервное копирование с моментальным восстановлением

Файловые резервные копии бесполезны для баз данных. Если вы загрузите запущенный каталог данных PostgreSQL, вы получите поврежденную кучу файлов, которые не будут приниматься никаким разумным восстановлением. Резервные копии баз данных нуждаются в собственных инструментах, и, что более важно, они нуждаются в своевременном восстановлении.

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

Архивные копии WAL завершили сегменты WAL в безопасном каталоге. Без этого PostgreSQL перерабатывает старые файлы WAL, и вы теряете возможность воспроизводить их после определенного момента.

Сначала создайте резервные каталоги:

1
2

sudo mkdir -p /backup/postgresql/base /backup/postgresql/wal_archive
sudo chown -R postgres:postgres /backup/postgresql

Откройте файл конфигурации PostgreSQL:

1

sudo nano /var/lib/pgsql/17/data/postgresql.conf

Добавить или изменить эти настройки.

1
2
3
4

wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/postgresql/wal_archive/%f'
archive_timeout = 60

Перезагрузите PostgreSQL, чтобы применить изменения:

1

sudo systemctl restart postgresql-17

Автоматизация ежедневных резервных копий

Ручные резервные копии хороши для тестирования. Производственные системы нуждаются в автоматизированных, запланированных резервных копиях. Системные таймеры являются надежной альтернативой cron, со встроенной логистикой и устойчивостью при перезагрузке.

Резервное копирование PostgreSQL и Timer

Сначала создайте резервный сценарий:

1

sudo nano /usr/local/bin/pg-backup.sh

Добавьте следующий сценарий:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

#!/bin/bash
BACKUP_DIR="/backup/postgresql/base"
DATE=$(date +%Y%m%d_%H%M%S)
DEST="${BACKUP_DIR}/${DATE}"

mkdir -p "$DEST"
pg_basebackup -D "$DEST" -Ft -z -Xs -U postgres

# Keep only the last 7 daily backups
find "$BACKUP_DIR" -maxdepth 1 -type d -mtime +7 -exec rm -rf {} \;

Сделайте его исполняемым:

1

sudo chmod +x /usr/local/bin/pg-backup.sh

Создание системного сервисного блока:

1

sudo nano /etc/systemd/system/pg-backup.service

Определите службу для работы в качестве пользователя Postgres с низким приоритетом ввода/вывода:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

[Unit]
Description=PostgreSQL Base Backup
After=postgresql-17.service
Requires=postgresql-17.service

[Service]
Type=oneshot
User=postgres
Nice=10
IOSchedulingClass=idle
ExecStart=/usr/local/bin/pg-backup.sh

Создайте блок таймера:

1

sudo nano /etc/systemd/system/pg-backup.timer

Расписание его работы ежедневно в 01:00 с сохранением включено:

1
2
3
4
5
6
7
8
9

[Unit]
Description=Daily PostgreSQL Backup Timer

[Timer]
OnCalendar=*-*-* 01:00:00
Persistent=true

[Install]
WantedBy=timers.target

Настройка Persistent=true гарантирует, что резервная копия будет запущена при следующей возможности, если система была отключена в запланированное время.

Включите и запустите таймер:

1
2

sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer

Восстановление к точному второму

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

Остановить PostgreSQL:

1

sudo systemctl stop postgresql-17

Уберите поврежденный каталог данных с дороги. Не удаляйте его, если вам нужно исследовать:

1

sudo mv /var/lib/pgsql/17/data /var/lib/pgsql/17/data.corrupted

Создайте новый каталог данных и извлеките в него резервную копию базы:

1
2
3
4
5

sudo mkdir -p /var/lib/pgsql/17/data
sudo chown postgres:postgres /var/lib/pgsql/17/data
sudo chmod 700 /var/lib/pgsql/17/data
sudo -u postgres tar xzf /backup/postgresql/base/base.tar.gz -C /var/lib/pgsql/17/data/
sudo -u postgres tar xzf /backup/postgresql/base/pg_wal.tar.gz -C /var/lib/pgsql/17/data/pg_wal/

Создайте файл recovery.signal. Это говорит PostgreSQL ввести режим восстановления при запуске:

1

sudo -u postgres touch /var/lib/pgsql/17/data/recovery.signal

Откройте конфигурацию PostgreSQL, чтобы добавить параметры восстановления:

1

sudo nano /var/lib/pgsql/17/data/postgresql.conf

Добавьте эти три строки в конце файла. Замените метку времени на безопасное время восстановления:

1
2
3

restore_command = 'cp /backup/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-11-13 19:44:17'
recovery_target_action = 'promote'

Запустите PostgreSQL. Он автоматически входит в режим восстановления:

1

sudo systemctl start postgresql-17

Очистите поврежденный каталог данных, как только вы будете удовлетворены восстановлением:

1

sudo rm -rf /var/lib/pgsql/17/data.corrupted

Резервное копирование MariaDB и Timer

Создайте сценарий резервного копирования MariaDB:

1

sudo nano /usr/local/bin/mariadb-backup.sh

Добавьте следующий сценарий:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

#!/bin/bash
BACKUP_DIR="/backup/mariadb"
DATE=$(date +%Y%m%d_%H%M%S)
DEST="${BACKUP_DIR}/${DATE}"

mkdir -p "$DEST"
mariadb-backup --backup --target-dir="$DEST"
mariadb-backup --prepare --target-dir="$DEST"

# Keep only the last 7 daily backups
find "$BACKUP_DIR" -maxdepth 1 -type d -name "20*" -mtime +7 -exec rm -rf {} \;

Сделайте его исполняемым:

1

sudo chmod +x /usr/local/bin/mariadb-backup.sh

Создайте системный сервис:

1

sudo nano /etc/systemd/system/mariadb-backup.service

Определите его как root (для резервного копирования mariadb необходим доступ файловой системы к каталогу данных):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

[Unit]
Description=MariaDB Full Backup
After=mariadb.service
Requires=mariadb.service

[Service]
Type=oneshot
Nice=10
IOSchedulingClass=idle
ExecStart=/usr/local/bin/mariadb-backup.sh

Создайте таймер:

1

sudo nano /etc/systemd/system/mariadb-backup.timer

Расписание в 02:00 ежедневно, через час после резервной копии PostgreSQL, чтобы избежать разногласий по поводу ввода/вывода, если оба работают на одном хосте:

1
2
3
4
5
6
7
8
9

[Unit]
Description=Daily MariaDB Backup Timer

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true

[Install]
WantedBy=timers.target

Включите и запустите таймер:

1
2

sudo systemctl daemon-reload
sudo systemctl enable --now mariadb-backup.timer

Убедитесь, что оба таймера активны:

1

systemctl list-timers pg-backup.timer mariadb-backup.timer

Вы должны увидеть оба перечислены с их следующими триггерами:

1
2
3
4
5

NEXT                         LEFT          LAST PASSED UNIT                  ACTIVATES
Sun 2026-11-15 01:00:00 UTC  5h 14min left n/a  n/a    pg-backup.timer      pg-backup.service
Sun 2026-11-15 02:00:00 UTC  6h 14min left n/a  n/a    mariadb-backup.timer mariadb-backup.service

2 timers listed.

Включить Binary Logging

Бинарная регистрация может быть включена в вашей системе. Чтобы обеспечить правильные настройки для PITR, создайте специальный файл конфигурации.

Откройте новый файл конфигурации для настроек резервного копирования:

1

sudo nano /etc/my.cnf.d/backup.cnf

Добавить следующую конфигурацию:

1
2
3
4
5

[mysqld]
log_bin = /var/lib/mysql/mariadb-bin
binlog_format = ROW
expire_logs_days = 14
server_id = 1

Перезапустить MariaDB для применения:

1

sudo systemctl restart mariadb

Создайте резервный каталог:

1
2

sudo mkdir -p /backup/mariadb
sudo chown mysql:mysql /backup/mariadb

Восстановление к точному второму

MariaDB PITR представляет собой двухфазный процесс. Во-первых, восстановить резервную копию базы, чтобы вернуться к точке резервной копии во времени. Затем переиграйте двоичные журналы до метки времени цели, чтобы восстановить все изменения, внесенные между резервной копией и катастрофой.

Остановить MariaDB:

1

sudo systemctl stop mariadb

Переместите текущий (испорченный) каталог данных в сторону:

1

sudo mv /var/lib/mysql /var/lib/mysql.corrupted

Восстановите резервную копию базы с помощью --copy-back:

1

sudo mariadb-backup --copy-back --target-dir=/backup/mariadb/full

Исправить право собственности на восстановленный каталог данных. MariaDB требует, чтобы пользователь mysql владел всеми файлами:

1

sudo chown -R mysql:mysql /var/lib/mysql

Запустите MariaDB с восстановления резервной копии базы:

1

sudo systemctl start mariadb

MariaDB PITR полностью готов. Резервное копирование базы восстановило. Очистите старый каталог данных:

1

sudo rm -rf /var/lib/mysql.corrupted

Производственные соображения

Прохождение выше использует агрессивные настройки, чтобы сделать демонстрацию быстрой. Производственные среды требуют различной настройки.

archive_timeout: Мы использовали 60 секунд для тестирования. В производстве установите это на 300 (5 минут) или 600 (10 минут). Более низкие значения генерируют больше файлов WAL и потребляют больше дискового пространства, но уменьшают максимальное окно потери данных. 5-минутный тайм-аут означает, что вы теряете максимум 5 минут транзакций, если сервер и архивный диск WAL выходят из строя одновременно.

Место хранения резервного копирования: Храните резервные копии на физически отдельном диске или удаленном сервере. Резервная копия, сидящая на том же диске, что и каталог данных, умирает вместе с ней. Монтажи NFS, S3-совместимое хранилище объектов или выделенный сервер резервного копирования через rsync/scp - все это жизнеспособные варианты.

Тест восстанавливается регулярно. Запланируйте ежемесячный тест восстановления на постановочном сервере. Резервная копия, которую вы никогда не тестировали, не является резервной копией. Автоматизируйте проверку восстановления, если это возможно: восстановите, запустите запрос, проверьте количество строк, сорвитесь.

Мониторинг отставания архива WAL для PostgreSQL. Если команда архива выходит из строя, сегменты WAL накапливаются в pg wal/ и в конечном итоге заполняют диск. Контролируйте колонки last_archived_wal и last_failed_wal из pg_stat_archiver. Для полного стека мониторинга см. мониторинг PostgreSQL с Prometheus и Grafana.

Использование диска-binlog MariaDB: Двоичные журналы быстро растут в больших базах данных. Установите expire_logs_days, чтобы соответствовать вашему резервному хранению. Если вы храните 7 дней резервных копий, 14 дней блогов дают вам адекватное совпадение. Мониторинг использования диска в /var/lib/mysql/ для файлов binlog и настройка мониторинга MariaDB с Prometheus и Grafana, чтобы улавливать проблемы, прежде чем они обострятся.

Шифрование и разрешения: Файлы резервного копирования содержат все ваши данные. Установите 700 разрешений каталогов, принадлежащих пользователю базы данных. Для наружного хранения шифруйте в состоянии покоя. pg_basebackup от PostgreSQL не шифруется изначально, но вы можете использовать gpg или использовать зашифрованную файловую систему. MariaDB поддерживает mariadb-backup резервное копирование --encryp - шифрование со встроенным шифрованием AES.

Завершение

Восстановление производственной базы данных в 3 часа утра является достаточно напряженным процессом без предварительного чтения документации.

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

Если вам понравился этот пост, поделитесь им со своими друзьями в социальных сетях, используя кнопки обмена ниже, или просто оставьте комментарий в разделе комментариев. Спасибо.