Как сделать бэкап и восстановление PostgreSQL: полное руководство

Введение

PostgreSQL — одна из самых надёжных СУБД, но даже она не застрахована от сбоев оборудования, ошибок администратора, случайного удаления данных или вредоносных атак. Регулярное резервное копирование — обязательная часть эксплуатации любой базы данных. В этом руководстве мы разберём, как правильно делать бэкап и восстановление PostgreSQL, какие инструменты использовать и как избежать типичных ошибок.

Мы рассмотрим как логическое резервирование (pg_dump, pg_dumpall), так и физическое (pg_basebackup, непрерывное архивирование WAL), а также работу через графический интерфейс pgAdmin 4. Вы научитесь создавать резервные копии вручную и автоматизировать процесс с помощью cron, а также безопасно восстанавливать данные.

Требования и подготовка

Прежде чем приступить, убедитесь, что у вас есть:

• Доступ к серверу PostgreSQL (локально или удалённо) с правами суперпользователя или роли с правами на чтение всех нужных баз.

• Установленные утилиты pg_dump, pg_dumpall, pg_restore, psql. Они входят в пакет postgresql-client.

• Достаточно свободного места на диске для хранения дампов.

• Понимание структуры ваших баз данных и критичности данных.

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

psql --version
pg_dump --version

Важно, чтобы версия утилит pg_dump/pg_restore была не ниже версии сервера. В идеале — совпадала.

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

Существует два основных подхода:

1. Логическое резервное копирование — создание SQL-скриптов или архивов, содержащих команды для воссоздания объектов и данных. Плюсы: гибкость, возможность переноса между версиями и платформами, выборочное восстановление. Минусы: медленнее, занимает больше места, не позволяет восстановиться на момент времени (PITR).

2. Физическое резервное копирование — копирование файлов данных кластера. Плюсы: быстрое восстановление, возможность PITR. Минусы: привязка к версии и архитектуре, требуется остановка или специальный режим.

Для большинства задач достаточно логического бэкапа. Если у вас большая база (сотни гигабайт) и требования к RPO (точке восстановления) — рассмотрите физическое резервирование и архивирование WAL.

Шаг 2. Создание логического дампа с помощью pg_dump

pg_dump создаёт согласованный дамп одной базы данных. Синтаксис:

pg_dump -U username -h hostname -p port -Fc -f /path/to/backup.dump dbname

Ключи:

• -Fc — формат custom (сжатый, поддерживает выборочное восстановление через pg_restore).

• -Fd — формат directory (многопоточный дамп).

• -Ft — tar-архив.

• -Fp — plain SQL (по умолчанию).

Пример для локальной базы:

pg_dump -U postgres -Fc -f /backups/mydb_$(date +%Y%m%d).dump mydb

Для сжатия в plain-формате можно использовать gzip:

pg_dump -U postgres mydb | gzip > /backups/mydb.sql.gz

Шаг 3. Дамп всех баз и глобальных объектов: pg_dumpall

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

pg_dumpall -U postgres -f /backups/all_databases.sql

Для сжатия:

pg_dumpall -U postgres | gzip > /backups/all_databases.sql.gz

Учтите: pg_dumpall не создаёт согласованный снимок всех баз одновременно (кроме случая с --globals-only). Для критичных систем лучше делать дампы каждой базы отдельно.

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

Восстановление зависит от формата дампа.

Plain SQL:

psql -U postgres -d target_db -f /backups/mydb.sql

Custom или directory:

pg_restore -U postgres -d target_db -Fc /backups/mydb.dump

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

• -j N — параллельное восстановление (для directory-формата).

• -c — очистить объекты перед созданием.

• -C — создать базу перед восстановлением.

• -t table — восстановить только указанную таблицу.

Пример параллельного восстановления:

pg_restore -U postgres -d target_db -j 4 /backups/mydb_dir

Перед восстановлением убедитесь, что целевая база существует (или используйте -C).

Шаг 5. Физическое резервное копирование: pg_basebackup

pg_basebackup создаёт бинарную копию всего кластера. Требуется доступ по протоколу репликации. Пример:

