Как перенести данные из базы на другой инстанс

В сервисе DBaaS Postgres не предусмотрена возможность вручную создавать копии БД через pg_basebackup. Вместо этого, чтобы перенести базу данных или отдельные таблицы на другой инстанс вы можете использовать утилиту pg_dump. В этом разделе приведены примеры сценариев такого переноса.

В отличие от pg_basebackup, описанные способы копирования не зависят от версии PostgreSQL на инстансах.

Перед началом работы

1. Для работы вам понадобятся следующие утилиты для работы с Postgres:

   • Утилита для резервного копирования pg_dump
   • Утилита для восстановления базы pg_restore
   • Консольный клиент psql

   Эти утилиты входит в состав пакетов для управления PostgreSQL, например postgres или libpq. Установите один из этих пакетов на рабочую машину, с которой будет выполняться подключение к инстансам, любым подходящим способом.

   Альтернативно вы можете использовать Docker-образ postgres. В этом случае предварительно настройте Docker и добавьте строчку docker run -it --rm postgres ко всем приведенным здесь примерам.

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

3. Если вы хотите сначала протестировать перенос, воспользуйтесь демонстрационной базой данных.

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

   Ключи командной строки

   • -v, --verbose — включить подробный режим. При этом pg_dump будет выводить в стандартный поток ошибок подробные комментарии к объектам, включая время начала и окончания выгрузки, а также сообщения о прогрессе выполнения.
   • -O, --no-owner — не устанавливать владельца объектов базы данных. Создаваемый скрипт может быть выполнен любым пользователем при восстановлении базы. Пользователь, запустивший восстановление будет назначен владельцем восстановленной базы.
   • -Fc, --format=custom — выгрузить данные в специальном архивном формате, пригодном для дальнейшего использования утилитой pg_restore. Наряду с форматом directory является наиболее гибким форматом, позволяющим вручную выбирать и сортировать восстанавливаемые объекты. Вывод в этом формате по умолчанию сжимается.
   • -Fd, --format=directory — выгрузить данные в формате каталога. Этот формат пригоден для дальнейшего использования утилитой pg_restore. При этом будет создан каталог, в котором для каждой таблицы и большого объекта будут созданы отдельные файлы, а также файл оглавления в машинно-читаемом формате, понятном для pg_restore. Этот формат по умолчанию сжимается, а также поддерживает работу в несколько потоков.
   • -h, --host — адрес хоста Postgres.
   • -U, --username — пользователь, от имени которого происходит подключение.
   • -d, --dbname — имя базы данных.
   • -t, --table — схема и имя таблицы. При использовании -t, pg_dump не выгружает прочие объекты, от которых выгружаемые таблицы могут зависеть. Таким образом не гарантируется, что выгруженные таблицы будут успешно восстановлены в чистой базе данных.

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

Примечание

Приведенные примеры не сработают в PowerShell!

В большинстве примеров ниже применяется разделитель потока или перенаправление (| или >) в командной строке. PowerShell не оперирует этими командами как cmd или bash, а направляет потоки байтов без преобразований. Если в качестве промежуточного формата хранения необходимо использовать текстовый формат, укажите файл вывода для pg_dump, например pg_dump ... -Fp -f out.sql.

Как выгрузить БД в SQL-формат и загрузить ее в БД на другом инстансе

Важно

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

1. Скопируйте БД из источника в SQL-формат утилитой pg_dump:

   pg_dump -v -O --exclude-schema "pgaas_utility" --exclude-schema "dwh_ods_grant_manage" --exclude-table "pgaas_utility.debezium_heartbeat" -h <адрес хоста-источника> -U <имя пользователя> -d <имя исходной БД> > source_dump.sql

   Введите пароль по запросу. Указанная БД будет скопирована в файл source_dump.sql на рабочей машине. Эту копию можно редактировать, например изменить имена объектов через текстовый редактор.

2. Восстановите БД из SQL-формата на приемнике утилитой psql:

   psql -h <адрес хоста-приемника> -U <имя пользователя> -d <имя БД на приемнике> < source_dump.sql

Как скопировать БД с одного инстанса на другой без создания файла резервной копии

Выполните команду:

pg_dump -v -O --exclude-schema "pgaas_utility" --exclude-schema "dwh_ods_grant_manage" -h <адрес хоста-источника> -U <имя пользователя на источнике> -d <имя БД на источнике> | psql -h <адрес хоста-приемника> -U <имя пользователя на приемнике> -d <имя БД на приемнике>

Как выгрузить отдельную таблицу в SQL-формат и загрузить ее в БД на другом инстансе

1. Скопируйте таблицу из источника в SQL-формат утилитой pg_dump:

   pg_dump -v -O --exclude-schema "pgaas_utility" --exclude-schema "dwh_ods_grant_manage" -h <адрес хоста-источника> -U <имя пользователя> -d <имя исходной БД> -t <схема.таблица> > source_dump2.sql

   Введите пароль по запросу. Указанная таблица будет скопирована в файл source_dump2.sql на рабочей машине. Эту копию можно редактировать, например изменить имена объектов через текстовый редактор.

2. Восстановите таблицу из SQL-формата в базу на приемнике утилитой psql:

   psql -h <адрес хоста-приемника> -U <имя пользователя> -d <имя БД на приемнике> < source_dump2.sql

Как выгрузить БД в custom-формат и загрузить ее в БД на другом инстансе без создания файла резервной копии

pg_dump -O -v -Fc --exclude-schema "pgaas_utility" --exclude-schema "dwh_ods_grant_manage" --exclude-table "pgaas_utility.debezium_heartbeat" -h <адрес хоста-источника> -U <имя пользователя> -d <имя исходной БД> | pg_restore -v -h <адрес хоста-приемника> -U <имя пользователя> -d <имя БД на приемнике>

Данная команда два раза запросит пароль, в первый раз введите пароль от УЗ хоста-источника. Во второй раз введите пароль от УЗ хоста-приемника.

Как выгрузить БД в custom-формат и загрузить ее в БД на другом инстансе в многопоточном режиме

Важно

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

1. Скопируйте БД из источника в custom-формат утилитой pg_dump:

   pg_dump -v -Fc --exclude-schema "pgaas_utility" --exclude-schema "dwh_ods_grant_manage" -h <адрес хоста-источника> -U <имя пользователя> -d <имя исходной БД> > source_dump.dump

   Указанная БД будет сжата и скопирована в файл source_dump.dump на рабочей машине. Отредактировать такой файл не получится, но на этапе восстановления вы можете вручную выбирать и сортировать восстанавливаемые объекты.

2. Восстановите БД из custom-формата на приемнике утилитой pg_restore в 2 потока:

   pg_restore -O -j 2 -h <адрес хоста-приемника> -U <имя пользователя> -d <имя БД на приемнике> source_dump.dump

Вы можете восстановить не всю БД, а только отдельные таблицы, используя ту же копию:

pg_restore -O -j 2 -h <адрес хоста-приемника> -U <имя пользователя> -d <имя БД на приемнике> -t <схема.таблица> source_dump.dump

Примечание

Если на переносимых таблицах установлены триггеры, добавьте к командам копирования и восстановления ключ --disable-triggers, чтобы отключить триггеры при переносе.