С любой текущей технологией логической репликации для Postgres у нас обычно есть отличные способы репликации событий DML (INSERT, UPDATE, DELETE), но мы остаемся наедине с задачей распространения изменений DDL. То есть, когда мы создаем новые таблицы, изменяем таблицы и тому подобное, мы должны управлять этим отдельно в процессе развертывания нашего приложения, чтобы внести те же изменения на логические реплики и добавить такие таблицы в репликацию.

Начиная с Postgres 13, нет встроенного способа выполнять «прозрачную репликацию DDL» на другие кластеры Postgres вместе с любой логической технологией репликации, построенной на стандартном Postgres.

Этот проект является попыткой сделать именно это. Основывается на следующих концепциях:

Во многих средах это может охватывать большинство, если не все, DDL операторов, которые выполняются в среде приложения. Мы знаем, что это не охватывает 100% крайних случаев, но мы считаем, что функциональность и надежность достаточно значительны, чтобы добавить большую ценность во многих средах Postgres. Это будет особенно хорошо работать в этих двух случаях использования:

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

Поскольку мы всегда ищем документацию по примеру, мы показываем это в первую очередь. Предположим, у нас есть основная база данных с IP-адресом 192.168.0.1, и мы хотим настроить логическую репликацию на реплику базы данных с IP-адресом 192.168.0.2.

На основной машине и на машине-реплике выполните следующее:

На основной машине выполните следующие команды последовательно:

--Get root rights
sudo -s

--Switch to a postgres user
su - postgres

--Connect to the database
psql

--Create provider role
CREATE ROLE app_owner SUPERUSER LOGIN;

--Switch to the provider role
SET ROLE app_owner;

--Create a test database
CREATE DATABASE test;

--Connect to the test database on behalf of the provider role
\c test app_owner

--Create extensions
CREATE EXTENSION pgl_ddl_deploy;
CREATE EXTENSION pg_background;

--Create a table
CREATE TABLE test_deploy (id serial PRIMARY KEY, name text);

--Create a publication
CREATE PUBLICATION test_ddl_pub FOR TABLE test_deploy;

--Setup configs
INSERT INTO pgl_ddl_deploy.set_configs
  (set_name,
  include_schema_regex,
  driver)
VALUES
  ('test_ddl_pub',
  '.*',
  'native'::pgl_ddl_deploy.driver);

--Deploy DDL replication
SELECT pgl_ddl_deploy.deploy('test_ddl_pub')
FROM pgl_ddl_deploy.set_configs;

--Setup permissions
SELECT pgl_ddl_deploy.add_role(oid) FROM pg_roles WHERE rolname in('app_owner', 'replication_role');

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

--Get root rights
sudo -s

--Switch to a postgres user
su - postgres

--Connect to the database
psql

--Create subscriber role
CREATE ROLE app_owner SUPERUSER LOGIN;

--Switch to the subscriber role
SET ROLE app_owner;

--Create a test database
CREATE DATABASE test;

--Connect to the test database on behalf of the subscriber role
\c test app_owner

--Create extensions
CREATE EXTENSION pgl_ddl_deploy;
CREATE EXTENSION pg_background;

--Create a table
CREATE TABLE test_deploy (id serial PRIMARY KEY, name text);

--Create a subscription
CREATE SUBSCRIPTION test_ddl_sub CONNECTION 'host=192.168.0.1 dbname=test' PUBLICATION test_ddl_pub;

--Update the list of tables for replication
ALTER SUBSCRIPTION test_ddl_sub REFRESH PUBLICATION;

В главной базе данных добавьте столбец в таблицу test_deploy, создайте новую таблицу another_one_table и заполните её данными:

ALTER TABLE test_deploy ADD COLUMN value NUMERIC;

CREATE TABLE another_one_table (
  id serial PRIMARY KEY,
  bla INT
);

INSERT INTO another_one_table (bla) VALUES (1),(2),(3);

На реплике проверьте таблицы:

\dt
               List of relations
 Schema |       Name        | Type  |   Owner   
--------+-------------------+-------+-----------
 public | another_one_table | table | app_owner
 public | test_deploy       | table | app_owner
(2 rows)

SELECT * FROM test_deploy;
 id | name | value 
----+------+-------
(0 rows)

