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

Евгения Кузина

Обзор

Логическая репликация — это метод репликации объектов данных и их изменений, использующий репликационные идентификаторы (обычно это первичный ключ). Термин "логическая репликация" используется, чтобы отличать такую репликацию от физической, которая построена на адресах блоков и побайтовом копировании. Логическая репликация позволяет контролировать не только репликацию данных, но и аспекты безопасности.

Логическая репликация использует модель публикаций/подписок (publish/subscribe) с одним или несколькими подписчиками (subscribers), которые подписываются на одну или несколько публикаций (publications) на ноде публикации (publishing node). Подписчики получают данные из публикаций и могут повторно публиковать данные для каскадной репликации или более сложных конфигураций репликации.

Логическая репликация таблицы начинается с создания снепшота на публикующей ноде и копирования его подписчику (см. Architecture). После этого изменения на публикующей ноде отправляются подписчику в режиме реального времени по мере их возникновения. Публикации в рамках одной подписки гарантируют целостность транзакций. Подписчик применяет изменения данных в том же порядке, что и нода публикации, чтобы гарантировать согласованность транзакций публикации в рамках одной подписки. Этот метод репликации данных также называется репликацией транзакций.

Типичные сценарии использования логической репликации:

  • передача подписчикам инкрементальных изменений в одной базе данных или подмножестве базы данных;

  • срабатывание триггеров для отдельных изменений, когда их получает подписчик;

  • объединение нескольких баз данных в одну, например, для целей анализа;

  • репликация данных между разными мажорными версиями PostgreSQL;

  • репликация данных между экземплярами PostgreSQL на разных платформах;

  • предоставление доступа к реплицированным данным различным группам пользователей;

  • совместное использование данных в нескольких базах данных.

База данных подписчика функционирует так же, как и любой другой экземпляр ADP/PostgreSQL, и может стать публикующей нодой, если создать в базе данных публикации. Когда подписчик действует как приложение с доступом только на чтение, конфликтов не возникает. Если какие-либо приложения или другие подписчики пишут в один и тот же набор таблиц, могут возникнуть конфликты.

Чтобы настроить логическую репликацию, выполните следующие действия:

Ограничения логической репликации

Логическая репликация имеет следующие ограничения:

  • Схема базы данных и DDL-команды не реплицируются.

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

  • Репликация команды TRUNCATE поддерживается, но удаление данных из групп таблиц, связанных внешними ключами, следует выполнять с осторожностью. Если некоторые таблицы, подлежащие очистке на подписчике, имеют ссылки внешнего ключа на таблицы, которые не являются частью той же (или какой-либо) подписки, применение действия очистки к подписчику завершится неудачно.

  • Большие объекты (large object) не реплицируются.

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

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

Для получения более подробной информации об ограничениях логической репликации обратитесь к статье Restrictions.

Параметры конфигурации

Логическая репликация требует установки нескольких параметров конфигурации. Используйте раздел ADPG configurations на вкладке Clusters → Кластер ADP → Services → ADPG → Primary configuration пользовательского интерфейса ADCM, чтобы указать эти параметры. Получить дополнительную информацию по настройке параметров можно в статье Конфигурационные параметры.

Установите следующие параметры на ноде публикации:

  • Параметр wal_level должен иметь значение logical.

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

  • Параметр max_wal_senders должен быть не меньше чем max_replication_slots плюс количество физических реплик, подключаемых одновременно.

 

На ноде подписчика необходимо указать параметры, перечисленные ниже:

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

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

  • max_worker_processes должен включать дополнительные рабочие процессы для репликации, как минимум max_logical_replication_workers плюс 1. Обратите внимание, что некоторые расширения и параллельные запросы занимают слоты из числа max_worker_processes.

Также необходимо проверить, не препятствуют ли репликации параметры, указанные в разделе PG_HBA на вкладке Clusters → Кластер ADP → Services → ADPG → Primary configuration пользовательского интерфейса ADCM. Эти настройки зависят от конфигурации вашей сети и пользователя, от имени которого будет осуществляться подключение.

Публикация

