Резервная копия PostgreSQL: pg_dump и восстановление

Резервная копия PostgreSQL, которую ни разу не восстановили, не считается проверенной. Для PostgreSQL 16 разница между рабочим сценарием и неприятным сюрпризом часто сводится к формату дампа, версии клиентских утилит и тому, есть ли у роли права на создание базы и объектов. Ниже — практическая схема: создать архив через pg_dump, восстановить его в отдельную базу, проверить результат и понять, когда нужен pg_dumpall.

Что выбрать: SQL-файл или архив pg_dump

pg_dump сохраняет одну базу данных. В дамп попадают таблицы, данные, индексы, ограничения, функции и другие объекты этой базы. Роли, права на уровне кластера и tablespace в полном объёме — уже отдельная задача.

Для регулярных копий обычно выбирают формат custom. Он создаётся с ключом --format=custom, имеет расширение .dump или .backup и восстанавливается через pg_restore. Формат plain — обычный SQL-текст: его удобно читать и выполнять через psql, но выборочное восстановление и параллельную загрузку он не поддерживает.

Копия становится резервной только после успешного восстановления на отдельном экземпляре PostgreSQL.

Сноска. Дамп — это логическое описание базы: набор команд и данных, из которых PostgreSQL собирает объекты заново. Это не побайтовая копия файлов кластера и не замена физической репликации или архивированию WAL.

Перед созданием дампа

Примеры ниже рассчитаны на PostgreSQL 16 и клиентские утилиты той же версии. Используйте pg_dump и pg_restore той же или более новой основной версии, чем сервер. Клиент старее сервера может не уметь читать новые возможности протокола и формата.

• Проверьте, что утилиты установлены: pg_dump --version и pg_restore --version.
• Запишите имя базы, хост, порт и роль, от имени которой выполняется копирование.
• Убедитесь, что на диске достаточно места для дампа и временных файлов.
• Решите, нужно ли сохранять владельцев объектов, ACL и большие объекты. Не отключайте эти данные без причины.
• Подготовьте отдельную тестовую базу или сервер для восстановления. Не проверяйте первый запуск на единственной рабочей базе.

Пароль не стоит передавать в командной строке: он может попасть в историю shell или список процессов. Используйте файл .pgpass с правами доступа, которые допускает документация PostgreSQL, либо механизм секретов вашей CI/CD-системы.

Рабочий сценарий для регулярной копии

1. Создайте архив в формате custom

Команда подключается к базе shop на локальном сервере и записывает архив в каталог /var/backups/postgresql. Каталог должен существовать, а пользователь ОС — иметь права на запись.

mkdir -p /var/backups/postgresql
pg_dump \
  --host=127.0.0.1 \
  --port=5432 \
  --username=backup_user \
  --format=custom \
  --file=/var/backups/postgresql/shop-2025-01-15.dump \
  shop

Если команда завершилась с кодом 0 и файл появился, дамп создан. Проверьте размер и время изменения файла, но не принимайте ненулевой размер за доказательство его целостности.

2. Посмотрите содержимое архива

До восстановления можно проверить, что в архиве действительно есть ожидаемые схемы и таблицы.

pg_restore \
  --list \
  /var/backups/postgresql/shop-2025-01-15.dump

В выводе должны присутствовать, например, определения таблиц, последовательностей, индексов и данные. Если архив повреждён или указан не тот файл, утилита сообщит об ошибке чтения.

3. Создайте чистую базу для проверки

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

createdb \
  --host=127.0.0.1 \
  --port=5432 \
  --username=restore_user \
  --encoding=UTF8 \
  shop_restore

Проверка проста: подключение к shop_restore проходит, а новая база отображается в списке баз.

4. Восстановите архив

pg_restore \
  --host=127.0.0.1 \
  --port=5432 \
  --username=restore_user \
  --dbname=shop_restore \
  --no-owner \
  --exit-on-error \
  /var/backups/postgresql/shop-2025-01-15.dump

