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

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

Типичная ошибка — хранение дампов на том же диске, где работает база. При сбое диска вы теряете и базу, и бэкап. Мы всегда размещаем бэкапы в отдельном объектном хранилище, например S3, с настройкой жизненного цикла. Наш опыт показывает, что 30% продуктовых инцидентов с PostgreSQL связаны с некорректными бэкапами. Мы гарантируем, что после нашей настройки вы сможете восстановить базу в течение часа при RPO не более 24 часов.

Почему pg_dump остаётся стандартом для резервного копирования?

pg_dump — утилита для логического дампа, не требует остановки базы (pg_dump documentation). Работает на любой версии PostgreSQL, дамп переносим между версиями (при условии, что целевая версия >= исходной). Для баз до 100 ГБ это оптимальный выбор. Ключевое преимущество — гибкость: можно выборочно восстановить таблицы, схемы или данные.

Но у pg_dump есть ограничения: большой дамп может занимать часы, а при сбое во время выполнения дамп будет неполным. Поэтому мы используем пользовательский формат (-Fc) — он сжимает данные, поддерживает параллельное восстановление и позволяет проверять целостность. Custom-формат (-Fc) примерно в 5 раз компактнее Plain SQL, а восстановление с pg_restore -j 4 ускоряется в 3-4 раза на многоядерных серверах.

Если нужно забекапить весь кластер с ролями и tablespace, используйте pg_dumpall. Но для одной базы pg_dump быстрее и даёт больше возможностей при восстановлении.

Сравнение форматов дампа

Custom-формат (-Fc) — наш выбор по умолчанию: сжатие 5-10x, pg_restore запускается в несколько потоков, можно восстановить только нужные таблицы.

PostgreSQL Documentation

Сравнение pg_dump и pg_dumpall

Как мы настраиваем бэкапирование PostgreSQL: кейс с 50 ГБ базой

Рассмотрим реальный проект: база 50 ГБ, ежедневный полный дамп, хранение 30 дней, S3 как удалённое хранилище. Требование: RTO < 1 часа, RPO < 24 часа. Восстановление 50 ГБ базы в формате -Fc на тестовом стенде с 4 ядрами занимает 20 минут.

Мы подготовили скрипт с верификацией, загрузкой в S3 и уведомлением в Telegram при сбое. Скрипт запускается по cron, логи пишутся в файл и отправляются в систему мониторинга.

Код бэкапа:

#!/bin/bash
set -euo pipefail

DB_HOST="localhost"
DB_PORT="5432"
DB_USER="postgres"
DB_NAME="production"
BACKUP_DIR="/backup/postgresql"
S3_BUCKET="s3://backups-company/postgresql"
RETENTION_DAYS=30

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"

mkdir -p "$BACKUP_DIR"

echo "Starting backup at $(date)" | tee "$LOG_FILE"

# Создание дампа
PGPASSFILE="${HOME}/.pgpass" pg_dump \
  -Fc \
  -h "$DB_HOST" \
  -p "$DB_PORT" \
  -U "$DB_USER" \
  -d "$DB_NAME" \
  --no-password \
  -f "$BACKUP_FILE" 2>> "$LOG_FILE"

# Проверка целостности
pg_restore --list "$BACKUP_FILE" > /dev/null 2>&1 || {
  echo "ERROR: Backup integrity check failed" | tee -a "$LOG_FILE"
  exit 1
}

# Загрузка в S3
aws s3 cp "$BACKUP_FILE" "${S3_BUCKET}/${DB_NAME}_${DATE}.dump" \
  --storage-class STANDARD_IA \
  --server-side-encryption aws:kms 2>> "$LOG_FILE"

echo "Upload to S3 completed" | tee -a "$LOG_FILE"

# Очистка локальных копий старше RETENTION_DAYS
find "$BACKUP_DIR" -name "*.dump" -mtime +${RETENTION_DAYS} -delete

echo "Backup completed successfully at $(date)" | tee -a "$LOG_FILE"

Параметр --no-password заставляет pg_dump читать пароль из ~/.pgpass, что безопаснее, чем переменная окружения. Файл .pgpass имеет права 600.