Публикация может быть определена на любом ведущем физическом сервере репликации. Сервер, содержащий публикацию, называется публикующим (publisher). Публикация — это набор изменений, сгенерированный из таблицы или группы таблиц. Она также может называться набором репликации (replication set). Каждая публикация существует только в одной базе данных.

Публикации отличаются от схем и не влияют на доступ к таблице. Каждую таблицу можно включить в несколько публикаций. В настоящее время публикации могут содержать только таблицы. Объекты должны быть добавлены к ним явно, если только публикация не создается для всех таблиц с модификатором FOR ALL TABLES или FOR TABLES IN SCHEMA.

Публикации могут ограничивать набор содержащихся в них изменений. Можно выбрать любую комбинацию операций INSERT, UPDATE, DELETE и TRUNCATE, подобно тому, как триггеры могут срабатывать при различных типах событий. По умолчанию реплицируются все типы операций.

Чтобы реплицировать операции UPDATE и DELETE, опубликованная таблица должна иметь идентификатор реплики (replica identity). Идентификатор реплики позволяет найти соответствующие строки для их обновления или удаления на стороне подписчика. По умолчанию первичный ключ является идентификатором реплики. Можно указать другой уникальный идентификатор для реплики. Если в таблице нет подходящего ключа, в качестве ключа можно использовать всю строку целиком. Для этого укажите значение идентификатора реплики full. Однако такой подход очень неэффективен и его следует использовать, если нет другого решения. Если на стороне публикующей ноды выбран идентификатор, отличный от full, идентификатор, состоящий из такого же или меньшего количества столбцов, также должен быть определен на стороне подписчика. За дополнительными сведениями о назначении идентификатора реплики обратитесь к описанию REPLICA IDENTITY. Если таблица без идентификатора реплики добавляется к публикации, которая реплицирует операции UPDATE или DELETE, эти операции вызовут ошибку на ноде публикации. Операции INSERT могут быть обработаны без указания идентификатора реплики.

У каждой публикации может быть несколько подписчиков.

Создайте таблицы для тестов:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    date DATE NOT NULL,
    amount DECIMAL(10,2),
    status VARCHAR(20));

ALTER TABLE orders REPLICA IDENTITY DEFAULT;

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    author_id INT NOT NULL,
    in_stock INTEGER DEFAULT 0);

ALTER TABLE books REPLICA IDENTITY DEFAULT;

INSERT INTO books(title, author_id, in_stock) VALUES
    ('Mrs. Dalloway',1,25),
    ('To the Lighthouse',1,13),
    ('To Kill a Mockingbird',2,0),
    ('The Great Gatsby',3,3),
    ('The Lord of the Rings',4,45);

Используйте команду CREATE PUBLICATION, чтобы создать публикацию. Следующая команда создает публикацию, которая публикует все изменения в таблице books:

CREATE PUBLICATION publication1 FOR TABLE books;

Код ниже создает публикацию, которая публикует все изменения во всех таблицах. Эта команда будет также распространяться на таблицы, которые будут созданы позже:

CREATE PUBLICATION all_tables FOR ALL TABLES;

Также можно определить публикацию, которая реплицирует изменения для всех таблиц в указанном списке схем, включая таблицы, которые будут созданы. Для этого используйте FOR TABLES IN SCHEMA:

CREATE PUBLICATION all_tables_in_schema FOR TABLES IN SCHEMA public;

Следующая публикация публикует операции DELETE, выполненные в таблице books:

CREATE PUBLICATION delete_publication FOR TABLE books WITH (publish = 'delete');

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

Приведенная ниже команда изменяет публикацию delete_publication, чтобы она публиковала не только удаления, но и обновления:

ALTER PUBLICATION delete_publication SET (publish = 'update, delete');

Код ниже добавляет таблицу orders в publication1:

ALTER PUBLICATION publication1 ADD TABLE orders;

Следующий код удаляет таблицу orders из publication1:

ALTER PUBLICATION publication1 DROP TABLE orders;

Операции ADD TABLE и DROP TABLE являются транзакционными. Таким образом, репликация таблицы будет начата или закончена только после фиксации транзакции.

Чтобы удалить публикацию, вызовите команду DROP PUBLICATION:

DROP PUBLICATION all_tables;

