Администрирование базы данных потребителя посредством механизма публикации в PostgreSQL#

GraDely использует механизмы Platform V Pangolin DB, посредством которого осуществляется администрирование БД потребителей репликации. Модуль захвата берет данные о настройках репликации из БД.

Выбор таблиц для репликации с использованием SQL-команд управления публикацией#

  1. Зайдите в клиентский терминал psql, в БД источника репликации пользователем с правами на создание публикации.

  2. Создайте публикацию: CREATE PUBLICATION {имя_публикации} FOR ALL TABLES, чтобы реплицировать все таблицы, или CREATE PUBLICATION {имя_публикации} FOR TABLE {имя_таблицы1}, {имя_таблицы2}для репликации отдельных таблиц.

    Команда CREATE PUBLICATION работает только с DML операциями (подробнее CREATE PUBLICATION). Допустимые операции: INSERT, UPDATE, DELETE. По умолчанию реплицируются все перечисленные типы операций.

    Имя публикации должно отличаться от названия любой существующей публикации в текущей базе данных.

  3. Пропишите в клиентском терминале psql, в БД источника REPLICA IDENTITY FULL, если нужно реплицировать таблицу без первичного ключа.

    Выбрать таблицы для репликации также нужно в визуальном редакторе маппинга (подробнее в «Руководстве пользователя интерфейса консоли управления», разделе «Настройка маппинга»).

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

  4. Обновите публикацию для репликации конкретных таблиц после обновления схемы, в которую входят выбранные таблицы. Для этого создайте публикацию: ALTER PUBLICATION {имя_публикации} ADD TABLE {имя_таблицы1}, {имя_таблицы2}; или удалите публикацию командой DROP PUBLICATION {имя_публикации}и пропишите команду CREATE PUBLICATION для этих таблиц заново.

    При добавлении новой таблицы в маппинг, так же добавьте эту таблицу в публикацию командой ALTER PUBLICATION.

Настройка синхронной репликации в PostgreSQL#

