Поделиться через

Перенос базы данных PostgreSQL с помощью дампа и ее восстановление

  • Статья

ОБЛАСТЬ ПРИМЕНЕНИЯ: База данных Azure для PostgreSQL — гибкий сервер

Базу данных PostgreSQL можно извлечь в файл дампа с помощью pg_dump. Метод восстановления базы данных зависит от формата выбранного дампа. Если дамп принимается с обычным форматом (который по умолчанию -Fpявляется стандартным, поэтому не требуется указывать конкретный параметр), то единственным вариантом восстановления является psql, так как он выводит обычный текстовый файл. Для других трех методов дампа: настраиваемых, каталогов и tar pg_restore следует использовать.

Внимание

Инструкции и команды, приведенные в этой статье, предназначены для выполнения в терминалах Bash. К ним относятся такие среды, как подсистема Windows для Linux (WSL), Azure Cloud Shell и другие интерфейсы, совместимые с bash. Убедитесь, что вы используете терминал bash для выполнения действий и выполнения команд, описанных в этом руководстве. Использование другого типа среды терминала или оболочки может привести к различиям в поведении команд и не может привести к ожидаемым результатам.

В этой статье мы рассмотрим обычные (по умолчанию) и форматы каталогов. Формат каталога полезен, так как он позволяет использовать несколько ядер для обработки, что может значительно повысить эффективность, особенно для больших баз данных.

Портал Azure упрощает этот процесс с помощью колонки Connect, предлагая предварительно настроенные команды, адаптированные к серверу, с значениями, замененными пользовательскими данными. Важно отметить, что колонка Connect доступна только для База данных Azure для PostgreSQL — гибкого сервера, а не для одного сервера. Вот как можно использовать эту функцию:

  1. Доступ портал Azure. Сначала перейдите к портал Azure и выберите колонку "Подключиться".

    Снимок экрана: размещение колонки Connect в портал Azure.

  2. Выберите базу данных: в колонке "Подключение" вы найдете раскрывающийся список баз данных. Выберите базу данных, из которой вы хотите выполнить дамп.

    Снимок экрана: раскрывающийся список, в котором можно выбрать конкретную базу данных.

  3. Выберите подходящий метод: в зависимости от размера базы данных можно выбрать один из двух методов:

    • pg_dump & psql — использование единственного текстового файла: идеально подходит для небольших баз данных, этот параметр использует один текстовый файл для процесса дампа и восстановления.
    • pg_dump & pg_restore — использование нескольких ядер: для больших баз данных этот метод эффективнее, так как он использует несколько ядер для обработки процесса дампа и восстановления.

    Снимок экрана: два возможных метода дампа.

  4. Команды копирования и вставки: портал предоставляет готовые к использованию pg_dump и psql pg_restore команды. Эти команды поставляются со значениями, уже замененными в соответствии с выбранным сервером и базой данных. Скопируйте и вставьте эти команды.

Необходимые компоненты

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

Примечание.

psqlТак как pg_dumpвсе pg_restore pg_dumpall служебные программы зависят от libpq, вы можете использовать любые поддерживаемые переменные среды, которые он предлагает, или использовать файл пароля, чтобы избежать запроса пароля при каждом выполнении любой из этих команд.