По умолчанию подписчикам передаются все данные из всех опубликованных таблиц. Начиная с ADP 16.3.1 можно ограничить количество реплицируемых строк с помощью фильтров, а количество столбцов — с помощью списков.

Фильтры строк

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

Используйте выражение WHERE, заключенное в круглые скобки, после имени таблицы, чтобы указать фильтр.

Примеры:

CREATE PUBLICATION publication2 FOR TABLE orders WHERE (amount > 100 AND status = 'shipped');

CREATE PUBLICATION publication3 FOR TABLE orders WHERE (amount > 200 AND status = 'shipped'), books WHERE (book_id = 1);

Выражение WHERE допускает только простые выражения. Оно не может содержать определяемые пользователем функции, операторы, типы, параметры сортировки и т.д.

Если публикация передает операции UPDATE или DELETE, выражение WHERE фильтра строк должно содержать только столбцы, включенные в идентификатор реплики (REPLICA IDENTITY). Если публикация передает только операции INSERT, WHERE может использовать любой столбец.

При обработке UPDATE выражение фильтра строк оценивается как для старой, так и для новой строки. Если обе строки соответствуют фильтру, происходит репликация UPDATE. Если обе строки не отвечают условиям фильтра, изменение не реплицируется. Если только одна старая или новая строка соответствует выражению фильтра, UPDATE преобразуется в INSERT или DELETE, чтобы избежать несогласованности данных. Если старая строка удовлетворяет выражению фильтра строк (отправляется подписчику), а новая строка — нет, старую строку следует удалить у подписчика. Таким образом, UPDATE преобразуется в DELETE. Если старая строка не соответствует фильтру (не отправляется подписчику), а новая строка соответствует, новую строку следует добавить у подписчика — UPDATE преобразуется в INSERT.

Если публикация содержит секционированную таблицу, параметр публикации publish_via_partition_root определяет, какой фильтр строк используется. Если publish_via_partition_root имеет значение true, используется фильтр строк корневой секционированной таблицы. Если параметр publish_via_partition_root имеет значение false (по умолчанию), используется фильтр строк каждой секции.

Списки столбцов

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

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

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

Для секционированных таблиц параметр публикации publish_via_partition_root определяет, какой список столбцов используется. Если publish_via_partition_root имеет значение true, применяется список столбцов корневой секционированной таблицы. Если publish_via_partition_root имеет значение false (по умолчанию), используется список столбцов каждой секции.

Пример:

CREATE PUBLICATION publication4 FOR TABLE books (book_id, title, author_id);

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

Каталог pg_publication содержит все публикации, созданные в базе данных.

SELECT * FROM pg_publication;
  oid  |       pubname        | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+----------------------+----------+--------------+-----------+-----------+-----------+-------------+------------
 37252 | publication1         |       10 | f            | t         | t         | t         | t           | f
 37255 | delete_publication   |       10 | f            | f         | t         | t         | f           | f
 37258 | publication2         |       10 | f            | t         | t         | t         | t           | f
 37260 | publication3         |       10 | f            | t         | t         | t         | t           | f
 37263 | publication4         |       10 | f            | t         | t         | t         | t           | f
 37265 | all_tables_in_schema |       10 | f            | t         | t         | t         | t           | f

Поля pubinsert, pubupdate, pubdelete и pubtruncate показывают, реплицируются ли операции INSERT, UPDATE, DELETE и TRUNCATE соответственно.

Если поле pubviaroot имеет значение t (true), операции с конечной секцией реплицируются не через эту секцию, а через включенную в публикацию родительскую секцию самого верхнего уровня, с ее именем и схемой.

Представление pg_publication_tables сопоставляет публикации и содержащиеся в них таблицы.

SELECT * FROM pg_publication_tables;
        pubname        | schemaname |    tablename   |            attnames                        |                rowfilter
----------------------+------------+-----------------+--------------------------------------------+--------------------------------------------------------------------
 publication1         | public     | books           | {book_id,title,author_id,in_stock}         |
 delete_publication   | public     | books           | {book_id,title,author_id,in_stock}         |
 publication2         | public     | orders          | {order_id,customer_id,date,amount,status}  | ((amount > (100)::numeric) AND ((status)::text = 'shipped'::text))
 publication3         | public     | orders          | {order_id,customer_id,date,amount,status}  | ((amount > (100)::numeric) AND ((status)::text = 'shipped'::text))
 publication3         | public     | books           | {book_id,title,author_id,in_stock}         | (author_id = 1)
 publication4         | public     | books           | {book_id,title,author_id}                  |
 all_tables_in_schema | public     | orders          | {order_id,customer_id,date,amount,status}  |
 all_tables_in_schema | public     | books           | {book_id,title,author_id,in_stock}         |