pg_basebackup -U replicator -h primary_host -D /backups/base -Ft -z -P

Ключи:

• -D — каталог для бэкапа.

• -Ft — tar-формат.

• -z — сжатие gzip.

• -P — прогресс.

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

Шаг 6. Непрерывное архивирование и PITR

Для возможности восстановления на любой момент времени настройте архивирование WAL:

1. В postgresql.conf установите wal_level = replica (или logical). 2. Включите archive_mode = on. 3. Задайте archive_command, например: 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f'.

Затем сделайте базовый бэкап (pg_basebackup) и сохраняйте все WAL-файлы. Для восстановления потребуется базовый бэкап и цепочка WAL до нужного момента.

Шаг 7. Бэкап через pgAdmin 4

В pgAdmin 4 можно создавать дампы без командной строки:

1. Подключитесь к серверу. 2. Правой кнопкой по базе → Backup. 3. Укажите формат (Custom, Tar, Plain), файл назначения, при необходимости — сжатие. 4. Нажмите Backup.

Для восстановления: правой кнопкой по базе → Restore, выберите файл и параметры.

Шаг 8. Автоматизация с помощью cron

Создайте скрипт backup_pg.sh:

#!/bin/bash
BACKUP_DIR=/backups/postgres
DATE=$(date +%Y%m%d_%H%M%S)
pg_dump -U postgres -Fc -f $BACKUP_DIR/mydb_$DATE.dump mydb
# Удаление старых бэкапов (старше 7 дней)
find $BACKUP_DIR -name '*.dump' -mtime +7 -delete

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

chmod +x /usr/local/bin/backup_pg.sh
crontab -e

Пример запуска ежедневно в 2:00:

0 2 * * * /usr/local/bin/backup_pg.sh

Шаг 9. Проверка целостности бэкапа

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

createdb test_restore
pg_restore -U postgres -d test_restore /backups/latest.dump

Если восстановление прошло успешно — бэкап валиден. Удалите тестовую базу.

Шаг 10. Хранение и ротация бэкапов

Следуйте правилу 3-2-1: минимум 3 копии, на 2 разных носителях, 1 из которых — вне офиса. Настройте ротацию: храните ежедневные бэкапы за неделю, еженедельные — за месяц, ежемесячные — за год. Используйте find или специализированные утилиты (например, pgbackrest).

Типичные ошибки

• Игнорирование проверки бэкапов. Бэкап, который не проверяли на восстановление, может оказаться бесполезным.

• Отсутствие сжатия. Дампы без сжатия занимают много места и медленно копируются.

• Запуск pg_dump без указания пароля. Используйте .pgpass или переменную PGPASSWORD (осторожно с безопасностью).

• Восстановление в существующую базу без очистки. Может привести к конфликтам. Используйте -c или создавайте новую базу.

• Неучёт глобальных объектов. При восстановлении только одной базы роли и табличные пространства не воссоздаются.

• Отсутствие мониторинга. Настройте уведомления об успехе/неудаче бэкапа.

FAQ

Вопрос: Можно ли сделать бэкап без остановки сервера? Да, pg_dump и pg_basebackup работают без остановки. Для физического бэкапа требуется режим репликации, но сервер продолжает работу.

Вопрос: Чем отличается pg_dump от pg_dumpall? pg_dump сохраняет одну базу, pg_dumpall — весь кластер, включая роли и табличные пространства.

Вопрос: Как восстановить только одну таблицу? Используйте pg_restore -t table_name с custom-дампом.

Вопрос: Какой формат дампа лучше? Для гибкости — custom (-Fc). Для простоты и переносимости — plain SQL.

Вопрос: Как автоматизировать бэкапы? С помощью cron или systemd timers, а также специализированных инструментов (pgBackRest, Barman).

Итог

Резервное копирование PostgreSQL — не разовая акция, а регулярный процесс. Выберите подходящую стратегию: для небольших баз хватит pg_dump, для крупных и критичных — комбинация pg_basebackup и архивирования WAL. Обязательно проверяйте восстановление, храните копии в нескольких местах и автоматизируйте процесс. Тогда любые сбои будут безболезненны.