Рекомендуется настроить синхронную репликацию через DropApp, как описано в разделе «Администрирование через DropApp».

  1. Создайте на сервере-источнике публикацию для всех таблиц и слот репликации:

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

    CREATE PUBLICATION {имя_публикации} FOR ALL TABLES`;
SELECT * FROM pg_create_logical_replication_slot('{имя слота}', 'pgoutput');

  2. Измените настройки в файле postgresql.auto.conf на сервере-источнике:

    alter system set synchronous_commit = remote_apply;
alter system set synchronous_standby_names = '*';
select pg_reload_conf();

  3. Запустите репликацию в консоли веб-интерфейса (UI) GraDeLy.

  4. Для проверки режима репликации на стороне БД-источника выполните запрос: SELECT usename , sync_state FROM pg_stat_replication;.

Для переключения в асинхронный режим измените настройки в файле postgresql.auto.conf на сервере-источнике:

alter system set synchronous_commit = on;
alter system set synchronous_standby_names = '';
select pg_reload_conf();

Настройка многопоточной репликации#

Для наибольшей скорости записи в базу данных на стороне воркера применителя удалите ограничения (constraints), первичные (primary key) и внешние ключи (foreign key).

Если нужно сохранить ограничения, повысьте скорость записи в БД, установив отложенные ограничения (deferred constraints). Это обеспечит небольшой прирост скорости записи в БД приемника, примерно 3%-5%.

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

alter table ${table_name} alter constraint ${constraint_name} DEFERRABLE INITIALLY DEFERRED;

Или при создании таблицы объявите внешние ключи как DEFERRABLE INITIALLY DEFERRED, если на таблицу нет ссылок из других таблиц по внешним ключам:

CREATE TABLE IF NOT EXISTS source.review
(
id bigint NOT NULL,
check_in_date date,
details character varying(5000) COLLATE pg_catalog."default",
idx integer,
rating integer,
title character varying(255) COLLATE pg_catalog."default",
trip_type integer,
hotel_id bigint,
CONSTRAINT review_pkey PRIMARY KEY (id) deferrable initially deferred,
CONSTRAINT review_hotel_id_fkey FOREIGN KEY (hotel_id)
    REFERENCES source.hotel (id)
    DEFERRABLE INITIALLY DEFERRED
);

Чтобы поменять все внешние ключи в указанной схеме на отложенные, пропишите в клиентском терминале psql, в БД применителя функцию:

CREATE OR REPLACE FUNCTION change_constraints_deferrable(
p_schema_name character varying,
p_def boolean)
RETURNS setof text
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
v_table_name VARCHAR;
v_constraint_name VARCHAR;
BEGIN
FOR v_table_name IN
    SELECT c.relname FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = p_schema_name AND c.relkind = 'r'
LOOP
        FOR v_constraint_name IN
        SELECT conname FROM pg_constraint
        WHERE contype = 'f'
        AND condeferred != p_def
        AND conrelid = (SELECT c.oid
                FROM pg_class c
                JOIN pg_namespace nsp     ON nsp.oid = C.RELNAMESPACE
                WHERE c.relname = v_table_name and nsp.nspname = p_schema_name )
    LOOP
        EXECUTE 'ALTER TABLE ' || p_schema_name || '.' || v_table_name || ' ALTER CONSTRAINT ' || v_constraint_name || case when p_def then ' DEFERRABLE INITIALLY DEFERRED' else '' end;
        RETURN NEXT v_table_name || '.'|| v_constraint_name;
    END LOOP;
END LOOP;
END;
$BODY$;

Для вызова функции, введите команду SELECT:

select * from change_constraints_deferrable(${schema_name}, true)

После установки отложенных ограничений, включите многопоточное применение. Многопоточное применение увеличит скорость записи в БД в 2-3 раза.

Чтобы включить многопоточное применение, добавьте параметр "apply.thread.count": {число_потоков}, указав число потоков, которое вы хотите запустить, в интерфейсе консоли управления, во вкладке Соединения, окне Редактирование свойств соединения Target, поле Опции:

Многопоточное применение

Параметры многопоточной репликации#

  • apply.thread.count — количество потоков репликации (default = 1);

  • max.pool.size — количество соединений к БД приемнику в пуле, рекомендуется установить как apply.thread.count + 2 (default = 3);

  • transaction.size — количество векторов изменений в транзакции (default = 500);

  • db.linger.ms — количество миллисекунд на формирование пачки векторов изменений в транзакции (default = 1000).

Если время на сбор транзакции db.linger.ms меньше значения по умолчанию, то количество векторов изменений в одной транзакции transaction.size может быть меньше, чем выставлено в параметре.

Рекомендуемые значения параметров многопоточной репликации#

   {
   "max.pool.size": 20,
   "apply.thread.count": 18,
   "transaction.size": 100, 
   "db.linger.ms": 1000
   }

Настройка двунаправленной репликации#

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

Для избежания зацикливания транзакций к каждой транзакции добавляется атрибут origin, указывающий на базу-источник транзакции. Этот атрибут хранится в WAL-журнале и при чтении журнала позволяет выбирать транзакции только из нужных источников.

Чтобы настроить двунаправленную репликацию:

  1. Предоставьте модулю Applier права на функции для присвоения атрибута origin.

  2. Настройте фильтрацию транзакций.

  3. Предоставьте пользователям права на публикацию и чтение слотов.

Чтобы предоставить модулю Applier права на функции#

Чтобы модуль Applier мог присваивать метки транзакциям сессии, нужно предоставить ему права на функции:

SELECT pg_replication_origin_create(origin_name);
SELECT pg_replication_origin_session_setup(origin_name);
SELECT pg_replication_origin_session_reset();
SELECT pg_replication_origin_drop(origin_name);

С помощью них при запуске репликации модуль Applier инициализирует пул соединений (java connection pool) с БД и создает для каждого соединения собственный источник функцией SELECT pg_replication_origin_create(origin_name); и присвоит имя всем транзакциям сеанса функцией pg_replication_origin_session_setup().

Для предоставления прав на функции:

Инструкция для администратора PostgreSQL

  1. Подключитесь к базе от имени суперпользователя:

    psql -U postgres -d your_database

  2. Выдайте пользователю, под которым работает модуль Applier, привилегии на функции pg_replication_origin:

    GRANT EXECUTE ON FUNCTION pg_replication_origin_create(text) TO replicator;
GRANT EXECUTE ON FUNCTION pg_replication_origin_session_setup(text) TO replicator;
GRANT EXECUTE ON FUNCTION pg_replication_origin_session_reset() TO replicator;
GRANT EXECUTE ON FUNCTION pg_replication_origin_drop(text) TO replicator;

  3. Убедитесь, что у модуля Applier есть необходимые права:

    SET ROLE replicator;
SELECT pg_replication_origin_session_reset();

Чтобы настроить фильтрацию транзакций#

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

  1. Добавьте в параметрах соединения модуля Capture параметр allowed_origins. Для этого в поле Опции, в окне Редактирование свойств соединения Capture, во вкладке Соединения пропишите "allowed_origins": [{имя_источника_по_шаблону}].

    Для указания имени источника GraDeLy поддерживает шаблоны с джокером «?», который равносилен любому символу.

    Например:

       {
   "allowed_origins": [
    "grdl_????"
    ]
   }

    Теперь каждая транзакция с меткой, соответствующей шаблону (например: grdl_0001, grdl_0007, grdl_0362), будет отправлена в trail для репликации в БД-приемник.

    Модуль Capture захватывает все транзакции:

    • которые приходят из источника, указанного в allowed_origins;

    • у которых атрибут origin пуст.

    Или добавьте в параметрах соединения модуля Capture параметр disаllowed_origins для создания «черного списка» транзакций, которые реплицировать не нужно. Для этого в поле Опции, в окне Редактирование свойств соединения Capture, во вкладке Соединения пропишите "disаllowed_origins": [{имя_источника_по_шаблону}]:

       {
   "disallowed_origins": [
    "grdl_????"
    ]
   }

    Теперь каждая транзакция с меткой, содержащей указанный префикс источника, в trail для дальнейшей репликации в БД-приемник отправляться не будет.

  2. Добавьте в параметрах соединения модуля Applier параметр origin_name. Значение origin_name указывает, откуда были сгенерированы изменения, чтобы затем их можно было фильтровать на принимающей стороне.

    Для этого в поле Опции, в окне Редактирование свойств соединения Applier, во вкладке Соединения пропишите "origin_name": {префикс_источника}:

       {
   "origin_name": "grdl"
   }

Чтобы предоставить права на публикацию#

Для работы пользователей GraDeLy с PostgreSQL нужно разрешение на публикацию логических слотов.

Инструкция для администратора PostgreSQL:

  1. Подключитесь к экземпляру PostgreSQL от имени суперпользователя:

    psql -U postgres -d your_database

  2. Убедитесь, что нужный пользователь существует:

    \du

    Если пользователя нет, создайте его:

    CREATE USER your_user WITH PASSWORD 'your_secure_password';

  3. Предоставьте пользователю роль rds_replication:

    GRANT rds_replication TO your_user;

  4. Убедитесь, что права применены:

    SELECT * FROM pg_roles WHERE rolname = 'your_user';

    Посмотрите, присутствует ли в колонке rolreplication значение true.

Создание и запуск новой реплики#

  1. Подготовьтесь к резервному копированию.

  2. Создайте резервную копию.

  3. Восстановите базу данных.

  4. Запустите репликацию с позиции.

Подготовка к резервному копированию#

  1. Убедитесь, что на базе-источнике параметр wal_level установлен в logical, параметр max_wal_senders >= 2. Для этого:

    1. Проверьте значение wal_level: psql -c show wal_level;

      Если значение wal_level не logical: psql -c alter system set wal_level = logical;

    2. Проверьте значение max_wal_senders: psql -c show max_wal_senders;

      Если значение max_wal_senders < 2: psql -c alter system set max_wal_senders = 10;

    3. После изменения параметров перезапустите сервер: pg_ctl restart.

  2. Запустите модуль Capture на вкладке Графы репликации в пользовательском интерфейсе консоли управления.

Создание резервной копии#

Выполните pg_basebackup на базе-источнике. Для этого:

  1. Проверьте, есть ли разрешение на локальное подключение по протоколу репликации для роли backup_test (можно выбрать любую другую роль для проведения репликации):

    psql -c `SELECT type, database, user_name, address, auth_method FROM pg_hba_file_rules() WHERE 'replication' = ANY(database) AND user_name = '{backup_test}';`

    Если разрешение есть, отобразится:

    Создание резервной копии, basebackup

    Если разрешения для роли нет, добавьте в файл pg_hba.conf: echo "host replication backup_test {адрес_базы_данных} trust" >> /pgdata/06/data/pg_hba.conf.

  2. Убедитесь, что у роли backup_test есть атрибут REPLICATION, чтобы утилита pg_basebackup могла подключиться к серверу под этой ролью:

    psql -c `\du backup_test`

    Если атрибут есть, отобразится:

    Создание резервной копии, basebackup

    Если атрибута у роли нет, добавьте его: psql -c ALTER ROLE backup_test REPLICATION.

  3. Выполните pg_basebackup -h localhost -v -U backup_test -D /home/postgres/backup.

    Укажите в параметре -D путь к директории созданной резервной копии, если у базы-источника один tablespace.

    Укажите в параметре -D путь к директории для основного tablespace, если tablespace несколько. Для остальных tablespaces укажите маппинг:

    pg_basebackup -h localhost -v -U backup_test -D /home/postgres/backup/data --tablespace mapping=/pgdata/06/grdl_ts_data=/home/postgres/backup/grdl_ts_data --tablespace-mapping=/pgdata/06/grdl_ts_idx=/home/postgres/backup/grdl_ts_idx

    Маппинг можно не указывать, а выгрузить tablespaces в .tar архивах:

    pg_basebackup --format=tar -h localhost -v -U backup_test -D /home/postgres/backup.

  4. Переместите резервную копию на сервер базы-приемника.

    1. Выполните pg_basebackup на базе-приемнике.

      Убедитесь, что на базе-источнике разрешен доступ к базе-приемнику. Для этого проверьте на базе-источнике файл pg_hba.conf:

      psql -c `SELECT type, database, user_name, address, auth_method FROM pg_hba_file_rules() WHERE 'replication' = ANY(database) AND user_name = '{backup_test}';

      Если доступ есть, отобразится:

      Создание резервной копии, basebackup

      Если доступа нет, добавьте в файл pg_hba.conf адрес базы-приемника: echo "host replication backup_test {адрес_базы-приемника} trust" >> /pgdata/06/data/pg_hba.conf.

    2. Проверьте атрибуты роли backup_test аналогично пунктам 1,2.

    3. Выполните на базе-приемнике: pg_basebackup {адрес_базы-источника} -v -U backup_test -D /home/postgres/backup.

      Укажите в параметре -D путь к директории для основного tablespace, если tablespace несколько.