Подписка

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

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

У подписчика может быть несколько подписок. Можно определить несколько подписок между одной парой нода публикации/подписчик. При этом необходимо следить за тем, чтобы объекты публикации не пересекались.

Каждая подписка получает изменения через один слот репликации. Для начальной синхронизации данных могут потребоваться дополнительные слоты репликации. Они удаляются после завершения синхронизации данных. За дополнительной информацией обратитесь к статье Replication slot management.

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

Подписки могут быть заархивированы во время создания дамп-файла базы данных. Утилита pg_dump добавляет подписки в файл SQL dump, если она запускается от имени суперпользователя. В противном случае подписки пропускаются, так как пользователи без прав суперпользователя не могут прочитать информацию о подписке из каталога pg_subscription.

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

Столбцы таблицы также сопоставляются по имени. Порядок столбцов может не совпадать. Типы данных столбцов также могут отличаться, если текстовое представление данных может быть преобразовано в целевой тип. Например, вы можете реплицировать столбец типа integer в столбец типа bigint. В целевой таблице также могут быть дополнительные столбцы, которых нет в опубликованной таблице. Эти столбцы заполняются значениями по умолчанию, указанными в определении целевой таблицы.

Создание подписки

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

CREATE PUBLICATION publication1 FOR TABLE books;

CREATE PUBLICATION publication2 FOR TABLE orders WHERE (amount > 100 AND status = 'shipped');

Используйте команду CREATE SUBSCRIPTION, чтобы добавить подписки:

CREATE SUBSCRIPTION subscription1
    CONNECTION 'host=10.92.40.128 port=5432 user=postgres dbname=postgres password=postgres'
    PUBLICATION publication1;

CREATE SUBSCRIPTION subscription2
    CONNECTION 'host=10.92.40.128 port=5432 user=postgres dbname=postgres password=postgres'
    PUBLICATION publication2;

Протестируйте подписку subscription1. Для этого выполните следующие шаги:

  1. Проверьте данные таблицы books на подписчике:

    SELECT * FROM books;
     book_id |         title         | author_id | in_stock
---------+-----------------------+-----------+----------
       1 | Mrs. Dalloway         |         1 |       25
       2 | To the Lighthouse     |         1 |       13
       3 | To Kill a Mockingbird |         2 |        0
       4 | The Great Gatsby      |         3 |        3
       5 | The Lord of the Rings |         4 |       45

  2. Выполните следующие операции над таблицей books на публикующем сервере:

    DELETE FROM books WHERE book_id=5;

UPDATE books SET in_stock = 15 WHERE book_id=1;

  3. Проверьте результат на подписчике:

    SELECT * FROM books;
     book_id |         title         | author_id | in_stock
---------+-----------------------+-----------+----------
       1 | Mrs. Dalloway         |         1 |       15
       2 | To the Lighthouse     |         1 |       13
       3 | To Kill a Mockingbird |         2 |        0
       4 | The Great Gatsby      |         3 |        3

Данные на подписчике обновились успешно через subscription1.

Протестируйте subscription2:

  1. Проверьте данные таблицы orders на подписчике:

    SELECT * FROM orders;
     order_id | customer_id | date | amount | status
----------+-------------+------+--------+--------
(0 rows)

  2. На публикующем сервере добавьте данные в таблицу orders:

    INSERT INTO orders (customer_id, date, amount, status)
SELECT
(random() * 500)::integer,
CURRENT_DATE - (random() * 365)::integer,
(random() * 500)::decimal,
CASE WHEN random() < 0.4 THEN 'pending'
WHEN random() < 0.7 THEN 'shipped'
ELSE 'delivered' END
FROM generate_series(1, 30);

  3. Проверьте таблицу orders на подписчике:

    SELECT * FROM orders;
     order_id | customer_id |    date    | amount | status
