Логическая репликация и логическое декодирование в Базе данных Azure для PostgreSQL (гибкий сервер)

Область применения: гибкий сервер Базы данных Azure для PostgreSQL

гибкий сервер База данных Azure для PostgreSQL поддерживает следующие методики извлечения логических данных и реплика.

1. Логическая репликация

   1. Использование встроенной логической репликации PostgreSQL для репликации объектов данных. Логическая репликация позволяет тщательно контролировать репликацию данных, включая репликацию данных на уровне таблицы.
   2. Использование расширения pglogical, которое обеспечивает логическую потоковую передачу реплика tion и другие возможности, такие как копирование начальной схемы базы данных, поддержка TRUNCATE, возможность реплика te DDL и т. д.

2. Логическое декодирование, которое реализуется путем декодирования содержимого журнала с упреждающим протоколированием.

Сравнение логических реплика и логического декодирования

У логической репликации и логического декодирования есть несколько сходств. Обе эти возможности:

• Разрешают реплицировать данные из Postgres.

• Используют упреждающее протоколирование (WAL) в качестве источника изменений.

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

• Используют свойство REPLICA IDENTITY таблицы для определения того, какие изменения можно отправлять.

• Не выполняют репликацию изменений DDL

Две технологии имеют свои различия:

Логическое реплика tion:

• Позволяет указать таблицу или набор таблиц для репликации.

Логическое декодирование:

• Извлекает изменения из всех таблиц базы данных.

Предварительные требования для логического реплика и логического декодирования

1. Перейдите на страницу параметров сервера на портале.

2. Задайте для параметра сервера wal_level значение logical.

3. Если вы хотите использовать расширение pglogical, найдите shared_preload_librariesи azure.extensions параметры и выберите pglogical в раскрывающемся списке.

4. Задайте для параметра max_worker_processes значение не менее 16. В противном случае могут возникнуть такие WARNING: out of background worker slotsпроблемы.

5. Сохраните изменения и перезапустите сервер, чтобы применить изменения.

6. Убедитесь, что База данных Azure для PostgreSQL гибкий экземпляр сервера разрешает сетевой трафик из подключаемого ресурса.

7. Предоставьте администратору разрешения на репликацию.

   ALTER ROLE <adminname> WITH REPLICATION;
   

8. Возможно, вы хотите убедиться, что используемая роль имеет привилегии в схеме, которую вы реплика. В противном случае может возникнуть ошибка, например Permission denied for schema.

Примечание.

Всегда рекомендуется отделять пользователя реплика tion от обычной учетной записи администратора.

Использование логического реплика и логического декодирования

Использование собственного логического реплика tion — самый простой способ реплика База данных Azure для PostgreSQL гибкого сервера. Для использования изменений можно использовать интерфейс SQL или протокол потоковой передачи. Вы также можете использовать интерфейс SQL для использования изменений с помощью логического декодирования.

Встроенная логическая репликация

Логическая репликация использует термины "издатель" и "подписчик".

• Издатель — это База данных Azure для PostgreSQL гибкая база данных сервера, из которую вы отправляете данные.
• Подписчик — это База данных Azure для PostgreSQL гибкая база данных сервера, в которую вы отправляете данные.

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

1. Подключитесь к базе данных издателя. Создайте таблицу и добавьте данные.

   CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
   INSERT INTO basic VALUES (1, 'apple');
   INSERT INTO basic VALUES (2, 'banana');
   

2. Создайте публикацию для таблицы.

   CREATE PUBLICATION pub FOR TABLE basic;
   

3. Подключитесь к базе данных подписчика. Создайте таблицу используя ту же схему, что и для издателя.

   CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
   

4. Создайте подписку, которая подключается к созданной ранее публикации.

   CREATE SUBSCRIPTION sub CONNECTION 'host=<server>.postgres.database.azure.com user=<rep_user> dbname=<dbname> password=<password>' PUBLICATION pub;
   