Пример содержимого .pgpass: localhost:5432:production:postgres:yourpassword

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

Чтобы не пропустить момент, когда бэкап перестал создаваться, мы добавляем проверку в Nagios/Icinga:

#!/bin/bash
DB_NAME="production"
LAST_BACKUP_DATE=$(aws s3 ls s3://backups-company/postgresql/ \
  --recursive | grep "${DB_NAME}_" | sort | tail -1 | awk '{print $1" "$2}')
AGE_HOURS=$(( ($(date +%s) - $(date -d "$LAST_BACKUP_DATE" +%s)) / 3600 ))
if [ $AGE_HOURS -gt 25 ]; then
  echo "CRITICAL: Last backup is ${AGE_HOURS}h old"
  exit 2
elif [ $AGE_HOURS -gt 13 ]; then
  echo "WARNING: Last backup is ${AGE_HOURS}h old"
  exit 1
else
  echo "OK: Last backup is ${AGE_HOURS}h old"
  exit 0
fi

Этот скрипт проверяет, что возраст последнего дампа не превышает 25 часов. Если бэкап не создавался более 13 часов — warning, более 25 — critical. Такой подход позволяет обнаружить проблемы до того, как RPO будет нарушено.

Почему важен мониторинг бэкапов?

Без мониторинга вы узнаете о проблеме только при попытке восстановления. К тому моменту может быть поздно. Мы включаем в настройку мониторинг не только возраста, но и размера дампа: резкое уменьшение размера может указывать на ошибку в скрипте или проблемы с базой. Кроме того, мы настраиваем алерты в Telegram или Slack.

Восстановление базы из дампа с помощью pg_restore

Восстановление из custom-дампа выполняется одной командой:

pg_restore -j 4 -d new_db -U postgres -h localhost production_backup.dump 

Флаг -j 4 запускает 4 параллельных потока — это сокращает время восстановления на 50-70% на многоядерных системах. Если нужно восстановить только одну таблицу, укажите -t table_name.

Что входит в настройку под ключ

• Аудит текущей схемы бэкапов и требований к RPO/RTO
• Разработка скриптов с верификацией дампа и загрузкой в S3
• Интеграция с системами мониторинга (Prometheus, Zabbix, Telegram)
• Документация процедуры восстановления (пошаговая инструкция)
• Обучение дежурной смены
• Сопровождение в течение первого месяца после внедрения

Процесс работы

1. Анализ: оцениваем размер базы, требования к RPO/RTO, доступное хранилище.
2. Проектирование: выбираем формат дампа, схему хранения (локально + S3), планируем расписание.
3. Реализация: пишем скрипты, настраиваем cron, интегрируем с S3.
4. Тестирование: выполняем тестовое восстановление на стенде, проверяем целостность.
5. Деплой: устанавливаем в продакшен, настраиваем мониторинг.
6. Документация: описываем процедуру восстановления, контакты ответственных.

Сроки и стоимость

Настройка базового сценария (pg_dump + S3 + мониторинг) занимает от 1 до 3 рабочих дней в зависимости от сложности. Стоимость рассчитывается индивидуально, исходя из объёма работ и требований к отказоустойчивости. Мы гарантируем, что после нашей настройки вы сможете восстановить базу по инструкции за 1 час.

Типичные ошибки при настройке бэкапов

• Хранение дампов на одном диске с базой.
• Использование plain SQL без сжатия (дамп занимает много места, долго восстанавливается).
• Отсутствие верификации дампов.
• Пароль в открытом виде в cron (используйте .pgpass).
• Нет мониторинга возраста бэкапов.

Наша команда имеет более 5 лет опыта в администрировании PostgreSQL и реализовала более 50 проектов по резервному копированию для компаний разного масштаба. Свяжитесь с нами для аудита вашей текущей схемы бэкапов — мы предложим оптимальное решение. Закажите настройку под ключ и получите гарантию восстановления за 1 час. Получите консультацию по настройке бэкапов PostgreSQL уже сегодня.