Резервное копирование PostgreSQL: pg_dump и pg_restore

Коротко

• pg_dump создаёт логическую копию одной базы: согласованный снимок схемы и данных на момент старта дампа.
• Custom-формат (-Fc) — лучший выбор по умолчанию: сжат, восстанавливается через pg_restore, позволяет восстановить отдельные таблицы и работать параллельно.
• Роли и другие объекты уровня кластера в дамп базы не попадают — сохраняйте их через pg_dumpall --globals-only.
• Бэкап считается бэкапом, только если вы хотя бы раз успешно его восстановили.

Форматы дампа

# Custom-формат, сжат по умолчанию
pg_dump -Fc -d appdb -f appdb.dump

# Directory-формат в 4 параллельных потока
pg_dump -Fd -j 4 -d appdb -f appdb_dir

# Plain SQL со сжатием gzip
pg_dump -d appdb | gzip > appdb.sql.gz

# Роли и табличные пространства всего кластера
pg_dumpall --globals-only -f globals.sql

Уровень сжатия для custom- и directory-форматов меняется флагом -Z.

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

1. Установите PostgreSQL той же или более новой мажорной версии. Восстановление дампа с более нового сервера на более старый не гарантируется.
2. Восстановите роли: psql -U postgres -f globals.sql. Ошибки о том, что роль уже существует (например, postgres), — это нормально.
3. Создайте пустую базу и восстановите дамп:

createdb -O appuser appdb
pg_restore -d appdb -j 4 appdb.dump

Для дампа в plain SQL:

gunzip -c appdb.sql.gz | psql -d appdb

Полезные опции pg_restore:

• --no-owner — если роли на новом сервере другие; объекты будут принадлежать тому, кто выполняет восстановление.
• -t table_name — восстановить одну таблицу.
• --list — показать содержимое архива без восстановления.

Используйте pg_dump той же или более новой версии, чем сервер, с которого снимаете дамп.

Автоматизация через cron

Подготовьте папку, принадлежащую пользователю postgres:

sudo mkdir -p /var/backups/postgres
sudo chown postgres:postgres /var/backups/postgres

Скрипт /usr/local/bin/pg-backup.sh:

#!/usr/bin/env bash
set -euo pipefail

DB="appdb"
DIR="/var/backups/postgres"
FILE="$DIR/${DB}_$(date +%F_%H-%M).dump"

pg_dump -Fc -d "$DB" -f "$FILE.tmp"
mv "$FILE.tmp" "$FILE"

find "$DIR" -name "${DB}_*.dump" -mtime +14 -delete

Сделайте его исполняемым (chmod +x) и добавьте в crontab пользователя postgres (sudo crontab -u postgres -e):

30 2 * * * /usr/local/bin/pg-backup.sh >> /var/backups/postgres/backup.log 2>&1

Запуск от postgres использует локальную peer-аутентификацию, поэтому пароль в скрипте не хранится. Если подключаться нужно с паролем, используйте файл ~/.pgpass с правами 600. Запись сначала во временный .tmp гарантирует, что неудачный дамп не будет выглядеть как готовый.

Хранение и ротация

• Определите, сколько данных вы готовы потерять, — от этого зависит, как часто делать дамп.
• Храните несколько поколений: например, ежедневные дампы за пару недель и еженедельные за несколько месяцев.
• Следуйте правилу 3-2-1: три копии, на двух разных типах носителей, одна из них вне площадки. Бэкап на том же сервере умирает вместе с сервером.
• Шифруйте дампы, которые хранятся вне вашей инфраструктуры: в них все ваши данные.
• Настройте оповещения, если задача упала или свежий файл не появился.

Проверка восстановления

Непроверенный бэкап — это надежда, а не бэкап. Регулярно:

1. Восстанавливайте свежий дамп на отдельный тестовый сервер или в отдельную базу.
2. Проверяйте количество строк в ключевых таблицах и то, что приложение запускается на восстановленных данных.
3. Засекайте время восстановления — это ваше реальное время возврата в строй.

Ограничения pg_dump

Дамп — это снимок: изменения, сделанные после него, при восстановлении потеряются. Для больших баз и восстановления на точный момент времени используйте физические бэкапы с архивированием WAL (pg_basebackup или инструменты вроде pgBackRest). Подробности — в документации PostgreSQL по резервному копированию.