Передача данных в эндпоинт-приемник PostgreSQL

С помощью сервиса Yandex Data Transfer вы можете переносить данные в базу PostgreSQL и реализовывать различные сценарии переноса, обработки и трансформации данных. Для реализации трансфера:

1. Ознакомьтесь с возможными сценариями передачи данных.
2. Настройте один из поддерживаемых источников данных.
3. Подготовьте базу данных PostgreSQL к трансферу.
4. Настройте эндпоинт-приемник в Yandex Data Transfer.
5. Создайте и запустите трансфер.
6. Выполняйте необходимые действия по работе с базой и контролируйте трансфер.
7. При возникновении проблем, воспользуйтесь готовыми решениями по их устранению.

Сценарии передачи данных в PostgreSQLСценарии передачи данных в PostgreSQL

1. Миграция — перенос данных из одного хранилища в другое. Часто это перенос базы из устаревших локальных баз в управляемые облачные.

   • Миграция кластера PostgreSQL;
   • Миграция из AWS RDS for PostgreSQL;
   • Миграция со сменой хранилища: MySQL® в PostgreSQL.

2. Поставка данных — процесс доставки произвольных данных в целевые хранилища. Процесс поставки включает извлечение данных из очереди и их десериализацию с последующей трансформацией данных в формат целевого хранилища.

   • Поставка данных из Apache Kafka® в PostgreSQL.

3. Загрузка данных в витрины — процесс трансфера подготовленных данных в хранилища с целью последующей визуализации.

   • Загрузка данных из Greenplum® в PostgreSQL;
   • Загрузка данных из Object Storage в PostgreSQL.

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

Настройка источника данныхНастройка источника данных

Настройте один из поддерживаемых источников данных:

• PostgreSQL;
• MySQL®;
• Greenplum®;
• Apache Kafka®;
• Airbyte®;
• YDS;
• Yandex Object Storage;
• Oracle.

Полный список поддерживаемых источников и приемников в Yandex Data Transfer см. в разделе Доступные трансферы.

Подготовка базы данных приемникаПодготовка базы данных приемника

Данные, хранящиеся в MATERIALIZED VIEW, не переносятся. Для переноса данных из MATERIALIZED VIEW создайте обыкновенный VIEW, ссылающийся на переносимый MATERIALIZED VIEW.

Если определение переносимого VIEW содержит вызов VOLATILE функции, то трансфер читает данные из такого VIEW с уровнем изоляции READ UNCOMMITTED. Консистентность между данными в этом VIEW и данными других переносимых объектов не гарантируется. Чтение из MATERIALIZED VIEW в определении VIEW равносильно вызову VOLATILE функции.

Настройка эндпоинта-приемника PostgreSQLНастройка эндпоинта-приемника PostgreSQL

При создании или изменении эндпоинта вы можете задать:

• Настройки подключения к кластеру Yandex Managed Service for PostgreSQL или пользовательской инсталляции, в т. ч. на базе виртуальных машин Yandex Compute Cloud. Эти параметры обязательные.
• Дополнительные параметры.

Кластер Managed Service for PostgreSQLКластер Managed Service for PostgreSQL

Подключение к БД с указанием идентификатора кластера в Yandex Cloud.

resource "yandex_datatransfer_endpoint" "<имя_эндпоинта_в_Terraform>" {
  name = "<имя_эндпоинта>"
  settings {
    postgres_target {
      security_groups = ["<список_идентификаторов_групп_безопасности>"]
      connection {
        mdb_cluster_id = "<идентификатор_кластера>"
      }
      database = "<имя_переносимой_базы_данных>"
      user     = "<имя_пользователя_для_подключения>"
      password {
        raw = "<пароль_пользователя>"
      }
    }
  }
}

Пользовательская инсталляцияПользовательская инсталляция

Для случая OnPremise все поля заполняются вручную.

