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

pg_dump создаёт логическую резервную копию одной базы данных в виде SQL-команд или архивного файла. Дамп представляет собой согласованный снимок на момент начала работы утилиты и не блокирует чтение или запись другими сессиями. Это делает pg_dump пригодным для миграций между версиями и архитектурами, но для регулярного бэкапа крупных продакшен-баз официальная документация рекомендует рассматривать другие методы (непрерывное архивирование WAL, file-level backup).

Выбор формата дампа​


pg_dump поддерживает четыре формата вывода, и от выбора зависит способ восстановления и доступные возможности.

ФорматФлагВосстановлениеСжатиеПараллельный дампВыборочный restore
Plain text-Fp (по умолчанию)psqlНет (внешнее через gzip)НетНет
Custom-Fcpg_restoreДа (встроенное)НетДа
Directory-Fdpg_restoreДа (gzip по умолчанию)Да (-j)Да
Tar-Ftpg_restoreНетНетОграниченно

Для большинства сценариев оптимален custom format (-Fc): он сжат, позволяет выборочно восстанавливать объекты и работает с pg_restore. Directory format (-Fd) нужен, когда важна скорость дампа больших баз за счёт параллелизма.

Plain text удобен для отладки и быстрого просмотра содержимого, но не поддерживает выборочное восстановление и параллелизм.

Создание дампа​


Базовая команда​


Bash: 
pg_dump -Fc -f mydb.dump mydb

Здесь -Fc задаёт custom-формат, -f указывает выходной файл, mydb — имя базы. Утилита подключается к локальному серверу с портом по умолчанию и именем пользователя, совпадающим с текущим системным.

Подключение к удалённому серверу​


Bash: 
pg_dump -h db.example.com -p 5432 -U backup_user -Fc -f mydb.dump mydb

pg_dump — обычный клиент PostgreSQL. Для полного дампа базы практически всегда требуется суперпользователь или роль с правами чтения на все таблицы. Если прав недостаточно, можно ограничить дамп конкретными схемами (-n) или таблицами (-t).

Параллельный дамп​


Bash: 
pg_dump -j 4 -Fd -f /backup/mydb_dir mydb

-j 4 запускает четыре параллельных процесса, каждый из которых дампит отдельную таблицу. Работает только с directory-форматом. pg_dump откроет njobs + 1 соединений, поэтому max_connections на сервере должен это учитывать.

Параллельный дамп требует поддержки synchronized snapshots (PostgreSQL 9.2+ на primary, 10+ на standby). Без неё разные worker-процессы могут увидеть несогласованные данные.

Дамп с сжатием через gzip (plain text)​


Bash: 
pg_dump mydb | gzip > mydb.sql.gz

Восстановление:

Bash: 
gunzip -c mydb.sql.gz | psql -X -d mydb

Дамп всей кластерной структуры​


pg_dump работает с одной базой. Роли и tablespaces — объекты уровня кластера. Для полного бэкапа используется pg_dumpall:

Bash: 
pg_dumpall -f cluster_backup.sql

Восстановление требует суперпользователя:

Bash: 
psql -X -f cluster_backup.sql postgres

Если вы делаете дампы отдельных баз через pg_dump, глобальные объекты нужно сохранить отдельно:

Bash: 
pg_dumpall --globals-only -f globals.sql

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

Восстановление​


Custom и directory форматы — pg_restore​


Bash: 
createdb -T template0 mydb
pg_restore -d mydb mydb.dump

База должна существовать до запуска pg_restore (если не используется флаг --create). Создание из template0 обязательно, если в исходной базе были добавлены объекты через template1.

Plain text — psql​


Bash: 
createdb -T template0 mydb
psql -X --set ON_ERROR_STOP=on -d mydb < mydb.sql

-X отключает загрузку .psqlrc, чтобы настройки пользователя не повлияли на восстановление. ON_ERROR_STOP=on прерывает выполнение при первой SQL-ошибке вместо продолжения с частичным результатом.

Атомарное восстановление​


Для критичных сценариев, где частичное восстановление недопустимо:

Bash: 
psql -X --single-transaction -d mydb < mydb.sql

Или для pg_restore:

Bash: 
pg_restore --single-transaction -d mydb mydb.dump

Весь дамп выполняется в одной транзакции: либо полностью применяется, либо полностью откатывается. Обратная сторона — даже незначительная ошибка откатывает многочасовое восстановление.

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


Bash: 
pg_restore -j 4 -d mydb mydb.dump

Работает с custom и directory форматами. Каждый job — отдельный процесс или поток с собственным соединением к серверу. Оптимальное число jobs зависит от CPU и дисковой подсистемы; разумная отправная точка — количество ядер сервера.

--single-transaction несовместим с -j.

Выборочное восстановление​


Bash: 
## Только данные, без DDL
pg_restore --data-only -d mydb mydb.dump

