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

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

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

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

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

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

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

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

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

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

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

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

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

  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.

Замечание

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

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

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

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

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

  • Издатель — это база данных Azure Database for PostgreSQL с гибкой конфигурацией сервера, из которой вы отправляете данные.
  • Подписчик — это база данных на гибком сервере Azure Database для 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 роли, и проверьте содержимое таблицы.

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

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

При настройке логической репликации между разными базами данных, размещенными на одном и том же Azure Database для PostgreSQL экземпляре гибкого сервера, важно следовать определенным рекомендациям, чтобы избежать ограничений, присутствующих при реализации, которые в настоящее время существуют. По состоянию на данный момент создание подписки, подключающейся к той же группе баз данных, будет успешным только в том случае, если слот репликации не создается в рамках одной и той же команды; в противном случае вызов 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);

Затем в исходной базе данных создайте публикацию для таблицы и отдельно создайте слот логической репликации с помощью 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_slot в false, чтобы предотвратить создание нового слота в гибкой серверной инстанции базы данных 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');

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

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

-- Run this on the target database
TABLE basic;

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

Расширение pglogical

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

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

    \c myDB
CREATE EXTENSION pglogical;

  2. Если пользователь репликации отличается от пользователя администрирования сервера (который создал сервер), убедитесь, что вы предоставляете членство в роли 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=<password>');

  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=<password>' );

  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=<password>');

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

    SELECT subscription_name, status FROM pglogical.show_subscription_status();

Caution

Pglogical в настоящее время не поддерживает автоматическую репликацию DDL. Исходная схема может быть скопирована вручную с помощью pg_dump --schema-only. Инструкции DDL можно выполнять на поставщике и подписчике одновременно с помощью функции pglogical.replicate_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.

Monitor

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

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

SELECT * FROM pg_replication_slots;

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

Ограничения

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

  • Слоты и переключение при отказе для высокой доступности - В PostgreSQL 16 и более ранних версиях при использовании серверов с поддержкой высокой доступности (HA) с Azure Database for PostgreSQL следует учитывать, что слоты логической репликации не сохраняются во время событий переключения при отказе. Чтобы поддерживать слоты логической репликации и обеспечить согласованность данных после отработки отказа, рекомендуется использовать расширение слотов отработки отказа PG и настроить вспомогательные параметры, такие как hot_standby_feedback = on. Дополнительные сведения о включении этого расширения см. в документации.

Поддержка отказоустойчивости для слотов логической репликации

Для PostgreSQL 17 и выше синхронизация слотов поддерживается встроенно. Если включить правильные конфигурации PostgreSQL (sync_replication_slots, hot_standby_feedback), слоты логической репликации сохраняются автоматически после отработки отказа, и никаких расширений не требуется.

Это важно

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

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

  • Last updated on