Прежде чем приступить к выполнению этого руководства, необходимы следующие компоненты:

  • Сервер базы данных Azure для PostgreSQL с правилами брандмауэра, разрешающими доступ к этом серверу.
  • pg_dump, psql, pg_restore и pg_dumpall в случае, если вы хотите перенести роли и разрешения, установленные служебные программы командной строки.
  • Определите расположение дампа: выберите место, из которого вы хотите выполнить дамп. Это можно сделать из разных расположений, таких как отдельная виртуальная машина, облачная оболочка (где служебные программы командной строки уже установлены, но не могут находиться в соответствующей версии, поэтому всегда проверяйте версию с помощью, например, psql --versionили собственный ноутбук. Всегда помните расстояние и задержку между сервером PostgreSQL и расположением, из которого выполняется дамп или восстановление.

Внимание

Важно использовать pg_dumppsqlpg_restore pg_dumpall служебные программы, которые имеют одну или ту же основную версию или более высокую основную версию, чем сервер базы данных, из которых экспортируются или импортируются данные. Это может привести к неудачной миграции данных. Если целевой сервер имеет более высокую основную версию, чем исходный сервер, используйте служебные программы, которые совпадают с той же основной версией или выше, чем целевой сервер.

Примечание.

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

Дампа пользователей и ролей с помощью pg_dumpall -r

pg_dump используется для извлечения базы данных PostgreSQL в файл дампа. Однако важно понимать, что pg_dump не дамп ролей или определений пользователей, так как они считаются глобальными объектами в среде PostgreSQL. Для комплексной миграции, включая пользователей и ролей, необходимо использовать pg_dumpall -r. Эта команда позволяет записывать все сведения о роли и пользователя из среды PostgreSQL. Если вы переносите базы данных на том же сервере, вы можете пропустить этот шаг и перейти к разделу "Создать новую базу данных ".

pg_dumpall -r -h <server name> -U <user name> > roles.sql

Например, если у вас есть сервер с именем и пользователь с именем mydemoserver myuser выполните следующую команду:

pg_dumpall -r -h mydemoserver.postgres.database.azure.com -U myuser > roles.sql

Если вы используете отдельный сервер, ваше имя пользователя включает компонент имени сервера. Поэтому вместо myuserэтого используйте myuser@mydemoserver.

Дампа ролей с гибкого сервера

В среде гибкого сервера расширенные меры безопасности означают, что у пользователей нет доступа к таблице pg_authid, где хранятся пароли ролей. Это ограничение влияет на выполнение дампа ролей, так как стандартная pg_dumpall -r команда пытается получить доступ к этой таблице для паролей и завершиться ошибкой из-за отсутствия разрешений.

При дампах ролей с гибкого сервера важно включить --no-role-passwords этот параметр в pg_dumpall команду. Этот параметр предотвращает pg_dumpall попытку доступа pg_authid к таблице, которую она не может прочитать из-за ограничений безопасности.

Чтобы успешно дампать роли с гибкого сервера, используйте следующую команду:

pg_dumpall -r --no-role-passwords -h <server name> -U <user name> > roles.sql

Например, если у вас есть сервер с именем mydemoservermyuserпользователя, выполните следующую команду:

pg_dumpall -r --no-role-passwords -h mydemoserver.postgres.database.azure.com -U myuser > roles.sql

Очистка дампа ролей

При переносе выходного файла roles.sql могут включать определенные роли и атрибуты, которые не применимы или допустимы в новой среде. Вот что вам нужно рассмотреть:

  • Удаление атрибутов, которые могут быть заданы только суперпользователями: при миграции в среду, в которой у вас нет привилегий суперпользователя, удалите атрибуты, такие как NOSUPERUSER и NOBYPASSRLS из дампа ролей.

  • Исключение пользователей, относящихся к службе: исключение пользователей службы с одним сервером, например azure_superuser или azure_pg_admin. Они относятся к службе и будут созданы автоматически в новой среде.

Используйте следующую sed команду для очистки дампа ролей:

sed -i '/azure_superuser/d; /azure_pg_admin/d; /azuresu/d; /^CREATE ROLE replication/d; /^ALTER ROLE replication/d; /^ALTER ROLE/ {s/NOSUPERUSER//; s/NOBYPASSRLS//;}' roles.sql

Эта команда удаляет строки со строкамиazure_superuser, azure_pg_adminazuresuначинающимися с CREATE ROLE replication иALTER ROLE replication, и удаляет NOSUPERUSER NOBYPASSRLS атрибуты из ALTER ROLE инструкций.

Создание файла дампа, содержащего необходимые для загрузки данные

Чтобы экспортировать существующую базу данных PostgreSQL в локальную или виртуальную машину в файл скрипта SQL, выполните следующую команду в существующей среде:

pg_dump <database name> -h <server name> -U <user name> > <database name>_dump.sql

Например, если у вас есть сервер с именем , пользователь с именем mydemoservermyuser и вызываемая testdbбаза данных, выполните следующую команду:

pg_dump testdb -h mydemoserver.postgres.database.azure.com -U myuser > testdb_dump.sql

Если вы используете отдельный сервер, ваше имя пользователя включает компонент имени сервера. Поэтому вместо myuserэтого используйте myuser@mydemoserver.

Восстановление данных в целевую базу данных

Восстановление ролей и пользователей

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

Чтобы восстановить роли и пользователей в целевой базе данных, используйте следующую команду:

psql -f roles.sql -h <server_name> -U <user_name>

Замените <server_name> именем целевого сервера и <user_name> именем пользователя. Эта команда использует psql программу для выполнения команд SQL, содержащихся в roles.sql файле, эффективно восстанавливая роли и пользователей в целевой базе данных.

Например, если у вас есть сервер с именем mydemoservermyuserпользователя, выполните следующую команду:

psql -f roles.sql -h mydemoserver.postgres.database.azure.com -U myuser

Если вы используете отдельный сервер, ваше имя пользователя включает компонент имени сервера. Поэтому вместо myuserэтого используйте myuser@mydemoserver.

Примечание.

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

Создание базы данных

Перед восстановлением базы данных может потребоваться создать новую пустую базу данных. Для этого пользователь, который используется, должен иметь CREATEDB разрешение. Ниже приведены два часто используемых метода:

  1. С помощью createdb служебной программы программа createdb позволяет создавать базы данных непосредственно из командной строки Bash без необходимости войти в PostgreSQL или оставить среду операционной системы. Например:

    createdb <new database name> -h <server name> -U <user name>

    Например, если у вас есть сервер с именемmydemoservermyuser, имя пользователя и новая база данных, которую вы хотите создатьtestdb_copy, выполните следующую команду:

    createdb testdb_copy -h mydemoserver.postgres.database.azure.com -U myuser

    Если вы используете отдельный сервер, ваше имя пользователя включает компонент имени сервера. Поэтому вместо myuserэтого используйте myuser@mydemoserver.

  2. Чтобы создать базу данных с помощью команды SQL, необходимо подключиться к серверу PostgreSQL с помощью интерфейса командной строки или средства управления базами данных. После подключения можно использовать следующую команду SQL для создания новой базы данных:

CREATE DATABASE <new database name>;

Замените <new database name> именем, которое вы хотите предоставить новой базе данных. Например, чтобы создать базу данных с именем testdb_copy, команда будет:

CREATE DATABASE testdb_copy;

Восстановление дампа

После создания целевой базы данных можно восстановить данные в эту базу данных из файла дампа. Во время восстановления внесите все ошибки errors.log в файл и проверьте его содержимое на наличие ошибок после завершения восстановления.

psql -f <database name>_dump.sql <new database name> -h <server name> -U <user name> 2> errors.log

Например, если у вас есть сервер с именем , пользователь с именем mydemoservermyuser и новая база данныхtestdb_copy, выполните следующую команду:

psql -f testdb_dump.sql testdb_copy -h mydemoserver.postgres.database.azure.com -U myuser 2> errors.log

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

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

Оптимизация процесса миграции

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

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

Следующие шаги