Настройка резервного копирования PostgreSQL: pg_dump, WAL, Barman

Представь, что твоя база данных — это сейф с деньгами. Резервное копирование — это дубликат ключа, спрятанный в надежном месте. Без него любая аппаратная поломка, человеческая ошибка или сбой ПО превращается в катастрофу. Давай разберем, как правильно настроить резервное копирование PostgreSQL, чтобы спать спокойно.

Выбор стратегии резервного копирования

Перед настройкой определись с целями: RPO (допустимая потеря данных) и RTO (время восстановления). От этого зависит метод.

Метод	Тип	Преимущества	Недостатки	Использование
`pg_dump`	Логический, полный	Перенос между версиями, выбор объектов	Блокировка, медленно для больших БД	Ежедневные дампы, миграции
`pg_basebackup`	Физический, полный	Быстро, минимальное влияние на работу	Большой размер, требует WAL для PITR	Базовый бэкап для реплик
WAL-архивирование	Физический, инкрементальный	Восстановление на момент времени (PITR)	Сложная настройка, хранение логов	Непрерывная защита данных

Пошаговая настройка базового резервного копирования

Начнем с классики — автоматического создания дампов с помощью pg_dump.

Шаг 1: Создание пользователя для бэкапов

Создадим отдельного пользователя с минимальными правами:

sql

CREATE USER backup_user WITH PASSWORD 'StrongPassword123!';
GRANT CONNECT ON DATABASE mydb TO backup_user;
GRANT USAGE ON SCHEMA public TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup_user;

Шаг 2: Скрипт для создания дампа

bash

#!/bin/bash

DB_NAME="mydb"
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +"%Y-%m-%d_%H-%M-%S")
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"
RETENTION_DAYS=30

# Создание директории
mkdir -p $BACKUP_DIR

# Создание дампа с компрессией
pg_dump -U backup_user -h localhost -d $DB_NAME \
  --format=plain \
  --no-owner \
  --no-acl \
  --verbose \
  | gzip > $BACKUP_FILE

# Проверка успешности
if [ $? -eq 0 ]; then
  echo "Backup created: $BACKUP_FILE"
  
  # Очистка старых бэкапов
  find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -type f -mtime +$RETENTION_DAYS -delete
  echo "Old backups cleaned"
else
  echo "Backup failed!" >&2
  exit 1
fi

Совет: Для больших баз используй pg_dump --jobs=4 для параллельного дампа и --format=directory для сжатия отдельных таблиц.

Шаг 3: Настройка cron для автоматизации

bash

# Добавь в crontab -e
# Ежедневный бэкап в 2:00 ночи
0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

# Еженедельный полный бэкап в воскресенье
0 3 * * 0 /usr/local/bin/pg_backup_full.sh >> /var/log/pg_backup_full.log 2>&1

Настройка непрерывного резервного копирования с WAL

Для восстановления на конкретный момент времени нужна комбинация pg_basebackup и WAL-архивирования.

Шаг 1: Конфигурация postgresql.conf

config

# Включение архивации WAL
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'
# Или с gzip сразу:
# archive_command = 'gzip < %p > /var/lib/postgresql/wal_archive/%f.gz'

# Увеличь checkpoint_timeout для меньшего количества WAL
max_wal_size = 2GB
min_wal_size = 1GB

# Для надежности
wal_compression = on

Шаг 2: Создание базовой копии

bash

# Создаем директории
mkdir -p /var/lib/postgresql/backups/base
mkdir -p /var/lib/postgresql/wal_archive

# Делаем базовый бэкап
pg_basebackup -D /var/lib/postgresql/backups/base/$(date +"%Y-%m-%d") \
  -U backup_user \
  -h localhost \
  -p 5432 \
  --format=plain \
  --progress \
  --wal-method=stream \
  --checkpoint=fast

Важно: После создания базовой копии сохрани файл backup_label — он содержит LSN (Log Sequence Number), с которого нужно начать восстановление.

Восстановление PostgreSQL из резервной копии

Бэкап без проверки восстановления — это не бэкап. Протестируем оба сценария.

Восстановление из pg_dump

bash

# Останови PostgreSQL, если восстанавливаешь поверх
sudo systemctl stop postgresql

# Очистка старой БД (осторожно!)
dropdb --if-exists mydb_restored
createdb mydb_restored

# Восстановление
gunzip -c /var/backups/postgresql/mydb_2024-01-15.sql.gz | psql -U postgres -d mydb_restored

