Как создавать резервные копии и восстанавливать базы данных с помощью pg_dump и pg_restore

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

Это руководство посвящено логическим резервным копиям отдельных баз данных. Для аварийного восстановления всего кластера, восстановления на момент времени или очень крупных установок используйте это вместе с физическими резервными копиями и архивированием WAL.

Понимание pg_dump

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

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

pg_dump поддерживает несколько форматов вывода:

Обычный текст (-Fp, по умолчанию): Записывает SQL, который восстанавливается с помощью psql. Его легко просматривать и редактировать, но большие восстановления могут быть медленнее и менее гибкими.
Пользовательский архив (-Fc): Записывает сжатый архив для pg_restore. Поддерживает выборочное восстановление и параллельное восстановление.
Формат каталога (-Fd): Записывает каталог файлов. Поддерживает параллельный дамп и параллельное восстановление, что полезно для больших баз данных.
Формат tar (-Ft): Записывает tar-архив для pg_restore. Это один файл, но он не поддерживает сжатие внутри формата так же, как пользовательские архивы.

Распространенные параметры pg_dump

Вот некоторые из наиболее часто используемых параметров для pg_dump:

-h <hostname>: Подключиться к хосту.
-p <port>: Подключиться к порту.
-U <username>: Подключиться как пользователь.
-W: Принудительно запрашивать пароль перед подключением.
-f <filename>: Записать в файл или каталог вместо стандартного вывода.
-F <format>: Выбрать p, c, d или t.
-Z <level>: Установить уровень сжатия для форматов, поддерживающих сжатие.
--exclude-table=<pattern>: Исключить соответствующие таблицы.
-j <number> или --jobs=<number>: Использовать параллельные задания при дампе в формате каталога.
--clean: Включить команды удаления перед командами создания в выводе простого SQL.
--create: Включить команды для создания самой базы данных.

Пример: Базовая резервная копия в виде обычного текста

Чтобы создать резервную копию в виде обычного текста базы данных с именем mydatabase, принадлежащей пользователю myuser, и сохранить ее в mydatabase_backup.sql:

pg_dump -U myuser -f mydatabase_backup.sql mydatabase

Если нужно указать хост и порт:

pg_dump -h localhost -p 5432 -U myuser -f mydatabase_backup.sql mydatabase

В зависимости от метода аутентификации может быть запрошен пароль myuser.

Пример: Резервная копия в пользовательском формате (рекомендуется)

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

pg_dump -U myuser -Fc -f mydatabase_backup.dump mydatabase

Это создает mydatabase_backup.dump в формате, подходящем для pg_restore.

Пример: Резервная копия в формате каталога с параллельными заданиями

Для больших баз данных параллельный дамп может сократить время резервного копирования. Требуется формат каталога:

pg_dump -U myuser -Fd -j 4 -f mydatabase_backup_dir mydatabase

Эта команда создаст каталог с именем mydatabase_backup_dir, содержащий несколько файлов, используя 4 параллельных задания.

Понимание pg_restore

pg_restore восстанавливает пользовательские, каталоговые или tar-архивы, созданные pg_dump. Обычные SQL-файлы восстанавливаются с помощью psql, а не pg_restore.

Распространенные параметры pg_restore

-h <hostname>: Подключиться к хосту.
-p <port>: Подключиться к порту.
-U <username>: Подключиться как пользователь.
-W: Принудительно запрашивать пароль перед подключением.
-d <database>: Подключиться к этой базе данных для восстановления.
-v: Выводить подробный вывод восстановления.
-c или --clean: Удалить объекты базы данных перед их повторным созданием.
--create: Создать дампированную базу данных перед восстановлением. Подключиться с помощью -d к существующей служебной базе данных, обычно postgres.
-j <number>: Использовать параллельные задания для восстановления, где это поддерживается.
-F <format>: Указать формат архива, если автоматического определения недостаточно.

Восстановление из резервных копий в виде обычного текста SQL

Если вы создали резервную копию в виде обычного текста SQL с помощью pg_dump (например, mydatabase_backup.sql), восстановите ее с помощью psql:

psql -U myuser -d mydatabase -f mydatabase_backup.sql

Если файл резервной копии включает команды CREATE DATABASE и \connect от pg_dump --create, подключитесь к служебной базе данных, например postgres:

psql -U myuser -f mydatabase_backup.sql postgres

Пример: Восстановление из резервной копии в пользовательском формате

Чтобы восстановить резервную копию в пользовательском формате mydatabase_backup.dump в базу данных с именем restored_db:

pg_restore -U myuser -d restored_db mydatabase_backup.dump

Если архив был создан с помощью pg_dump --create, вы можете попросить pg_restore создать дампированную базу данных. Значение -d должно быть существующей базой данных, используемой для начального подключения, а не новой создаваемой базой данных:

pg_restore -U myuser --create -d postgres mydatabase_backup.dump

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

createdb -U myuser restored_db
pg_restore -U myuser -d restored_db mydatabase_backup.dump

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

Для более быстрого восстановления, особенно с большими наборами данных, используйте параметр -j:

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup.dump

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

Пример: Восстановление из резервной копии в формате каталога

Чтобы восстановить резервную копию, созданную в формате каталога (например, mydatabase_backup_dir):

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup_dir

Обратите внимание, что последний аргумент — это путь к каталогу.

Лучшие практики для резервных копий

Планируйте резервные копии: Используйте cron, системные таймеры systemd или вашу платформу оркестрации. Выберите расписание на основе того, сколько данных вы можете позволить себе потерять.
Используйте правильный формат: Пользовательский формат — хороший выбор по умолчанию. Формат каталога лучше, когда вам нужен параллельный дамп для большой базы данных.
Тестируйте восстановления: Восстанавливайте в промежуточную базу данных по расписанию. Резервная копия, которую вы никогда не тестировали, — это всего лишь догадка.
Захватывайте глобальные объекты: Дамп ролей и табличных пространств отдельно с помощью pg_dumpall --globals-only при перестройке сервера с нуля.
Храните копии вне сайта: Держите резервные копии вдали от основного сервера и защищайте их шифрованием и контролем доступа.
Мониторьте задания: Оповещайте о неудачных дампах, необычно маленьких файлах резервных копий и старых временных метках резервных копий.
Планируйте хранение: Решите, сколько почасовых, ежедневных, еженедельных и ежемесячных резервных копий вы храните, затем автоматизируйте очистку.
Будьте осторожны с --clean: Он удаляет объекты в целевой базе данных. Используйте его только тогда, когда вы намерены заменить существующие объекты.

Вывод

Используйте pg_dump -Fc для гибкой однофайловой резервной копии, pg_dump -Fd -j, когда нужен параллельный дамп, psql для восстановления обычного SQL и pg_restore для восстановления архивов. Затем подтвердите процесс, восстановив в тестовую базу данных, прежде чем это понадобится в экстренной ситуации.