5. Теперь можно выполнить запрос к таблице подписчика. Вы видите, что он получил данные от издателя.

   SELECT * FROM basic;
   

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

   Если данные не отображаются, включите привилегию входа и azure_pg_admin проверка содержимое таблицы.

   ALTER ROLE azure_pg_admin login;
   

Дополнительные сведения о логической репликации см. в документации по PostgreSQL.

Использование логической реплика между базами данных на одном сервере

При настройке логических реплика между разными базами данных, размещенными на одном и том же База данных Azure для PostgreSQL гибком экземпляре сервера, важно следовать определенным рекомендациям, чтобы избежать ограничений реализации, которые в настоящее время существуют. По состоянию на данный момент создание подписки, которая подключается к одному кластеру базы данных, будет выполнена только в том случае, если слот реплика tion не создается в той же команде. В противном случае CREATE SUBSCRIPTION вызов зависает в LibPQWalReceiverReceive событии ожидания. Это происходит из-за существующего ограничения в обработчике Postgres, которое может быть удалено в будущих выпусках.

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

Сначала создайте таблицу с именем basic с идентичной схемой как в исходных, так и в целевых базах данных:

-- Run this on both source and target databases
CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);

Затем в исходной базе данных создайте публикацию для таблицы и отдельно создайте логический слот реплика tion с помощью pg_create_logical_replication_slot функции, которая помогает предотвратить зависание проблемы, которая обычно возникает при создании слота в той же команде, что и подписка. Необходимо использовать подключаемый pgoutput модуль:

-- Run this on the source database
CREATE PUBLICATION pub FOR TABLE basic;
SELECT pg_create_logical_replication_slot('myslot', 'pgoutput');

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

-- Run this on the target database
CREATE SUBSCRIPTION sub
   CONNECTION 'dbname=<source dbname> host=<server>.postgres.database.azure.com port=5432 user=<rep_user> password=<password>'
   PUBLICATION pub
   WITH (create_slot = false, slot_name='myslot');

Настроив логическую реплика tion, теперь его можно протестировать, вставив новую запись в таблицу "базовый" в исходной базе данных, а затем убедившись, что она реплика tes в целевую базу данных:

-- Run this on the source database
INSERT INTO basic SELECT 3, 'mango';

-- Run this on the target database
TABLE basic;

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

Расширение pglogical

Ниже приведен пример настройки pglogical на сервере базы данных поставщика и подписчике. Дополнительные сведения см. в нашей документации по расширению pglogical. Кроме того, убедитесь, что вы выполнили необходимые задачи, перечисленные выше.

1. Установите расширение pglogical в базе данных на серверах базы данных поставщика и подписчика.

   \c myDB
   CREATE EXTENSION pglogical;
   

2. Если пользователь реплика tion отличается от пользователя администрирования сервера (который создал сервер), убедитесь, что вы предоставляете членство в роли azure_pg_admin пользователю и назначаете атрибуты REPLICATION и LOGIN пользователю. Дополнительные сведения см. в документации по pglogical.

   GRANT azure_pg_admin to myUser;
   ALTER ROLE myUser REPLICATION LOGIN;
   

3. На сервере базы данных поставщика (источника или издателя) создайте узел поставщика.

   select pglogical.create_node( node_name := 'provider1',
   dsn := ' host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=myPassword');
   

4. Создайте набор репликации.

   select pglogical.create_replication_set('myreplicationset');
   

5. Добавьте в набор репликации все таблицы из базы данных.

   SELECT pglogical.replication_set_add_all_tables('myreplicationset', '{public}'::text[]);
   

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

   SELECT pglogical.replication_set_add_all_tables('default', ARRAY['testUser']);
   

6. На сервере базы данных подписчика создайте узел подписчика.

   select pglogical.create_node( node_name := 'subscriber1',
   dsn := ' host=mySubscriberServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=myPasword' );
   

