Перейти к основному содержимому

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

Вы можете перенести таблицы из одной или нескольких баз данных PostgreSQL в облачную базу данных PostgreSQL с помощью облака для Trino. Такой способ подходит, если нужно перенести отдельные таблицы, отфильтровать или преобразовать данные при переносе.

Сервер с исходной базой данных и кластер PostgreSQL с целевой базой данных подключаются к облаку для Trino как два каталога с помощью коннектора PostgreSQL. Каталог PostgreSQL подключается к одной базе данных: если вы переносите несколько баз данных, нужно добавить по паре каталогов для каждой. Trino читает данные из исходной базы данных и записывает их в целевую в одном SQL-запросе, без промежуточных файлов и выгрузок.

При переносе данных с помощью Trino действуют ограничения.

Ограничения

Гарантия переноса данных

Облако для Trino находится на стадии публичного тестирования (public preview):

  • продукт предоставляется без SLA;
  • доступность сервисов не гарантируется;
  • возможны сбои в работе продукта.

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

Объекты базы данных

Запрос CREATE TABLE ... AS SELECT переносит только данные таблиц: таблица создается без первичных и внешних ключей, индексов, ограничений, значений по умолчанию, последовательностей, триггеров и функций. Создайте их после переноса стандартными запросами PostgreSQL через процедуру system.execute — например, запросом CALL <target_catalog>.system.execute(query => 'ALTER TABLE <schema>.<table_name> ADD PRIMARY KEY (<column>)'). Также можно заранее создать структуру таблиц в целевой базе данных из вывода pg_dump --schema-only и переносить данные запросом INSERT INTO ... SELECT вместо CREATE TABLE ... AS SELECT.

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

Типы данных

Типы столбцов в целевой базе данных могут отличаться от исходных: например, json записывается как jsonb. Массивы и неподдерживаемые типы данных по умолчанию пропускаются, но вы можете перенести их, подробнее в подразделе Перенести данные с помощью Trino. Подробнее о таких типах данных в разделе Type mapping документации Trino

Последовательности

Столбец типа serial переносится как integer без значения по умолчанию. Если приложение добавляет строки без id, создайте последовательность и установите ее значение больше максимального id

Представления

Запрос SHOW TABLES показывает и таблицы, и представления. Из представления запрос CREATE TABLE ... AS SELECT создаст обычную таблицу с данными на момент переноса

Согласованность данных

Каждая таблица читается в определенный момент времени. Если не остановить запись в исходной базе данных на время переноса, СУБД продолжит операции записи в таблицы, из-за чего часть данных окажется несогласованной. Остановите запись в исходной базе данных на время переноса данных или повторно перенесите изменившиеся данные перед переключением сервисов

Место на диске

При выполнении запроса INSERT Trino сначала записывает данные во временную таблицу, а затем переносит их в целевую. На время загрузки на диске целевого кластера должно быть достаточно места для объема добавляемых данных

Большие таблицы

Коннектор PostgreSQL читает каждую таблицу одним запросом, поэтому скорость переноса одной большой таблицы ограничена каналом до исходной базы данных и производительностью баз данных. Разбейте большую таблицу на части по диапазонам ключа и выполните несколько запросов INSERT INTO ... SELECT ... WHERE id BETWEEN ... параллельно

Перенос данных через интернет

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

Если вы хотите перенести данные из Trino в облачную базу данных PostgreSQL через приватную подсеть, целевой кластер PostgreSQL и кластер Trino должны находиться в одном пуле и одной приватной подсети

Перенести данные

  1. Подготовьте сервер с исходной базой данных PostgreSQL.
  2. Подготовьте целевой кластер PostgreSQL.
  3. Создайте кластер Trino с каталогами.
  4. Подключитесь к кластеру Trino.
  5. Проверьте каталоги.
  6. Перенесите данные с помощью Trino.
  7. Проверьте результат.
  8. Переключите сервисы.

1. Подготовить сервер с исходной базой данных PostgreSQL

Чтобы защитить данные от изменения в исходной базе данных, Trino будет подключаться к ней от имени отдельного пользователя с правами только на чтение.

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

  2. Создайте нового пользователя и выдайте ему права только на чтение с помощью привилегии SELECT:

    CREATE USER <user_name> WITH PASSWORD '<password>';
    GRANT CONNECT ON DATABASE <source_database> TO <user_name>;
    GRANT USAGE ON SCHEMA public TO <user_name>;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;

    Укажите:

    • <user_name> — имя пользователя;
    • <password> — пароль пользователя;
    • <source_database> — имя базы данных, из которой нужно перенести данные.

    Если вы переносите несколько схем, выдайте привилегии USAGE и SELECT на каждую схему.

  3. Разрешите подключения к исходной базе данных — добавьте в файл pg_hba.conf строку:

    host <source_database> <user_name> 0.0.0.0/0 <authentication_method>

    Укажите:

    • <source_database> — имя базы данных, из которой нужно перенести данные;
    • <user_name> — имя пользователя;
    • <authentication_method> — метод аутентификации, который используется на сервере, обычно scram-sha-256. Если на сервере используется md5, укажите его вместо scram-sha-256.

  4. Убедитесь, что PostgreSQL принимает подключения на внешнем интерфейсе: в файле postgresql.conf значение параметра listen_addresses должно содержать публичный IP-адрес сервера или *. Изменение параметра listen_addresses применяется только после перезапуска PostgreSQL.

  5. Примените изменения в файле pg_hba.conf:

    SELECT pg_reload_conf();

  6. Убедитесь, что сервер доступен из интернета по публичному IP-адресу или DNS-адресу.

  7. Если на сервере используется фильтрация трафика, разрешите входящие подключения на порт PostgreSQL (обычно — 5432) в файрволе или группах безопасности облачного провайдера.