SELECT * FROM another_one_table;
 id | bla 
----+-----
  1 |   1
  2 |   2
  3 |   3
(3 rows)

Функциональность этого требует версии postgres 9.5+ и рабочей установки pglogical.

DEB доступен в официальном репозитории PGDG как postgresql-${PGSQL_VERSION}-pgl-ddl-deploy см. инструкцию по установке на https://wiki.postgresql.org/wiki/Apt

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

Это расширение требует установки pglogical перед тем, как вы сможете создать расширение в любой базе данных. Затем расширение может быть развернуто как любое расширение postgres:

CREATE EXTENSION pgl_ddl_deploy;

Это расширение должно быть установлено на поставщике и всех подписчиках. Начиная с версии 1.5.0, у вас должна быть одинаковая версия pgl_ddl_deploy как на поставщике, так и на подписчике (НЕ обязательно одинаковая версия Postgres).

Чтобы обновить pgl_ddl_deploy до версии 1.5 с предыдущей версии, установите последние версии пакетов на вашем сервере(ах), затем выполните в базе(ах) данных:

ALTER EXTENSION pgl_ddl_deploy UPDATE;

Для нативной логической репликации репликация DDL настраивается на основе каждой публикации. Для pglogical репликация DDL настраивается на основе каждого набора репликации.

Существует три основных типа конфигурации:

Вышеуказанные 3 опции являются взаимоисключающими. Однако вы можете использовать дополнительную опцию ddl_only_replication вместе с include_schema_regex или include_everything. Это означает, что таблицы не добавляются в репликацию автоматически. Это используется, если вы хотите поддерживать синхронизацию схемы двух систем, но не обязательно реплицировать данные для всех таблиц.

Добавьте строки в pgl_ddl_deploy.set_configs, чтобы настроить (но еще не развернуть) репликацию DDL для конкретной публикации/набора репликации. Обратите особое внимание на то, что driver управляет, для какой технологии репликации это предназначено, native или pglogical. Например:

--Only some options are shown.  See below for all options

--This type of configuration will DDL replicate all user schemas, and auto-add
--new matching schemas/tables to replication:
INSERT INTO pgl_ddl_deploy.set_configs (set_name, include_schema_regex, driver)
VALUES ('default', '.*', 'native'::pgl_ddl_deploy.driver);

--This type of configuration will maintain only the specific set of tables
--in the given replication set for any `ALTER TABLE` statements:
INSERT INTO pgl_ddl_deploy.set_configs (set_name, include_only_repset_tables, driver)
VALUES ('my_special_tables', TRUE, 'native'::pgl_ddl_deploy.driver);

Соответствующие настройки:

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

SELECT pgl_ddl_deploy.exclude_regex();

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

SELECT sc.set_name, n.nspname
FROM pg_namespace n
INNER JOIN pgl_ddl_deploy.set_configs sc
  ON nspname !~* pgl_ddl_deploy.exclude_regex()
  AND n.nspname ~* sc.include_schema_regex
ORDER BY sc.set_name, n.nspname;

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

SELECT n.nspname
FROM pg_namespace n
WHERE nspname !~* pgl_ddl_deploy.exclude_regex()
  AND n.nspname ~* 'test'
ORDER BY n.nspname;

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

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

SELECT pgl_ddl_deploy.add_role(oid)
FROM pg_roles
WHERE rolname IN('app_owner_role');

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

Чтобы развернуть (то есть активировать) репликацию DDL для заданного набора репликации, выполните:

--Deploy any set_configs with given set_name:
SELECT pgl_ddl_deploy.deploy(set_name);

--Deploy only a single set_config_id:
SELECT pgl_ddl_deploy.deploy(set_config_id);

   command_tag
-----------------
 ALTER FUNCTION
 ALTER SEQUENCE
 ALTER TABLE
 ALTER TYPE
 ALTER VIEW
 CREATE FUNCTION
 CREATE SCHEMA
 CREATE SEQUENCE
 CREATE TABLE
 CREATE TABLE AS
 CREATE TYPE
 CREATE VIEW
 DROP FUNCTION
 DROP SCHEMA
 DROP SEQUENCE
 DROP TABLE
 DROP TYPE
 DROP VIEW
 SELECT INTO

