SQL-дамп

PostgreSQL включает утилиту pg_dump, создающую дамп-файлы. Утилита pg_dump не требует блокировки доступа других пользователей и может создавать SQL-дампы во время использования базы данных. Базовый синтаксис 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:

В следующем примере создается резервная копия таблицы books базы данных books_store:

Чтобы указать, к какому серверу базы данных должен обращаться 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 предназначены для переноса баз данных между различными архитектурами. За подробностями обратитесь к статье pg_dump.

Для восстановления данных из дамп-файлов используется утилита psql. Базовый синтаксис команды для восстановления дампа:

Где:

Приведенная выше команда не создает указанную базу данных. Необходимо создать её из шаблона template0 перед выполнением команды.

Утилита psql продолжает выполнять скрипт после возникновения SQL-ошибок. Вы можете использовать переменную ON_ERROR_STOP, чтобы изменить это поведение и обеспечить выход из psql со статусом 3 в случае ошибок.

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

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

Дампы, выгруженные в архивном формате, восстанавливаются утилитой pg_restore.

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

Где имя_файла — это имя дамп-файла, который будет создан.

Базу данных из полученного дамп можно восстановить с помощью psql:

Опция -f означает, что psql читает команды из указанного файла dumpfile. postgres — имя базы данных. Вы можете указать имя любой существующей базы данных вместо postgres, но если вы восстанавливаете резервную копию, содержащую несколько баз данных, в пустой кластер, следует использовать postgres. Также необходимо иметь права суперпользователя для восстановления дампа pg_dumpall. Если вы используете табличные пространства, убедитесь, что пути к табличным пространствам в дампе соответствуют новой среде.

Утилита pg_dumpall добавляет в файл дампа команды для пересоздания ролей, табличных пространств и пустых баз данных, после чего вызывает pg_dump для каждой базы данных. Это означает, что хотя каждая база данных внутренне согласована, состояние разных баз данных не синхронизируются.

Чтобы выгрузить только глобальные данные кластера, используйте опцию pg_dumpall --globals-only.

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