Настройка бэкапа PostgreSQL: пошаговая инструкция, pg_dump, WAL, восстановление

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

Основные стратегии резервного копирования PostgreSQL

В PostgreSQL существует два фундаментально разных подхода к бэкапу, и выбор зависит от твоих требований к RPO (целевая точка восстановления) и RTO (целевое время восстановления).

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

Настройка логического бэкапа с помощью pg_dump

Это самый простой способ начать. pg_dump создает SQL-дамп, который можно восстановить на любой сервер PostgreSQL.

Базовые команды pg_dump

bash

# Дамп всей базы в один файл
pg_dump -U postgres -h localhost -d mydatabase -f /backup/mydatabase_full.sql

# Дамп в сжатом формате (рекомендуется)
pg_dump -U postgres -h localhost -d mydatabase | gzip > /backup/mydatabase_full.sql.gz

# Дамп только схемы (без данных)
pg_dump -U postgres -h localhost -d mydatabase --schema-only > /backup/mydatabase_schema.sql

# Дамп только данных (без DDL)
pg_dump -U postgres -h localhost -d mydatabase --data-only > /backup/mydatabase_data.sql

# Параллельный дамп больших баз (PostgreSQL 9.3+)
pg_dump -U postgres -h localhost -d mydatabase -j 4 -Fd -f /backup/mydatabase_dir/

Важно: Для дампа больших баз используй формат directory (-Fd) с параллельным режимом (-j). Это значительно ускоряет процесс и позволяет сжимать каждый файл отдельно.

Автоматизация бэкапов через cron

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

bash

#!/bin/bash
# /usr/local/bin/pg_backup.sh

# Конфигурация
BACKUP_DIR="/var/backups/postgresql"
DB_NAME="mydatabase"
DB_USER="postgres"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)

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

# Делаем дамп
pg_dump -U $DB_USER -h localhost -d $DB_NAME \
  | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# Проверяем успешность
if [ $? -eq 0 ]; then
  echo "[$(date)] Backup successful: ${DB_NAME}_${DATE}.sql.gz" >> $BACKUP_DIR/backup.log
  
  # Удаляем старые бэкапы
  find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete
else
  echo "[$(date)] Backup FAILED!" >> $BACKUP_DIR/backup.log
  exit 1
fi

Добавляем в crontab для ежедневного выполнения в 2:00 ночи:

bash

# crontab -e
0 2 * * * /usr/local/bin/pg_backup.sh

Настройка непрерывного архивирования WAL (PITR)

Для продакшен-среды, где потеря даже минуты данных недопустима, нужен физический бэкап с непрерывным архивированием Write-Ahead Log (WAL). Это позволяет восстановить базу на любой момент времени.

Конфигурация 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'

# Максимальное время для восстановления
wal_keep_size = 1GB  # Минимум 1GB WAL файлов сохраняется в pg_wal/

# Проверяем, что архив создан корректно
archive_timeout = 300  # Принудительно переключаем WAL каждые 5 минут если нет активности

Внимание: После изменения этих параметров требуется перезагрузка PostgreSQL (pg_ctl reload или SELECT pg_reload_conf()). Убедись, что директория wal_archive существует и у пользователя postgres есть права на запись.

Создание базового бэкапа

Физический бэкап делается утилитой pg_basebackup:

bash

# Создаем полный физический бэкап
pg_basebackup -D /var/backups/postgresql/basebackup_$(date +%Y%m%d) \
  -U postgres -h localhost -P -v \
  --wal-method=stream \
  --checkpoint=fast

# С параметрами для репликации
pg_basebackup -D /var/backups/postgresql/basebackup_latest \
  -U replicator -h primary-server -P -v \
  --wal-method=stream \
  --write-recovery-conf \
  --slot=backup_slot

Скрипт для автоматизации полного цикла

bash

#!/bin/bash
# /usr/local/bin/pg_physical_backup.sh

BACKUP_ROOT="/var/backups/postgresql"
BASE_DIR="$BACKUP_ROOT/base"
WAL_DIR="$BACKUP_ROOT/wal"
DATE=$(date +%Y%m%d)
RETENTION_DAYS=14

mkdir -p $BASE_DIR $WAL_DIR

# 1. Создаем базовый бэкап
echo "[$(date)] Starting base backup..."
pg_basebackup -D $BASE_DIR/base_$DATE \
  -U postgres -h localhost -P \
  --wal-method=stream \
  --checkpoint=fast

if [ $? -ne 0 ]; then
  echo "[$(date)] Base backup FAILED!" >> $BACKUP_ROOT/backup.log
  exit 1
fi

# 2. Архивируем WAL файлы
echo "[$(date)] Archiving WAL files..."
find /var/lib/postgresql/wal_archive -name "*.gz" -mtime -1 -exec cp {} $WAL_DIR/ \;

# 3. Очищаем старые бэкапы
find $BASE_DIR -name "base_*" -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;
find $WAL_DIR -name "*.gz" -mtime +$RETENTION_DAYS -delete