Репликация DDL может быть отключена/включена (это отключит/включит триггеры событий):

--By set_name
SELECT pgl_ddl_deploy.disable(set_name);
SELECT pgl_ddl_deploy.enable(set_name);

--By set_config_id
SELECT pgl_ddl_deploy.disable(set_name);
SELECT pgl_ddl_deploy.enable(set_name);

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

SELECT pgl_ddl_deploy.undeploy(set_name);
SELECT pgl_ddl_deploy.undeploy(set_config_id);

Если вы хотите изменить конфигурацию в set_configs, вы можете повторно развернуть, снова запустив pgl_ddl_deploy.deploy на указанном set_name. В настоящее время нет принудительного выполнения/предупреждения, если вы изменили конфигурацию, но не развернули, но добавить такую функцию должно быть легко.

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

SET SESSION_REPLICATION_ROLE TO REPLICA;

-- I don't care to send this to subscribers (note that you can also exclude statements
-- like this by using exclude_alter_table_subcommands)
ALTER TABLE foo SET (autovacuum_vacuum_threshold = 1000);

RESET SESSION_REPLICATION_ROLE;

Эта структура будет регистрировать все изменения DDL, которые пытаются быть распространены. Она также щедра в разрешении процедур репликации DDL терпеть неудачу, чтобы не препятствовать развертыванию приложений DDL. Исключение никогда не будет разрешено блокировать оператор DDL. Максимум, что произойдет, это будет поднят уровень журнала WARNING. Эта функция основана на предположении, что мы не хотим, чтобы проблемы с репликацией когда-либо блокировали функциональность приложения, ориентированного на клиента.

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

Есть поля resolved в таблицах unhandled и exceptions, которые могут быть помечены так, чтобы мониторинг показывал только новые проблемы на основе этой таблицы, используя функции:

Один оператор DDL SQL, который изменяет как реплицированные, так и нереплицированные таблицы, не может быть поддержан. Например, если у меня есть include_schema_regex, который включает только регулярное выражение '^replicated.*', это не поддерживается:

DROP TABLE replicated.foo, notreplicated.bar;

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

ALTER TABLE replicated.foo ADD COLUMN foo_id INT REFERENCES unreplicated.foo (id);

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

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

В этом случае оператор DDL может завершиться неудачей на подписчике. Чтобы решить эту проблему, см. Решение проблем с неудачными DDL на подписчиках.

CREATE TABLE AS и SELECT INTO не поддерживаются для репликации DDL из-за ограничений на транзакционную согласованность. То есть, если таблица создается из набора данных на стороне поставщика, выполнение того же SQL на стороне подписчика никоим образом не гарантирует согласованность данных. Например:

CREATE TABLE foo AS
SELECT field_1, field_2, now() AS refreshed_at
FROM table_1;

Не только возможно, что table_1 даже не существует на подписчике, даже если она существует, она может быть не полностью актуальной с провайдером, в этом случае данные, созданные в таблице на подписчике, не будут совпадать. Хуже того, функция now() практически гарантированно будет отличаться на подписчике.

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

CREATE TABLE foo (field_1 INT PRIMARY KEY, field_2 TEXT, refreshed_at TIMESTAMPTZ);
INSERT INTO foo (field_1, field_2, refreshed_at)
SELECT field_1, field_2, now() AS refreshed_at
FROM table_1;

Вышеперечисленное полностью поддерживается этой системой, учитывая некоторые крайние случаи с многострочными операторами. CREATE TABLE будет автоматически реплицирован этой системой, и таблица будет добавлена в репликацию, так как у нее есть первичный ключ. Затем INSERT будет реплицирован обычной логической репликацией.

ПРИМЕЧАНИЕ: временные таблицы не затрагиваются этим ограничением, так как временные объекты всегда исключаются из репликации DDL.

Чтобы устранить их, см. Устранение необработанных DDL.

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

Сложности и ограничения возникают, когда клиент отправляет все SQL-запросы в виде одной строки в Postgres. Предположим, следующие SQL-запросы:

CREATE TABLE foo (id serial primary key, bla text);
INSERT INTO foo (bla) VALUES ('hello world');

Если бы это было в файле, который я вызвал через psql, он бы выполнялся как две отдельные строки SQL-команд.