Ключ --no-owner полезен, если на тестовом сервере нет ролей исходной базы. Для восстановления в среду, где владельцы и роли уже настроены, этот ключ нужно применять осознанно. С --exit-on-error процесс остановится на первой ошибке, а не завершится с частично загруженной базой.

5. Проверьте результат запросами

Проверка должна быть содержательной: сравните число ключевых таблиц, наличие индексов и несколько контрольных запросов приложения. Если у проекта есть миграции и smoke-тесты, запустите их против восстановленной базы. Несколько строк из одной таблицы не подтверждают, что восстановлены права, функции, последовательности и большие объекты.

Если нужен обычный SQL-файл

Plain-дамп можно открыть редактором и передать в psql. Это удобно для небольших баз или миграции, когда нужно видеть SQL-команды. Цена простоты — отсутствие выборочного восстановления средствами pg_restore и невозможность использовать его параметры для управления порядком объектов.

pg_dump \
  --host=127.0.0.1 \
  --port=5432 \
  --username=backup_user \
  --format=plain \
  --file=/var/backups/postgresql/shop-2025-01-15.sql \
  shop

createdb \
  --host=127.0.0.1 \
  --port=5432 \
  --username=restore_user \
  shop_restore

psql \
  --host=127.0.0.1 \
  --port=5432 \
  --username=restore_user \
  --dbname=shop_restore \
  --file=/var/backups/postgresql/shop-2025-01-15.sql

Если в SQL-файле есть команды, создающие базу или переключающие подключение, сценарий отличается. Перед запуском просмотрите начало и конец файла, особенно если дамп получен с ключом --create.

Что часто ломается

Клиентская утилита старее сервера

Обычно проблема проявляется сообщением о том, что сервер новее клиента, либо неизвестным параметром. Установите клиентские утилиты PostgreSQL 16 или более новой версии и повторите операцию. Смешивать бинарники от разных установок только ради запуска команды не стоит: сначала проверьте вывод --version.

Не хватает прав на таблицы или схему

pg_dump может завершиться ошибкой доступа к таблице, последовательности или схеме. Проверьте права роли и владельцев объектов; выдавать роли постоянные сверхширокие права только ради резервного копирования опасно.

Восстановление остановилось на владельце или расширении

В целевой системе может не быть роли-владельца или установленного расширения. Для тестового восстановления используйте --no-owner, а список расширений и их версии подготовьте заранее. Сам дамп не устанавливает расширения операционной системы.

База восстановилась, но приложение работает неправильно

Проверьте последовательности, часовой пояс, расширения, права и настройки подключения. Если приложение ожидает внешние файлы, сертификаты или объектное хранилище, логического дампа базы недостаточно: эти зависимости нужно копировать отдельно.

Когда pg_dump недостаточно

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

pg_dumpall \
  --host=127.0.0.1 \
  --port=5432 \
  --username=backup_user \
  --globals-only \
  > /var/backups/postgresql/globals-2025-01-15.sql

Такая команда не заменяет дамп базы: она сохраняет глобальные объекты, а не таблицы и их данные. Для больших баз с жёсткими требованиями к времени простоя смотрите в сторону физического резервного копирования и архивирования WAL — это отдельная архитектура, а не дополнительный ключ pg_dump.

Что делать дальше

Сделайте восстановление частью расписания, а не разовой ручной процедурой. Храните несколько копий в разных местах, ограничьте доступ к файлам дампов и задайте срок хранения. В мониторинге отслеживайте время создания, размер архива и код завершения команды.

Главный тест — регулярное восстановление на отдельный сервер. Зафиксируйте длительность операции, время появления готовой базы и результат контрольных запросов. Если эти показатели не укладываются в допустимое окно простоя, одной настройки pg_dump будет мало: нужно менять формат, включать параллельные операции там, где это уместно, или проектировать физическое резервное копирование.