# Или с прогрессом
pg_restore -U postgres -d mydb_restored \
  --jobs=4 \
  --verbose \
  /var/backups/postgresql/mydb_2024-01-15.dump

PITR (Point-in-Time Recovery)

bash

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

# 2. Очистка data директории (предварительно сделай бэкап!)
rm -rf /var/lib/postgresql/16/main/*

# 3. Восстановление базовой копии
cp -r /var/lib/postgresql/backups/base/2024-01-15/* /var/lib/postgresql/16/main/

# 4. Настройка recovery.conf (PostgreSQL 12+ - в postgresql.conf)
echo "restore_command = 'gunzip < /var/lib/postgresql/wal_archive/%f.gz > %p'" >> /var/lib/postgresql/16/main/postgresql.conf
echo "recovery_target_time = '2024-01-15 14:30:00'" >> /var/lib/postgresql/16/main/postgresql.conf
echo "recovery_target_action = promote" >> /var/lib/postgresql/16/main/postgresql.conf

# 5. Создание сигнального файла
touch /var/lib/postgresql/16/main/recovery.signal

# 6. Запуск
sudo systemctl start postgresql

# Следим за логами
tail -f /var/log/postgresql/postgresql-16-main.log

Продвинутые инструменты: Barman и pgBackRest

Для production-среды рекомендую использовать специализированные инструменты.

Barman: Установка и базовая настройка

bash

# Установка на отдельном сервере
sudo apt install barman

# Конфигурация /etc/barman.conf
[barman]
barman_home = /var/lib/barman
barman_user = barman
log_file = /var/log/barman/barman.log

[mydb_server]
description = "Production PostgreSQL"
conninfo = host=db-server user=barman dbname=postgres
streaming_conninfo = host=db-server user=streaming_barman dbname=postgres
backup_method = postgres
streaming_archiver = on
slot_name = barman
archiver = on

# На стороне PostgreSQL создаем пользователя
CREATE USER barman WITH SUPERUSER REPLICATION LOGIN PASSWORD 'BarmanPass123!';
CREATE USER streaming_barman WITH REPLICATION LOGIN PASSWORD 'StreamPass456!';
CREATE PUBLICATION barman_publication FOR ALL TABLES;

# Инициализация слота репликации
sudo barman receive-wal --create-slot mydb_server

# Первый бэкап
sudo barman backup mydb_server

# Список бэкапов
sudo barman list-backup mydb_server

Автоматизация с помощью скриптов и мониторинга

Проверка целостности: Регулярно запускай barman check mydb_server
Мониторинг: Настрой алерты при пропуске бэкапа
Тестовое восстановление: Раз в месяц восстанавливай бэкап на тестовом стенде
Шифрование: Используй gpg для чувствительных данных
Хранение: Применяй правило 3-2-1 (3 копии, 2 носителя, 1 вне площадки)

Производительность: Для минимизации влияния на продакшен настрой pg_basebackup с --max-rate=100M для ограничения скорости и делай бэкапы в часы наименьшей нагрузки.

Частые вопросы (FAQ)

Как часто нужно делать резервное копирование?

Зависит от RPO. Для большинства проектов: полный бэкап раз в неделю + инкрементальный (WAL) постоянно + ежедневные дампы ключевых данных. Для финансовых систем — чаще.

Почему pg_dump блокирует таблицы и как этого избежать?

pg_dump по умолчанию использует режим ShareLock. Используй --jobs=N для параллелизма или pg_dump --snapshot для создания снимка. Для минимальных блокировок лучше подходит pg_basebackup.

Как организовать хранение резервных копий?

Используй иерархию: локальный SSD для быстрого восстановления + объектное хранилище (S3) + ленточная библиотека для долгосрочного архива. Не забывай про географическое распределение.

Можно ли делать бэкап работающей базы без остановки?

Да! pg_basebackup и инструменты вроде Barman делают консистентные снимки через механизм контрольных точек. WAL-архивирование и вовсе непрерывно.

Заключение

Настройка резервного копирования PostgreSQL — не разовая задача, а процесс. Начни с простых дампов по расписанию, затем добавь WAL-архивирование для PITR, и наконец внедри Barman или pgBackRest для enterprise-уровня.

Ключевые принципы:

Автоматизируй всё — ручные бэкапы забываются
Проверяй восстановление — бэкап без теста бесполезен
Мониторь — получай алерты при сбоях
Документируй — процесс восстановления должен быть понятен коллегам
Соблюдай 3-2-1 — это страховка от любых сценариев

Потрать день на настройку сейчас — сэкономишь недели и нервы в будущем. Удачи в построении надежной системы!