resource "yandex_datatransfer_endpoint" "<имя_эндпоинта_в_Terraform>" {
  name = "<имя_эндпоинта>"
  settings {
    postgres_target {
      security_groups = ["<список_идентификаторов_групп_безопасности>"]
      connection {
        on_premise {
          hosts = ["<список_хостов>"]
          port  = <порт_для_подключения>
        }
      }
      database = "<имя_переносимой_базы_данных>"
      user     = "<имя_пользователя_для_подключения>"
      password {
        raw = "<пароль_пользователя>"
      }
    }
  }
}

Дополнительные настройкиДополнительные настройки

После настройки источника и приемника данных создайте и запустите трансфер.

Действия с базой данных во время трансфераДействия с базой данных во время трансфера

Для трансферов типа Копирование и Копирование и репликация:

• в статусе Копируется запрещено изменять схему данных на источнике и приемнике;

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

  Например, пусть в таблицу test_table источника добавили новый столбец:

  ALTER TABLE test_table ADD COLUMN val2 TEXT;
  

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

  ALTER TABLE test_table ADD COLUMN val2 TEXT;
  

  После этого трансфер сможет продолжить работу.

Решение проблем, возникающих при переносе данныхРешение проблем, возникающих при переносе данных

Известные проблемы, связанные с использованием эндпоинта PostgreSQL:

• Остановка сессии мастер-транзакции трансфера
• Превышение квоты на длительность соединения
• Ошибка трансфера при переносе представлений (VIEW)
• Ошибка добавления записи в таблицу по constraint
• Ошибка при переносе всех таблиц схемы
• Невозможно создать объекты с участием функций расширения
• Низкая скорость трансфера
• Не переносятся таблицы-наследники
• Не хватает слотов репликации в базе данных источника
• Перестали переноситься данные после изменения эндпоинта-источника
• Ошибка трансфера при смене хоста-мастера
• Ошибка при трансфере вложенных транзакций
• Ошибка трансфера таблиц с отложенными ограничениями
• Не удается создать слот репликации на стадии активации
• Чрезмерное увеличение журнала WAL
• Ошибка при репликации из внешнего источника
• Ошибка трансфера при переносе таблиц без первичных ключей
• Повторяющееся значение ключа нарушает уникальное ограничение
• Ошибка удаления таблицы при политике очистки Drop
• Ошибка при переносе таблиц с генерируемыми столбцами

См. полный список рекомендаций в разделе Решение проблем.

Остановка сессии мастер-транзакции трансфераОстановка сессии мастер-транзакции трансфера

Текст ошибки:

Cannot set transaction snapshot:
ERROR: invalid snapshot identifier: "<идентификатор_снапшота>" (SQLSTATE 22023).

Возможные причины:

• На источнике работает cron-задание или другое приложение, которое периодически завершает слишком длительные сессии.
• Кто-то вручную завершил мастер-транзакцию.
• Ресурсов CPU на источнике не хватает для выполнения запроса.
• В настройке кластера PostgreSQL Session duration timeout установлено ограничение на время жизни активной сессии.

Решение: отключите такое cron-задание, добавьте дополнительные ресурсы для CPU на источник, а также установите значение параметра Session duration timeout равным 0 на время трансфера. После внесения изменений активируйте трансфер повторно.

Превышение квоты на длительность соединенияПревышение квоты на длительность соединения

В Yandex Managed Service for PostgreSQL существует квота на длительность соединения — 12 часов.
​
​Решение: если перенос базы данных требует больше времени, измените настройку кластера Yandex Managed Service for PostgreSQL Session duration timeout.

Ошибка трансфера при переносе представлений (VIEW)Ошибка трансфера при переносе представлений (VIEW)

Текст ошибки:

[ERROR] "... _view": no key columns found

Репликация новых данных из представлений невозможна. При трансфере PostgreSQL — PostgreSQL переносятся только те представления, которые указаны в параметре эндпоинта-источника Фильтр таблиц → Список включенных таблиц.

Решение: вручную исключите из трансфера все представления, записав их в параметре эндпоинта-источника Фильтр таблиц → Список исключённых таблиц, после чего активируйте трансфер повторно.

