# Занятие 19: Репликация / Обзор логической репликации

> Исходная страница в нативном Markdown: Репликация / Обзор логической репликации

---

Индекс LLMS: [llms.txt](/ru/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета.
> **HTML source:** [dev2_19_replica_overview_logical.html](https://edu.postgrespro.ru/16/dev2-16/dev2_19_replica_overview_logical.html)
> **Archive:** [DEV2-handouts-16.zip](https://edu.postgrespro.ru/16/DEV2-handouts-16.zip)
> **Страниц PDF:** 26

## Page 1: Репликация / Обзор логической репликации {#source-page-001}

### Авторские права

© Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
### Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.
### Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru
### Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

## Page 2: Темы {#source-page-002}

- Логическая репликация
- Публикации и подписчики
- Уровни журнала
- Обнаружение и разрешение конфликтов
- Особенности работы триггеров
- Задачи, решаемые с помощью логической репликации
- Ограничения

## Page 3: Логическая репликация {#source-page-003}

**Механизм**
  - публикующий сервер читает собственные журнальные записи,
  - декодирует их в изменения строк данных и отправляет подписчикам
  - сервер-подписчик применяет изменения к своим таблицам
- Особенности
  - публикация-подписка: поток данных возможен в обе стороны
  - требуется совместимость на уровне протокола
  - возможна выборочная репликация отдельных таблиц

### Комментарии преподавателя

При физической репликации только один сервер (мастер) выполняет изменения и генерирует журнальные записи. Остальные серверы (реплики) только читают журнальные записи мастера и применяют их.

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

Публикующий сервер читает собственные журнальные записи, но не пересылает их подписчикам в исходном виде (как при физической репликации), а предварительно декодирует их в «логический», независимый от платформы и версии PostgreSQL вид. Поэтому для логической репликации обычно не нужна двоичная совместимость, реплика должна лишь понимать протокол репликации. Также можно получать и применять изменения не всех объектов, а только отдельных таблиц.

<https://postgrespro.ru/docs/postgresql/16/logical-replication>

## Page 4: Логическая репликация {#source-page-004}

![Source slide page 4: Логическая репликация](images/page-004.png){#source-figure-004 caption="Source slide page 4: Логическая репликация"}


### Комментарии преподавателя

На публикующем сервере процесс wal\_sender формирует журнальные записи, отражающие изменения в публикуемых данных, а фоновый процесс logical replication worker на сервере-подписчике получает информацию от публикующего сервера и применяет ее.

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

<https://postgrespro.ru/docs/postgresql/16/logical-replication-publication>

<https://postgrespro.ru/docs/postgresql/16/logical-replication-subscription>

## Page 5: Публикации и подписки {#source-page-005}

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

### Комментарии преподавателя

Логическая репликация использует модель «публикация-подписка».

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

Реплицируются только измененные строки таблиц (а не команды SQL). Команды DDL не передаются; таблицы-приемники на подписчике надо создавать вручную. Но есть возможность автоматической начальной синхронизации содержимого таблиц при создании подписки.

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

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

<https://postgrespro.ru/docs/postgresql/16/logical-replication>

## Page 6: Уровни журнала {#source-page-006}

**Параметр wal\\\_level**
- minimal < replica < logical
- восстановление восстановление восстановление после сбоя после сбоя после сбоя восстановление восстановление из резервной копии, из резервной копии, репликация репликация логическая репликация

### Комментарии преподавателя

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

Такой расширенный уровень журнала называется logical. Поскольку по умолчанию используется уровень replica, для логической репликации потребуется изменить значение параметра wal\_level на публикующем сервере.

<https://postgrespro.ru/docs/postgresql/16/protocol-logical-replication#PROT> OCOL-LOGICAL-MESSAGES-FLOW

## Page 7: Логическая репликация {#source-page-007}

Пусть на первом сервере имеется таблица:
```sql
=> CREATE DATABASE replica_overview_logical_dev;
```
```sql
CREATE DATABASE
```
```sql
=> \c replica_overview_logical_dev
```
```text
You are now connected to database "replica_overview_logical_dev" as user "student".
```
```sql
=> CREATE TABLE test(id integer PRIMARY KEY, descr text);
```
```sql
CREATE TABLE
```
Склонируем кластер с помощью резервной копии, как мы делали в теме «Обзор физической репликации», но команде pg\_basebackup не будем передавать ключ -R, поскольку нам потребуется независимый сервер, а не реплика.
```sh
student$ pg_basebackup --pgdata=/home/student/tmp/backup --checkpoint=fast
```
Если второй сервер работает, остановим его
```sh
student$ sudo pg_ctlcluster 16 replica stop
```
```text
Cluster is not running.
```
Перемещаем резервную копию в каталог данных второго сервера, поменяв владельца файлов:
```sh
student$ sudo rm -rf /var/lib/postgresql/16/replica
```
```sh
student$ sudo mv /home/student/tmp/backup /var/lib/postgresql/16/replica
```
```sh
student$ sudo chown -R postgres: /var/lib/postgresql/16/replica
```
Запускаем второй сервер:
```sh
student$ sudo pg_ctlcluster 16 replica start
```
Получили два независимых сервера, на каждом из них есть пустая таблица test. Добавим в таблицу на первом сервере пару строк:
```sql
=> INSERT INTO test VALUES (1, 'Раз'), (2, 'Два');
```
```sql
INSERT 0 2
```
Теперь мы хотим настроить между серверами логическую репликацию. Для этого понадобится дополнительная информация в журнале первого сервера:
```sql
=> ALTER SYSTEM SET wal_level = logical;
```
```sql
ALTER SYSTEM
```
```sh
student$ sudo pg_ctlcluster 16 main restart
```
На первом сервере создаем публикацию:
```sh
student$ psql -d replica_overview_logical_dev
```
```sql
=> CREATE PUBLICATION test_pub FOR TABLE test;
```
```sql
CREATE PUBLICATION
```
```text
=> \dRp+
```
```text
                           Publication test_pub
  Owner  | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------+------------+---------+---------+---------+-----------+----------
 student | f          | t       | t       | t       | t         | f
Tables:
    "public.test"
```
На втором сервере подписываемся на эту публикацию:
```sh
student$ psql -p 5433 -d replica_overview_logical_dev
```
```sql
=> CREATE SUBSCRIPTION test_sub
CONNECTION 'port=5432 user=student dbname=replica_overview_logical_dev'
PUBLICATION test_pub;
```

## Page 8: Page 8 {#source-page-008}

```sql
NOTICE:  created replication slot "test_sub" on publisher
CREATE SUBSCRIPTION
```
При создании подписки на сервере публикации автоматически создался слот репликации — объект, который помнит, какие изменения получил подписчик, и в случае разрыва соединения позволяет возобновить передачу с нужного места.
```text
=> \dRs
```
```text
           List of subscriptions
   Name   |  Owner  | Enabled | Publication
----------+---------+---------+-------------
 test_sub | student | t       | {test_pub}
(1 row)
```
Проверяем репликацию:
```sql
=> INSERT INTO test VALUES (3, 'Три');
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM test;
```
```text
 id | descr
----+-------
  1 | Раз
  2 | Два
  3 | Три
(3 rows)
```
Состояние подписки можно посмотреть в представлении:
```sql
=> SELECT * FROM pg_stat_subscription \gx
```
```text
-[ RECORD 1 ]---------+------------------------------
subid                 | 24758
subname               | test_sub
pid                   | 48689
leader_pid            |
relid                 |
received_lsn          | 0/70074F0
last_msg_send_time    | 2026-04-10 16:19:23.81952+03
last_msg_receipt_time | 2026-04-10 16:19:23.820728+03
latest_end_lsn        | 0/70074F0
latest_end_time       | 2026-04-10 16:19:23.81952+03
```
К процессам сервера-подписчика добавился logical replication worker (его номер указан в pg\_stat\_subscription.pid):
```sh
student$ ps -o pid,command --ppid 48376
```
```text
    PID COMMAND
  48377 postgres: 16/replica: checkpointer
  48378 postgres: 16/replica: background writer
  48381 postgres: 16/replica: walwriter
  48382 postgres: 16/replica: autovacuum launcher
  48383 postgres: 16/replica: logical replication launcher
  48655 postgres: 16/replica: student replica_overview_logical_dev [local] idle
  48689 postgres: 16/replica: logical replication apply worker for subscription 24758
```

## Page 9: Конфликты {#source-page-009}

**Режимы идентификации для изменения и удаления**
  - столбцы первичного ключа (по умолчанию)
  - столбцы указанного уникального индекса с ограничением NOT NULL
  - все столбцы
  - без идентификации (по умолчанию для системного каталога)
- Конфликты — нарушение ограничений целостности
  - репликация приостанавливается до устранения конфликта вручную

### Комментарии преподавателя

Вставка новых строк происходит достаточно просто. Интереснее обстоит дело при изменениях и удалениях — в этом случае надо как-то идентифицировать старую версию строки. По умолчанию для этого используются столбцы первичного ключа, но при определении таблицы можно указать и другие способы (replica identity): использовать уникальный индекс или использовать все столбцы. Можно вообще отказаться от поддержки репликации для некоторых таблиц (по умолчанию не реплицируются таблицы системного каталога).

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

<https://postgrespro.ru/docs/postgresql/16/sql-altertable>

<https://postgrespro.ru/docs/postgresql/16/logical-replication-conflicts>

<https://postgrespro.ru/docs/postgresql/16/sql-altersubscription>

## Page 10: Конфликты {#source-page-010}

Локальные изменения на подписчике не запрещаются, вставим строку в таблицу на втором сервере.
```sql
=> INSERT INTO test VALUES (4, 'Четыре (локально)');
```
```sql
INSERT 0 1
```
Если теперь строку с таким же значением первичного ключа вставить на первом сервере, при ее применении на стороне подписки произойдет конфликт.
```sql
=> INSERT INTO test VALUES (4, 'Четыре');
```
```sql
INSERT 0 1
```
```sql
=> INSERT INTO test VALUES (5, 'Пять');
```
```sql
INSERT 0 1
```
Подписка не может применить изменение, репликация остановилась.
```sql
=> SELECT * FROM pg_stat_subscription \gx
```
```text
-[ RECORD 1 ]---------+---------
subid                 | 24758
subname               | test_sub
pid                   |
leader_pid            |
relid                 |
received_lsn          |
last_msg_send_time    |
last_msg_receipt_time |
latest_end_lsn        |
latest_end_time       |
```
```sql
=> SELECT * FROM test;
```
```text
 id |       descr
----+-------------------
  1 | Раз
  2 | Два
  3 | Три
  4 | Четыре (локально)
(4 rows)
```
Чтобы разрешить конфликт, удалим строку на втором сервере и немного подождем...
```sql
=> DELETE FROM test WHERE id=4;
```
```sql
DELETE 1
```
```sql
=> SELECT * FROM test;
```
```text
 id | descr
----+--------
  1 | Раз
  2 | Два
  3 | Три
  4 | Четыре
  5 | Пять
(5 rows)
```
Репликация возобновилась.

## Page 11: Триггеры на подписчике {#source-page-011}

**При обычной работе**
  - в обслуживающих процессах (session\_replication\_role = origin или local)
  - ENABLE TRIGGER
  - ENABLE ALWAYS TRIGGER
- При применении реплицированных изменений
  - в процессе logical replication worker (session\_replication\_role = replica)
  - ENABLE REPLICA TRIGGER
  - ENABLE ALWAYS TRIGGER
  - только на уровне строк (за исключением начальной синхронизации)

### Комментарии преподавателя

Срабатывание триггеров на сервере-подписчике имеет некоторые особенности.

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

Если нужно, чтобы триггер все-таки срабатывал, его необходимо определить как репликационный (ALTER TABLE ... ENABLE REPLICA TRIGGER) или универсальный (ENABLE ALWAYS TRIGGER).

Изменения реплицируются построчно, поэтому работают только триггеры таблиц на уровне строк (FOR EACH ROW), но не на уровне оператора (FOR EACH STATEMENT). Исключение составляет начальная синхронизация — при ней срабатывают триггеры и на уровне строк, и на уровне оператора.

При обычной работе срабатывают «обычные» триггеры (просто созданные командой CREATE TRIGGER, или явно разрешенные командой ALTER TABLE ... ENABLE TRIGGER) и универсальные триггеры (ENABLE ALWAYS TRIGGER).

Сервер отличает обычный процесс от процесса, применяющего реплицированные изменения, с помощью параметра сеанса session\_replication\_role. По умолчанию параметр имеет значение origin, а процесс logical replication worker задает ему значение replica.

<https://postgrespro.ru/docs/postgresql/16/sql-altertable>

## Page 12: Триггеры на подписчике {#source-page-012}

![Source slide page 12: Триггеры на подписчике](images/page-012.png){#source-figure-012 caption="Source slide page 12: Триггеры на подписчике"}

Попробуем создать триггер на сервере-подписчике.
```sql
=> CREATE FUNCTION change_descr() RETURNS trigger AS $$
BEGIN
  NEW.descr := NEW.descr || ' — триггер сработал';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
```sql
=> CREATE TRIGGER test_before_row
BEFORE INSERT OR UPDATE ON test
FOR EACH ROW
EXECUTE FUNCTION change_descr();
```
```sql
CREATE TRIGGER
```
Добавляем строку на публикующем сервере:
```sql
=> INSERT INTO test VALUES (6, 'Шесть');
```
```sql
INSERT 0 1
```
Что получится?
```sql
=> SELECT * FROM test WHERE id = 6;
```
```text
 id | descr
----+-------
  6 | Шесть
(1 row)
```
Обычный триггер не срабатывает при применении изменений из публикации, зато он будет срабатывать при локальных изменениях на подписчике:
```sql
=> INSERT INTO test VALUES (7, 'Семь');
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM test WHERE id = 7;
```
```text
 id |          descr
----+-------------------------
  7 | Семь — триггер сработал
(1 row)
```
Допустим, нам нужно, чтобы триггер срабатывал только для изменений, происходящих при репликации. Для этого явно включим его как репликационный:
```sql
=> ALTER TABLE test ENABLE REPLICA TRIGGER test_before_row;
```
```sql
ALTER TABLE
```
```sql
=> INSERT INTO test VALUES (8, 'Восемь');
```
```sql
INSERT 0 1
```
```sql
=> INSERT INTO test VALUES (9, 'Девять');
```
```sql
INSERT 0 1
```
Проверим:
```sql
=> SELECT * FROM test WHERE id >= 8;
```
```text
 id |           descr
----+---------------------------
  8 | Восемь — триггер сработал
  9 | Девять
(2 rows)
```
Теперь триггер работает только при применении реплицированных изменений.
Если необходимо добиться срабатывания триггера и при репликации, и при локальных изменениях, нужно определить его как ALWAYS TRIGGER. А чтобы различить эти изменения, используем параметр session\_replication\_role:

## Page 13: Page 13 {#source-page-013}

![Source slide page 13: Page 13](images/page-013.png){#source-figure-013 caption="Source slide page 13: Page 13"}

```sql
=> ALTER TABLE test ENABLE ALWAYS TRIGGER test_before_row;
```
```sql
ALTER TABLE
```
```sql
=> CREATE OR REPLACE FUNCTION change_descr() RETURNS trigger AS $$
BEGIN
  NEW.descr := NEW.descr || ' (' || current_setting('session_replication_role') || ')';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
```
```sql
CREATE FUNCTION
```
Вставим еще по одной строке на каждом сервере и проверим, что получилось:
```sql
=> INSERT INTO test VALUES (10, 'Десять');
```
```sql
INSERT 0 1
```
```sql
=> INSERT INTO test VALUES (11, 'Одиннадцать');
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM test WHERE id >= 10;
```
```text
 id |        descr
----+----------------------
 10 | Десять (replica)
 11 | Одиннадцать (origin)
(2 rows)
```
Заметим, что проверки внешних ключей в PostgreSQL реализованы посредством триггеров, поэтому присвоение параметру session\_replication\_role значения replica отключает эти проверки, а это может привести к нарушению согласованности данных.
### Удаление подписки
Если репликация больше не нужна, надо аккуратно удалить подписку — иначе на публикующем сервере останется открытым репликационный слот.
```sql
=> DROP SUBSCRIPTION test_sub;
```
```sql
NOTICE:  dropped replication slot "test_sub" on publisher
DROP SUBSCRIPTION
```

## Page 14: Задачи: консолидация {#source-page-014}

![Source slide page 14: Задачи: консолидация](images/page-014.png){#source-figure-014 caption="Source slide page 14: Задачи: консолидация"}

получение и консолидация данных нескольких филиалов

### Комментарии преподавателя

Рассмотрим несколько задач, которые можно решить с помощью логической репликации.

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

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

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

На иллюстрации: на центральном сервере работают два процесса приема журналов, по одному на каждую подписку.

## Page 15: Задачи: справочники {#source-page-015}

![Source slide page 15: Задачи: справочники](images/page-015.png){#source-figure-015 caption="Source slide page 15: Задачи: справочники"}

репликация справочников

### Комментарии преподавателя

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

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

## Page 16: Задачи: мастер-мастер {#source-page-016}

![Source slide page 16: Задачи: мастер-мастер](images/page-016.png){#source-figure-016 caption="Source slide page 16: Задачи: мастер-мастер"}

кластер, в котором данные могут изменять несколько серверов

### Комментарии преподавателя

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

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

Двунаправленная репликация появилась в PostgreSQL 16. Система умеет различать источники репликации (origin), и при создании подписки можно указать, чтобы с публикующего сервера не отправлялись изменения, помеченные определенным источником.

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

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

## Page 17: Ограничения {#source-page-017}

**Не реплицируются**
  - команды DDL
  - значения последовательностей
  - большие объекты (lo)
  - изменения материализованных представлений, внешних таблиц
- Могут вызвать проблемы
  - массовые изменения данных
- Не поддерживается
  - автоматическое разрешение конфликтов

### Комментарии преподавателя

Логическая репликация имеет ряд ограничений.

Не реплицируются команды DDL — все изменения схемы данных надо переносить вручную.

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

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

Изменения реплицируются построчно, поэтому выполнение на публикаторе команды SQL, затрагивающей много строк, приведет к повышенной нагрузке на подписчик.

И, как уже говорилось, нет возможности автоматического разрешения конфликтов.

Эти ограничения уменьшают применимость логической репликации.

<https://postgrespro.ru/docs/postgresql/16/logical-replication-restrictions>

## Page 18: Итоги {#source-page-018}

**Логическая репликация передает изменения строк отдельных таблиц**
  - разнонаправленная
  - совместимость на уровне протокола
- Модель «публикация — подписка»
- Следует учитывать имеющиеся ограничения

## Page 19: Практика {#source-page-019}

Мы открываем второй магазин. Он работает независимо от основного (свои пользователи, склад, закупки и продажи), но имеет тот же ассортимент. 1. Разверните второй сервер из резервной копии, как показано в демонстрации. Очистите таблицу операций. 2. Организуйте репликацию справочников книг и авторов из основного магазина во второй. Предполагается, что эти таблицы могут меняться только на основном сервере.
  - Убедитесь, что изменения названий книг и т. п. передаются
  - на второй сервер, но изменения наличного количества
  - происходят на двух серверах независимо.

## Page 20: 1. Развертывание второго магазина {#source-page-020}

Для развертывания сервера выполняем те же команды, что и в демонстрации:
```sh
student$ pg_basebackup --pgdata=/home/student/backup --checkpoint=fast
```
```sh
student$ sudo pg_ctlcluster 16 replica stop
```
```sh
student$ sudo rm -rf /var/lib/postgresql/16/replica
```
```sh
student$ sudo mv /home/student/backup /var/lib/postgresql/16/replica
```
```sh
student$ sudo chown -R postgres: /var/lib/postgresql/16/replica
```
```sh
student$ sudo pg_ctlcluster 16 replica start
```
```sql
=> ALTER SYSTEM SET wal_level = logical;
```
```sql
ALTER SYSTEM
```
```sql
=> \q
```
```sh
student$ sudo pg_ctlcluster 16 main restart
```
```sh
student$ psql -d bookstore2
```
```sh
student$ psql -p 5433 -d bookstore2
```
Очистим таблицу операций, поскольку второй магазин ведет свою деятельность независимо от основного:
```text
=> TRUNCATE TABLE operations;
```
```text
TRUNCATE TABLE
```
При выполнении команды TRUNCATE не сработает триггер update\_onhand\_qty\_trigger, поэтому очистим наличное количество вручную:
```sql
=> SELECT book_id, onhand_qty FROM books LIMIT 5;
```
```text
 book_id | onhand_qty
---------+------------
      32 |         48
      13 |         38
      58 |         44
      75 |         44
      17 |         34
(5 rows)
```
```sql
=> UPDATE books SET onhand_qty = 0;
```
```sql
UPDATE 100
```
### 2. Репликация справочников книг и авторов
Все три связанные таблицы должны входить в публикацию. Иначе на второй сервер реплицируются данные с нарушением внешних ключей:
```sql
=> CREATE PUBLICATION books_pub FOR TABLE books, authors, authorships;
```
```sql
CREATE PUBLICATION
```
Как только мы настроим репликацию справочников, на второй сервер в том числе будут передаваться и изменения наличного количества (books.onhand\_qty). Очевидно, что это неудачное решение: такие изменения не нужны второму серверу, наличное количество вычисляется на нем независимо от основного сервера.
Следовало бы поместить наличное количество в отдельную таблицу, изменив соответствующим образом триггер update\_onhand\_qty\_trigger и интерфейсную функцию get\_catalog.
Другой способ — отменять нежелательные обновления с помощью триггера. Он не требует изменения остального кода системы, но не устраняет бесполезную пересылку данных по сети.
```sql
=> CREATE FUNCTION public.keep_onhand_qty() RETURNS trigger
AS $$
BEGIN
    NEW.onhand_qty := OLD.onhand_qty;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
```

## Page 21: Page 21 {#source-page-021}

```sql
CREATE FUNCTION
```
```sql
=> CREATE TRIGGER keep_onhand_qty_trigger
BEFORE UPDATE ON public.books
FOR EACH ROW
WHEN (NEW.onhand_qty IS DISTINCT FROM OLD.onhand_qty)
EXECUTE FUNCTION public.keep_onhand_qty();
```
```sql
CREATE TRIGGER
```
Созданный триггер будет срабатывать только при репликации и не будет мешать обновлять наличное количество при нормальной работе:
```sql
=> ALTER TABLE books ENABLE REPLICA TRIGGER keep_onhand_qty_trigger;
```
```sql
ALTER TABLE
```
Создаем подписку. Поскольку текущее состояние справочников уже попало на второй сервер из резервной копии, отключаем начальную синхронизацию данных:
```sql
=> CREATE SUBSCRIPTION books_sub
CONNECTION 'host=localhost port=5432 user=postgres password=postgres dbname=bookstore2'
PUBLICATION books_pub WITH (copy_data = false);
```
```sql
NOTICE:  created replication slot "books_sub" on publisher
CREATE SUBSCRIPTION
```

## Page 22: Практика+ {#source-page-022}

1. На двух серверах ведутся однотипные таблицы бухгалтерских транзакций. Требуется консолидировать все записи в ту же таблицу на одном из серверов (например, для целей построения общей отчетности). Предложите способ различить «свои» и «чужие» записи и предотвратить конфликты при логической репликации. Реализуйте предложенную схему. 2. Настройте логическую репликацию в обратном направлении. Проверьте работу двунаправленной репликации.

### Комментарии преподавателя

1. Исходите из следующего вида таблицы транзакций:

```sql
CREATE TABLE transactions (
  trx_id     bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  debit_acc  integer NOT NULL,
  credit_acc integer NOT NULL,
  amount     numeric(15,2) NOT NULL
);
```

2. Чтобы публикация не отправляла реплицированные изменения, задайте подписке параметр origin = none.

## Page 23: 1. Консолидация {#source-page-023}

На первом (публикующем) сервере установим необходимый уровень журнала:
```sql
=> ALTER SYSTEM SET wal_level = logical;
```
```sql
ALTER SYSTEM
```
```sh
student$ sudo pg_ctlcluster 16 main restart
```
Создадим таблицу транзакций:
```sh
student$ psql
```
```sql
=> CREATE DATABASE replica_overview_logical_dev;
```
```sql
CREATE DATABASE
```
```sql
=> \c replica_overview_logical_dev
```
```text
You are now connected to database "replica_overview_logical_dev" as user "student".
```
```sql
=> CREATE TABLE transactions (
    trx_id     bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    debit_acc  integer NOT NULL,
    credit_acc integer NOT NULL,
    amount     numeric(15,2) NOT NULL
);
```
```sql
CREATE TABLE
```
Чтобы отличать строки разных серверов, понадобится дополнительный столбец. Например:
```sql
=> ALTER TABLE transactions
    ADD server text NOT NULL DEFAULT current_setting('cluster_name');
```
```sql
ALTER TABLE
```
Чтобы не допустить конфликта значений первичного ключа, выделим каждому из серверов свое множество значений: первому серверу — нечетные, второму — четные. Имя последовательности, которая используется для генерации уникальных номеров, можно узнать с помощью функции:
```sql
=> SELECT pg_get_serial_sequence('transactions','trx_id');
```
```text
     pg_get_serial_sequence
--------------------------------
 public.transactions_trx_id_seq
(1 row)
```
```sql
=> ALTER SEQUENCE transactions_trx_id_seq
    START WITH 1 INCREMENT BY 2;
```
```sql
ALTER SEQUENCE
```
Недостатком такой схемы является то, что при появлении третьего сервера ее придется перестраивать.
Другой возможный вариант — использовать для первичного ключа универсальные уникальные идентификаторы, представленные типом данных uuid. Для этого можно использовать встроенную функцию get\_random\_uuid или средства из расширений pgcrypto и uuid-ossp:
```sql
=> SELECT gen_random_uuid();
```
```text
           gen_random_uuid
--------------------------------------
 095380f6-8251-47ec-94a8-7b29d0584bbd
(1 row)
```
Однако тип uuid занимает 16 байт (bigint — только 8) и генерация нового значения происходит относительно долго.
Еще один вариант — создать составной первичный ключ:
```text
PRIMARY KEY (trx_id, server)
```
В этом случае конфликтов гарантированно не будет, но индекс получится больше.
Мы используем первый вариант: последовательности, генерирующие разные значения.

## Page 24: Page 24 {#source-page-024}

Теперь развернем второй сервер из резервной копии, как показано в демонстрации.
```sh
student$ pg_basebackup --pgdata=/home/student/backup
```
```sh
student$ sudo pg_ctlcluster 16 replica stop
```
```text
Cluster is not running.
```
```sh
student$ sudo rm -rf /var/lib/postgresql/16/replica
```
```sh
student$ sudo mv /home/student/backup /var/lib/postgresql/16/replica
```
```sh
student$ sudo chown -R postgres: /var/lib/postgresql/16/replica
```
```sh
student$ sudo pg_ctlcluster 16 replica start
```
```sh
student$ psql -p 5433 -d replica_overview_logical_dev
```
Заполним таблицу транзакций тестовыми данными — разными на разных серверах.
```sql
=> INSERT INTO transactions(debit_acc, credit_acc, amount)
    SELECT trunc(random()*3),     -- 0..2
           trunc(random()*3) + 3, -- 3..5
           random()*100_000
    FROM generate_series(1,10_000);
```
```sql
INSERT 0 10000
```
```sql
=> SELECT * FROM transactions LIMIT 5;
```
```text
 trx_id | debit_acc | credit_acc |  amount  | server
--------+-----------+------------+----------+---------
      1 |         2 |          3 | 17415.03 | 16/main
      3 |         1 |          3 | 33835.53 | 16/main
      5 |         1 |          3 |  7237.65 | 16/main
      7 |         1 |          3 | 91030.49 | 16/main
      9 |         0 |          3 | 86661.64 | 16/main
(5 rows)
```
На втором сервере не забудем заменить настройки последовательности:
```sql
=> ALTER SEQUENCE transactions_trx_id_seq
    START WITH 2 INCREMENT BY 2 RESTART;
```
```sql
ALTER SEQUENCE
```
```sql
=> INSERT INTO transactions(debit_acc, credit_acc, amount)
    SELECT trunc(random()*3) + 3, -- 3..5
           trunc(random()*3),     -- 0..2
           random()*100_000
    FROM generate_series(1,10_000);
```
```sql
INSERT 0 10000
```
```sql
=> SELECT * FROM transactions LIMIT 5;
```
```text
 trx_id | debit_acc | credit_acc |  amount  |   server
--------+-----------+------------+----------+------------
      2 |         4 |          0 | 40795.00 | 16/replica
      4 |         5 |          2 | 32412.30 | 16/replica
      6 |         3 |          0 | 24097.54 | 16/replica
      8 |         4 |          2 | 57402.43 | 16/replica
     10 |         4 |          1 | 56227.21 | 16/replica
(5 rows)
```
Настроим репликацию с первого сервера на второй.
```sql
=> CREATE PUBLICATION trx_pub FOR TABLE transactions;
```
```sql
CREATE PUBLICATION
```
```sql
=> CREATE SUBSCRIPTION trx_sub
CONNECTION 'dbname=replica_overview_logical_dev'
PUBLICATION trx_pub;
```
```sql
NOTICE:  created replication slot "trx_sub" on publisher
CREATE SUBSCRIPTION
```
После небольшой паузы убедимся, что данные первого сервера успешно реплицированы:
```sql
=> SELECT server, count(*) FROM transactions GROUP BY server;
```

## Page 25: 2. Двунаправленная репликация {#source-page-025}

```text
   server   | count
------------+-------
 16/main    | 10000
 16/replica | 10000
(2 rows)
```
Настройка уровня журнала уже скопирована на второй сервер:
```text
=> SHOW wal_level;
```
```text
 wal_level
-----------
 logical
(1 row)
```
Теперь очистим таблицы и сбросим последовательности
```text
=> TRUNCATE TABLE transactions;
```
```text
TRUNCATE TABLE
```
```sql
=> SELECT setval('transactions_trx_id_seq',1);
```
```text
 setval
--------
      1
(1 row)
```
```text
=> TRUNCATE TABLE transactions;
```
```text
TRUNCATE TABLE
```
```sql
=> SELECT setval('transactions_trx_id_seq',2);
```
```text
 setval
--------
      2
(1 row)
```
Чтобы публикация не отправляла подписке реплицированные изменения, нужно задать для подписки параметр origin:
```sql
=> ALTER SUBSCRIPTION trx_sub SET (origin = none);
```
```sql
ALTER SUBSCRIPTION
```
Теперь настроим репликацию со второго сервера на первый.
```sql
=> CREATE PUBLICATION trx_pub FOR TABLE transactions;
```
```sql
CREATE PUBLICATION
```
```sql
=> CREATE SUBSCRIPTION trx_sub
CONNECTION 'port=5433 dbname=replica_overview_logical_dev'
PUBLICATION trx_pub
WITH (origin = none, copy_data = false);
```
```sql
NOTICE:  created replication slot "trx_sub" on publisher
CREATE SUBSCRIPTION
```
Проверим:
```sql
=> INSERT INTO transactions(debit_acc, credit_acc, amount)
    SELECT trunc(random()*3),     -- 0..2
           trunc(random()*3) + 3, -- 3..5
           random()*100_000
    FROM generate_series(1,5000);
```
```sql
INSERT 0 5000
```
```sql
=> INSERT INTO transactions(debit_acc, credit_acc, amount)
    SELECT trunc(random()*3) + 3, -- 3..5
           trunc(random()*3),     -- 0..2
           random()*100_000
    FROM generate_series(1,5000);
```
```sql
INSERT 0 5000
```
```sql
=> SELECT server, count(*) FROM transactions GROUP BY server;
```

## Page 26: Page 26 {#source-page-026}

```text
   server   | count
------------+-------
 16/main    |  5000
 16/replica |  5000
(2 rows)
```
```sql
=> SELECT server, count(*) FROM transactions GROUP BY server;
```
```text
   server   | count
------------+-------
 16/main    |  5000
 16/replica |  5000
(2 rows)
```
Удалим треть строк на каждом сервере:
```sql
=> DELETE FROM transactions WHERE trx_id % 3 = 1;
```
```sql
DELETE 3333
```
```sql
=> DELETE FROM transactions WHERE trx_id % 3 = 2;
```
```sql
DELETE 3333
```
```sql
=> SELECT server, count(*) FROM transactions GROUP BY server;
```
```text
   server   | count
------------+-------
 16/main    |  1667
 16/replica |  1667
(2 rows)
```
```sql
=> SELECT server, count(*) FROM transactions GROUP BY server;
```
```text
   server   | count
------------+-------
 16/main    |  1667
 16/replica |  1667
(2 rows)
```
Репликация работает.
Удалим подписки, чтобы не оставлять активные слоты репликации.
```sql
=> DROP SUBSCRIPTION trx_sub;
```
```sql
NOTICE:  dropped replication slot "trx_sub" on publisher
DROP SUBSCRIPTION
```
```sql
=> DROP SUBSCRIPTION trx_sub;
```
```sql
NOTICE:  dropped replication slot "trx_sub" on publisher
DROP SUBSCRIPTION
```