Однако, если в python или в ActiveRecord на ruby я создаю одну строку, как указано выше, и выполняю её, то она будет отправлена в Postgres как одна строка SQL-команды. В таком случае эта структура осознает, что выполняется многооператорное выражение, используя парсер Postgres для получения тегов команд полного SQL выражения. У вас есть небольшая свобода здесь с опцией allow_multi_statements:

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

Чтобы устранить их, см. Устранение необработанных DDL.

Набор регрессионных тестов в папке sql содержит примеры нескольких из этих случаев.

Таким образом, ограничения на многооператорный SQL в значительной степени зависят от того, как ваш клиент отправляет свои сообщения в SQL в Postgres, и, аналогично, как ваши разработчики склонны писать SQL. Хорошая новость заключается в том, что должно быть намного проще обучить разработчиков логически разделять SQL в их миграциях, в отличие от гораздо большего объема работы, которую нам нужно выполнять, когда у нас нет какого-либо прозрачного репликации DDL.

Эти ограничения, очевидно, должны быть взвешены против стоимости полного отказа от использования подобного фреймворка в вашей среде.

Таблицы unhandled и журналы WARNING предназначены для использования с мониторингом для создания оповещений о необходимости ручного вмешательства при изменениях DDL.

Единственное известное ограничение конфигурации для нативной логической репликации DDL заключается в том, что только публикация, включающая репликацию вставок, может поддерживать репликацию DDL. Это связано с тем, как работает механизм очереди DDL. По умолчанию CREATE PUBLICATION в postgres включает публикацию вставок. Это также можно настроить специально с помощью WITH (publish = 'insert'). Для получения более подробной информации см. https://www.postgresql.org/docs/current/sql-createpublication.html.

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

Если вы используете queue_subscriber_failures, любые ошибки DDL для вашей данной конфигурации будут зарегистрированы как неудачные в pgl_ddl_deploy.subscriber_logs. Вы можете решить проблемы и попытаться повторно развернуть, используя функции:

--Retry all failed, in transactional order
SELECT pgl_ddl_deploy.retry_all_subscriber_logs();

--Retry only a single failed log
SELECT pgl_ddl_deploy.retry_subscriber_log(subscriber_log_id);

Когда вы повторно просматриваете журналы подписчика, создаются новые строки для повторных попыток, таким образом строки с succeeded = f все еще остаются.

Запросы, подобные этому, полезны:

SELECT id,
  LEFT(ddl_sql, 30) AS sql,
  origin_subscriber_log_id,
  next_subscriber_log_id,
  succeeded
FROM pgl_ddl_deploy.subscriber_logs
WHERE NOT succeeded;

Затем, чтобы проверить повторную попытку:

WITH old AS (
SELECT id,
  LEFT(ddl_sql, 30) AS sql,
  origin_subscriber_log_id,
  next_subscriber_log_id,
  succeeded
FROM pgl_ddl_deploy.subscriber_logs
WHERE NOT succeeded)

SELECT id,
  LEFT(ddl_sql, 30) AS sql,
  origin_subscriber_log_id,
  next_subscriber_log_id,
  succeeded
FROM pgl_ddl_deploy.subscriber_logs
WHERE id IN (SELECT next_subscriber_log_id FROM old);

ALTER TABLE pgl_ddl_deploy.queue DISABLE TRIGGER execute_queued_ddl;

ALTER TABLE pgl_ddl_deploy.queue ENABLE REPLICA TRIGGER execute_queued_ddl;

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

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

Следующее относится только к нативной логической репликации.

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

Отключите триггер на таблице очереди DDL, чтобы игнорировать всю репликацию DDL на подписчике:

ALTER TABLE pgl_ddl_deploy.queue DISABLE TRIGGER execute_queued_ddl;

Сделайте все необходимое для ручных исправлений. Если вы подозреваете ошибку, пожалуйста, сообщите об этом. Когда вы закончите, ВЫ ДОЛЖНЫ ВКЛЮЧИТЬ ТРИГГЕР ТОЛЬКО В РЕЖИМЕ РЕПЛИКАЦИИ:

ALTER TABLE pgl_ddl_deploy.queue ENABLE REPLICA TRIGGER execute_queued_ddl;