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

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

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

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

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

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

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

   • Захват изменений из PostgreSQL и поставка в YDS;
   • Захват изменений из PostgreSQL и поставка в Apache Kafka®.

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

   • Загрузка данных из PostgreSQL в витрину ClickHouse®.

4. Загрузка данных в масштабируемое хранилище Object Storage позволяет удешевить хранение и облегчает обмен данных с контрагентами.

   • Загрузка данных PostgreSQL в масштабируемое хранилище Object Storage.

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

Подготовка базы данных источникаПодготовка базы данных источника

Настройка

1. В файле postgresql.conf измените значение параметра wal_level на logical:

   wal_level = logical
   

2. Перезапустите PostgreSQL.

SELECT NOW() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' ORDER BY 1 DESC;

ignore_slots:
  - database: <база_данных>
    name: <слот_репликации>
    plugin: wal2json
    type: logical

Warn(Termination): unable to create new pg source: Replication slotID <имя_слота_репликации> does not exist.

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

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

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

Перед началом работы ознакомьтесь с особенностями работы сервиса с источниками и приемниками PostgreSQL.

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

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

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

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

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

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

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

Настройки переноса схемы при активации и деактивации трансфераНастройки переноса схемы при активации и деактивации трансфера

В процессе работы трансфера схема базы данных переносится с источника на приемник. Перенос выполняется в два этапа:

1. На стадии активации.

   Этот этап выполняется при активации трансфера перед копированием или репликацией для создания схемы на приемнике. Вы можете выбрать, какие части схемы будут перенесены. По умолчанию это TABLE, VIEW, PRIMARY KEY, SEQUENCE, SEQUENCE OWNED BY, RULE, TYPE, FUNCTION, DEFAULT.

2. На стадии деактивации.

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

   На финальной стадии предполагается, что при деактивации трансфера на источнике нет пишущей нагрузки. Это можно гарантировать переводом в режим только чтение (read-only). На этой стадии схема базы данных на приемнике приводится в состояние, когда она будет консистентна схеме на источнике.

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

Перенос схемы и на первичной, и на финальных стадиях выполняется с помощью утилиты pg_dump.

Значения позиций в последовательности при репликации нельзя гарантированно сохранить, поэтому рекомендуется сделать обновление sequence на приемнике.

Настройка приемника данныхНастройка приемника данных

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

• PostgreSQL;
• MySQL®;
• ClickHouse®;
• Greenplum®;
• Yandex Managed Service for YDB;
• Yandex Object Storage;
• Apache Kafka®;
• YDS;
• Elasticsearch;
• OpenSearch.

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

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

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

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

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

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

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

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

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

В 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

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

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

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

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

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

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

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

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

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

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

Решение:

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

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

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

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

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

  Важно

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