2. Подготовить целевой кластер PostgreSQL

к сведению

Облако для Trino доступно только в пуле ru-7. Если вы хотите перенести данные из Trino в облачную базу данных PostgreSQL через приватную подсеть, создайте кластер PostgreSQL в пуле ru-7 и используйте одну подсеть для кластеров PostgreSQL и Trino.

Если кластеры PostgreSQL и Trino будут находиться в разных пулах, Trino сможет подключаться к PostgreSQL только через интернет по публичному плавающему IP-адресу.

  1. Создайте кластер PostgreSQL. При создании кластера:

    • если хотите перенести данные через приватную подсеть, выберите локацию — пул ru-7;

    • выберите версию PostgreSQL не ниже версии исходной базы данных;

    • выберите конфигурацию нод с размером диска, достаточным для объема переносимых данных.

  2. Создайте пользователя.

  3. Создайте базу данных.

  4. Выдайте пользователю доступ к созданной базе данных в качестве владельца. Владелец базы данных может создавать в ней схемы и таблицы — это нужно Trino для записи данных.

  5. Если хотите перенести данные через интернет, подключите публичный плавающий IP-адрес к кластеру.

3. Создать кластер Trino с каталогами

Каталог PostgreSQL подключается к одной базе данных: если вы переносите несколько баз данных, добавьте по паре каталогов для каждой.

Используйте инструкцию Создать кластер Trino. При создании кластера:

  • в блоке Сеть:

    • если целевой кластер PostgreSQL создан в пуле ru-7, выберите подсеть целевого кластера;

    • чтобы подключаться к Trino из интернета, отметьте чекбокс Внешний доступ к графическому интерфейсу;

  • в блоке Каталоги добавьте два каталога с типом коннектора PostgreSql для исходной и целевой баз данных. При добавлении каталогов используйте параметры подключения:

ПараметрКаталог сервера с исходной базой данныхКаталог целевого кластера
ХостПубличный IP-адрес или DNS-адрес сервера

  • если кластер PostgreSQL в пуле ru-7 — DNS-адрес мастер-ноды кластера, можно скопировать в панели управления: в верхнем меню нажмите Продукты → Облачные базы данных → вкладка Активные → страница кластера → вкладка Подключение → блок Адреса для подключения → строка ноды → в столбце DNS-адрес нажмите . Адрес привязан к роли мастер-ноды и не изменится, если мастером станет реплика;

  • если кластер PostgreSQL не в пуле ru-7 — публичный IP-адрес кластера, можно скопировать в панели управления: в верхнем меню нажмите Продукты → Облачные базы данных → вкладка Активные → страница кластера → вкладка Подключение → блок Адреса для подключения → строка ноды → в столбце IP-адрес нажмите рядом с публичным IP-адресом

ПортПорт исходной базы данных, обычно 5432

5432 — прямое подключение к PostgreSQL. Не используйте порт 5433 — он ведет через пулер соединений, а подготовленные выражения, которые использует JDBC-драйвер Trino, в режиме пулинга transaction могут приводить к ошибкам

База данныхИмя исходной базы данныхИмя базы данных, которую вы создали при подготовке целевого кластера PostgreSQL на шаге 3
Имя пользователяИмя пользователя, которого вы создали при подготовке сервера с исходной базой данных PostgreSQL на шаге 2Имя пользователя, которого вы создали при подготовке целевого кластера PostgreSQL на шаге 2
ПарольПароль пользователя, которого вы создали при подготовке сервера с исходной базой данных PostgreSQL на шаге 2Пароль пользователя, которого вы создали при подготовке целевого кластера PostgreSQL на шаге 2

4. Подключиться к кластеру Trino

Вы можете подключиться к кластеру через графический интерфейс Trino, консольный клиент Trino CLI и DBeaver.

  1. В панели управления в верхнем меню нажмите Продукты и выберите Trino.

  2. В карточке кластера в столбце GUI нажмите .

  3. Введите логин — admin.

  4. Введите пароль администратора — задается при создании кластера. После создания кластера пароль нельзя посмотреть в панели управления, но можно изменить.

  5. Нажмите Log In.

5. Проверить каталоги

Выведите список каталогов, схем и таблиц исходной базы данных и схем целевой базы данных:

SHOW CATALOGS;
SHOW SCHEMAS FROM <source_catalog>;
SHOW TABLES FROM <source_catalog>.public;
SHOW SCHEMAS FROM <target_catalog>;