Ошибка добавления записи в таблицу по constraintОшибка добавления записи в таблицу по constraint

Решение: подготовьте источник в соответствии с разделом Подготовка к трансферу.

Ошибка при переносе всех таблиц схемыОшибка при переносе всех таблиц схемы

Текст ошибки:

Unable to apply DDL of type 'TABLE', name '<схема>'.'<таблица>', error:
ERROR: schema "<схема>" does not exist (SQLSTATE 3F000)

Трансфер прерывается при указании списка таблиц определенной схемы в виде <схема>.*. Это происходит из-за особенностей работы утилиты pg_dump, которая используется для переноса схемы. При указании таблиц всей схемы <схема>.* в параметре эндпоинта-источника Фильтр таблиц → Список включенных таблиц типы PostgreSQL из этой схемы не извлекаются, даже если в схеме есть таблицы, зависящие от этих типов.

Решение: создайте типы PostgreSQL в базе-приемнике вручную.

Невозможно создать объекты с участием функций расширенияНевозможно создать объекты с участием функций расширения

Текст ошибки:

Unable to apply DDL of type 'TABLE', <имя_объекта>, error:
failed to push non-row item 0 of kind "pg:DDL" in batch:
Push failed: ERROR: function <имя_схемы>.<имя_функции>() does not exist 
(SQLSTATE 42883)

В Managed Service for PostgreSQL в базе-приемнике невозможно установить расширение в пользовательскую схему. Поэтому трансфер прерывается, если в пользовательской инсталляции Managed Service for PostgreSQL есть расширения, установленные в пользовательскую схему, и эти расширения используются в DDL переносимых объектов.

Решение: проверьте DDL объектов, имена которых указаны в ошибке. Если в этих объектах есть вызов функций из пользовательской схемы, вручную создайте в приемнике DDL, которые вызывают функции без указания схемы. В политике очистки эндпоинта-приемника установите значение Truncate, чтобы трансфер не удалил эти объекты.

Низкая скорость трансфера Низкая скорость трансфера

​Может возникать у трансферов типа Копирование или Копирование и репликация из PostgreSQL в PostgreSQL.

Возможные причины:

• Протокол записи.

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

  Решение: установите в эндпоинте-приемнике тип политики очистки Drop и исключите другие пишущие процессы.

• Параллельность чтения таблиц.

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

  Решение: настройте параллельное копирование и активируйте трансфер повторно.

Не переносятся таблицы-наследникиНе переносятся таблицы-наследники

Во время трансфера не переносятся таблицы-наследники, либо переносятся без данных, если таблица партицированная.

Решение: установите следующие параметры эндпоинта-источника:

1. Включите опцию Объединить наследуемые таблицы в расширенных настройках.
2. Укажите в поле Список включенных таблиц все таблицы-наследники, данные которых нужно перенести.
3. Убедитесь, что у пользователя есть доступ к таблицам-наследникам.

Для увеличения скорости трансфера при переносе таблиц-наследников настройте параллельное копирование.

Не хватает слотов репликации в базе данных источникаНе хватает слотов репликации в базе данных источника

Текст ошибки:

Warn(Activate): failed to create a replication slot "<идентификатор_трансфера>" at source:
failed to create a replication slot:
failed to create a replication slot:
ERROR: all replication slots are in use
(SQLSTATE 53400)

Решение: увеличьте количество слотов репликации в базе-источнике (по умолчанию 10).

Перестали переноситься данные после изменения эндпоинта-источникаПерестали переноситься данные после изменения эндпоинта-источника

После добавления таблиц в Список включенных таблиц в параметрах эндпоинта-источника трансфер перезапустился и перестал переносить данные.

Решение:

