[PostgreSQL, Администрирование баз данных, Data Engineering] Логическая репликация в PostgreSQL. Репликационные идентификаторы и популярные ошибки

Ответить на тему

Автор

Сообщение

news_bot ^®

Стаж: 6 лет 9 месяцев
Сообщений: 27286

news_bot ^® написал(а)
01-Мар-2021 01:32

Цитировать

Начиная с 10 версии, перенести данные с одной базы PostgreSQL на другую несложно, с обновлением, без обновления — неважно. Об этом немало сказано и сказанное сводится к следующему: на мастере, 10 версии и выше, устанавливаем параметр конфигурации wal_level="logical". В pg_hba.conf добавляем такую строку:
host db_name postgres 192.168.1.3/32 trust
Затем рестартуем на мастере postgres и выполняем на реплике из-под пользователя postgres:

pg_dumpall --database=postgres --host=192.168.1.2 --no-password --globals-only --no-privileges | psql
pg_dump --dbname db_name --host=192.168.1.2 --no-password --create --schema-only | psql

Теперь подключаемся на мастере пользователем postgres к базе db_name и создаём публикацию:

CREATE PUBLICATION db_pub FOR ALL TABLES;

а на реплике создаём подписку:

CREATE SUBSCRIPTION db_sub CONNECTION 'host=192.168.1.2 dbname=db_name' PUBLICATION db_pub;

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

Передача подписчикам инкрементальных изменений в одной базе данных или подмножестве базы данных, когда они происходят.
Срабатывание триггеров для отдельных изменений, когда их получает подписчик.
Объединение нескольких баз данных в одну (например, для целей анализа).
Репликация между разными основными версиями PostgreSQL.
Репликация между экземплярами PostgreSQL на разных платформах (например, с Linux на Windows).
Предоставление доступа к реплицированным данным другим группам пользователей.
Разделение подмножества базы данных между несколькими базами данных.

Логическая репликация удобна тем, что вначале переносится схема данных. На ней можно тестировать возможность репликации заливкой данных с предварительно восстановленных резервных копий; проверять гипотезы по устранению возникающих при репликации проблем и имеющихся в базе ошибок проектирования. Также в этом случае появляется возможность внести и проверить такие изменения в схеме, которые на рабочей системе делать опасно.
Это тот момент, когда можно сказать — «А помните, мы хотели уменьшить объём базы за счёт выравнивания? Давайте сейчас столбцы и перераспределим!». Также на стороне реплики возможно, например, провести перераспределение данных из одного столбца типа JSON в несколько других столбцов, или даже таблиц, либо наоборот, после чего заполнять уже доработанную и протестированную базу, при необходимости обрабатывая данные напильником триггерами. Можно какие-то поля отправить в TOAST, а какие-то наоборот — достать. В некоторых пределах можно поменять типы значений в столбцах. Также причиной выбора является возможность провести практически бесшовное обновление, одновременно с котором допустимо некоторое изменение схемы данных, а при некотором усердии — кардинальное перекраивание схемы данных. В общем, к списку добавляется один пункт:

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

Но, как и любой другой инструмент, логическая репликация имеет, помимо преимуществ, ещё и ограничения и недостатки. Знать их не помешает, ведь приведённый над катом пример будет работать без проблем только в сферической учебной БД.
Документация и примечания к выпускам
Надо помнить, что поведение СУБД в различных мажорных версиях может заметно разниться. Поэтому перед обновлением, да и вообще, важно ознакомиться с документацией и списком изменений (10, 11, 12, 13) и определить, какие из них могут изменить поведение вашей БД.
Здесь не будет рассказываться о создании публикаций и подписок, всё это есть в документации. Статья носит обзорный характер и не служит заменой документации.
Примечание
В статье приводится много примеров для воспроизведения которых лучше использовать psql, так как pgcli работает немного по-другому и перетащенный туда скрипт работает с ущербом для наглядности. Также создайте базу test, а в ней схему ts:

CREATE DATABASE test;
\c test
CREATE SCHEMA ts;
REVOKE ALL PRIVILEGES
ON DATABASE test
FROM public; -- ведь у вас на проде всё точно так же?
---

Не все скрипты можно перетаскивать как есть, в некоторых придётся поменять IP-адреса.
Приведённые примеры кода ни в коем случае не выполняйте на экземплярах СУБД, которые кем-либо используются. Некоторые примеры приведены для демонстрации падения СУБД и могут принести немало неприятностей. Лучше всего создать пару ВМ и экспериментировать на них.
Обновление до последней корректирующей (минорной) версии
Первое, что нужно запланировать и сделать при подготовке к использованию логической репликации — обновиться на последнюю минорную версию, особенно если в создаваемых публикациях планируется использовать предложение FOR ALL TABLES.
Почему стоит обновиться? Например, поэтому:

В версиях 10.8 и 11.3 был исправлен баг с обработкой изменений, вносящихся во временные и нежурналируемые таблицы. Данные в таких таблицах в логической репликации не участвуют, поэтому им не требуется настройка репликационных идентификаторов, но, при попытке обновить в таких таблицах данные, сервер выдавал сообщение об ошибке: ERROR: cannot update table "logical_replication_test" because it does not have a replica identity and publishes updates и отменял транзакцию. Хорошего в этом мало, поэтому, если ваше приложение использует временные или нежурналируемые таблицы, то обновление обязательно;
В версиях 10.11 и 11.6 был устранён вывод ошибки в случае, когда состав столбцов идентификации на мастере и на реплике различался. Правда и репликация изменения или удаления строк в таком случае прекращается;
В версиях 10.12, 11.7, 12.2 был устранено несколько багов, которые приводили к невозможности значительно изменять схему таблиц на реплике по сравнению со схемой таблиц на мастере. Например, на реплике нельзя было создавать дополнительные столбцы с функцией в качестве значения по умолчанию ("… clmname numeric DEFAULT random() ...").
В версиях 10.16, 11.11, 12.6 13.2 устранили утечки памяти в процессах walsender при передаче новых снимков для логического декодирования

Как видите, для снижения километража истрёпанных нервов, предпочтительнее обновиться, пусть это и займёт некоторое время.
Создание ролей и строк аутентификации в pg_hba.conf
После минорного обновления следует создать те роли, которые планируется использовать для создания публикации и подписки. При этом требования к ролям на мастере и реплике немного различаются.
В целом на реплике можно вообще не создавать отдельную роль, так как создание подписки разрешается только суперпользователям и применение полученных изменений происходит с правами суперпользователя. Поэтому на реплике можно пользоваться ролью postgres.
На мастере быть суперпользователем не обязательно, но если планируется обновление, то этот вариант удобнее. Конечно, можно обойтись и без выделенной роли, но на мастере роль предпочтительно создать для того, чтоб ограничить возможность подключения только тем сервером, на который происходит репликация.
Создать роль можно такой командой:

