SQL-дамп
Техника резервного копирования с помощью SQL-дампа создает файл с командами SQL, который воссоздает базу данных в том же состоянии, в каком она была на момент создания дампа.
Создание SQL-дампа
PostgreSQL включает утилиту pg_dump, создающую дамп-файлы. Утилита pg_dump не требует блокировки доступа других пользователей и может создавать SQL-дампы во время использования базы данных.
Базовый синтаксис pg_dump:
$ pg_dump <имя_базы_данных> > <имя_файла>Где:
-
<имя_базы_данных>— имя базы данных, для которой создается резервная копия; -
<имя_файла>— имя создаваемого дамп-файла.
Утилита pg_dump записывает результат в стандартный вывод. Дамп может быть выведен в виде текстового файла, содержащего скрипт, или архивного файла (в формате custom, directory или tar). За дополнительной информации обратитесь к секции Options статьи pg_dump. Приведенная выше команда создает текстовый файл со скриптом.
Дамп-файлы, содержащие скрипт, представляют собой текстовые файлы с SQL-командами. Чтобы восстановить базу данных из такого файла, используется утилита psql. Дамп-файлы можно использовать для создания резервных копий и для переноса базы данных на машины с другой архитектурой. Также можно вручную отредактировать файл со скриптом для восстановления базы данных в другой СУБД.
Утилита pg_dump выполняет резервное копирование одной базы данных. Для резервного копирования всего кластера или глобальных объектов, общих для всех баз данных в кластере, таких как роли и табличные пространства, используйте pg_dumpall.
Поскольку pg_dump является клиентским приложением, резервную копию можно создать с любого удаленного хоста, имеющего доступ к базе данных. У него должен быть доступ на чтение ко всем таблицам, резервные копии которых нужно создать. Чтобы создать резервную копию всей базы данных, практически всегда необходимо запускать pg_dump от имени суперпользователя. Если у вас нет такой привилегии, можно создать резервные копии частей базы данных, к которым у вас есть доступ на чтение. Для этого используйте параметры -n (схема) или -t (таблица), как описано в статье pg_dump.
В приведенном ниже примере выполняется резервное копирование базы данных books_store в файл books_store20221215.sql:
$ pg_dump books_store > books_store20221215.sqlВ следующем примере создается резервная копия таблицы books базы данных books_store:
$ pg_dump -d books_store -t books > books_20221215.sqlЧтобы указать, к какому серверу базы данных должен обращаться pg_dump, используйте параметры командной строки -h (хост) и -p (порт). Хост по умолчанию — localhost или хост, указанный в переменной окружения PGHOST. Порт по умолчанию задается в переменной окружения PGPORT. Если PGPORT не назначен, pg_dump использует значение, указанное во время компиляции. Значение порта по умолчанию для сервиса ADPG — 5432. Приложение pg_dump подключается с именем пользователя базы данных, которое равно текущему имени пользователя операционной системы. Чтобы переопределить это, укажите параметр -U или установите переменную среды PGUSER.
В приведенном ниже примере выполняется резервное копирование базы данных books_store, которая находится на хосте 10.92.6.26. Подключение осуществляется от имени пользователя postgres:
$ pg_dump -h 10.92.6.26 -U postgres books_store > books_store20221215.sqlАрхивные файлы утилиты pg_dump предназначены для переноса баз данных между различными архитектурами. За подробностями обратитесь к статье pg_dump.
|
ПРИМЕЧАНИЕ
Дамп-файлы, созданные pg_dump, можно загрузить в более новые версии PostgreSQL, тогда как резервное копирование на уровне файлов и непрерывное архивирование (PITR) зависят от версии PostgreSQL.
|
Восстановление SQL-дампа
Для восстановления данных из дамп-файлов используется утилита psql. Базовый синтаксис команды для восстановления дампа:
$ psql <имя_базы_данных> < <имя_файла>Где:
-
<имя_базы_данных>— имя базы данных, которая должна быть восстановлена; -
<имя_файла>— имя дамп-файла.
Приведенная выше команда не создает указанную базу данных. Необходимо создать её из шаблона template0 перед выполнением команды.
$ createdb -T template0 books_store|
ПРИМЕЧАНИЕ
Утилита pg_dump создает дампы относительно template0. Языки, процедуры и другие элементы, добавленные через шаблон template1, также будут помещены в дамп-файл. Если вы используете измененный template1, создайте пустую базу данных из шаблона template0, как показано в приведенном выше примере.
|
Утилита psql продолжает выполнять скрипт после возникновения SQL-ошибок. Вы можете использовать переменную ON_ERROR_STOP, чтобы изменить это поведение и обеспечить выход из psql со статусом 3 в случае ошибок.
$ psql --set ON_ERROR_STOP=on books_store < books_store20221215.sqlПеред восстановлением SQL-дампа все пользователи, которые владели объектами или имели права на объекты в выгруженной базе данных, должны существовать. Если они отсутствуют, при восстановлении базы данных будут ошибки пересоздания объектов с первоначальными владельцами или правами.
После восстановления резервной копии также необходимо запустить ANALYZE для каждой базы данных, чтобы предоставить оптимизатору запросов правильную статистику.
Дампы, выгруженные в архивном формате, восстанавливаются утилитой pg_restore.
|
РЕКОМЕНДАЦИЯ
Для улучшения производительности восстановления дамп-файлов, обратите внимание на рекомендации из раздела Улучшение производительности pg_dump.
|
Создание дампа кластера
Утилита pg_dump создает резервную копию базы данных. Она не выводит информацию о ролях или табличных пространствах, поскольку они указаны для всего кластера, а не для отдельной базы данных. Утилита pg_dumpall предназначена для создания дампа всего кластера. Она создает резервную копию каждой базы данных в кластере и сохраняет общие данные для всего кластера, такие как определения ролей и табличных пространств.
$ pg_dumpall > <имя_файла>Где <имя_файла> — это имя дамп-файла, который будет создан.
Базу данных из полученного дамп можно восстановить с помощью psql:
$ psql -f dumpfile postgresОпция -f означает, что psql читает команды из указанного файла dumpfile. postgres — имя базы данных. Вы можете указать имя любой существующей базы данных вместо postgres, но если вы восстанавливаете резервную копию, содержащую несколько баз данных, в пустой кластер, следует использовать postgres. Также необходимо иметь права суперпользователя для восстановления дампа pg_dumpall. Если вы используете табличные пространства, убедитесь, что пути к табличным пространствам в дампе соответствуют новой среде.
Утилита pg_dumpall добавляет в файл дампа команды для пересоздания ролей, табличных пространств и пустых баз данных, после чего вызывает pg_dump для каждой базы данных. Это означает, что хотя каждая база данных внутренне согласована, состояние разных баз данных не синхронизируются.
Чтобы выгрузить только глобальные данные кластера, используйте опцию pg_dumpall --globals-only.
Резервное копирование больших баз данных
Операционные системы могут иметь ограничения на максимальный размер файла, это приводит к проблемам при создании больших выходных файлов pg_dump. Так как pg_dump пишет в стандартный вывод, можно использовать стандартные инструменты Unix, чтобы решить эту проблему.
Использование сжатых дамп-файлов
Вы можете использовать программу сжатия, например gzip:
$ pg_dump <имя_базы_данных> | gzip > <имя_файла.gz>Восстановить дамп можно следующим образом:
$ gunzip -c <имя_файла.gz> | psql <имя_базы_данных>или
$ cat <имя_файла.gz> | gunzip | psql <имя_базы_данных>Использование split
Команда split позволяет разделить вывод на файлы меньшего размера. В следующем примере файл разбивается на фрагменты по 2 ГБ:
$ pg_dump <имя_базы_данных> | split -b 2G - <имя_файла>Восстановить дамп можно следующим образом:
$ cat <имя_файла>* | psql <имя_базы_данных>Если вы используете GNU split, можно использовать его и gzip вместе:
$ pg_dump <имя_базы_данных> | split -b 2G --filter='gzip > $FILE.gz'Воспользуйтесь утилитой zcat для восстановления такой резервной копии.
Использование дамп-формата custom
Поскольку ADPG включает в себя библиотеку zlib, формат custom утилиты pg_dump сжимает данные. В этом формате размер файла дампа близок к размеру, полученному с помощью gzip, но позволяет выборочно восстанавливать таблицы. Следующая команда выгружает базу данных в формате custom:
$ pg_dump -Fc <имя_базы_данных> > <имя_файла>Дамп-файл формата custom не является скриптом, для его восстановления используется утилита pg_restore:
$ pg_restore -d <имя_базы_данных> <имя_файла>Для очень больших баз данных вам может понадобиться объединить использование утилиты split с форматом custom или сжатыми дамп-файлами.
Использование параллельной выгрузки в pg_dump
Чтобы ускорить выгрузку большой базы данных, вы можете использовать режим параллельной выгрузки pg_dump. При этом одновременно будут выгружаться несколько таблиц. Управлять числом параллельных заданий позволяет параметр -j. Параллельная выгрузка поддерживается только для формата directory.
$ pg_dump -j <число> -F d -f <выходной_каталог> <имя_базы_данных>Вы также можете использовать утилиту pg_restore с опцией -j, чтобы восстановить дамп в параллельном режиме. Это поддерживается для любого архива в формате directory или custom, даже если архив создавался не командой pg_dump -j.