• Создайте таблицы в базе-приемнике вручную.

  1. Создайте в базе-приемнике новые таблицы с Primary Key и без Foreign key.
  2. Добавьте новые таблицы в Список включенных таблиц в параметрах эндпоинта-источника.
  3. Перенесите дамп с историческими данными в базу-приемник.
  4. При появлении в логах ошибок решите их согласно конкретной ошибке.
  5. Если ошибок нет, но логи пусты, обратитесь в техническую поддержку или к вашему аккаунт-менеджеру для снятия дампа горутин. Это может помочь восстановить репликацию без повторного запуска трансфера.

• Деактивируйте и активируйте трансфер повторно.

• Создайте отдельный трансфер типа Копирование для новых таблиц. При этом исходный трансфер можно не деактивировать.

Ошибка трансфера при смене хоста-мастераОшибка трансфера при смене хоста-мастера

Ошибка возникает в трансферах типа Репликация или Копирование и репликация из-за отсутствия нужных частей Write-Ahead Log (WAL). Это происходит, когда отставание логической репликации WAL с текущего мастера на реплику превышает доступный объем WAL на других хостах. Поэтому при переключении мастера на эту реплику слот репликации не может синхронизироваться с WAL на новом мастере.

Решение: увеличьте лимит в дополнительном параметре эндпоинта-источника Максимальный размер WAL для слота репликации и активируйте трансфер повторно.

Ошибка при трансфере вложенных транзакцийОшибка при трансфере вложенных транзакций

Трансфер PostgreSQL версий ниже 14 не поддерживает перенос таблиц с примененными транзакциями, которые вложены более 1024 раз и на каждом уровне вложенности есть изменения для репликации. Количество вложенностей определяется по числу вложенных конструкций begin; .. commit;.

Решение:

• Используйте PostgreSQL версии 14 или выше.
• Исключите из трансфера транзакции с таким уровнем вложенности.

Ошибка трансфера таблиц с отложенными ограничениямиОшибка трансфера таблиц с отложенными ограничениями

Ошибка возникает в трансферах типа Репликация или Копирование и репликация, так как обновление таблиц и транзакций с отложенными (DEFERRABLE) ограничениями не поддерживается Data Transfer. Подробнее об отложенных ограничениях см. в документации PostgreSQL.

Решение: замените тип ограничений в таких таблицах на IMMEDIATE и активируйте трансфер повторно.

Не удается создать слот репликации на стадии активацииНе удается создать слот репликации на стадии активации

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

Решение:

1. Найдите PID процесса, конкурирующего за блокировки с трансфером:

   /* поиск PID трансфера */
   SELECT active_pid
     FROM pg_replication_slots
     WHERE slot_name = '<ID_трансфера>';
   
   /* поиск PID блокирующего процесса */
   SELECT pid, pg_blocking_pids(pid) as blocked_by
     FROM pg_stat_activity
     WHERE cardinality(pg_blocking_pids(pid)) > 0;
   

           pid      | blocked_by
   -----------------+-------------------
    <PID_трансфера> | {<PID_блокирующей_транзакции>}
   (1 row)
   

2. Посмотрите блокирующий запрос:

   SELECT query, usename
     FROM pg_stat_activity
     WHERE pid = <PID_блокирующей_транзакции>;
   

3. (Опционально) Остановите транзакцию командой:

   SELECT pg_terminate_backend(<PID_блокирующей_транзакции>);
   

4. Активируйте трансфер повторно.

Чрезмерное увеличение журнала WALЧрезмерное увеличение журнала WAL

В базе данных источника PostgreSQL размер журнала Write-Ahead Log (WAL) достигает значения десятков гигабайт из-за долгих запросов (выполняющихся более 5 минут). Такие запросы блокируют журнал WAL и не дают ему продвинуться вперед и очиститься.

Увеличение размера журнала WAL можно заметить по:

• увеличению места, занимаемого базой данных источника;
• возрастанию графика Read buffer size в мониторинге Data Transfer.

Решение:

1. Найдите сессии запросов, выполняющиеся дольше 5 минут:

   SELECT now()-xact_start, query, pid FROM pg_stat_activity
   WHERE (now()-xact_start)>'5minute'::interval AND STATE != 'idle'
   ORDER BY 1 DESC;
   