SET password_encryption = 'scram-sha-256';
CREATE ROLE log_rep_rolename
WITH SUPERUSER
LOGIN
PASSWORD 'sadp!'; -- sadp! = StrongAndDifficultPassword!
-- По возможности избегайте кавычек (', ") в пароле,
-- его предстоит в строке подключения указывать.

В pg_hba.conf на мастере нужно добавить две записи: одну для локального подключения, другую для подключения с реплики. Предпочтительно указывать точные адреса реплик — лучше сто записей в pg_hba, чем одна дыра в безопасности.
host test log_rep_rolename 127.0.0.1/32 scram-sha-256
host test log_rep_rolename 192.168.122.95 255.255.255.255 scram-sha-256
Обратите внимание что, несмотря на то что логическая репликация основана в значительной мере на потоковой, указывается не специальная запись replication, а имя базы или all — указание на все базы. Если указать replication, то создать подписку на реплике не получится из-за ошибки аутентификации.
После этого перезагружаем настройки из-под суперпользователя:

SELECT pg_reload_conf();
---

Репликационные идентификаторы
Весь процесс логической репликации в принципе строится на идее репликационных идентификаторов. Поэтому дальнейшая подготовка состоит в проверке наличия во всех реплицируемых таблицах либо первичного ключа, либо индекса, соответствующего некоторым минимальным требованиям и задействованного в REPLICA IDENTITY USING INDEX, либо назначении REPLICA IDENTITY FULL. То есть проверка наличия в таблицах репликационных идентификаторов. Они нужны для однозначной идентификации изменяемых или удаляемых строк при репликации команд UPDATE и DELETE и передаются на реплику в специальном поле для каждой записи.
Репликационные идентификаторы можно не настраивать, или даже отключить, если планируется реплицировать только команды INSERT. Главное не забыть правильно создать публикацию — исключить из неё команды UPDATE и DELETE. Но если вам на реплике нужны актуальные данные из активно изменяющихся таблиц, а первичные ключи или уникальные NOT NULL индексы в таблицах отсутствуют, то репликационные идентификаторы придётся настраивать с нуля. Не выполнив это условие, можно добиться того, что UPDATE и DELETE будут приводить к отмене транзакций на мастере, малоприятный факт на рабочей базе.

Поиск таблиц, не имеющих репликационных идентификаторов

SPL

SELECT pgn.nspname || '.' || pgc.relname AS "Таблицы без репликационных идентификаторов"
FROM pg_class AS pgc,
pg_namespace AS pgn
WHERE pgn.nspname !~ '^(?:pg_.*|information_schema)$'
AND pgc.relreplident IN ('n', 'd')
AND pgc.relkind IN ('r', 'p')
AND pgc.oid NOT IN (SELECT pgi.indrelid FROM pg_index AS pgi WHERE pgi.indisprimary)
AND pgc.relnamespace = pgn.oid
ORDER BY 1;
---

Что может выступать в качестве репликационного идентификатора
Как понятно из названия репликационных идентификаторов — они должны идентифицировать строки таблиц. Если в вашей таблице есть поле или комбинация полей, которая никогда не повторяется и поэтому позволяет идентифицировать строку, её следует либо объявить первичным ключом, либо создать по ней уникальный индекс.
Если столбцов пригодных к роли репликационных идентификаторов нет, что очень странно, то придётся их создавать. Каждое значение в таком столбце должно быть уникальным. В качестве источника уникальности могут выступать как естественные, так и искусственные ключи и их комбинации — зависит от архитектуры базы данных. При этом неважно что именно использовать в качестве естественных ключей, главное, чтоб они выполняли роль однозначного идентификатора. В качестве искусственных ключей используются, как правило, различные последовательности и типы UUID.
Последовательности
Использовать последовательности при создании репликационных идентификаторов можно двумя с половиной способами: ручное указание вызова функции получения следующего значения последовательности, например nextval; назначение столбцу последовательного псевдотипа serial; использование столбцов идентификации в соответствии со стандартом SQL. Вполне рабочим вариантом может быть отсутствие значения по умолчанию, ведь можно возложить эту обязанность на приложение, но столбец должен быть NOT NULL.
SEQUENCE и serial
Наиболее гибким и мощным является использование различных SEQUENCE. В таком случае, после создания последовательности, значения по умолчанию для столбцов необходимо прописывать самостоятельно. При необходимости назначить таблицу-владельца последовательности также придётся поработать руками.
Тип serial это синтаксический сахар для обычного способа создания последовательностей, этакий шаблон. Всё что нужно сделать, это назначить столбцу тип smallserial/serial/bigserial.
Использование типа serial менее гибко, но его использование позволяет избавиться от необходимости создавать последовательности вручную. Также такой последовательности автоматически назначается свойство OWNED BY. Это указание на столбец таблицы, при создании которого была создана последовательность.
Такую последовательность в дальнейшем нельзя удалить, не удалив это указание. И здесь кроется опасность — удалив такую последовательность с указанием ключевого слова CASCADE можно устроить локальный армагеддон. В привязанном к удалённой последовательности столбце останется включенным свойство NOT NULL, а вот свойство DEFAULT обнулится.
У полей serial есть ещё одна неприятная особенность — пользователь без права на использование автоматически созданной последовательности, и с правами на INSERT в таблицу, практически вставку выполнять не сможет, если только не укажет значение поля serial вручную. Если не укажет, то получит ошибку доступа к соответствующей последовательности. В принципе это не проблема, нужно не забывать давать права на использование последовательности вместе с правами на вставку в таблицу.
Несколько таблиц на одной последовательности
Если нужно подключить к одной последовательности несколько таблиц, — делать это нужно самостоятельно. Для этого задаётся получение nextval(нужная_последовательность) в свойстве DEFAULT интересующего вас столбца. Воспользоваться можно и той последовательностью, что была создана с использованием serial — никто не мешает вручную прописать её для других таблиц, разве что потом будут некоторые проблемы с удалением самой первой таблицы: нужно будет поменять или удалить ссылку на таблицу-владельца такой последовательности: ALTER SEQUENCE name_of_your_seq OWNED BY NONE.

Воспроизведение

SPL

CREATE TABLE t1 (i serial, t text);
CREATE TABLE t2 (i int NOT NULL DEFAULT nextval('t1_i_seq'), t text);
INSERT INTO t2 (t) VALUES('The first value in table t2');
INSERT INTO t1 (t) VALUES('The first value in table t1');
SELECT * FROM t1;
SELECT * FROM t2;
\d t2
DROP TABLE t1;
DROP SEQUENCE t1_i_seq;
DROP TABLE t1 CASCADE;
INSERT INTO t2 (t) VALUES('Maybe the second value in table t2');
\d t2
DROP TABLE t2;
---

Столбцы идентификации
Пришедшие из стандарта SQL cтолбцы идентификации задаются либо при создании таблицы, либо ими могут стать имеющиеся столбцы, либо можно добавить такие столбцы отдельно.
Последовательность, созданную для столбца идентификации, в отличие от первых полутора вариантов, не стоит использовать в других таблицах. В дальнейшем это помешает удалить исходную таблицу, а если удалить её с предложением CASCADE, то свойство DEFAULT у таблиц использовавших эту последовательность обнулится. При этом свойство NOT NULL никуда не денется.
В результате появится шанс наблюдать на мастере орды null value in column "i" violates not-null constraint. С последовательностями, созданными с помощью serial тоже такое бывает, но для них это исправимо — поменяйте принадлежность последовательности либо на нужный столбец нужной таблицы, либо сделайте её «бесхозяйной». С последовательностями столбцов идентификации это не работает.
Первичными ключами столбцы идентификации автоматически не становятся, это просто синтаксис назначения столбцу особых свойств, несколько отличающихся от обычных последовательностей. В частности, при типе serial вы можете спокойно проводить вставку любых произвольных значений в ключевые поля, за исключением имеющихся конечно (если на столбце включен PRIMARY KEY).
Понятно, что это приведёт к тому, что однажды последовательность выдаст вставленные ранее произвольные значения и получившая их транзакция прервётся с ошибкой duplicate key. Использование столбцов идентификации позволит не беспокоиться о таком развитии событий — в столбцы идентификации, созданные с ключом ALWAYS, вставить произвольное число не так просто, нужно использовать специальную форму команды INSERT. При создании таких столбцов поддерживаются те же параметры, что и при создании обычной последовательности.
Universally Unique IDentifiers
Если назначить столбцу тип UUID, то значение для такого столбца не будет генерироваться автоматически. Для получения нового значения UUID необходимо пользоваться одним из двух дополнительных модулей: uuid-ossp или pgcrypto
В отличие от последовательностей UUID имеет длину не 16/32/64 бита, а 128 бит, — что нужно учитывать при расчётах нагрузки на сетевую подсистему. Зато у него есть то преимущество, что UUID генерирует такие строки, содержимое которых не повторяется в распределённых системах.
К слову, при использовании последовательностей можно использовать независимые последовательности с добавлением префикса, уникального для каждого участвующего во взаимообмене данными сервера. Столбец в таком случае придётся сделать текстовым, но даже так получится экономичнее.
В простейшем случае получать значения UUID можно через расширение pg_crypto. В нём есть только одна функция получения UUID:

CREATE EXTENSION pgcrypto;
SELECT gen_random_uuid();

Модуль uuid-ossp, в отличие от pg_crypto предоставляет больше возможностей по части выбора типа UUID. Если PostgreSQL установлен из пакетов, то можно сразу устанавливать расширение, только его имя обязательно нужно заключить в двойные кавычки, так как оно содержит дефис. Если собираете PostgreSQL из исходников, то нужно воспользоваться ключом --with-uuid=ossp (работает в Debian, как в RHEL — не знаю). Для этого, в дополнение к уже установленному постгресу, понадобится поставить несколько пакетов:

# Debian Buster
# пакет postgresql-10 можно установить до
# или после компиляции и установки расширения.
sudo apt-get install gcc libossp-uuid-dev libreadline-dev make zlib1g-dev
wget https://ftp.postgresql.org/pub/source/v10.16/postgresql-10.16.tar.gz
gunzip postgresql-10.16.tar.gz
tar xf postgresql-10.16.tar
cd postgresql-10.16
./configure --with-uuid=ossp
make
make install

CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v1();
SELECT uuid_generate_v1mc();
SELECT uuid_generate_v3(uuid_ns_url(), 'https://postgrespro.ru/');
SELECT uuid_generate_v4();
SELECT uuid_generate_v5(uuid_ns_dns(), 'postgrespro.ru');

Первичные ключи и уникальные индексы
Определившись с источниками уникальности нужно указать какой столбец, или какие столбцы, надлежит использовать как репликационные идентификаторы. По умолчанию ими являются первичные ключи. Они, как и уникальные индексы, позволяют добавлять на стороне реплики произвольное число столбцов, не опасаясь приостановки репликации при операциях UPDATE и DELETE.
Главное, чтобы у добавленных столбцов не было назначено свойство NOT NULL с отсутствующим значением по умолчанию — при начальной репликации и репликации команды INSERT возникнет ошибка на стороне реплики, устранять которую возможно только на стороне реплики. На стороне мастера тут уже ничего не поделаешь, разве что удалить слот репликации — чтоб журнал предзаписи не переполнялся, и мастер не создавал каждые пять секунд процесс декодирования. Также можно запретить доступ через pg_hba.conf или на балансировщике, затем исправить неполадки на реплике и снова разрешить доступ.
Перед началом репликации таблицы первичные ключи должны быть настроены с обоих сторон, а имеющиеся в реплицируемой таблице столбцы, даже не являющиеся репликационными идентификаторами, должны присутствовать и в реплицированной таблице. То есть на мастере нельзя удалять столбцы, если они есть на реплике.
При непосредственном создании первичных ключей таблица блокируется на запись блокировкой SHARE ROW EXCLUSIVE. Поэтому предпочтительно использовать предварительное создание уникального индекса в режиме CONCURRENTLY и затем уже привязку его в качестве первичного ключа. Да — это ресурсоёмкая операция, зато доступность БД на запись не страдает.
Обратите внимание на то, что если вы создаёте первичный ключ на столбце, не имеющем свойства NOT NULL, то такое свойство будет создано автоматически, но после после удаления ограничения автоматически не удалится.

Демонстрация появления ограничения NOT NULL

SPL

CREATE TABLE ts.testt (i serial, clmn_1 text, clmn_2 int);
\d ts.testt
/* Table "ts.testt"
Column | Type | Nullable | Default
--------+---------+----------+---------------------------------
i | integer | not null | nextval('testt_i_seq'::regclass)
clmn_1 | text | |
clmn_2 | integer | | */
ALTER TABLE ts.testt ADD PRIMARY KEY (i, clmn_1);
\d ts.testt
/* Table "ts.testt"
Column | Type | Nullable | Default
--------+---------+----------+---------------------------------
i | integer | not null | nextval('testt_i_seq'::regclass)
clmn_1 | text | not null |
clmn_2 | integer | |
Indexes:
"testt_pkey" PRIMARY KEY, btree (i, clmn_1) */
ALTER TABLE ts.testt DROP CONSTRAINT testt_pkey;
\d ts.testt
/* Table "ts.testt"
Column | Type | Nullable | Default
--------+---------+----------+---------------------------------
i | integer | not null | nextval('testt_i_seq'::regclass)
clmn_1 | text | not null |
clmn_2 | integer | | */
ALTER TABLE ts.testt ADD PRIMARY KEY (i);
\d ts.testt
/* Table "ts.testt"
Column | Type | Nullable | Default
--------+---------+----------+---------------------------------
i | integer | not null | nextval('testt_i_seq'::regclass)
clmn_1 | text | not null |
clmn_2 | integer | |
Indexes:
"testt_pkey" PRIMARY KEY, btree (i) */
ALTER TABLE ts.testt ALTER COLUMN clmn_1 DROP NOT NULL;
\d ts.testt
/* Table "ts.testt"
Column | Type | Nullable | Default
--------+---------+----------+---------------------------------
i | integer | not null | nextval('testt_i_seq'::regclass)
clmn_1 | text | |
clmn_2 | integer | |
Indexes:
"testt_pkey" PRIMARY KEY, btree (i) */
DROP TABLE ts.testt;
---

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

SPL

-- ПОСЛЕДОВАТЕЛЬНОСТИ
-- Сначала создаём последовательность
CREATE SEQUENCE ts.testt_sequence AS bigint
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
START WITH 1
OWNED BY NONE;
\ds+ ts.testt_sequence
-- Затем создаём таблицу
-- Либо сразу с последовательностью
CREATE TABLE ts.testt (
i bigint
PRIMARY KEY
NOT NULL
DEFAULT nextval('ts.testt_sequence'::regclass),
ac text);
\d+ ts.testt
DROP TABLE ts.testt;
-- Либо, если таблица и колонка уже существовали, добавляем недостающее
CREATE TABLE ts.testt (i bigint, ac text);
INSERT INTO ts.testt (i, ac) VALUES (1, '1'), (1, '2'), (2, '3'), (3, '4');
SELECT * FROM ts.testt;
ALTER TABLE ts.testt ALTER COLUMN i SET DEFAULT nextval('ts.testt_sequence');
INSERT INTO ts.testt (ac) VALUES ('5'), ('6'), ('7'), ('8');
SELECT * FROM ts.testt;
UPDATE ts.testt SET i = nextval('ts.testt_sequence'::regclass);
SELECT * FROM ts.testt;
ALTER TABLE ts.testt ALTER COLUMN i SET NOT NULL;
ALTER TABLE ts.testt ADD PRIMARY KEY (i);
\d+ ts.testt
DROP TABLE ts.testt;
DROP SEQUENCE ts.testt_sequence;
--
-- C использованием типа serial можно не создавать последовательность,
-- она будет создана автоматически
CREATE TABLE ts.testt (
i bigserial PRIMARY KEY,
ac text);
\d+ ts.testt
-- В качестве первичного ключа можно задать несколько колонок
-- Сначала удалим старый первичный ключ
ALTER TABLE ts.testt DROP CONSTRAINT testt_pkey;
\d+ ts.testt
-- Затем создадим новый, двухколоночный.
ALTER TABLE ts.testt ADD PRIMARY KEY (i, ac);
\d+ ts.testt
DROP TABLE ts.testt;
--
-- СТОЛБЦЫ ИДЕНТИФИКАЦИИ
-- Можно сразу создать таблицу с соответствующими столбцами
CREATE TABLE ts.testt (
i bigint
GENERATED ALWAYS AS IDENTITY (
INCREMENT BY 1
MINVALUE 1
START WITH 1)
PRIMARY KEY,
ac text);
\d+ ts.testt
\d ts.testt_i_seq
-- Меняем ALWAYS на BY DEFAULT
ALTER TABLE ts.testt ALTER COLUMN i SET GENERATED BY DEFAULT;
\d+ ts.testt
\d ts.testt_i_seq
-- Удаляем IDENTITY со столбца
ALTER TABLE ts.testt ALTER COLUMN i DROP IDENTITY;
\d+ ts.testt
\d ts.testt_i_seq
-- Можно добавить IDENTITY имеющемуся столбцу
ALTER TABLE ts.testt
ALTER COLUMN i ADD GENERATED ALWAYS AS IDENTITY (START WITH 1001);
\d+ ts.testt
\d ts.testt_i_seq
SELECT * FROM ts.testt_i_seq;
-- Можно добавить новый столбец с IDENTITY
ALTER TABLE ts.testt
ADD COLUMN impk bigint GENERATED ALWAYS AS IDENTITY;
\d+ ts.testt
\d ts.testt_impk_seq
-- В этот раз создадим первичный ключ через
-- предварительное создание уникального индекса
-- который создадим неблокирующим способом:
CREATE UNIQUE INDEX CONCURRENTLY testt_hm_idx ON ts.testt (i, impk);
ALTER TABLE ts.testt DROP CONSTRAINT testt_pkey,
ADD CONSTRAINT hm_pkey PRIMARY KEY USING INDEX testt_hm_idx;
\d+ ts.testt
DROP TABLE ts.testt;
---

Уникальные индексы
Наравне с первичными ключами можно использовать уникальные индексы, но их использование в качестве идентификатора репликации необходимо указывать явно с помощью предложения ... REPLICA IDENTITY USING INDEX name_of_index ... и с ними нужно быть поосторожнее. В отличие от первичных ключей столбцы, на которых строится индекс, нужно самостоятельно снабдить ограничением NOT NULL.
Требования к индексам перечислены в документации: индекс должен быть уникальным, не частичным, не отложенным и включать только столбцы, помеченные NOT NULL. Состав столбцов, по которым построен индекс на мастере, должен совпадать с их составом на реплике. Если в нужных таблицах в реплицируемой схеме нет первичных ключей — можно на мастере создать уникальный индекс, назначить его репликационным идентификатором, а после переноса схемы на реплику — создать там идентичный по составу и порядку столбцов первичный ключ. Можно и наоборот.

Пример репликации, где на мастере индекс, а на реплике ПК

SPL

-- Подготавливаем мастер
CREATE TABLE ts.testt (i serial, t text NOT NULL DEFAULT random(), d text DEFAULT random());
CREATE UNIQUE INDEX CONCURRENTLY testt_idx ON ts.testt USING btree (i, t);
ALTER TABLE ts.testt REPLICA IDENTITY USING INDEX testt_idx;
INSERT INTO ts.testt (t, d) VALUES ('001', 'first'), ('002', 'second'), ('003', 'third');
CREATE PUBLICATION testt_pub for TABLE ts.testt;
SELECT * FROM ts.testt;
-- Подготавливаем реплику
CREATE TABLE ts.testt (i serial, t text NOT NULL DEFAULT random(), d text DEFAULT random());
ALTER TABLE ts.testt ADD PRIMARY KEY (t, i);
CREATE SUBSCRIPTION testt_sub
CONNECTION 'host=192.168.122.182
dbname=test
user=log_rep_rolename
password=sadp!'
PUBLICATION testt_pub;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt;
-- Выполняем на мастере вставку строки
INSERT INTO ts.testt (t, d) VALUES ('004', 'fourth');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT * FROM ts.testt;
-- Выполняем на мастере UPDATE
UPDATE ts.testt SET t = t || ' upd' WHERE random() >= 0.5;
INSERT INTO ts.testt (t, d) VALUES ('005', 'AFTER UPDATE');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT * FROM ts.testt;
-- Выполняем на мастере DELETE
DELETE FROM ts.testt WHERE random() >= 0.5;
INSERT INTO ts.testt (t, d) VALUES ('006', 'AFTER DELETE');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT * FROM ts.testt;
-- Чистим реплику
DROP SUBSCRIPTION IF EXISTS testt_sub;
DROP TABLE IF EXISTS ts.testt;
-- Чистим мастер
DROP PUBLICATION IF EXISTS testt_pub;
DROP TABLE IF EXISTS ts.testt;
---

REPLICA IDENTITY FULL
В отличие от первичных ключей и индексов при REPLICA IDENTITY FULL идентификатором служит вся строка. В этом случае в журнал предзаписи попадает вся старая строка и по протоколу репликации также передаётся вся старая строка. Не передаются только значения полей TOAST — если изменения их не коснулись. Исходя из этого можно представить, насколько разрастается объём хранимых и передаваемых данных.
Поэтому от применения REPLICA IDENTITY FULL нужно максимально воздерживаться. Мало того — ошибки в его использовании могут привести к необходимости рестарта репликации. Когда используется REPLICA IDENTITY FULL, то состав столбцов в таблице на мастере и реплике преимущественно должен совпадать (порядок столбцов значения не имеет), иначе изменение и удаление данных реплицироваться не будет и последствия будут различаться в зависимости от того, где есть лишние столбцы — на мастере или на реплике.
Если лишние столбцы будут на мастере и публикация будет создана для команд UPDATE и DELETE — репликация приостановится до тех пор, пока на реплике будут отсутствовать нужные столбцы.
Вставка данных и начальная синхронизация не так строго ограничены по части состава столбцов — на реплике могут быть дополнительные столбцы, и они даже могут содержать какие-то данные. Но репликация будет идти благополучно для всех команд только если добавленные на реплике столбцы не содержат данных. Это связано с тем, что поля со значением NULL в идентификации строк не участвуют.
Однако пользы от такого способа добавления столбцов немного — при обычном добавлении столбцов, без данных, перезаписи таблицы не происходит, поэтому добавлять пустые столбцы можно и на рабочей базе.
Если же нужно менять данные на реплике, но на мастере нет возможности создать первичный ключ — его можно создать на реплике, тогда репликация всех команд будет происходить благополучно, хоть в дополнительных столбцах реплики и будут данные. Это работает, потому что в сообщениях протокола логической репликации, при REPLICA IDENTITY FULL на мастере, в качестве идентификатора отправляется вся строка старых данных и процесс применения сообщений выбирает из него значение того поля, которое на реплике является полем первичного ключа.
REPLICA IDENTITY FULL удобно использовать для трансляции небольших, редко изменяемых таблиц или для таблиц с небольшим размером строк — им не нужны индексы на мастере и данные очень быстро пишутся и отправляются. На реплике же можно и индексы строить, и первичные ключи создавать — вполне удобно. Но для обновляемых данных в масштабных таблицах применять его очень опрометчиво.
Включить для таблицы этот идентификатор крайне просто:

ALTER TABLE ts.testt REPLICA IDENTITY FULL;

Работа с REPLICA IDENTITY FULL

SPL

-- Подготавливаем мастер
CREATE TABLE ts.testt (i serial, t text, d text DEFAULT now());
ALTER TABLE ts.testt REPLICA IDENTITY FULL;
INSERT INTO ts.testt (t) VALUES ('first'), ('second'), ('third');
CREATE PUBLICATION testt_pub for TABLE ts.testt;
SELECT * FROM ts.testt;
-- Подготавливаем реплику
CREATE TABLE ts.testt (i serial, t text, d text);
ALTER TABLE ts.testt REPLICA IDENTITY FULL;
CREATE SUBSCRIPTION testt_sub
CONNECTION 'host=192.168.122.182
dbname=test
user=log_rep_rolename
password=sadp!'
PUBLICATION testt_pub;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt;
-- Выполняем на мастере вставку строки
INSERT INTO ts.testt (t) VALUES ('INSERTED');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT * FROM ts.testt;
-- Выполняем на мастере UPDATE
UPDATE ts.testt SET t = t || ' upd' WHERE i = 1;
INSERT INTO ts.testt (t) VALUES ('AFTER UPDATE');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT * FROM ts.testt;
-- Выполняем на мастере DELETE
DELETE FROM ts.testt WHERE i = 1;
INSERT INTO ts.testt (t) VALUES ('AFTER DELETE');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT * FROM ts.testt;
-- Чистим реплику
DROP SUBSCRIPTION IF EXISTS testt_sub;
DROP TABLE IF EXISTS ts.testt;
-- Чистим мастер
DROP PUBLICATION IF EXISTS testt_pub;
DROP TABLE IF EXISTS ts.testt;
---

Если на мастере REPLICA IDENTITY FULL задана, то на реплике её наличие роли не играет — но только при условии, что состав столбцов таблицы на реплике идентичен таковому на мастере. Иначе на реплике будут применяться только команды INSERT, а если у вас 11 версия мастера — то и TRUNCATE. Команды UPDATE и DELETE будут применяться только если дополнительные столбцы в изменяемой/удаляемой строке будут равны NULL.

Пример реплики с REPLICA IDENTITY NOTHING

SPL

-- Подготавливаем мастер
CREATE TABLE ts.testt (i serial,
t text,
d text);
ALTER TABLE ts.testt REPLICA IDENTITY FULL;
INSERT INTO ts.testt (t, d) VALUES ('001', 'first'), ('002', 'second'), ('003', 'third');
CREATE PUBLICATION testt_pub for TABLE ts.testt;
SELECT * FROM ts.testt;
-- Подготавливаем реплику
SELECT pg_reload_conf();
CREATE TABLE ts.testt (d text,
t text,
i serial);
ALTER TABLE ts.testt REPLICA IDENTITY NOTHING;
CREATE SUBSCRIPTION testt_sub
CONNECTION 'host=192.168.122.182
dbname=test
user=log_rep_rolename
password=sadp!'
PUBLICATION testt_pub;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt;
-- Выполняем на мастере INSERT, UPDATE, DELETE
INSERT INTO ts.testt (t, d) VALUES ('004', 'fourth');
UPDATE ts.testt SET t = t || ' upd' WHERE i = 4;
DELETE FROM ts.testt WHERE i = 1;
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT * FROM ts.testt;
-- Чистим реплику
DROP SUBSCRIPTION IF EXISTS testt_sub;
DROP TABLE IF EXISTS ts.testt;
-- Чистим мастер
DROP PUBLICATION IF EXISTS testt_pub;
DROP TABLE IF EXISTS ts.testt;
---

Если нужно реплицировать исторические данные или провести только начальную синхронизацию — можно вообще убрать идентификаторы репликации:

ALTER TABLE ts.testt REPLICA IDENTITY NOTHING;

это снизит нагрузку на журнал предзаписи, декодирование и сеть. Но нужно обязательно создавать публикацию только для команд INSERT, иначе обновление и удаление в этой таблице работать перестанут, независимо от того — есть подписка или нет.
Состав и порядок столбцов
Вне зависимости от выбранных репликационных идентификаторов — порядок столбцов, даже ключевых — значения практически не имеет. Начальная синхронизация и репликация команд INSERT проходят без проблем, независимо от настройки репликационных идентификаторов — идентификаторы строк в этом случае не передаются.
Если же бездумно добавлять столбцы к таблице с той или другой стороны, то, например, при REPLICA IDENTITY FULL, придётся перезапускать репликацию для такой таблицы. Причём, в зависимости от способа прицеливания в ногу, можно либо добиться неконсистентности на реплике, либо ещё и раздуть на мастере журнал предзаписи до невероятных размеров. Что, в одном случае, не даст возможности восстановить согласованность данных в проблемной таблице на реплике и приведёт к необходимости перезаливки данных. Во втором случае такая возможность останется (условно) и реализуется автоматически — после выявления и устранения причины такой ситуации, однако целостность данных после этого всё равно останется под сомнением.
С первичными ключами и индексами ситуация значительно лучше. Хоть на реплике и нельзя удалять столбцы если такие есть на мастере, зато появляется возможность добавлять новые. И не просто добавлять, а различными способами заполнять их данными, без опаски получить остановку репликации или неконсистентность.

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

SPL

-- Подготавливаем мастер
CREATE TABLE ts.testt (i serial,
t varchar NOT NULL DEFAULT random(),
d varchar DEFAULT random());
CREATE UNIQUE INDEX CONCURRENTLY testt_idx ON ts.testt USING btree (i, t);
ALTER TABLE ts.testt REPLICA IDENTITY USING INDEX testt_idx;
INSERT INTO ts.testt (t, d) VALUES ('001', 'first'), ('002', 'second'), ('003', 'third');
CREATE PUBLICATION testt_pub for TABLE ts.testt;
SELECT * FROM ts.testt;
-- Подготавливаем реплику (Обратите внимание на типы)
CREATE TABLE ts.testt (d text DEFAULT random(),
s text DEFAULT now(),
t text NOT NULL DEFAULT random(),
i bigserial);
CREATE UNIQUE INDEX CONCURRENTLY testt_idx ON ts.testt USING btree (t, i);
ALTER TABLE ts.testt REPLICA IDENTITY USING INDEX testt_idx;
CREATE SUBSCRIPTION testt_sub
CONNECTION 'host=192.168.122.182
dbname=test
user=log_rep_rolename
password=sadp!'
PUBLICATION testt_pub;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt;
-- Выполняем на мастере вставку строки
INSERT INTO ts.testt (t, d) VALUES ('004', 'fourth');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT * FROM ts.testt;
-- Выполняем на мастере UPDATE
UPDATE ts.testt SET t = t || ' upd' WHERE random() >= 0.5;
INSERT INTO ts.testt (t, d) VALUES ('005', 'AFTER UPDATE');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT * FROM ts.testt;
-- Выполняем на мастере DELETE
DELETE FROM ts.testt WHERE random() >= 0.5;
INSERT INTO ts.testt (t, d) VALUES ('006', 'AFTER DELETE');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT * FROM ts.testt;
-- Чистим реплику
DROP SUBSCRIPTION IF EXISTS testt_sub;
DROP TABLE IF EXISTS ts.testt;
-- Чистим мастер
DROP PUBLICATION IF EXISTS testt_pub;
DROP TABLE IF EXISTS ts.testt;
---

Склад грабель горизонтального хранения
Предупреждён — значит вооружен. Ниже приведены несколько самых распространённых сообщений об ошибках и просто общих рассуждений. Лучше прочитать эти сообщения здесь, чем на рабочих серверах — читаем и вооружаемся. Если у вас есть что-нибудь интересное на эту тему — расскажите в комментариях.
Если на мастере репликационные идентификаторы не заложены в бюджетПо умолчанию публикация создаётся для команд INSERT, UPDATE и DELETE (и TRUNCATE, начиная с 11 версии). При этом проверки идентификаторов репликации в целевых таблицах не происходит, от этого может получиться так, что они будут не у всех таблиц. Мало того — после создания публикации допускается сброс или удаление репликационного идентификатора:

ALTER TABLE tablename REPLICA IDENTITY DEFAULT;
ALTER TABLE tablename REPLICA IDENTITY NOTHING;

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

ERROR: cannot update table "tablename" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
ERROR: cannot delete from table "tablename" because it does not have a replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

Так будет в случае, когда публикация не создана исключительно для команд INSERT и, для одиннадцатой версии — TRUNCATE. Вариантов исправления такой ситуации два — изменить подписку на publish='insert' или добавить репликационные идентификаторы.

Воспроизведение

SPL

-- Подготавливаем мастер
CREATE TABLE ts.testt (i serial,
t varchar,
d varchar);
ALTER TABLE ts.testt REPLICA IDENTITY NOTHING;
INSERT INTO ts.testt (t, d) VALUES ('001', 'first'), ('002', 'second'), ('003', 'third');
CREATE PUBLICATION testt_pub for TABLE ts.testt;
SELECT * FROM ts.testt;
-- Проверяем INSERT
INSERT INTO ts.testt (t, d) VALUES ('004', 'fourth');
-- Проверяем UPDATE
UPDATE ts.testt SET t = t || ' upd' WHERE i = 3;
-- Проверяем DELETE
DELETE FROM ts.testt WHERE i = 4;
SELECT * FROM ts.testt;
-- Исправляем публикацию
ALTER PUBLICATION testt_pub SET (publish = 'insert'); -- v.10
--ALTER PUBLICATION testt_pub SET (publish = 'insert, truncate'); -- v. >=11
-- Проверяем UPDATE
UPDATE ts.testt SET t = t || ' upd' WHERE i = 3;
-- Проверяем DELETE
DELETE FROM ts.testt WHERE i = 4;
SELECT * FROM ts.testt;
-- Чистим мастер
DROP PUBLICATION IF EXISTS testt_pub;
DROP TABLE IF EXISTS ts.testt;
---

... not find row.. .

DEBUG: logical replication did not find row for update in replication target relation "your_table_name"
DEBUG: logical replication could not find row for delete in replication target relation "your_table_name"

Пример сообщений которые можно увидеть когда:
⁍ на реплике добавили столбец и его значение не NULL;
⁍ на реплике по какой-либо причине строки отсутствуют, а на мастере они есть;
⁍ на реплике были изменены строки;
⁍ на реплике нет строки с переданным значением репликационного идентификатора
Если на реплике сложилась одна из приведённых ситуаций, то, при обычном значении параметра log_min_messages=warning, этих сообщений в логах реплики не появится. От того и о пропавших данных можно узнать, когда будет уже очень поздно. А можно и не узнать. С точки зрения СУБД в таком поведении нет ничего предосудительного, возможно таков был план. Но если это не был план, то для приложения это катастрофа, так что тут нужно быть предусмотрительным и внимательным.
Наблюдать эти сообщения в логах возможно при log_min_messages=debug1. Однако так увеличится объём логов, но консистентности данных не прибавится — реплика, получив сообщение об изменениях, не смогла найти изменяемую/удаляемую строку и благополучно выкинула сообщение на свалку истории, а второго шанса ей не предоставится. Мастер не будет уведомлен об этом и в свои логи писать ничего не станет. Следует учесть, что в этом режиме в лог будет записываться строка подключения, вместе с именем пользователя и его паролем — сомнительное преимущество использования такого уровня сообщений журнала.
В данном случае можно радоваться хотя бы тому, что на мастере такое поведение почти никак не скажется. В этом случае реплика получает сообщения репликации, затем, не применив их, отчитывается мастеру, что всё хорошо и журнал предзаписи не разрастается. Конечно, после такого инцидента придётся выправлять сложившееся положение дел: 1. На мастере исключать таблицу из публикации;
2. На реплике обновлять подписку и вычищать таблицу;
3. Привести состав столбцов к единому виду;
4. Включать на мастере таблицу в публикацию;
5. Обновлять подписку на реплике и ждать перезаливки данных.
А всего нужно было — не добавлять столбцы в таблицу на стороне реплики если используется REPLICA IDENTITY FULL или не изменять, бездумно, данные на реплике.
В отличие от UPDATE и DELETE, операция INSERT и начальная синхронизация будут нормально обрабатываться, даже если на реплике есть столбцы отсутствующие на мастере.

Воспроизведение

SPL

-- Подготавливаем мастер
CREATE TABLE ts.testt (i serial,
t varchar,
d varchar);
ALTER TABLE ts.testt REPLICA IDENTITY FULL;
INSERT INTO ts.testt (t, d)
VALUES ('001', 'first'), ('002', 'second'), ('003', 'third');
CREATE PUBLICATION testt_pub for TABLE ts.testt;
SELECT * FROM ts.testt;
-- Подготавливаем реплику
SELECT setting AS lmm FROM pg_settings WHERE name = 'log_min_messages' \gset
ALTER SYSTEM SET log_min_messages=debug1;
SELECT pg_reload_conf();
CREATE TABLE ts.testt (i serial,
t varchar,
d varchar,
x numeric);
CREATE SUBSCRIPTION testt_sub
CONNECTION 'host=192.168.122.182
dbname=test
user=log_rep_rolename
password=sadp!'
PUBLICATION testt_pub;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt;
-- Выполняем на мастере UPDATE и DELETE
UPDATE ts.testt SET t = t || ' upd' WHERE i = 2;
DELETE FROM ts.testt WHERE i = 3;
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
-- Заодно меняем одну строчку и удаляем другую
SELECT * FROM ts.testt;
UPDATE ts.testt SET t = 'updated on the replica' WHERE i = 1;
DELETE FROM ts.testt WHERE i = 2;
SELECT * FROM ts.testt;
-- На мастере обновляем строку которой уже нет на реплике
-- а удаляем ту, которая есть, но уже отличается
-- от версии на мастере. Для того чтоб убедиться в
-- том, что репликация прошла - после изменений вставим строки
-- если репликация приостановится, то вставленные строки
-- не появятся на реплике.
UPDATE ts.testt SET t = 'updated on the master' WHERE i = 2;
DELETE FROM ts.testt WHERE i = 1;
INSERT INTO ts.testt (t, d)
VALUES ('004', 'fourth'), ('005', 'fifth element'), ('006', 'sixth');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT * FROM ts.testt; -- полимеры утрачены, смотрим логи
-- Добавляем первичные ключи через предварительное создание
-- уникального индекса.
-- Cначала на реплике, иначе ошибки приостановят репликацию
CREATE UNIQUE INDEX CONCURRENTLY testt_hm_idx ON ts.testt (i);
ALTER TABLE ts.testt
ADD CONSTRAINT hm_pkey PRIMARY KEY USING INDEX testt_hm_idx;
-- Затем на мастере
CREATE UNIQUE INDEX CONCURRENTLY testt_hm_idx ON ts.testt (i);
ALTER TABLE ts.testt
ADD CONSTRAINT hm_pkey PRIMARY KEY USING INDEX testt_hm_idx;
-- Изменим значения полей на реплике
UPDATE ts.testt SET t = 'updated on the replica again' WHERE i <> 1;
SELECT * FROM ts.testt;
-- Обновляем и удаляем пару строк на мастере.
-- Для того чтоб убедиться в том, что репликация
-- прошла - после изменений вставим строку.
-- Если репликация приостановится, то вставленная строка
-- не появится на реплике.
UPDATE ts.testt SET t = 'recently updated on the master' WHERE i = 6;
DELETE FROM ts.testt WHERE i = 4;
INSERT INTO ts.testt (t, d)
VALUES ('007', 'seventh');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
-- i=4 удалён, i=6 обновлён, новая строка появилась
-- вот что PK животворящий делает!
SELECT * FROM ts.testt;
-- Чистим реплику
DROP SUBSCRIPTION IF EXISTS testt_sub;
DROP TABLE IF EXISTS ts.testt;
ALTER SYSTEM SET log_min_messages = :lmm;
SELECT pg_reload_conf();
-- Чистим мастер
DROP PUBLICATION IF EXISTS testt_pub;
DROP TABLE IF EXISTS ts.testt;
---

Лишний столбец на мастереЕсли же случилось так, что кто-то удосужился создать лишний столбец на мастере, то на реплике начнёт появляться множество таких сообщений:

DEBUG: starting logical replication worker for subscription "your_subscription_name"
DEBUG: registering background worker "logical replication worker for subscription 16766"
DEBUG: starting background worker process "logical replication worker for subscription 16766"
LOG: logical replication apply worker for subscription "your_subscription_name" has started
DEBUG: connecting to publisher using connection string "host=192.168.122.182 dbname=your_db_name"
ERROR: logical replication target relation "public.your_table_name" is missing some replicated columns
DEBUG: unregistering background worker "logical replication worker for subscription 16766"
LOG: background worker "logical replication worker" (PID 10708) exited with exit code 1

А на мастере множество таких:

DEBUG: received replication command: IDENTIFY_SYSTEM
DEBUG: received replication command: START_REPLICATION SLOT "your_subscription_name" LOGICAL 0/0 (proto_version '1', publication_names '"your_publication_name"')
LOG: starting logical decoding for slot "your_subscription_name"
DETAIL: streaming transactions committing after 0/1AC5FAD0, reading WAL from 0/1AC5FA98
LOG: logical decoding found consistent point at 0/1AC5FA98
DETAIL: There are no running transactions.
DEBUG: got new restart lsn 0/1AC5FC40 at 0/1AC5FC40
DEBUG: "your_subscription_name" has now caught up with upstream server

То есть вставка, изменение и удаление строк не принимается репликой, ведь на стороне реплики нет столбцов для поступивших данных. О сложившейся ситуации мастер «информируется». В этом случае на реплике постоянно создаётся и уничтожается новый процесс репликации для сбойной подписки.
В результате на мастере начнёт раздувать журнал предзаписи независимо от типа операции. Так можно довести и до останова сервера, не заметив вовремя разбухание журнала. Удаление столбца на стороне мастера не спасёт — логическое декодирование будет использовать записи из журнала предзаписи, а там добавленный столбец есть и никуда не денется. Также не спасёт и удаление таблицы из публикации — это связано с тем, что публикацией может пользоваться несколько подписок и такое лекарство окажется опаснее болезни, потому и возможность такую не реализовали.
Лично я считаю, что для публикаций с одним подписчиком не помешает добавить такую возможность — но пока что разработчики так не думают. В общем имеется три варианта:

Добавить на реплику недостающий столбец и надеяться, что на обоих серверах их содержимое совпадает (\x6c6f6c), иначе все операции UPDATE и DELETE по несовпадающим строкам пропадут и оставят после себя уже знакомые записи в логе "... not find row for ..." — это относится к REPLICA IDENTITY FULL.
Отсутствие в добавленном столбце данных, имеющихся на мастере, приведет к тому, что пробка, конечно, рассосётся — только вот накопившиеся операции INSERT данных попадут в таблицу на реплике, а UPDATE и DELETE — нет. Потому что идентификатором строки будет вся строка, а в одном из столбцов данные не совпадают.
Выбор так себе, но так можно относительно безопасно разгрести журнал предзаписи на мастере и не доводить до второго способа. Ну а далее — чистим публикацию от проблемной таблицы, обновляем подписку, добавляем таблицу обратно в публикацию, добавляем в таблицу недостающие столбцы, обновляем подписку, ждём. Если же на обоих серверах нормальные репликационные идентификаторы — первичный ключ или уникальный индекс — репликация восстановится без потерь данных.
Удалить подписку целиком и создать вновь, не забыв очистить таблицу перед пересозданием, ну и столбцы в неё добавить. Сработает потому, что удалится слот на стороне мастера.
Можно, на основе общего с мастером столбца, создать на реплике первичный ключ, или уникальный индекс в качестве REPLICA IDENTITY. После этого добавить недостающий столбец. Однако, при этом нужно быть уверенным, что значения в выбранном для репликационного идентификатора столбце — уникальны для всех строк таблицы. Тут возникает вопрос — почему первичный ключ не был создан сразу?

Воспроизведение

SPL

Дубликаты значений в столбцах репликационных идентификаторов или «Раньше думать надо было!»

При начальной синхронизации
ERROR: duplicate key value violates unique constraint "testt_pkey"
DETAIL: Key (i)=(3) already exists.
CONTEXT: COPY testt, line 3 -- Заодно узнали какой командой переносятся данные при ничальной синхронизации
После перехода в режим репликации
ERROR: duplicate key value violates unique constraint "testt_pkey"
DETAIL: Key (i)=(4) already exists.

Возникают такие ошибки потому, что с мастера на реплику приходит новая строка с имеющимся на реплике значением репликационного идентификатора на основе первичного ключа или уникального индекса. При обновлении логической репликацией или простом переносе данных такие ошибки не появляются. Они появляются если база находится в доступе на запись для пользователей или приложений и те этим пользуются. По сути, это не ошибка PostgreSQL, это ошибка проектирования и вопросы нужно задавать себе.
Исправлять такие инциденты не так уж и просто — сначала необходимо определить какая из двух конфликтующих строк неправильная, и насколько. Если неправильным является содержимое строки на реплике, то репликация продолжится после удаления с реплики такой строки.
Совсем другое дело, когда неправильной является строка, пришедшая с мастера. В этом случае всё равно придётся либо удалять строку на реплике, либо назначать ей заведомо большой идентификатор; ждать завершения загрузки с мастера отставших транзакций; заменять пришедшую с мастера строку старой строкой с реплики.
Ситуация в обоих случаях усугубляется тем, что лишних строк может быть очень много. Настолько, что новые транзакции будут выполняться быстрее, чем будут разбираться конфликтные ситуации. И всё время, пока расследуется инцидент, размер журнала предзаписи будет разрастаться, а пользователи реплики не будут иметь возможности получать актуальные данные.
Если предполагается использовать логическую репликацию не для обновления, а для обмена данными между различными базами — следует очень внимательно проработать вопрос об идентификации строк. Так как для идентификации, обычно, используются последовательности, то можно посоветовать пробежаться по слайдам доклада CTO Stickeroid Ai, Камиля Исламова о способах применения последовательностей в PostgreSQL и документацию по последовательностям, затем перепроектировать их в сбойной системе баз данных.

Воспроизведение

SPL

-- Подготавливаем мастер
CREATE TABLE ts.testt (i serial PRIMARY KEY,
t varchar);
ALTER TABLE ts.testt REPLICA IDENTITY FULL;
INSERT INTO ts.testt (t)
VALUES ('001'), ('002'), ('003');
CREATE PUBLICATION testt_pub for TABLE ts.testt;
SELECT * FROM ts.testt;
-- Подготавливаем реплику
CREATE TABLE ts.testt (i bigserial PRIMARY KEY,
t text);
INSERT INTO ts.testt (i, t)
VALUES (3, '003 only replica value'),
(4, '004 only replica value');
CREATE SUBSCRIPTION testt_sub
CONNECTION 'host=192.168.122.182
dbname=test
user=log_rep_rolename
password=sadp!'
PUBLICATION testt_pub;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt;
-- Смотрим логи, потом удаляем лишнюю строчку на реплике
-- на мастере удалять не поможет - порядок применения
-- сообщений репликации - транзакционный. Удаленная на мастере
-- строка удалится на реплике только после разрешения инцидента.
DELETE FROM ts.testt WHERE i = 3;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt;
-- Чистим реплику
DROP SUBSCRIPTION IF EXISTS testt_sub;
DROP TABLE IF EXISTS ts.testt;
-- Чистим мастер
DROP PUBLICATION IF EXISTS testt_pub;
DROP TABLE IF EXISTS ts.testt;
---

Столбец NOT NULL без DEFAULT на реплике

ERROR: null value in column "column_name" violates not-null constraint

Для любых столбцов на реплике не должно быть установлено свойство NOT NULL без указания значения по умолчанию. Особенно это касается тех столбцов, которые есть на мастере, потому что на реплике для них значение DEFAULT подставляться не будет — как пришел NULL, так и будет записываться.
Если в дополнительные NOT NULL столбцы значение по умолчанию не поставить, то репликация приостановится с приведённой выше ошибкой. Как только удалите такой столбец, зададите ему значение по умолчанию или удалите ограничение NOT NULL — репликация возобновится без потери данных.
Если в реплицируемой таблице на стороне мастера изначально есть значения NULL — сначала необходимо заполнить такие поля, либо удалить ограничение NOT NULL на реплике.
При появлении такой ошибки в начале репликации можно узнать из логов название проблемной таблицы, но не схему в которой она расположена:

ERROR: null value in column "d" violates not-null constraint
DETAIL: Failing row contains (3, 003, null).
CONTEXT: COPY testt, line 3: "3 003 \N"

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

Воспроизведение

SPL

-- Подготавливаем мастер
CREATE TABLE ts.testt (i serial PRIMARY KEY, t text, d text);
INSERT INTO ts.testt (t, d) VALUES ('001', 'first'), ('002', 'second'), ('003', NULL);
CREATE PUBLICATION testt_pub for TABLE ts.testt;
SELECT * FROM ts.testt;
-- Подготавливаем реплику
CREATE TABLE ts.testt (i serial PRIMARY KEY, t text, d text NOT NULL DEFAULT 'null from master');
CREATE SUBSCRIPTION testt_sub
CONNECTION 'host=192.168.122.182
dbname=test
user=log_rep_rolename
password=sadp!'
PUBLICATION testt_pub;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt;
-- Начальная синхронизация не сработала - смотрите логи
-- Исправляем проблему со столбцом d
-- Добавляем новый столбец, которого нет на мастере
ALTER TABLE ts.testt ALTER COLUMN d DROP NOT NULL,
ALTER COLUMN d DROP DEFAULT;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt;
ALTER TABLE ts.testt ADD COLUMN x text NOT NULL DEFAULT 'start default';
ALTER TABLE ts.testt ALTER COLUMN x DROP DEFAULT;
SELECT * FROM ts.testt;
-- Выполняем на мастере вставку
INSERT INTO ts.testt (t) VALUES ('004');
SELECT * FROM ts.testt;
-- Проверяем логи реплике - там уже куча ошибок
-- Затем исправляем на реплике проблему
-- Либо убираем NOT NULL
-- ALTER TABLE ts.testt ALTER COLUMN x DROP NOT NULL;
-- Либо добавляем DEFAULT
-- ALTER TABLE ts.testt ALTER COLUMN x SET DEFAULT 'second default';
-- Либо удаляем столбец
-- ALTER TABLE ts.testt DROP COLUMN x;
-- Проверяем на реплике - произошла репликация или нет
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt;
-- Чистим реплику
DROP SUBSCRIPTION IF EXISTS testt_sub;
DROP TABLE IF EXISTS ts.testt;
-- Чистим мастер
DROP PUBLICATION IF EXISTS testt_pub;
DROP TABLE IF EXISTS ts.testt;
---

На мастере есть первичный ключ, на реплике он отсутствует

ERROR: logical replication target relation "ts.testt" has neither REPLICA IDENTITY index nor PRIMARY KEY and published relation does not have REPLICA IDENTITY FULL

Ошибка наблюдается при репликации команд UPDATE и DELETE, если в таблице на реплике отсутствует первичный ключ, который есть на мастере. Повторяется до тех пор, пока на реплике не будет создан соответствующий первичный ключ. Репликация при этом приостанавливается и продолжается после устранения причины ошибки. Состав столбцов на обоих серверах либо идентичен, либо на реплике могут быть дополнительные столбцы; порядок столбцов может различаться.

Воспроизведение

SPL

-- Подготавливаем мастер
CREATE TABLE ts.testt (i serial PRIMARY KEY, t text, d text);
INSERT INTO ts.testt (t, d) VALUES ('001', 'first'), ('002', 'second'), ('003', 'third');
CREATE PUBLICATION testt_pub for TABLE ts.testt;
SELECT * FROM ts.testt;
-- Подготавливаем реплику
CREATE TABLE ts.testt (i serial, t text, d text);
CREATE SUBSCRIPTION testt_sub
CONNECTION 'host=192.168.122.182
dbname=test
user=log_rep_rolename
password=sadp!'
PUBLICATION testt_pub;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt;
-- Выполняем на мастере обновление
UPDATE ts.testt SET t = t || ' updated' WHERE i = 1;
INSERT INTO ts.testt (t, d) VALUES ('004', 'AFTER UPDATE');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt; -- репликация не произошла
-- Исправляем на реплике отсутствие первичного ключа, ждём, проверяем репликацию, удаляем первичный ключ для воспроизведения ошибки при удалении строки.
ALTER TABLE ts.testt ADD PRIMARY KEY (i);
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt; -- репликация возобновилась
ALTER TABLE ts.testt DROP CONSTRAINT testt_pkey;
-- Выполняем на мастере удаление
DELETE FROM ts.testt WHERE i = 1;
INSERT INTO ts.testt (t, d) VALUES ('005', 'AFTER DELETE');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt; -- репликация не произошла
-- Исправляем на реплике отсутствие первичного ключа, ждём, проверяем репликацию
ALTER TABLE ts.testt ADD PRIMARY KEY (i);
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt; -- репликация возобновилась
-- Чистим реплику
DROP SUBSCRIPTION IF EXISTS testt_sub;
DROP TABLE IF EXISTS ts.testt;
-- Чистим мастер
DROP PUBLICATION IF EXISTS testt_pub;
DROP TABLE IF EXISTS ts.testt;
---

На мастере и реплике первичные ключи или индексы построены на разных столбцах

ERROR: publisher did not send replica identity column expected by the logical replication target relation "ts.testt"

Ошибка появляется при репликации команд UPDATE и DELETE, если в таблице на реплике первичный ключ (индекс) построен не на том столбце, на котором построен первичный ключ (индекс) на мастере. Повторяется до тех пор, пока на реплике не будет удалён неправильный и не будет создан правильный первичный ключ (индекс). Репликация при этом приостанавливается и продолжается после устранения причины ошибки. На реплике могут быть дополнительные столбцы, порядок столбцов может различаться.

Воспроизведение

SPL

-- Подготавливаем мастер
CREATE TABLE ts.testt (i serial PRIMARY KEY, t text, d text);
INSERT INTO ts.testt (t, d) VALUES ('001', 'first'), ('002', 'second'), ('003', 'third');
CREATE PUBLICATION testt_pub for TABLE ts.testt;
SELECT * FROM ts.testt;
-- Подготавливаем реплику
CREATE TABLE ts.testt (i serial, t text PRIMARY KEY, d text);
CREATE SUBSCRIPTION testt_sub
CONNECTION 'host=192.168.122.182
dbname=test
user=log_rep_rolename
password=sadp!'
PUBLICATION testt_pub;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt;
-- Выполняем на мастере обновление
UPDATE ts.testt SET t = t || ' upd' WHERE i = 1;
INSERT INTO ts.testt (t, d) VALUES ('004', 'AFTER UPDATE');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt; -- посмотреть ошибки
-- Исправляем на реплике неправильный первичный ключ, ждём, проверяем репликацию, возвращаем неправильный первичный ключ для воспроизведения ошибки при удалении строки.
ALTER TABLE ts.testt DROP CONSTRAINT testt_pkey;
ALTER TABLE ts.testt ADD PRIMARY KEY (i);
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt; -- репликация возобновилась
ALTER TABLE ts.testt DROP CONSTRAINT testt_pkey;
ALTER TABLE ts.testt ADD PRIMARY KEY (t);
-- Выполняем на мастере удаление
DELETE FROM ts.testt WHERE i = 1;
INSERT INTO ts.testt (t, d) VALUES ('005', 'AFTER DELETE');
SELECT * FROM ts.testt;
-- Проверяем на реплике - произошла репликация или нет
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt; -- репликация не произошла
-- Исправляем на реплике неправильный первичный ключ, ждём, проверяем репликацию.
ALTER TABLE ts.testt DROP CONSTRAINT testt_pkey;
ALTER TABLE ts.testt ADD PRIMARY KEY (i);
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt; -- репликация возобновилась
-- Чистим реплику
DROP SUBSCRIPTION IF EXISTS testt_sub;
DROP TABLE IF EXISTS ts.testt;
-- Чистим мастер
DROP PUBLICATION IF EXISTS testt_pub;
DROP TABLE ts.testt;
---

Фантомного индекса боль

ERROR: cannot delete from table "test" because it does not have a replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

Ошибка очень интересная и наблюдается на мастере при выполнении команд UPDATE и DELETE, если в таблице на мастере в качестве репликационного идентификатора использовался уникальный индекс, который потом удалили, например потому, что создали взамен первичный ключ, или просто так удалили (всяко быват, уж мы их ругам-ругам, ничо не помогат).
Ошибку эту можно получить только на мастере и только на таблицах, включенных в публикации, поддерживающие UPDATE и DELETE. Вообще эта ошибка означает именно отсутствие любого репликационного идентификатора, но в данном случае всё немного интереснее — идентификатором остаётся индекс, который удалили.
Чтоб исправить ошибку нужно вернуть REPLICA IDENTITY DEFAULT, перенацелив тем самым поиcк идентификатора туда, где он есть — на первичный ключ. Также можно воссоздать индекс и снова перенацелиться на него, так как oid у него будет уже другой, и система не будет его видеть.
Если же нет времени строить индексы и первичные ключи — включаем REPLICA IDENTITY FULL, а уж затем восстанавливаем индексы и так далее. В рабочих системах начинать изменения нужно с реплики, иначе вылезут другие ошибки.
Идентифицировать именно такой вариант развития событий не так уж и просто, хотя бы потому, что никому такое в голову не придёт. И, не приведи вселенная, если ещё до вас кто-то удалил и снова создал индекс — выявить причину происходящего будет практически невозможно.

Воспроизведение

SPL

-- Воспроизводить на мастере
-- Создаём таблицу
CREATE TABLE ts.testt (i serial, t text, d text);
-- Создаём индекс
CREATE UNIQUE INDEX CONCURRENTLY testt_idx ON ts.testt USING btree (i);
-- Назначаем индекс в качестве репликационного идентификатора
ALTER TABLE ts.testt REPLICA IDENTITY USING INDEX testt_idx;
-- Информация о таблице отображает индекс как REPLICA IDENTITY
\d ts.testt
-- Смотрим что назначено репликационным идентификатором
-- relreplident = i, т.е. индекс
SELECT relname, relreplident -- i-index, d-default
FROM pg_class
WHERE oid = 'ts.testt'::regclass;
-- Без следующей строки ошибка воспроизводится, но не так наглядно.
ALTER TABLE ts.testt ADD CONSTRAINT pkey PRIMARY KEY (i);
-- Добавился первичный ключ в информации по таблице
\d ts.testt
-- Смотрим что назначено репликационным идентификатором
-- всё ещё relreplident = i, т.е. индекс
-- так и должно быть, ведь ПК как РИ не назначали
SELECT relname, relreplident -- i-index, d-default
FROM pg_class
WHERE oid = 'ts.testt'::regclass;
CREATE PUBLICATION testt_pub for TABLE ts.testt;
INSERT INTO ts.testt (t, d) VALUES ('001', 'first'), ('002', 'second'), ('003', 'third');
DROP INDEX ts.testt_idx;
-- После удаления индекса - информации про него нет
\d ts.testt
-- Смотрим что назначено репликационным идентификатором
-- всё ещё relreplident = i, т.е. индекс
-- несмотря на то, что индекса уже нет, он всё ещё РИ
SELECT relname, relreplident -- i-index, d-default
FROM pg_class
WHERE oid = 'ts.testt'::regclass;
-- Сейчас будут ошибки, а посмотришь на \d testt выполненный выше
-- то непонятно, как же так - первичный ключ ведь есть.
-- есть-то он есть, да вот только запрос к pg_class
-- показывает что идентификатором служит индекс - i, а не d - default
-- а индекс-то этот уже удалён
UPDATE ts.testt SET t = t || ' upd' WHERE i = 1;
DELETE FROM ts.testt WHERE i = 3;
-- Cоздаём одноимённый индекс обратно и попробуйте
-- найти в чем причина ошибки - индекс-то существует, идентификатором
-- он вроде как назначен...
CREATE UNIQUE INDEX CONCURRENTLY testt_idx ON ts.testt USING btree (i);
ALTER TABLE ts.testt REPLICA IDENTITY USING INDEX testt_idx;
-- Смотрим есть ли индексы на таблице со столбцом indisreplident=t
-- и такой индекс есть, это воссозданный исходный индекс
SELECT pgc.oid, pgc.relname, pgi.indisreplident
FROM pg_class AS pgc,
pg_index AS pgi
WHERE pgc.oid = pgi.indexrelid
AND pgi.indrelid IN (SELECT oid FROM pg_class WHERE relname = 'testt');
-- а тут написано что идентификатором служит индекс:
\d ts.testt
-- Однако UPDATE и DELETE так и не работают
UPDATE ts.testt SET t = t || ' upd' WHERE i = 1;
DELETE FROM ts.testt WHERE i = 3;
-- Пустим в ход дефибриллятор:
ALTER TABLE ts.testt REPLICA IDENTITY DEFAULT;
ALTER TABLE ts.testt REPLICA IDENTITY USING INDEX testt_idx;
\d ts.testt
SELECT relname, relreplident -- i-index, d-default
FROM pg_class
WHERE oid = 'ts.testt'::regclass;
-- Визуально в свойствах таблицы всё то же самое, но теперь всё работает
UPDATE ts.testt SET t = t || ' upd' WHERE i = 1;
DELETE FROM ts.testt WHERE i = 3;
DROP TABLE ts.testt;
DROP PUBLICATION testt_pub;
-- Не полагайтесь только на /d, всегда ищите и в системных каталогах!
-- Если индекс есть, то не факт, что он используется.
---

Непреобразуемые типы

При начальной синхронизации
ERROR: invalid input syntax for type bigint: "\xaabbcc"
CONTEXT: COPY testt, line 1, column t: "\xaabbcc"
После перехода в режим репликации
ERROR: invalid input syntax for type bigint: "\xdeadbeef"
CONTEXT: processing remote data for replication target relation "ts.testt" column "t", remote type bytea, local type bigint

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

Воспроизведение

SPL

-- Подготавливаем мастер
CREATE TABLE ts.testt (i serial PRIMARY KEY, t bytea);
INSERT INTO ts.testt (t) VALUES('\xaabbcc'::bytea);
SELECT * FROM ts.testt;
CREATE PUBLICATION testt_pub FOR TABLE ts.testt;
-- Подготавливаем реплику
CREATE TABLE ts.testt (i bigserial PRIMARY KEY, t bigint);
CREATE SUBSCRIPTION testt_sub
CONNECTION 'host=192.168.122.182
dbname=test
user=log_rep_rolename
password=sadp!'
PUBLICATION testt_pub;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt;
-- Начальная синхронизация не прошла
-- Всё - ошибка в логах, теперь надо её исправить.
-- Вот это вот не поможет:
ALTER TABLE ts.testt ALTER COLUMN t SET DATA TYPE bytea USING t::bytea;
-- Только так:
ALTER TABLE ts.testt DROP COLUMN t, ADD COLUMN t bytea;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt; -- Убеждаемся в завершении начальной синхронизации
-- Начальная синхронизаация завершилась и на реплике
-- снова поменяем столбец на неправильный
ALTER TABLE ts.testt DROP COLUMN t, ADD COLUMN t bigint;
-- Вставим данные на мастере
INSERT INTO ts.testt (t) VALUES('\xdeadbeef'::bytea);
SELECT * FROM ts.testt;
-- Всё - репликация снова на паузе
SELECT * FROM ts.testt;
-- Снова ремонтируем, но делаем такой тип данных,
-- в который можно преобразовать bytea
ALTER TABLE ts.testt DROP COLUMN t, ADD COLUMN t text;
SELECT pg_sleep(5.5);
SELECT * FROM ts.testt; -- Убеждаемся в работе репликации
-- Чистим реплику
DROP SUBSCRIPTION testt_sub;
DROP TABLE ts.testt;
-- Чистим мастер
DROP PUBLICATION testt_pub;
DROP TABLE ts.testt;
---

И такое может пригодиться, если что-то натворили непонятное

-- Удаление слота на мастере
SELECT pg_drop_replication_slot('test_sub');
-- Удаление подписки на реплике при отсутствующем слоте
ALTER SUBSCRIPTION testt_sub DISABLE;
ALTER SUBSCRIPTION testt_sub SET (slot_name=NONE);
DROP SUBSCRIPTION testt_sub;

Общие замечания
Лучше первичных ключей нет на свете репликационных идентификаторов. В случае, когда в таблице отсутствует PRIMARY KEY и его добавление невозможно, то всегда имеется возможность создать его используя неблокирующее построение индекса с последующим назначением его ограничением первичного ключа.
Выполнять создание индексов желательно до переноса схемы данных, с тем, чтоб они перенеслись на реплику в составе схемы БД, ну или не забыть создать их и на реплике. К тому же на реплике возможно сразу создать первичный ключ с аналогичным составом столбцов ещё до начала репликации.
Использовать REPLICA IDENTITY FULL стоит только если строки в таблице незначительного размера и редко изменяются. При использовании такого типа идентификатора желательно воздержаться от изменения структуры принимающей таблицы.
Если после переноса схемы вам необходимо поменять в ней некоторые таблицы, то возможно проверять безопасность изменений в новой базе используя специально восстановленную резервную копию мастера как источник данных и устанавливая log_min_messages=debug1 для проверки правильности работы репликации по части UPDATE и DELETE. Изменение этого параметра не требует перезапуска сервера, поэтому возможно его переключать в любое время, например на время проверки внесенных изменений.
После начала репликации на мастере нельзя добавлять столбцы, а если добавлять, то начинать надо с реплики.
Новые столбцы с NOT NULL на реплике хороши только если в комплекте идёт DEFAULT.
Ошибки могут случаться, но в большинстве случаев они некритичные и легко устраняются, только нужно с умом подойти к выбору репликационных идентификаторов. Если речь идёт не об обновлении, а о более сложных схемах применения логической репликации, то к проектированию системы в целом нужно подойти очень ответственно, иначе можно отправить всё в нокдаун или потерять данные.
И на сладкое: можно добиться того, что системные каталоги на реплике сохранят информацию о подписках, которые, как казалось, были удалены. Тут может помочь только реинициализация кластера БД. В таком случае команда \dRs показывает наличие подписки, а SELECT * FROM pg_subscription; — нет. При этом на реплике все необходимые для обслуживания процессы запускаются, но ничего полезного не делают, кроме множества ошибок в логах (не можем подключиться, слота нет...), даже при специально повторно созданных объектах и слотах. То есть было утеряно некоторое количество внутренней информации о подписке. За месяц экспериментов такое положение дел было достигнуто только два раза, и оба раза это произошло после отправки хоста тестовых виртуалок в ждущий или спящий режим. Хоть и получалось так не всегда — не делайте так. Но случиться такое с хост-системой всё-таки может, так что про такую вероятность нужно знать.
===========
Источник:
habr.com
===========
Похожие новости:

Теги для поиска: #_postgresql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_data_engineering, #_postgersql, #_postgres_pro, #_logicheskaja_replikatsija (логическая репликация), #_blog_kompanii_postgres_professional (
Блог компании Postgres Professional
), #_postgresql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
), #_data_engineering

Профиль ЛС

Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 22-Ноя 19:21
Часовой пояс: UTC + 5