echo "[$(date)] Physical backup completed successfully" >> $BACKUP_ROOT/backup.log

Восстановление из бэкапа

Бэкап без проверки восстановления — это не бэкап. Давай разберем как восстановить данные в разных сценариях.

Восстановление из логического дампа

bash

# Восстановление всей базы
psql -U postgres -h localhost -d mydatabase < /backup/mydatabase_full.sql

# Или для сжатого файла
gunzip -c /backup/mydatabase_full.sql.gz | psql -U postgres -h localhost -d mydatabase

# Восстановление в новую базу
createdb -U postgres -h localhost mydatabase_restored
psql -U postgres -h localhost -d mydatabase_restored < /backup/mydatabase_full.sql

# Восстановление только одной таблицы
pg_restore -U postgres -h localhost -d mydatabase \
  -t specific_table /backup/mydatabase_dir/

Восстановление из физического бэкапа с PITR

bash

# 1. Останавливаем PostgreSQL
sudo systemctl stop postgresql

# 2. Удаляем/перемещаем старые данные
mv /var/lib/postgresql/12/main /var/lib/postgresql/12/main_old

# 3. Копируем базовый бэкап
cp -r /var/backups/postgresql/base/base_20240101 /var/lib/postgresql/12/main

# 4. Создаем recovery.conf (PostgreSQL 12+ использует postgresql.auto.conf)
echo "restore_command = 'gunzip < /var/backups/postgresql/wal/%f.gz > %p'" \
  > /var/lib/postgresql/12/main/recovery.conf

echo "recovery_target_time = '2024-01-15 14:30:00'" \
  >> /var/lib/postgresql/12/main/recovery.conf

# 5. Запускаем PostgreSQL - он автоматически восстановится до указанного времени
sudo systemctl start postgresql

Мониторинг и проверка бэкапов

Бэкап должен не только создаваться, но и регулярно проверяться. Вот что нужно мониторить:

Размер бэкапов: Внезапное уменьшение может означать проблему
Время создания: Если бэкап занимает слишком много времени, возможно, нужна оптимизация
Целостность: Периодически проверяй восстановление на тестовом сервере
Свободное место: Отслеживай заполнение диска с бэкапами

bash

# Скрипт проверки бэкапа
#!/bin/bash
BACKUP_FILE="/var/backups/postgresql/mydatabase_$(date +%Y%m%d).sql.gz"

# Проверяем существование
if [ ! -f "$BACKUP_FILE" ]; then
  echo "ERROR: Backup file not found!"
  exit 1
fi

# Проверяем размер (минимум 1MB)
SIZE=$(stat -c%s "$BACKUP_FILE")
if [ $SIZE -lt 1048576 ]; then
  echo "ERROR: Backup file too small!"
  exit 1
fi

# Проверяем целостность gzip
gzip -t "$BACKUP_FILE"
if [ $? -ne 0 ]; then
  echo "ERROR: Backup file corrupted!"
  exit 1
fi

echo "Backup check passed"

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

Какой метод бэкапа выбрать для продакшена?

Для критичных продакшен-систем используй комбинированный подход: ежедневные физические бэкапы (pg_basebackup) + непрерывное архивирование WAL. Это дает возможность восстановления на любой момент времени (PITR) с минимальной потерей данных. Для менее критичных систем достаточно ежедневных логических бэкапов с помощью pg_dump.

Как минимизировать влияние бэкапа на работу базы?

Используй параметр --checkpoint=fast в pg_basebackup для быстрого контрольного пункта. Для логических бэкапов используй pg_dump --jobs для параллельного выполнения. Выполняй полные бэкапы в часы наименьшей нагрузки (ночью). Рассмотри возможность использования реплики для создания бэкапов, чтобы не нагружать основной сервер.

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

Придерживайся правила 3-2-1: 3 копии данных, 2 разных типа носителей, 1 копия вне площадки. Храни бэкапы на отдельном диске, не на том же, где работает PostgreSQL. Используй облачное хранилище (S3, Google Cloud Storage) для оффсайт-копий. Настрой автоматическую ротацию и удаление старых бэкапов.

Что делать, если не хватает места для WAL-архивов?

Во-первых, настрой более агрессивную ротацию архивов. Во-вторых, рассмотри использование утилиты pg_archivecleanup для автоматической очистки WAL-файлов после создания нового базового бэкапа. В-третьих, убедись, что archive_command корректно удаляет или перемещает обработанные файлы.

Заключение

Настройка надежного бэкапа PostgreSQL — не разовое мероприятие, а процесс. Начни с простого скрипта pg_dump и cron, затем переходи к физическим бэкапам с WAL-архивированием для продакшен-систем. Помни:

Тестируй восстановление хотя бы раз в квартал
Мониторь размеры и время создания бэкапов
Храни бэкапы по правилу 3-2-1
Документируй процесс восстановления

Потрать время на настройку сейчас — сэкономишь нервы и деньги в будущем, когда произойдет неизбежный сбой.