Укажите:

  • <source_catalog> — имя каталога сервера с исходной базой данных;
  • <target_catalog> — имя каталога целевого кластера.

В списке каталогов должны быть оба созданных каталога, а в схеме <source_catalog>.public — таблицы исходной базы данных.

6. Перенести данные с помощью Trino

  1. Опционально: чтобы перенести столбцы-массивы и столбцы с неподдерживаемыми типами данных, настройте сопоставление типов данных. По умолчанию коннектор PostgreSQL пропускает такие столбцы — они не попадут в целевую таблицу:

    SET SESSION <source_catalog>.array_mapping = 'AS_ARRAY';
    SET SESSION <source_catalog>.unsupported_type_handling = 'CONVERT_TO_VARCHAR';

    Укажите <source_catalog> — имя каталога сервера с исходной базой данных.

    Значение AS_ARRAY переносит массивы фиксированной размерности, для массивов произвольной размерности используйте значение AS_JSON. Значение CONVERT_TO_VARCHAR переносит столбцы с неподдерживаемыми типами как текст. Подробнее в разделе Type mapping документации Trino.

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

    CREATE SCHEMA IF NOT EXISTS <target_catalog>.<schema>;

    Укажите:

    • <target_catalog> — имя каталога целевого кластера;
    • <schema> — имя схемы.

  3. Перенесите таблицу:

    CREATE TABLE <target_catalog>.public.<target_table> AS
    SELECT * FROM <source_catalog>.public.<source_table>;

    Укажите:

    • <target_catalog> — имя каталога целевого кластера;
    • <target_table> — имя целевой таблицы;
    • <source_catalog> — имя каталога сервера с исходной базой данных;
    • <source_table> — имя исходной таблицы.

    Чтобы перенести часть данных или изменить их при переносе, добавьте в запрос фильтры и выражения — например, условие WHERE по дате или функцию lower(), которая приведет значения столбца к нижнему регистру.

    В выводе появится количество записанных строк.

  4. Опционально: догрузите строки, которые появились в исходной базе данных после переноса:

    INSERT INTO <target_catalog>.public.<target_table>
    SELECT * FROM <source_catalog>.public.<source_table>
    WHERE <column> > (SELECT coalesce(max(<column>), 0) FROM <target_catalog>.public.<target_table>);

    Укажите:

    • <target_catalog> — имя каталога целевого кластера;
    • <target_table> — имя целевой таблицы;
    • <source_catalog> — имя каталога сервера с исходной базой данных;
    • <source_table> — имя исходной таблицы;
    • <column> — столбец с возрастающим идентификатором строк.

    Запрос подходит для таблиц, в которые строки добавляются только с возрастающим идентификатором. Изменения и удаления строк в исходной базе данных таким образом не перенести нельзя.

  5. Повторите шаги 1–4 для каждой таблицы.

7. Проверить результат

  1. Сравните количество строк в исходной и целевой таблицах:

    SELECT
      (SELECT count(*) FROM <source_catalog>.public.<source_table>) AS source_rows,
      (SELECT count(*) FROM <target_catalog>.public.<target_table>) AS target_rows;

    Укажите:

    • <source_catalog> — имя каталога сервера с исходной базой данных;
    • <source_table> — имя исходной таблицы;
    • <target_catalog> — имя каталога целевого кластера;
    • <target_table> — имя целевой таблицы.

  2. Опционально: найдите строки исходной таблицы, которых нет в целевой:

    SELECT * FROM <source_catalog>.public.<source_table>
    EXCEPT
    SELECT * FROM <target_catalog>.public.<target_table>;

    Укажите:

    • <source_catalog> — имя каталога сервера с исходной базой данных;
    • <source_table> — имя исходной таблицы;
    • <target_catalog> — имя каталога целевого кластера;
    • <target_table> — имя целевой таблицы.

    Пустой вывод означает, что все строки перенесены. На больших таблицах запрос создает значительную нагрузку на обе базы данных. Столбцы типа json в EXCEPT сравнить нельзя — перечислите столбцы явно или ограничьтесь сравнением количества строк.

  3. Выведите список столбцов целевой таблицы:

    SHOW COLUMNS FROM <target_catalog>.public.<target_table>;

    Укажите:

    • <target_catalog> — имя каталога целевого кластера;
    • <target_table> — имя целевой таблицы.

  4. Сверьте столбцы целевой таблицы, которые вы получили на шаге 3, со структурой исходной таблицы — например, с выводом команды \d <source_table> в psql, где <source_table> — имя исходной таблицы. Столбцы с типами данных, которые Trino пропускает, не видны в каталоге — сверьте их со структурой исходной таблицы напрямую.

8. Переключить сервисы

  1. Переключите сервисы на целевой кластер — подробнее в инструкции Подключиться к кластеру PostgreSQL. Для доступа к кластеру из интернета подключите публичный плавающий IP-адрес.

  2. Если в целевом кластере PostgreSQL вы подключали публичный плавающий IP-адрес и он больше не нужен, отключите его.

  3. Если кластер Trino больше не нужен, удалите его.

  4. Если сервер с исходной базой данных MySQL больше не нужен, удалите его.