Восстановление базы данных#

  1. Убедитесь, что сервер приемника остановлен. Для этого выполните скрипт в консоли сервера базы-приемника: pg_ctl status.

    Если сервер остановлен, отобразится: pg_ctl:no server running.

    Остановите сервер, если он запущен: pg_ctl stop.

  2. Удалите файлы данных сервера приемника: sudo rm -rf /pgdata/06/.

    /pgdata/06/ — путь к директории с файлами данных базы-приемника.

  3. Скопируйте содержимое директории backup: sudo cp -r /home/postgres/backup/. /pgdata/06/.

    /home/postgres/backup/ — путь к директории, в которой находится резервная копия.

  4. Выдайте пользователю права на директорию: sudo chown -R postgres /pgdata/06.

  5. Запустите сервер: pg_ctl start.

Запуск репликации с позиции#

  1. Добавьте базу в пользовательский интерфейс консоли управления. Для этого:

    1. Создайте соединение во вкладке Соединения.

    2. Укажите в параметрах соединения в открывшемся окне Создание соединения:

      Создание соединения

    3. Нажмите Target на схеме графа во вкладке Графы репликации и значок Редактирование Target под графом:

      Редактирование Target

    4. Выберите новую базу-приемник в открывшемся окне Редактирование свойств источника/потребителя данных:

      Редактирование Target

  2. Выполните в консоли на базе-источнике sudo cat /home/postgres/backup/data/backup_manifest | jq -r '."WAL-Ranges"[0]."End-LSN"', чтобы узнать lsn для старта репликации.

  3. Преобразуйте полученный lsn в десятеричный формат: psql -c "SELECT '9/1C001730'::pg_lsn - '0/0'::pg_lsn as lsn;".

  4. Узнайте source_id на базе-источнике:

    psql -c "select system_identifier & 4294967295 # ('x' || lpad(rpad(substring(lpad(md5(case current_setting('cluster_name') when '' then 'PANGOLIN' else current_setting('cluster_name') end),32) from 25+2),16-2,'0'),16,'0'))::bit(64)::bigint # ('x'||rpad(substring(lpad(md5('slot_name'), 32, '0') from 29 for 2), 16, '0'))::bit(64)::bigint from pg_control_system();"

  5. Нажмите модуль Applier на схеме графа на вкладке Графы репликации и значок Графы репликации, Перезапуск графа в развернувшемся меню под модулем:

    Графы репликации, Перезапуск графа

  6. Введите lsn в поле Позиция, выберите DB как тип позиции и укажите ID базы-приемника в поле Source ID в открывшемся окне Старт модуля с заданной позиции:

Графы репликации, Перезапуск графа

  1. Нажмите Графы репликации, Перезапуск графа.