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

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

В мире управления базами данных надежные стратегии резервного копирования и восстановления не просто рекомендуются; они абсолютно критически важны. Потеря данных может быть вызвана различными источниками, включая сбои оборудования, человеческие ошибки или вредоносные атаки. PostgreSQL, мощная реляционная база данных с открытым исходным кодом, предоставляет основные утилиты командной строки, pg_dump и pg_restore, для обеспечения целостности и восстанавливаемости ваших данных. Этот учебник проведет вас через лучшие практики использования этих инструментов для создания надежных резервных копий и безопасного восстановления ваших баз данных PostgreSQL.

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

Обзор pg_dump

pg_dump — это утилита для создания резервных копий базы данных PostgreSQL. Она генерирует файл, содержащий команды SQL, которые могут быть использованы для повторного создания объектов базы данных (таблиц, функций, индексов и т. д.) и заполнения их данными. pg_dump работает путем подключения к базе данных, а затем генерации вывода. Она может выводить данные в нескольких форматах, каждый из которых имеет свои преимущества.

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

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

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

Общие параметры pg_dump

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

• -h <hostname>: Указывает имя хоста машины, на которой запущен сервер.
• -p <port>: Указывает TCP-порт, на котором сервер ожидает подключения.
• -U <username>: Подключиться как указанный пользователь.
• -W: Принудительно запрашивать пароль для pg_dump перед подключением.
• -f <filename>: Указывает имя выходного файла. Если не указано, pg_dump записывает в стандартный вывод.
• -F <format>: Указывает формат выходного файла (p, c, d, t).
• -Z <0-9>: Указывает уровень сжатия для сжатых форматов (например, Fc).
• --exclude-table=<table_name>: Исключает указанную таблицу из дампа.
• --jobs=<number>: Количество параллельных заданий для использования при дампинге (только для формата каталога).
• --clean: Включить команды для удаления объектов базы данных перед их созданием. Это полезно для полного восстановления для обеспечения чистого состояния.
• --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.

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

Использование пользовательского архивного формата обычно предпочтительнее из-за его гибкости и сжатия. Чтобы создать сжатую пользовательскую резервную копию mydatabase:

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 — это утилита для восстановления базы данных PostgreSQL из архива, созданного pg_dump в одном из пользовательских форматов, форматов каталога или tar. Она не используется для простых текстовых SQL-дампов (которые восстанавливаются с помощью команды psql).

Общие параметры pg_restore

• -h <hostname>: Указывает имя хоста машины, на которой запущен сервер.
• -p <port>: Указывает TCP-порт, на котором сервер ожидает подключения.
• -U <username>: Подключиться как указанный пользователь.
• -W: Принудительно запрашивать пароль для pg_restore перед подключением.
• -d <database>: Указывает имя базы данных для восстановления. Это обязательный параметр.
• -v: Подробный режим (verbose mode).
• -c или --clean: Очистить (удалить) объекты базы данных перед их повторным созданием.
• --create: Создать базу данных перед восстановлением в нее. Требует первоначального подключения к другой базе данных (например, postgres).
• -j <number>: Количество параллельных заданий для использования при восстановлении. Это значительно ускоряет восстановление, особенно для пользовательских форматов и форматов каталогов.
• -F <format>: Указывает формат архива (c, d, t). Обычно pg_restore может автоматически определять его.

Восстановление из простых текстовых SQL-резервных копий

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

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

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

psql -U myuser -f mydatabase_backup.sql postgres

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

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

pg_restore -U myuser -d restored_db mydatabase_backup.dump

Если целевая база данных restored_db не существует, вы можете использовать опцию --create. В этом случае вам необходимо подключиться к другой базе данных (например, postgres), так как целевая база данных будет создана.

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

Эта команда создаст базу данных restored_db, а затем восстановит в нее данные.

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

Для более быстрого восстановления, особенно с большими наборами данных, используйте опцию -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 (Linux/macOS) или Планировщика заданий (Windows) для запуска через регулярные интервалы (например, ежедневно, ежечасно).
• Выбор правильного формата: Для большинства случаев использования рекомендуется пользовательский формат (-Fc) из-за его сжатия и совместимости с параллельными функциями pg_restore. Используйте формат каталога (-Fd) для чрезвычайно больших баз данных, где параллельное дампинг/восстановление имеет решающее значение.
• Тестирование резервных копий: Резервная копия полезна только в том случае, если ее можно восстановить. Регулярно тестируйте процесс восстановления, восстанавливая данные в тестовой или девелоперской среде, чтобы убедиться в целостности данных и достоверности файлов резервных копий.
• Хранение резервных копий вне офиса: Храните копии резервных копий в отдельном физическом месте от вашего основного сервера базы данных для защиты от катастроф на уровне всего объекта.
• Мониторинг заданий резервного копирования: Внедрите мониторинг, чтобы гарантировать успешное выполнение запланированных заданий резервного копирования и оповещать вас в случае их сбоя.
• Рассмотрите сжатие: Используйте опции сжатия pg_dump или передавайте вывод через внешние инструменты сжатия (gzip, zstd), если не используете форматы, которые сжимаются автоматически.
• Разумное использование --clean и --create: При выполнении полного восстановления в существующую среду --clean может быть очень полезным. При переходе в новую среду или обеспечении полностью чистого состояния --create является обязательным. Будьте осторожны с этими опциями в производственных системах.
• Управление версиями резервных копий: Внедрите стратегию управления сроком хранения резервных копий, включая то, как долго они хранятся и как удаляются старые.

Заключение

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