Начиная с 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.

Почему стоит обновиться? Например, поэтому:

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

Что может выступать в качестве репликационного идентификатора

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

Если столбцов пригодных к роли репликационных идентификаторов нет, что очень странно, то придётся их создавать. Каждое значение в таком столбце должно быть уникальным. В качестве источника уникальности могут выступать как естественные, так и искусственные ключи и их комбинации — зависит от архитектуры базы данных. При этом неважно что именно использовать в качестве естественных ключей, главное, чтоб они выполняли роль однозначного идентификатора. В качестве искусственных ключей используются, как правило, различные последовательности и типы 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.


Столбцы идентификации

Пришедшие из стандарта 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, то такое свойство будет создано автоматически, но после после удаления ограничения автоматически не удалится.



Уникальные индексы

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

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


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 задана, то на реплике её наличие роли не играет — но только при условии, что состав столбцов таблицы на реплике идентичен таковому на мастере. Иначе на реплике будут применяться только команды INSERT, а если у вас 11 версия мастера — то и TRUNCATE. Команды UPDATE и DELETE будут применяться только если дополнительные столбцы в изменяемой/удаляемой строке будут равны NULL.


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

ALTER TABLE ts.testt REPLICA IDENTITY NOTHING;

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

Состав и порядок столбцов

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

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

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

Склад грабель горизонтального хранения

Предупреждён — значит вооружен. Ниже приведены несколько самых распространённых сообщений об ошибках и просто общих рассуждений. Лучше прочитать эти сообщения здесь, чем на рабочих серверах — читаем и вооружаемся. Если у вас есть что-нибудь интересное на эту тему — расскажите в комментариях.

Если на мастере репликационные идентификаторы не заложены в бюджет

По умолчанию публикация создаётся для команд 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' или добавить репликационные идентификаторы.

... 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 и начальная синхронизация будут нормально обрабатываться, даже если на реплике есть столбцы отсутствующие на мастере.

Лишний столбец на мастере

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

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. После этого добавить недостающий столбец. Однако, при этом нужно быть уверенным, что значения в выбранном для репликационного идентификатора столбце — уникальны для всех строк таблицы. Тут возникает вопрос — почему первичный ключ не был создан сразу?

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

При начальной синхронизации
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 и документацию по последовательностям, затем перепроектировать их в сбойной системе баз данных.

Столбец 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"

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

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

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

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

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

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

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

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

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, а уж затем восстанавливаем индексы и так далее. В рабочих системах начинать изменения нужно с реплики, иначе вылезут другие ошибки.

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

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

При начальной синхронизации
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

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

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

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

-- Удаление слота на мастере
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; — нет. При этом на реплике все необходимые для обслуживания процессы запускаются, но ничего полезного не делают, кроме множества ошибок в логах (не можем подключиться, слота нет...), даже при специально повторно созданных объектах и слотах. То есть было утеряно некоторое количество внутренней информации о подписке. За месяц экспериментов такое положение дел было достигнуто только два раза, и оба раза это произошло после отправки хоста тестовых виртуалок в ждущий или спящий режим. Хоть и получалось так не всегда — не делайте так. Но случиться такое с хост-системой всё-таки может, так что про такую вероятность нужно знать.