7. Создайте подписку для запуска процесса синхронизации и репликации.

   select pglogical.create_subscription (
   subscription_name := 'subscription1',
   replication_sets := array['myreplicationset'],
   provider_dsn := 'host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=myPassword');
   

8. Затем можно проверить состояние подписки.

   SELECT subscription_name, status FROM pglogical.show_subscription_status();
   

Внимание

Pglogical в настоящее время не поддерживает автоматическое реплика DDL. Исходная схема может быть скопирована вручную с помощью pg_dump --schema-only. Инструкции DDL можно выполнять на поставщике и подписчике одновременно с помощью pglogical. функция репликаte_ddl_command. Обратите внимание на другие ограничения расширения, перечисленные здесь.

Логическое декодирование

Логическое декодирование можно использовать через протокол потоковой передачи или интерфейс SQL.

Протокол потоковой передачи

Зачастую предпочтительным вариантом обработки изменений является использование протокола потоковой передачи. Вы можете создать собственный объект-получатель или соединитель, или использовать сторонние службы, такие как Debezium.

Ознакомьтесь с документацией по wal2json, чтобы увидеть пример использования протокола потоковой передачи с pg_recvlogical.

Интерфейс SQL

В приведенном ниже примере используется интерфейс SQL с подключаемым модулем wal2json.

1. Создайте слот.

   SELECT * FROM pg_create_logical_replication_slot('test_slot', 'wal2json');
   

2. Выполните команды SQL. Например:

   CREATE TABLE a_table (
      id varchar(40) NOT NULL,
      item varchar(40),
      PRIMARY KEY (id)
   );
   
   INSERT INTO a_table (id, item) VALUES ('id1', 'item1');
   DELETE FROM a_table WHERE id='id1';
   

3. Получите изменения.

   SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');
   

   Выходные данные выглядят следующим образом.

   {
         "change": [
         ]
   }
   {
         "change": [
                  {
                           "kind": "insert",
                           "schema": "public",
                           "table": "a_table",
                           "columnnames": ["id", "item"],
                           "columntypes": ["character varying(40)", "character varying(40)"],
                           "columnvalues": ["id1", "item1"]
                  }
         ]
   }
   {
         "change": [
                  {
                           "kind": "delete",
                           "schema": "public",
                           "table": "a_table",
                           "oldkeys": {
                                 "keynames": ["id"],
                                 "keytypes": ["character varying(40)"],
                                 "keyvalues": ["id1"]
                           }
                  }
         ]
   }
   

4. Удалите слот после завершения работы с ним.

   SELECT pg_drop_replication_slot('test_slot');
   

Дополнительные сведения о логическом декодировании см. в документации по PostgreSQL.

Azure Monitor

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

Столбец "активный" в pg_replication_slots представлении указывает, подключен ли потребитель к слоту.

SELECT * FROM pg_replication_slots;

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

Ограничения

• Ограничения логической репликации применяются, как описано здесь.

• Слоты и отработка отказа высокого уровня доступности . При использовании серверов с поддержкой высокой доступности с гибким сервером База данных Azure для PostgreSQL следует учитывать, что логические слоты реплика tion не сохраняются во время событий отработки отказа. Чтобы поддерживать логические слоты реплика tion и обеспечить согласованность данных после отработки отказа, рекомендуется использовать расширение слотов отработки отказа PG. Дополнительные сведения о включении этого расширения см. в документации.

Важно!

Необходимо удалить логический слот реплика на основном сервере, если соответствующий подписчик больше не существует. В противном случае wal-файлы накапливаются в основном, заполняя хранилище. Предположим, что порог хранилища превышает определенное пороговое значение, а логический слот реплика tion не используется (из-за недоступного подписчика). В этом случае гибкий экземпляр сервера База данных Azure для PostgreSQL автоматически удаляет неиспользуемый логический слот реплика. Это действие освобождает накопленные файлы WAL и позволяет избежать недоступности сервера из-за того, что хранилище заполняется ситуацией.

Связанный контент

• Параметры сети
• Расширения
• высокий уровень доступности