2. Завершите найденные сессии. Старайтесь не допускать появления таких запросов.

Ошибка при репликации из внешнего источникаОшибка при репликации из внешнего источника

Текст ошибки:

[XX000] ERROR: could not connect to the publisher:
SSL error: certificate verify failed FATAL:
no pg_hba.conf entry for replication connection
from host "<IP-адрес_хоста_PostgreSQL>", user "postgres", SSL off

Решение: подготовьте источник в соответствии с разделом Подготовка к трансферу.

Ошибка трансфера при переносе таблиц без первичных ключейОшибка трансфера при переносе таблиц без первичных ключей

Текст ошибки:

Primary key check failed: 14 Tables errors: Table no key columns found

Для типов трансфера Репликация и Копирование и репликация таблицы без первичных ключей не переносятся.

Решение: подготовьте источник в соответствии с разделом Подготовка к трансферу.

Повторяющееся значение ключа нарушает уникальное ограничениеПовторяющееся значение ключа нарушает уникальное ограничение

Текст ошибки:

ERROR: duplicate key value violates unique constraint "<название_ограничения>" (SQLSTATE 23505)

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

Решение: используйте один из вариантов:

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

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

• Отключите в базе-приемнике ограничения. В определенный момент времени возможно нарушение ограничений (например, когда применена часть транзакции из базы-источника в базе-приемнике). Но Data Transfer гарантирует согласованность данных в итоге (eventually consistency) — когда вторая часть транзакции применится в базе-приемнике, нарушения ограничений не будет.

Ошибка удаления таблицы при политике очистки DropОшибка удаления таблицы при политике очистки Drop

Текст ошибки:

ERROR: cannot drop table <имя_таблицы> because other objects depend on it (SQLSTATE 2BP01)

При политике очистки Drop трансфер удаляет таблицы в несколько итераций:

1. Трансфер последовательно пытается удалить все таблицы. Каскадное удаление не используется, так как может привести к удалению таблиц, не участвующих в трансфере. Если таблицу невозможно удалить, например, из-за связанности внешними ключами, возникает ошибка, но трансфер продолжит удаление таблиц.

2. Во время следующей итерации трансфер пытается удалить оставшиеся таблицы. Если блокирующие дочерние таблицы были удалены в предыдущей итерации, таблица со связанностью внешними ключами удаляется. В этом случае ошибка устраняется в процессе работы Data Transfer, дополнительных действий не требуется.

3. Если во время очередной итерации трансфер не удалил ни одной таблицы, процесс удаления таблиц завершается. При этом:

   • Трансфер продолжит работу, если все таблицы были удалены.
   • Трансфер прервется с ошибкой, если остались неудаленные таблицы.

Решение:

• Если дочерние таблицы не участвуют в других трансферах и их перенос не противоречит целям трансфера, добавьте эти таблицы:

  • В список включенных таблиц в параметрах эндпоинта-источника.
  • В список объектов для переноса в параметрах трансфера.

• Удалите блокирующие дочерние таблицы в базе-приемнике вручную.

• Используйте политику очистки Truncate.

• Пересоздайте базу в приемнике.

  Важно

  Это приведет к потере всех данных в базе.

Ошибка при переносе таблиц с генерируемыми столбцамиОшибка при переносе таблиц с генерируемыми столбцами

Текст ошибки:

ERROR: column "<имя_столбца>" is a generated column (SQLSTATE 42P10)

Ошибка может возникнуть, если из базы-источника переносится таблица, которая содержит генерируемые столбцы. Например, если генерируемый столбец определен как столбец идентификаторов (GENERATED ... AS IDENTITY), то ошибка возникнет при репликации данных. Если генерируемый столбец вычисляемый, то ошибка возникнет независимо от типа трансфера. Подробнее о генерируемых столбцах см. в документации PostgreSQL.

Решение: в параметрах эндпоинта-источника исключите из трансфера таблицы, которые содержат генерируемые столбцы.