## Только схема, без данных
pg_restore --schema-only -d mydb mydb.dump

## Конкретная таблица
pg_restore -t orders -d mydb mydb.dump

## Конкретная схема
pg_restore -n analytics -d mydb mydb.dump

При восстановлении отдельной таблицы pg_restore не подтягивает зависимости (индексы, внешние ключи из других таблиц). Если целевая база пустая, восстановление может завершиться ошибкой.

Перезапись существующей базы​


Bash: 
pg_restore --clean --if-exists -d mydb mydb.dump

--clean добавляет DROP перед каждым CREATE. --if-exists подавляет ошибки, если объект ещё не существует.

Для полного пересоздания базы:

Bash: 
pg_restore --create --clean -d postgres mydb.dump

Здесь -d postgres используется только для выполнения начальных команд DROP DATABASE / CREATE DATABASE; данные восстанавливаются в базу с именем из архива.

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


Успешное завершение pg_restore или psql не гарантирует, что данные корректны. Минимальная верификация включает несколько шагов.

1. Код возврата и ошибки​


pg_restore по умолчанию продолжает работу после ошибок и выводит их количество в конце. Для автоматизации используйте --exit-on-error:

Bash: 
pg_restore --exit-on-error -d mydb mydb.dump
echo $?

Ненулевой код возврата — сигнал, что восстановление не прошло полностью.

2. Подсчёт строк​


Сравните количество записей в ключевых таблицах источника и восстановленной базы:

SQL: 
SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY relname;

Это приближённая оценка (статистика может быть неактуальной), но для быстрой проверки подходит. Точный подсчёт:

SQL: 
SELECT COUNT(*) FROM orders;

3. ANALYZE после восстановления​


SQL: 
ANALYZE;

Без актуальной статистики планировщик запросов будет строить неоптимальные планы. Официальная документация прямо рекомендует запускать ANALYZE после каждого восстановления.

4. Проверка целостности ограничений​


SQL: 
-- Поиск нарушений внешних ключей (если восстановление было частичным)
SELECT conname, conrelid::regclass
FROM pg_constraint
WHERE contype = 'f' AND NOT convalidated;

5. Контрольные суммы на уровне приложения​


Если есть возможность, выполните на восстановленной базе те же бизнес-запросы, что и на источнике, и сравните результаты. Это особенно важно при миграциях между версиями PostgreSQL.

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


Восстановление в базу, созданную из template1. Если в template1 добавлены расширения или объекты, они конфликтуют с содержимым дампа. Всегда создавайте целевую базу из template0.

Отсутствие ролей. Перед восстановлением все пользователи, которые владеют объектами или имеют привилегии в дампе, должны существовать в целевом кластере. Иначе pg_restore не сможет назначить ownership и права.

Игнорирование предупреждений. pg_dump выводит warnings в stderr. Их стоит проверять — они могут указывать на пропущенные объекты или проблемы с правами.

Восстановление дампа из недоверенного источника. Дамп содержит произвольный SQL, который выполняется с правами суперпользователя при восстановлении. Non-plain-text дампы можно предварительно просмотреть через pg_restore --file или -l (list table of contents).

Использование plain text для больших баз без сжатия. Файл может превысить ограничения файловой системы. Решение: custom format, gzip-пайп или split.

Работа с большими базами​


Для баз, которые не помещаются в один файл или требуют ускорения:

Bash: 
## Параллельный дамп в directory-формат
pg_dump -j 8 -Fd -f /backup/large_db mydb

## Параллельное восстановление
pg_restore -j 8 -d mydb /backup/large_db

Если файловая система ограничивает размер файла, а custom format не подходит:

Bash: 
pg_dump mydb | split -b 2G - mydb_part_

Сборка при восстановлении:

Bash: 
cat mydb_part_* | psql -X -d mydb

Миграция между серверами​


pg_dump и psql работают через стандартный вывод и ввод, что позволяет передавать дамп напрямую:

Bash: 
pg_dump -h source_host mydb | psql -X -h target_host mydb

Это единственный метод, который корректно работает при переносе между разными архитектурами (например, 32-bit → 64-bit) и при обновлении на новую мажорную версию PostgreSQL.

Чек-лист перед запуском в продакшене​


  • Убедитесь, что роль для pg_dump имеет права чтения на все целевые таблицы.
  • Выберите формат: -Fc для гибкости, -Fd для параллелизма.
  • Проверьте, что max_connections учитывает дополнительные соединения при -j.
  • Создавайте целевую базу из template0.
  • Используйте --exit-on-error или --single-transaction в автоматизированных скриптах.
  • Запускайте ANALYZE после восстановления.
  • Проверяйте код возврата утилиты в CI/CD-пайплайне.
  • Храните дампы с учётом того, что они содержат полный SQL и потенциально чувствительные данные.

Источники​


 
Нажмите, чтобы раскрыть...
Назад
Верх Низ