----------+-------------+------------+--------+---------
       15 |         498 | 2025-10-02 | 336.85 | shipped
       17 |          55 | 2025-09-02 | 318.28 | shipped
       18 |         234 | 2025-12-14 | 402.03 | shipped
       26 |         180 | 2025-05-20 | 107.43 | shipped
       27 |           1 | 2025-11-20 | 363.33 | shipped
       33 |         158 | 2025-11-21 | 100.19 | shipped
       36 |         304 | 2026-01-24 | 126.47 | shipped
       37 |         351 | 2025-07-17 | 106.98 | shipped
       39 |         305 | 2025-06-19 | 337.23 | shipped

В таблицу orders на подписчике были реплицированы данные согласно установленным фильтрам строк (amount > 100 AND status = 'shipped').

Изменение подписки

Используйте команду ALTER SUBSCRIPTION, чтобы изменить подписку. Следующий код изменяет subscription1, делая ее подписанной на публикацию all_tables_in_schema:

ALTER SUBSCRIPTION subscription1 SET PUBLICATION all_tables_in_schema;

Команда ниже отключает подписку:

ALTER SUBSCRIPTION subscription1 DISABLE;

Удаление подписки

Чтобы удалить подписку, используйте DROP SUBSCRIPTION:

DROP SUBSCRIPTION subscription1;

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

Конфликты

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

Конфликт вызывает ошибку и останавливает репликацию. Необходимо разрешить конфликт вручную. Подробности о конфликте можно найти в журнале сервера подписчика (subscriber server log). Путь по умолчанию — /pg_data1/adpg16/log.

Пример:

2023-01-26 17:29:06.242 UTC [14525] LOG:  background worker "logical replication worker" (PID 1389) exited with exit code 1
2023-01-26 17:29:06.274 UTC [1390] ERROR:  duplicate key value violates unique constraint "test1_pkey"
2023-01-26 17:29:06.274 UTC [1390] DETAIL:  Key (employee_id)=(1) already exists.
2023-01-26 17:29:06.274 UTC [1390] CONTEXT:  COPY test1, line 1

Разрешение конфликта может заключаться либо в изменении данных на стороне подписчика, чтобы они не конфликтовали с приходящим изменением, либо в пропуске транзакции, конфликтующей с существующими данными. Пропустить транзакцию можно, вызвав функцию pg_replication_origin_advance, которой передается соответствующее имя подписки, а также позиция в качестве параметров. Текущую позицию источников можно найти в системном представлении pg_replication_origin_status.

При репликации операций UPDATE или DELETE отсутствие данных не вызывает конфликта, так что такие операции просто пропускаются.

Безопасность

Пользователь, имеющий право изменения схемы таблиц на стороне подписчика, может выполнить произвольный код как суперпользователь. Ограничьте круг владельцев и ролей, имеющих право TRIGGER для таких таблиц, доверенными ролями. Кроме того, если ненадежные пользователи могут создавать таблицы, используйте публикации, которые явно перечисляют таблицы. Создавайте подписку FOR ALL TABLES, только когда доверяете всем пользователям, имеющим право создавать таблицы на стороне публикации или подписки.

Роль, используемая для подключения репликации, должна иметь атрибут REPLICATION или быть суперпользователем. Если у роли нет атрибутов SUPERUSER и BYPASSRLS, при репликации могут выполняться политики защиты строк, определенные на стороне публикации. Если эта роль не может доверять владельцам всех таблиц, добавьте в строку подключения options=-crow_security=off;. Если владелец таблицы добавит политику защиты строк позже, при таком значении параметра репликация остановится, но политика выполняться не будет. Используйте раздел PG_HBA на вкладке Clusters → Кластер ADP → Services → ADPG → Primary configuration пользовательского интерфейса ADCM, чтобы настроить доступ для роли. Роль для репликации должна иметь атрибут LOGIN.

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

Чтобы создать публикацию, пользователь должен иметь привилегию CREATE в базе данных.

Чтобы добавлять таблицы в публикацию, пользователь должен иметь права владельца для этих таблиц. Создавать публикации, публикующие все таблицы, разрешено только суперпользователям.

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

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

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней